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 UNIFORM SIZE 1M;CREATE TABLESPACE test_data
LOGGING
DATAFILE 'C:\Oracle\PRODUCT\10.1.0\ORADATA\ORCL\TEST_DATA01.DBF'
SIZE 32M
AUTOEXTEND ON NEXT 32M MAXSIZE 2048M
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
SEGMENT SPACE MANAGEMENT AUTO;CREATE USER username IDENTIFIED BY password
DEFAULT TABLESPACE test_data
TEMPORARY TABLESPACE test_temp
QUOTA UNLIMITED ON test_data
PASSWORD EXPIRE ACCOUNT UNLOCK;| 層級 | 權限/角色 | 適用對象 |
|---|---|---|
| 基礎開發 | CONNECT, RESOURCE (CREATE SESSION, TABLE, VIEW, SEQUENCE, PROCEDURE...) | 應用開發帳號 |
| 進階開發 | CREATE ANY TABLE, ALTER ANY TABLE, DROP ANY TABLE SELECT ANY TABLE, INSERT/UPDATE/DELETE ANY TABLE | 資料遷移/ETL帳號 |
| 唯讀查詢 | 自訂角色 APP_READ_ONLY → GRANT SELECT ON schema.* | 報表/BI帳號 |
| DBA 維護 | DBA 角色 (含 SYSDBA 權限) | 極少數管理員 |
GRANT
CREATE SESSION,
CREATE TABLE, CREATE VIEW, CREATE INDEX, CREATE 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;-- 授予角色
GRANT app_role TO username;
-- 查詢所有角色
SELECT * FROM DBA_ROLES;
-- 查詢用戶/角色擁有的角色
SELECT * FROM DBA_ROLE_PRIVS WHERE grantee = 'USERNAME';
SELECT * FROM USER_ROLE_PRIVS;
-- 查詢用戶系統權限 (含透過角色繼承)
SELECT * FROM DBA_SYS_PRIVS WHERE grantee = 'USERNAME';
-- 查詢用戶物件權限
SELECT * FROM DBA_TAB_PRIVS WHERE grantee = 'USERNAME';| 需求 | SQL |
|---|---|
| 所有用戶清單 | SELECT username, account_status, default_tablespace, temporary_tablespace FROM DBA_USERS; |
| 鎖定/解鎖用戶 | ALTER USER username ACCOUNT LOCK / UNLOCK; |
| 修改密碼 | ALTER USER username IDENTIFIED BY new_password; |
| 修改預設表空間 | ALTER USER username DEFAULT TABLESPACE new_ts; |
DROP TABLESPACE test_data
INCLUDING CONTENTS AND DATAFILES
CASCADE CONSTRAINTS;INCLUDING CONTENTS：刪除段內資料。AND DATAFILES：同步刪除 OS 層資料檔。CASCADE CONSTRAINTS：刪除指向此表空間表的外鍵約束。標準化的「表空間→用戶→授權→審計」流程，配合最小權限原則與定期權限巡檢 (查詢 DBA_* 視圖)，是資料庫安全合規的基石。
