前言
Oracle 中「用戶」即「Schema」，擁有資料庫物件的容器。掌握用戶生命週期管理（建立、授權、表空間指派、密碼策略、鎖定/解鎖、刪除）與權限體系（系統權限、物件權限、角色），是 DBA 與開發的基本功。本文系統整理標準化操作流程與最佳實踐。
一、合法識別符命名規範
- 非保留關鍵字。
- 1~30 字元 (DB Name 最多 8 字元)。
- 首字元必須為字母。
- 僅含字母、數字、
#、$、_ (DB Link 可含 @、.)。
- 不含引號 (雙引號可強制大小寫/特殊字元，但強烈不建議)。
二、用戶建立與基礎授權標準流程
1. 建立用戶
CREATE USER app_user IDENTIFIED BY "ComplexP@ssw0rd"
DEFAULT TABLESPACE app_data
TEMPORARY TABLESPACE temp
QUOTA UNLIMITED ON app_data
QUOTA 100M ON app_index
PROFILE app_profile; -- 密碼/資源限制檔
2. 基礎角色授權 (最小權限原則)
-- 僅授予建立 Session 與基本資源建立權限
GRANT CREATE SESSION, CREATE TABLE, CREATE VIEW, CREATE SEQUENCE, CREATE PROCEDURE TO app_user;
-- 禁止直接授 DBA/RESOURCE/CONNECT (12c+ CONTAINERS 角色變更)
-- 若需 DBA 權限，建立專用管理帳號單獨管理
3. 密碼修改
ALTER USER app_user IDENTIFIED BY "NewComplexP@ssw0rd"
REPLACE "ComplexP@ssw0rd"; -- 非 DBA 自修需提供舊密碼
4. 帳號鎖定/解鎖
ALTER USER app_user ACCOUNT LOCK; -- 鎖定
ALTER USER app_user ACCOUNT UNLOCK; -- 解鎖 (無需重啟服務，舊版可能需重啟)
三、表空間指派與調整
查詢現有表空間
SELECT tablespace_name, contents, extent_management, allocation_type
FROM dba_tablespaces
ORDER BY tablespace_name;
修改用戶預設/臨時表空間
ALTER USER app_user
DEFAULT TABLESPACE app_data
TEMPORARY TABLESPACE temp;
調整配額
ALTER USER app_user QUOTA 500M ON app_data;
ALTER USER app_user QUOTA UNLIMITED ON app_index;
四、用戶刪除
-- 無物件
DROP USER app_user;
-- 有物件 (含表、索引、序列等)
DROP USER app_user CASCADE;
五、Schema 概念釐清
- Schema = 資料庫物件集合 (表、視圖、索引、Package、Type、Trigger、Java Class 等)。
- Schema Name = 擁有者用戶名。
- 所有物件皆屬於某 Schema，甚至 Data Dictionary 屬於
SYS Schema。
- User ≠ Schema：User 是認證主體 (名稱+密碼)；Schema 是物件命名空間。12c+ 可建立
NO AUTHENTICATION 用戶僅作 Schema 容器。
六、權限體系深度解析
| 類別 | 定義 | 範例 | 授權語法 |
|---|
| 系統權限 | 允許執行特定資料庫操作，不針對特定物件 | CREATE SESSION, CREATE TABLE, DROP ANY TABLE, ALTER SYSTEM | GRANT create session TO user [WITH ADMIN OPTION]; |
| 物件權限 | 允許操作特定 Schema 物件 | SELECT ON scott.emp, EXECUTE ON pkg_api | GRANT select ON scott.emp TO user [WITH GRANT OPTION]; |
| 角色 | 權限集合，簡化管理 | DBA, RESOURCE, CONNECT, 自訂角色 | CREATE ROLE app_role; GRANT select ON t TO app_role; GRANT app_role TO user; |
關鍵系統權限查詢
SELECT DISTINCT privilege FROM dba_sys_privs ORDER BY privilege;
撤銷權限
REVOKE create table FROM app_user;
REVOKE select ON scott.emp FROM app_user;
七、建表語法與範例速查
標準建表
CREATE TABLE scott.cd_collection (
album_title VARCHAR2(100),
artist VARCHAR2(100),
country VARCHAR2(25),
release_date DATE,
label VARCHAR2(25),
CONSTRAINT pk_cd PRIMARY KEY (album_title, artist)
);
跨 Schema 參考 (Foreign Key)
CREATE TABLE seagal.songs (
song_title VARCHAR2(100),
composer VARCHAR2(100),
length_sec NUMBER,
track_no NUMBER,
artist VARCHAR2(100),
album_title VARCHAR2(100),
CONSTRAINT fk_songs_cd FOREIGN KEY (artist, album_title)
REFERENCES seagal.cd_collection (artist, album_title),
CONSTRAINT pk_songs PRIMARY KEY (song_title, artist, album_title)
);
CTAS (Create Table As Select) — 僅複製結構與資料，不複製約束/索引/Trigger
CREATE TABLE emp_copy AS SELECT * FROM scott.emp WHERE 1=0; -- 僅結構
CREATE TABLE emp_copy AS SELECT * FROM scott.emp; -- 結構+資料
八、資料字典核心視圖
| 視圖前綴 | 可見範圍 | 典型用途 |
|---|
| USER_* | 當前用戶擁有的物件 | 開發者自查 |
| ALL_* | 當前用戶有權限的物件 | 應用查詢元資料 |
| DBA_* | 全資料庫物件 (需 DBA 權限) | DBA 管理、審計 |
| CDB_* | CDB 根/所有 PDB (12c+) | 多租戶管理 |
常用查詢範例
-- 當前用戶所有表
SELECT table_name, tablespace_name FROM user_tables;
-- DBA 檢視指定 Schema 表分佈
SELECT owner, table_name, tablespace_name
FROM dba_tables
WHERE owner IN ('SCOTT','HR')
ORDER BY owner, tablespace_name, table_name;
九、最佳實踐清單
- 專用表空間：業務表/索引/LOB 分離表空間，避免 SYSTEM 表空間膨脹。
- Profile 管控：建立
CREATE PROFILE app_profile LIMIT PASSWORD_LIFE_TIME 90 FAILED_LOGIN_ATTEMPTS 5 ... 統一密碼策略。
- 角色分層：
ROLE_READ / ROLE_WRITE / ROLE_ADMIN，用戶僅授角色。
- 審計關鍵操作：
AUDIT CREATE TABLE, DROP USER, ALTER SYSTEM BY ACCESS;。
- 定期清理：鎖定/過期帳號定期
DROP USER ... CASCADE 釋放資源。
