萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> Oracle 教學

Oracle 建立表空間、使用者與權限授予的標準化腳本範例

前言

建立表空間、使用者並授予適當權限是 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;

關鍵子句說明

  • BIGFILE:單一資料檔可擴展至 128TB(32K 區塊),簡化檔案管理,適合 ASM/大容量環境。
  • AUTOEXTEND ON NEXT 50M MAXSIZE UNLIMITED:自動擴展,無上限,避免「ORA-01652 unable to extend」中斷業務。
  • EXTENT MANAGEMENT LOCAL AUTOALLOCATE:本地管理 + 系統自動決定 Extent 大小(64K/1M/64M),減少碎片。
  • SEGMENT SPACE MANAGEMENT AUTO:ASSM(自動段空間管理),以位圖取代自由鏈表,提升並發插入效能。

二、建立使用者並指定預設/暫時表空間

CREATE USER wsp IDENTIFIED BY wsp
DEFAULT TABLESPACE wspspace
TEMPORARY TABLESPACE temp
QUOTA UNLIMITED ON wspspace
PROFILE DEFAULT
ACCOUNT UNLOCK;
  • DEFAULT TABLESPACE:物件建立時預存放位置。
  • TEMPORARY TABLESPACE:排序、雜湊、臨時表等暫存操作使用,建議指定專用 TEMP 表空間而非 SYSTEM。
  • QUOTA UNLIMITED:授予該表空間無限配額;若需限制可改為 QUOTA 10G ON wspspace。
  • PROFILE:關聯密碼/資源限制 Profile,預設為 DEFAULT。

三、權限授予:遵循最小權限原則

3.1 基礎連線與建立物件權限(開發/應用帳號常用)

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;  -- 進階物件類型

3.2 角色式授予(快速但較粗略)

-- CONNECT = CREATE SESSION
-- RESOURCE = 上述建立物件權限集合(含 UNLIMITED TABLESPACE,12c+ 已移除)
GRANT CONNECT, RESOURCE TO wsp;

注意:12c 起 RESOURCE 不再包含 UNLIMITED TABLESPACE,需額外授予配額或系統權限。

3.3 生產環境建議:精細化權限模板

-- 僅授予業務必要權限,避免 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';

結語

標準化的表空間/使用者/權限建立腳本,不僅能確保環境一致性、降低人為錯誤,更能落實最小權限原則,符合資安稽核要求。建議將上述腳本模板化、參數化,納入版本控管,並搭配自動化部署工具在各環境重複執行。

copyright © 萬盛學電腦網 all rights reserved