# 示例：创建用户
SQL> CREATE USER scott IDENTIFIED BY tiger DEFAULT TABLESPACE users TEMPORARY TABLESPACE temp;

# 示例：授予权限
SQL> GRANT CONNECT, RESOURCE TO scott;

# 示例：创建角色
SQL> CREATE ROLE developer;

# 示例：授予角色权限
SQL> GRANT CREATE TABLE, CREATE PROCEDURE TO developer;

# 示例：将角色授予用户
SQL> GRANT developer TO scott;

# 示例：修改用户密码
SQL> ALTER USER scott IDENTIFIED BY new_password;

# 示例：锁定用户
SQL> ALTER USER scott ACCOUNT LOCK;

# 示例：解锁用户
SQL> ALTER USER scott ACCOUNT UNLOCK;

# 示例：删除用户
SQL> DROP USER scott CASCADE;

# 示例：设置密码策略
SQL> ALTER PROFILE DEFAULT LIMIT
PASSWORD_LIFE_TIME 60
PASSWORD_GRACE_TIME 7
PASSWORD_REUSE_TIME 180
PASSWORD_REUSE_MAX UNLIMITED
FAILED_LOGIN_ATTEMPTS 5
PASSWORD_LOCK_TIME 1;

# 示例：查看用户信息
SQL> SELECT username, account_status, default_tablespace FROM dba_users;

# 示例：查看用户权限
SQL> SELECT grantee, privilege FROM dba_sys_privs WHERE grantee = ‘SCOTT’;

# 示例：查看用户角色
SQL> SELECT grantee, granted_role FROM dba_role_privs WHERE grantee = ‘SCOTT’;

# 示例：授予系统权限
SQL> GRANT CREATE SESSION TO scott;

# 示例：授予对象权限
SQL> GRANT SELECT, INSERT, UPDATE, DELETE ON hr.employees TO scott;

# 示例：授予角色权限
SQL> GRANT CREATE TABLE TO developer;

# 示例：撤销权限
SQL> REVOKE DELETE ON hr.employees FROM scott;

# 示例：创建细粒度访问控制策略
SQL> BEGIN
DBMS_RLS.ADD_POLICY(
object_schema => ‘hr’,
object_name => ’employees’,
policy_name => ’emp_policy’,
function_schema => ‘hr’,
policy_function => ’emp_security’,
statement_types => ‘SELECT, INSERT, UPDATE, DELETE’
);
END;
/

# 示例：创建安全函数
SQL> CREATE OR REPLACE FUNCTION hr.emp_security (p_schema IN VARCHAR2, p_object IN VARCHAR2) RETURN VARCHAR2 AS
v_user VARCHAR2(30) := SYS_CONTEXT(‘USERENV’, ‘SESSION_USER’);
BEGIN
IF v_user = ‘SCOTT’ THEN
RETURN ‘department_id = 10’;
ELSE
RETURN ‘1=1’;
END IF;
END;
/

# 示例：查看权限使用情况
SQL> SELECT * FROM dba_audit_trail WHERE action_name LIKE ‘%GRANT%’ OR action_name LIKE ‘%REVOKE%’;

# 示例：启用透明数据加密（TDE）

# 1. 创建主密钥
SQL> ADMINISTER KEY MANAGEMENT CREATE KEYSTORE ‘/oracle/app/oracle/admin/ORCL/keystore’ IDENTIFIED BY keystore_password;

# 2. 打开密钥库
SQL> ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY keystore_password;

# 3. 创建主加密密钥
SQL> ADMINISTER KEY MANAGEMENT SET ENCRYPTION KEY IDENTIFIED BY keystore_password;

# 4. 加密表空间
SQL> CREATE TABLESPACE secure_ts DATAFILE ‘/oracle/app/oracle/oradata/ORCL/secure_ts01.dbf’ SIZE 100M ENCRYPTION USING ‘AES256’ DEFAULT STORAGE(ENCRYPT);

# 5. 加密现有表空间
SQL> ALTER TABLESPACE users ENCRYPTION ONLINE USING ‘AES256’ ENCRYPT;

# 6. 加密列
SQL> CREATE TABLE employees (
employee_id NUMBER,
first_name VARCHAR2(50),
last_name VARCHAR2(50),
salary NUMBER ENCRYPT USING ‘AES256’
);

