Oracle用戶創建與權限授予全流程腳本化實戰：從表空間到角色的標準化交付

標準化交付腳本集

1. 表空間創建（先行）

-- create_tablespaces.sql
CREATE TABLESPACE APP_DATA
  DATAFILE '+DATA/PROD/DATAFILE/app_data_01.dbf' SIZE 1G
  AUTOEXTEND ON NEXT 100M MAXSIZE 100G
  EXTENT MANAGEMENT LOCAL AUTOALLOCATE
  SEGMENT SPACE MANAGEMENT AUTO;

CREATE TABLESPACE APP_INDEX
  DATAFILE '+DATA/PROD/DATAFILE/app_index_01.dbf' SIZE 500M
  AUTOEXTEND ON NEXT 50M MAXSIZE 50G
  EXTENT MANAGEMENT LOCAL AUTOALLOCATE
  SEGMENT SPACE MANAGEMENT AUTO;

CREATE TEMPORARY TABLESPACE APP_TEMP
  TEMPFILE '+DATA/PROD/TEMPFILE/app_temp_01.dbf' SIZE 2G
  AUTOEXTEND ON NEXT 100M MAXSIZE 10G
  EXTENT MANAGEMENT LOCAL UNIFORM SIZE 10M;

2. 用戶創建與基礎授權

-- create_users.sql
CREATE USER APP_OWNER IDENTIFIED BY "AppOwn3r_Str0ngP@ss"
  DEFAULT TABLESPACE APP_DATA
  TEMPORARY TABLESPACE APP_TEMP
  QUOTA UNLIMITED ON APP_DATA
  QUOTA UNLIMITED ON APP_INDEX
  PROFILE APP_PROFILE;

CREATE USER APP_READONLY IDENTIFIED BY "ReadOnly_P@ss"
  DEFAULT TABLESPACE APP_DATA
  TEMPORARY TABLESPACE APP_TEMP
  PROFILE APP_PROFILE;

CREATE USER APP_BATCH IDENTIFIED BY "BatchUs3r_P@ss"
  DEFAULT TABLESPACE APP_DATA
  TEMPORARY TABLESPACE APP_TEMP
  QUOTA UNLIMITED ON APP_DATA
  PROFILE APP_PROFILE;

3. 角色定義與權限聚合

-- create_roles.sql
CREATE ROLE APP_READONLY_ROLE;
CREATE ROLE APP_READWRITE_ROLE;
CREATE ROLE APP_ADMIN_ROLE;

-- 基礎會話權限
GRANT CREATE SESSION TO APP_READONLY_ROLE, APP_READWRITE_ROLE, APP_ADMIN_ROLE;

-- 讀寫角色對象權限（按Schema批量，實際部署時替換SCHEMA_NAME）
GRANT SELECT ON SCHEMA_NAME.TABLE_A TO APP_READONLY_ROLE;
GRANT SELECT, INSERT, UPDATE, DELETE ON SCHEMA_NAME.TABLE_A TO APP_READWRITE_ROLE;
GRANT ALL ON SCHEMA_NAME.TABLE_A TO APP_ADMIN_ROLE;

-- 開發構建權限
GRANT CREATE TABLE, CREATE VIEW, CREATE SEQUENCE, CREATE PROCEDURE, CREATE TRIGGER, CREATE SYNONYM TO APP_READWRITE_ROLE, APP_ADMIN_ROLE;

-- 管理權限
GRANT CREATE USER, DROP USER, ALTER USER, GRANT ANY PRIVILEGE TO APP_ADMIN_ROLE;

4. 角色授予用戶

-- grant_roles.sql
GRANT APP_READONLY_ROLE TO APP_READONLY;
GRANT APP_READWRITE_ROLE TO APP_OWNER, APP_BATCH;
GRANT APP_ADMIN_ROLE TO DBA_ADMIN;  -- 僅極少數運維賬號

-- 設置默認角色（登錄自動啟用）
ALTER USER APP_OWNER DEFAULT ROLE APP_READWRITE_ROLE;
ALTER USER APP_READONLY DEFAULT ROLE APP_READONLY_ROLE;
ALTER USER APP_BATCH DEFAULT ROLE APP_READWRITE_ROLE;

5. 刪除/清理腳本（回滾用）

-- cleanup.sql
DROP USER APP_OWNER CASCADE;
DROP USER APP_READONLY CASCADE;
DROP USER APP_BATCH CASCADE;
DROP ROLE APP_READONLY_ROLE;
DROP ROLE APP_READWRITE_ROLE;
DROP ROLE APP_ADMIN_ROLE;
DROP TABLESPACE APP_DATA INCLUDING CONTENTS AND DATAFILES;
DROP TABLESPACE APP_INDEX INCLUDING CONTENTS AND DATAFILES;
DROP TABLESPACE APP_TEMP INCLUDING CONTENTS AND DATAFILES;

6. 常用維護片段

-- 增加數據文件
ALTER TABLESPACE APP_DATA ADD DATAFILE
  '+DATA/PROD/DATAFILE/app_data_02.dbf' SIZE 1G AUTOEXTEND ON NEXT 100M MAXSIZE 100G;

-- 修改自動擴展
ALTER DATABASE DATAFILE '+DATA/PROD/DATAFILE/app_data_01.dbf' AUTOEXTEND OFF;

-- 修改密碼
ALTER USER APP_OWNER IDENTIFIED BY "New_AppOwn3r_P@ss";

-- 授予任意表查詢（審計/報表賬號）
GRANT SELECT ANY TABLE TO AUDIT_USER;
GRANT SELECT ANY DICTIONARY TO AUDIT_USER;

-- 撤銷權限
REVOKE CREATE PROCEDURE FROM APP_OWNER;

執行順序與驗證

1. @create_tablespaces.sql → 查詢DBA_TABLESPACES確認
2. @create_users.sql → SELECT * FROM DBA_USERS WHERE USERNAME LIKE 'APP_%';
3. @create_roles.sql → SELECT * FROM DBA_ROLES WHERE ROLE LIKE 'APP_%';
4. @grant_roles.sql → SELECT * FROM DBA_ROLE_PRIVS WHERE GRANTEE LIKE 'APP_%';
5. 業務部署@deploy_schema.sql（以APP_OWNER登錄）
6. 驗證@verify_deployment.sql（對象計數、約束、索引、統計信息）

版本控制與CI/CD集成

# Git 結構
oracle-ddl/
├── 01_tablespaces/
│   └── create_tablespaces.sql
├── 02_users/
│   ├── create_users.sql
│   └── grant_roles.sql
├── 03_roles/
│   └── create_roles.sql
├── 04_schema/
│   └── deploy_schema.sql
├── 05_verification/
│   └── verify_deployment.sql
└── 99_rollback/
    └── cleanup.sql

# Jenkins/GitLab CI 階段
stages:
  - validate_sql_syntax
  - deploy_tablespaces
  - deploy_users_roles
  - deploy_schema
  - verify
  - notify

總結

用戶權限交付標準化 = 表空間先行 → 用戶創建 → 角色聚合權限 → 角色授予用戶 → Schema部署 → 驗證簽收 → 版本控制。將上述腳本模板化（Jinja2/Shell參數化），納入GitOps流水線，實現「合併請求 → 自動部署測試庫 → 審批 → 生產部署」全流程可追溯、可回滾、可審計。