建立表空間、使用者並授予適當權限是 Oracle 環境部署的基礎作業。本文提供一組標準化、可參數化的 DDL 腳本範例,涵蓋大檔案表空間、自動擴展、無限成長、使用者建立與最小權限原則授權,適合納入環境建置 SOP 或自動化部署腳本。
-- 建立大檔案表空間,支援無限成長,適合大型資料倉庫
CREATE BIGFILE TABLESPACE wspspace
DATAFILE 'D:/dev/oracle/tablespace/wspspace.dbf'
SIZE 300M
AUTOEXTEND ON
NEXT 50M
MAXSIZE UNLIMITED
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
SEGMENT SPACE MANAGEMENT AUTO;
CREATE USER wsp IDENTIFIED BY wsp
DEFAULT TABLESPACE wspspace
TEMPORARY TABLESPACE temp
QUOTA UNLIMITED ON wspspace
PROFILE DEFAULT
ACCOUNT UNLOCK;
QUOTA 10G ON wspspace。GRANT CREATE SESSION TO wsp; -- 登入權限(最基本)
GRANT CREATE TABLE, CREATE VIEW, CREATE SEQUENCE, CREATE PROCEDURE, CREATE TRIGGER, CREATE SYNONYM TO wsp;
GRANT CREATE TYPE, CREATE MATERIALIZED VIEW TO wsp; -- 進階物件類型
-- CONNECT = CREATE SESSION
-- RESOURCE = 上述建立物件權限集合(含 UNLIMITED TABLESPACE,12c+ 已移除)
GRANT CONNECT, RESOURCE TO wsp;
注意:12c 起 RESOURCE 不再包含 UNLIMITED TABLESPACE,需額外授予配額或系統權限。
-- 僅授予業務必要權限,避免 DBA 權限濫用
GRANT CREATE SESSION TO wsp;
GRANT CREATE TABLE, CREATE VIEW, CREATE SEQUENCE, CREATE PROCEDURE TO wsp;
-- 若需建立索引(通常隱含在 CREATE TABLE),可額外授予
GRANT CREATE ANY INDEX TO wsp; -- 謹慎使用,建議改由 DBA 統一管理索引
-- 僅授予自有物件的 DML 權限(透過擁有者隱含擁有),跨 Schema 需額外授予
-- GRANT SELECT, INSERT, UPDATE, DELETE ON other_schema.target_table TO wsp;
DEFINE tbs_name = 'WSPSPACE'
DEFINE datafile = '/u01/app/oracle/oradata/&tbs_name..dbf'
DEFINE init_size = '500M'
DEFINE next_size = '100M'
DEFINE username = 'WSP'
DEFINE password = 'ComplexPass#2024'
DEFINE temp_tbs = 'TEMP'
-- 1. Tablespace
CREATE BIGFILE TABLESPACE &tbs_name
DATAFILE '&datafile' SIZE &init_size
AUTOEXTEND ON NEXT &next_size MAXSIZE UNLIMITED
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
SEGMENT SPACE MANAGEMENT AUTO;
-- 2. User
CREATE USER &username IDENTIFIED BY &password
DEFAULT TABLESPACE &tbs_name
TEMPORARY TABLESPACE &temp_tbs
QUOTA UNLIMITED ON &tbs_name;
-- 3. Grants
GRANT CREATE SESSION, CREATE TABLE, CREATE VIEW, CREATE SEQUENCE, CREATE PROCEDURE TO &username;
-- 視專案需求增減
-- GRANT SELECT_CATALOG_ROLE TO &username; -- 僅需查詢字典視圖時授予
-- 驗證表空間
SELECT tablespace_name, file_name, bytes/1024/1024 MB, autoextensible, maxbytes/1024/1024 MAX_MB
FROM dba_data_files WHERE tablespace_name = 'WSPSPACE';
-- 驗證使用者與權限
SELECT username, default_tablespace, temporary_tablespace, profile, account_status
FROM dba_users WHERE username = 'WSP';
SELECT * FROM dba_sys_privs WHERE grantee = 'WSP';
SELECT * FROM dba_tab_privs WHERE grantee = 'WSP';
SELECT * FROM dba_role_privs WHERE grantee = 'WSP';
標準化的表空間/使用者/權限建立腳本,不僅能確保環境一致性、降低人為錯誤,更能落實最小權限原則,符合資安稽核要求。建議將上述腳本模板化、參數化,納入版本控管,並搭配自動化部署工具在各環境重複執行。