Oracle 建立使用者、角色、授權、表空間完整 SQL 範本

Oracle 權限體系分為**系統權限**（如 CREATE TABLE、DROP ANY TABLE）與**物件權限**（如 SELECT、INSERT 於特定表）。角色 是權限的集合，簡化授權管理。本文提供從建立表空間、使用者、角色、授權到刪除清理的完整標準化 SQL 範本，適合 DBA 直接套用或納入自動化部署腳本。

一、建立表空間（先於使用者）

CREATE TABLESPACE app_data
DATAFILE '/u01/oradata/ORCL/app_data01.dbf' SIZE 1G AUTOEXTEND ON NEXT 100M MAXSIZE 10G
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
SEGMENT SPACE MANAGEMENT AUTO;


CREATE TEMPORARY TABLESPACE app_temp
TEMPFILE '/u01/oradata/ORCL/app_temp01.dbf' SIZE 512M AUTOEXTEND ON NEXT 50M MAXSIZE 5G
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;

二、建立使用者

CREATE USER app_user IDENTIFIED BY "Str0ngP@ssw0rd"
DEFAULT TABLESPACE app_data
TEMPORARY TABLESPACE app_temp
QUOTA UNLIMITED ON app_data
ACCOUNT UNLOCK
PASSWORD EXPIRE;  -- 首次登入強制修改密碼

參數說明：

• IDENTIFIED BY：密碼驗證（亦支援 EXTERNALLY OS 驗證、GLOBALLY 目錄服務）
• DEFAULT TABLESPACE：物件預建位置，避免建在 SYSTEM/USERS
• QUOTA UNLIMITED：允許在表空間無限制配置段，否則受限於 DBA_TS_QUOTAS
• PROFILE：可指定密碼策略、資源限制 Profile

三、修改密碼與帳號狀態

ALTER USER app_user IDENTIFIED BY "NewStr0ngP@ssw0rd";

PASSWORD app_user


ALTER USER app_user ACCOUNT UNLOCK;
ALTER USER app_user ACCOUNT LOCK;

四、三大預設角色（向下相容）

五、授權與撤銷標準語法

GRANT CREATE SESSION, CREATE TABLE, CREATE SEQUENCE TO app_user;
GRANT CREATE SESSION, CREATE TABLE TO dev_role;


GRANT SELECT, INSERT, UPDATE, DELETE ON app_schema.orders TO app_user;
GRANT SELECT ON app_schema.customers TO report_role;


GRANT CREATE TABLE TO app_user WITH ADMIN OPTION;   -- 系統權限可再授予他人
GRANT SELECT ON app_schema.orders TO app_user WITH GRANT OPTION;  -- 物件權限可再授予


REVOKE CREATE TABLE FROM app_user;
REVOKE SELECT ON app_schema.orders FROM app_user;

六、自訂角色管理

CREATE ROLE dev_role;
CREATE ROLE read_only_role;


GRANT CREATE SESSION, CREATE TABLE, CREATE VIEW, CREATE PROCEDURE TO dev_role;
GRANT SELECT ON app_schema.customers TO read_only_role;
GRANT SELECT ON app_schema.orders TO read_only_role;


GRANT dev_role TO app_user;
GRANT read_only_role TO report_user;


DROP ROLE dev_role;

七、刪除使用者與物件

DROP USER app_user;


DROP USER app_user CASCADE;

八、刪除表釋放空間最佳實務

⚠️ 切勿使用 DELETE FROM table 或 DROP TABLE 後期望空間自動釋放

• DELETE：產生大量 Undo/Redo、High Water Mark (HWM) 不下降、表空間不釋放
• DROP TABLE：段釋放但進入 Recyclebin（USER_RECYCLEBIN），空間仍佔用直到 PURGE
• 正確做法： TRUNCATE TABLE table_name; — 瞬間釋放段空間、重置 HWM、不產生 Undo、不可 Rollback
• 若需保留結構但清空資料並釋放空間：TRUNCATE TABLE table_name DROP STORAGE;（預設即為 DROP STORAGE）

九、完整部署範本（可直接執行）

-- 1. Tablespace
CREATE TABLESPACE app_data DATAFILE '+DATA' SIZE 5G AUTOEXTEND ON NEXT 500M MAXSIZE 50G
EXTENT MANAGEMENT LOCAL AUTOALLOCATE SEGMENT SPACE MANAGEMENT AUTO;
CREATE TEMPORARY TABLESPACE app_temp TEMPFILE '+DATA' SIZE 1G AUTOEXTEND ON NEXT 100M MAXSIZE 10G
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;

-- 2. User
CREATE USER app_user IDENTIFIED BY "Ch@ngeMe123"
DEFAULT TABLESPACE app_data TEMPORARY TABLESPACE app_temp
QUOTA UNLIMITED ON app_data ACCOUNT UNLOCK PASSWORD EXPIRE;

-- 3. Roles
CREATE ROLE app_rw;  -- 讀寫角色
CREATE ROLE app_ro;  -- 唯讀角色

-- 4. Grants
GRANT CREATE SESSION, CREATE TABLE, CREATE SEQUENCE, CREATE VIEW, CREATE PROCEDURE TO app_rw;
GRANT CREATE SESSION TO app_ro;
GRANT SELECT ON app_schema.customers TO app_ro;
GRANT SELECT, INSERT, UPDATE, DELETE ON app_schema.orders TO app_rw;

-- 5. Assign
GRANT app_rw TO app_user;
-- GRANT app_ro TO report_user;

作者：技術總結 | 遵循最小權限原則、表空間隔離、角色解耦授權，是 Oracle 安全與可維護性的基石。