# 7. 启用网络加密
SQL> ALTER SYSTEM SET sqlnet.encryption_server = ‘required’ SCOPE=SPFILE;
SQL> ALTER SYSTEM SET sqlnet.encryption_types_server = ‘(AES256)’ SCOPE=SPFILE;
SQL> ALTER SYSTEM SET sqlnet.crypto_checksum_server = ‘required’ SCOPE=SPFILE;

# 8. 加密备份
RMAN> BACKUP DATABASE PLUS ARCHIVELOG ENCRYPT;

# 9. 管理加密密钥
SQL> ADMINISTER KEY MANAGEMENT ROTATE ENCRYPTION KEY IDENTIFIED BY keystore_password;

# 10. 查看加密状态
SQL> SELECT tablespace_name, encryptionalg FROM dba_tablespaces WHERE encryptionalg IS NOT NULL;
SQL> SELECT owner, table_name, column_name, encryptionalg FROM dba_tab_cols WHERE encryptionalg IS NOT NULL;

# 示例：创建动态数据掩码策略

# 1. 创建掩码函数
SQL> CREATE OR REPLACE FUNCTION mask_credit_card(p_card_number VARCHAR2) RETURN VARCHAR2 AS
BEGIN
IF LENGTH(p_card_number) >= 16 THEN
RETURN ‘XXXX-XXXX-XXXX-‘ || SUBSTR(p_card_number, 13, 4);
ELSE
RETURN p_card_number;
END IF;
END;
/

# 2. 创建掩码策略
SQL> BEGIN
DBMS_REDACT.ADD_POLICY(
object_schema => ‘hr’,
object_name => ’employees’,
column_name => ‘credit_card’,
policy_name => ‘cc_mask’,
function_type => DBMS_REDACT.FULL,
expression => ‘SYS_CONTEXT(”USERENV”, ”SESSION_USER”) != ”ADMIN”’
);
END;
/

# 3. 测试掩码效果
SQL> CONN scott/tiger
SQL> SELECT employee_id, credit_card FROM hr.employees;

EMPLOYEE_ID CREDIT_CARD
———– ——————–
100 XXXX-XXXX-XXXX-1234
101 XXXX-XXXX-XXXX-5678

SQL> CONN admin/admin
SQL> SELECT employee_id, credit_card FROM hr.employees;

EMPLOYEE_ID CREDIT_CARD
———– ——————–
100 1234-5678-9012-3456
101 9876-5432-1098-7654

# 4. 修改掩码策略
SQL> BEGIN
DBMS_REDACT.ALTER_POLICY(
object_schema => ‘hr’,
object_name => ’employees’,
column_name => ‘credit_card’,
policy_name => ‘cc_mask’,
function_type => DBMS_REDACT.PARTIAL,
function_parameters => ‘VVVVFVVVVFVVVVFVVVV,1,4,13,16’,
expression => ‘SYS_CONTEXT(”USERENV”, ”SESSION_USER”) != ”ADMIN”’
);
END;
/

# 5. 删除掩码策略
SQL> BEGIN
DBMS_REDACT.DROP_POLICY(
object_schema => ‘hr’,
object_name => ’employees’,
policy_name => ‘cc_mask’
);
END;
/

# 示例：启用统一审计
SQL> ALTER SYSTEM SET audit_trail = ‘DB,EXTENDED’ SCOPE=SPFILE;
SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP;

# 示例：创建审计策略
SQL> CREATE AUDIT POLICY table_access_policy
ACTIONS SELECT, INSERT, UPDATE, DELETE ON hr.employees;

# 示例：启用审计策略
SQL> AUDIT POLICY table_access_policy;

# 示例：查看审计数据
SQL> SELECT * FROM unified_audit_trail WHERE object_name = ‘EMPLOYEES’ ORDER BY event_timestamp DESC;

# 示例：创建细粒度审计
SQL> CREATE AUDIT POLICY sensitive_data_access
ACTIONS SELECT ON hr.employees(salary, commission_pct);

SQL> AUDIT POLICY sensitive_data_access;

# 示例：监控用户登录
SQL> CREATE AUDIT POLICY login_policy
ACTIONS LOGON, LOGOFF;

SQL> AUDIT POLICY login_policy;

# 示例：查看审计设置
SQL> SELECT * FROM audit_unified_policies;
SQL> SELECT * FROM audit_unified_enabled_policies;

# 示例：分析审计日志
SQL> SELECT action_name, COUNT(*) FROM unified_audit_trail GROUP BY action_name ORDER BY COUNT(*) DESC;

# 示例：清理审计数据
SQL> EXEC DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL(
audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED,
use_last_arch_timestamp => TRUE
);