Oracle 資料庫建立表空間、用戶授權與權限查詢完整指南

在 Oracle 資料庫管理中，建立表空間、創建用戶並授予適當權限是基礎且關鍵的操作。本文將系統整理建立暫存表空間、永久表空間、創建用戶指定表空間、授予系統權限與角色，以及查詢用戶權限的完整語法與實務範例，供 DBA 參考操作。

一、建立暫存表空間

暫存表空間主要用於排序、哈希連結等臨時操作，建議啟用自動擴展：

CREATE TEMPORARY TABLESPACE test_temp
TEMPFILE 'C:\oracle\product\10.1.0\oradata\orcl\test_temp01.dbf'
SIZE 32M
AUTOEXTEND ON
NEXT 32M MAXSIZE 2048M
EXTENT MANAGEMENT LOCAL;

二、建立永久表空間

永久表空間存放實際業務數據，同樣建議採用本地範圍管理與自動擴展：

CREATE TABLESPACE HISTDB
DATAFILE 'D:\oracle\product\10.2.0\oradata\ZJGIS\test_temp03.dbf'
SIZE 32M
AUTOEXTEND ON
NEXT 32M MAXSIZE 2048M
EXTENT MANAGEMENT LOCAL;

三、創建用戶並指定預設與暫存表空間

CREATE USER username IDENTIFIED BY password
DEFAULT TABLESPACE TEST_DATA
TEMPORARY TABLESPACE TEST_TEMP;

四、授予系統權限（精細授權模式）

依最小權限原則，僅授予必要權限：

GRANT
  CREATE SESSION, CREATE ANY TABLE, CREATE ANY VIEW, CREATE ANY INDEX, CREATE ANY PROCEDURE,
  ALTER ANY TABLE, ALTER ANY PROCEDURE,
  DROP ANY TABLE, DROP ANY VIEW, DROP ANY INDEX, DROP ANY PROCEDURE,
  SELECT ANY TABLE, INSERT ANY TABLE, UPDATE ANY TABLE, DELETE ANY TABLE
TO username;

五、快速授權模式（開發/測試環境常用）

透過角色一次授予常用權限：

/* 四步驟快速建立 */
-- 1. 建立暫存表空間
CREATE TEMPORARY TABLESPACE user_temp
TEMPFILE 'D:\oracle\oradata\Oracle9i\user_temp.dbf'
SIZE 50M AUTOEXTEND ON NEXT 50M MAXSIZE 20480M
EXTENT MANAGEMENT LOCAL;

-- 2. 建立資料表空間
CREATE TABLESPACE user_data
LOGGING
DATAFILE 'D:\oracle\oradata\Oracle9i\user_data.dbf'
SIZE 50M AUTOEXTEND ON NEXT 50M MAXSIZE 20480M
EXTENT MANAGEMENT LOCAL;

-- 3. 建立用戶並指定表空間
CREATE USER username IDENTIFIED BY password
DEFAULT TABLESPACE user_data
TEMPORARY TABLESPACE user_temp;

-- 4. 授予連線、資源與 DBA 角色
GRANT CONNECT, RESOURCE, DBA TO username;

六、查詢用戶與權限相關視圖

1. 查詢所有用戶

SELECT * FROM DBA_USERS;     -- 需 DBA 權限，含詳細資訊
SELECT * FROM ALL_USERS;     -- 當前用戶可見用戶
SELECT * FROM USER_USERS;    -- 當前用戶資訊

2. 查詢系統權限

SELECT * FROM DBA_SYS_PRIVS;   -- 全庫系統權限
SELECT * FROM USER_SYS_PRIVS;  -- 當前用戶系統權限

3. 查詢物件/角色權限

SELECT * FROM DBA_TAB_PRIVS;   -- 全庫物件權限
SELECT * FROM ALL_TAB_PRIVS;   -- 可存取物件權限
SELECT * FROM USER_TAB_PRIVS;  -- 當前用戶物件權限

SELECT * FROM DBA_ROLES;       -- 所有角色
SELECT * FROM DBA_ROLE_PRIVS;  -- 用戶/角色擁有的角色
SELECT * FROM USER_ROLE_PRIVS; -- 當前用戶角色

七、清理操作：刪除用戶與表空間

-- 刪除用戶（CASCADE 連同物件一併刪除）
DROP USER username CASCADE;

-- 刪除表空間（INCLUDING CONTENTS AND DATAFILES 連同資料檔）
DROP TABLESPACE tablespaceName INCLUDING CONTENTS AND DATAFILES;

提醒：生產環境執行刪除操作前，請務必確認備份無誤，並評估影響範圍。