授權:GRANT CONNECT, RESOURCE, DBA TO user;
查詢:SELECT * FROM dba_sys_privs / dba_role_privs / role_sys_privs;
回收:REVOKE CONNECT, RESOURCE FROM user; (無級聯、可跨用戶回收)
傳遞:GRANT ... TO user WITH ADMIN OPTION;
GRANT SELECT, UPDATE ON user01.product TO user02 [WITH GRANT OPTION];GRANT ALL ON product TO PUBLIC; (不含 DROP)ALL_TAB_PRIVS (被授權)、USER_TAB_PRIVS (授出權限)。REVOKE SELECT ON product FROM user02; (級聯失效,被授予者權限同步失效)。CREATE PROFILE student LIMIT
FAILED_LOGIN_ATTEMPTS 3
PASSWORD_LOCK_TIME 5
PASSWORD_LIFE_TIME 30
IDLE_TIME 30; -- 空閒分鐘數
CREATE USER acc01 IDENTIFIED BY "12345"
DEFAULT TABLESPACE account
TEMPORARY TABLESPACE temp
PROFILE student
QUOTA 50M ON account;
GRANT CONNECT, RESOURCE TO acc01;
ALTER USER acc01 IDENTIFIED BY "newpass";
ALTER USER acc01 DEFAULT TABLESPACE users;
ALTER USER acc01 TEMPORARY TABLESPACE temp_data;
ALTER USER acc01 PROFILE common;
ALTER USER acc01 ACCOUNT LOCK / UNLOCK;
ALTER USER acc01 PASSWORD EXPIRE; -- 強制下次登入修改密碼
DROP USER acc01; -- 無物件
DROP USER acc01 CASCADE; -- 連同物件刪除
-- 查詢活躍會話
SELECT username, sid, serial#, machine, status, logon_time FROM v$session WHERE username IS NOT NULL;
-- 強制斷開會話
ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE;
-- 查詢會話執行 SQL
SELECT s.username, sq.sql_text
FROM v$session s JOIN v$sql sq ON s.sql_id = sq.sql_id
WHERE s.username IS NOT NULL;
CREATE ROLE app_role IDENTIFIED BY pwd; -- 或 NOT IDENTIFIED
GRANT SELECT, INSERT ON schema.table TO app_role;
GRANT app_role TO app_user;
SET ROLE app_role; -- 啟用角色 (可用 ALL EXCEPT/ NONE)
ALTER SESSION SET
NLS_LANGUAGE='SIMPLIFIED CHINESE',
NLS_TERRITORY='CHINA',
NLS_DATE_FORMAT='YYYY-MM-DD DAY',
NLS_TIMESTAMP_FORMAT='YYYY-MM-DD HH24:MI:SS.FF',
TIME_ZONE='+08:00';