您现在的位置：程式碼之家 >> 數據庫知識 > >> Oracle 資料庫 >> Oracle 資料庫相關知識

Oracle 命令列建立與刪除使用者完整腳本：含權限、表空間、設定檔

編輯：Oracle 資料庫相關知識

刪除使用者腳本


-- 以 SYSDBA 連線
CONNECT / AS SYSDBA;

-- 強制關閉資料庫 (清除所有連線)
SHUTDOWN ABORT;
STARTUP;

-- 級聯刪除使用者及其所有物件
DROP USER user1 CASCADE;

EXIT;

注意：CASCADE 會刪除使用者擁有的所有物件 (表、視圖、序列、程序等)、**不可逆**，執行前務必確認備份。

建立使用者完整腳本


-- 1. 建立使用者
CREATE USER user1
  IDENTIFIED BY VALUES 'fa091872a2cc669c'  -- 加密密碼值 (從 DBA_USERS.PASSWORD 取得)
  DEFAULT TABLESPACE user1                -- 預設永久表空間
  TEMPORARY TABLESPACE temp               -- 暫存表空間
  PROFILE default                         -- 設定檔 (密碼策略、資源限制)
  ACCOUNT UNLOCK;                         -- 解鎖帳號

-- 2. 授予角色 (含 ADMIN OPTION 可再授予他人)
GRANT RECOVERY_CATALOG_OWNER TO user1 WITH ADMIN OPTION;
GRANT RESOURCE TO user1 WITH ADMIN OPTION;
GRANT DBA TO user1 WITH ADMIN OPTION;
GRANT CONNECT TO user1 WITH ADMIN OPTION;

-- 3. 設定預設角色 (登入自動啟用)
ALTER USER user1 DEFAULT ROLE ALL;

-- 4. 授予系統權限
GRANT SELECT ANY DICTIONARY TO user1 WITH ADMIN OPTION;
GRANT UNLIMITED TABLESPACE TO user1 WITH ADMIN OPTION;
GRANT ALTER ANY PROCEDURE TO user1 WITH ADMIN OPTION;

關鍵子句說明

子句	說明	範例
`IDENTIFIED BY password`	明文密碼	`IDENTIFIED BY "Str0ngP@ss"`
`IDENTIFIED BY VALUES 'hash'`	從 `DBA_USERS.PASSWORD` 取得的雜湊值，用於遷移/複製帳號	`IDENTIFIED BY VALUES 'F8A3...'`
`DEFAULT TABLESPACE`	建立物件時預設表空間	建議為每個應用建立專屬表空間
`TEMPORARY TABLESPACE`	排序、雜湊、暫存表操作使用	通常共用 `TEMP`
`PROFILE`	密碼生命週期、失敗鎖定、資源限制	`PROFILE app_profile`
`ACCOUNT LOCK/UNLOCK`	帳號鎖定狀態	建立時通常 `UNLOCK`
`WITH ADMIN OPTION`	受權者可再授予/撤銷該權限/角色	慎用，通常僅 DBA 需要
`DEFAULT ROLE ALL`	登入自動啟用所有已授予角色	或指定 `DEFAULT ROLE role1, role2`

常用角色權限對照

角色	包含權限	適用對象
`CONNECT`	`CREATE SESSION`	所有需登入的使用者
`RESOURCE`	建立表、序列、程序、觸發器、類型等	開發人員、應用帳號
`DBA`	所有系統權限、`WITH ADMIN OPTION`	資料庫管理員
`SELECT_CATALOG_ROLE`	查詢資料字典視圖	監控、審計人員
`EXECUTE_CATALOG_ROLE`	執行系統套件 (DBMS_*)	進階開發、DBA
`RECOVERY_CATALOG_OWNER`	擁有 RMAN 復原目錄權限	RMAN Catalog 擁有者
`DATAPUMP_EXP_FULL_DATABASE`	Data Pump 完整匯出	備份帳號
`DATAPUMP_IMP_FULL_DATABASE`	Data Pump 完整匯入	恢復帳號

系統權限精選


-- 空間管理
GRANT UNLIMITED TABLESPACE TO user1;  -- 無配額限制
GRANT CREATE TABLESPACE TO user1;
GRANT ALTER TABLESPACE TO user1;

-- 物件管理
GRANT CREATE ANY TABLE TO user1;
GRANT ALTER ANY TABLE TO user1;
GRANT DROP ANY TABLE TO user1;

-- 程式碼管理
GRANT CREATE ANY PROCEDURE TO user1;
GRANT ALTER ANY PROCEDURE TO user1;
GRANT EXECUTE ANY PROCEDURE TO user1;

-- 使用者管理
GRANT CREATE USER TO user1;
GRANT ALTER USER TO user1;
GRANT DROP USER TO user1;

-- 備份恢復
GRANT SYSBACKUP TO user1;  -- 12c+ 備份管理權限
GRANT SYSDG TO user1;      -- Data Guard 管理
GRANT SYSKM TO user1;      -- 金鑰管理

表空間配額管理 (替代 UNLIMITED TABLESPACE)


-- 給予特定表空間配額
ALTER USER user1 QUOTA 100M ON user_data;
ALTER USER user1 QUOTA 50M ON user_index;
ALTER USER user1 QUOTA UNLIMITED ON user_lob;

-- 查詢配額使用情況
SELECT tablespace_name, bytes/1024/1024 AS used_mb, max_bytes/1024/1024 AS quota_mb
FROM dba_ts_quotas
WHERE username = 'USER1';

設定檔實戰範例


-- 建立應用專用設定檔
CREATE PROFILE app_profile LIMIT
  PASSWORD_LIFE_TIME 90          -- 密碼 90 天過期
  PASSWORD_GRACE_TIME 7          -- 寬限期 7 天
  PASSWORD_REUSE_TIME 365        -- 1 年內不可重複使用
  PASSWORD_REUSE_MAX 10          -- 歷史 10 組不可重複
  FAILED_LOGIN_ATTEMPTS 5        -- 5 次失敗鎖定
  PASSWORD_LOCK_TIME 1           -- 鎖定 1 天
  PASSWORD_VERIFY_FUNCTION verify_fn  -- 複雜度驗證函數
  SESSIONS_PER_USER 10           -- 同帳號最多 10 個 Session
  CPU_PER_SESSION 360000         -- 單 Session CPU 限制 (百分之一秒)
  CONNECT_TIME 480;              -- 連線最長 8 小時

-- 指派給使用者
ALTER USER user1 PROFILE app_profile;

驗證腳本


-- 檢查使用者建立狀態
SELECT username, account_status, default_tablespace, temporary_tablespace, profile
FROM dba_users WHERE username = 'USER1';

-- 檢查角色
SELECT granted_role, admin_option, default_role
FROM dba_role_privs WHERE grantee = 'USER1';

-- 檢查系統權限
SELECT privilege, admin_option
FROM dba_sys_privs WHERE grantee = 'USER1';

-- 檢查配額
SELECT * FROM dba_ts_quotas WHERE username = 'USER1';