ORACLE_OCP多租户之审计

ORACLE_OCP多租户之审计

一、CDB和PDB中的审计操作

• 连接到根容器或PDB.
• 创建审计策略:
  • 审计选项：系统范围或特定于某个对象或角色
  • 每个语句的触发条件
  • CONTAINER = CURRENT | ALL
• Enable/disable 审计策略:
  • AUDIT and NOAUDIT
  • 定义审计的用户：默认情况下是全部用户

二、在CDB / PDB级别创建审计策略

• 为整个CDB或特定的PDB创建设计策略.

  • 连接到根容器或指定的PDB.

    SQL> CONNECT / AS SYSDBA
    #or
    SQL> CONNECT system@PDBC

  • 根据系统权限，操作或角色创建针对性审计策略.

    SQL> CREATE AUDIT POLICY audit_mixed_pol1_CDB
    2 PRIVILEGES DROP ANY TABLE
    3 ACTIONS CREATE TABLE, DROP TABLE, TRUNCATE TABLE
    4 ROLES emp_role;

  • 根据特定的对象选项创建审计策略.

    SQL> CREATE AUDIT POLICY audit_objpriv_pol2_PDB
    2 ACTIONS EXECUTE, GRANT ON hr.raise_salary_proc;

  • 对每个会话触发条件进行评估

    SQL> CREATE AUDIT POLICY audit_mixed_pol3_PDB ROLES hr_role
    2 WHEN ‘SYS_CONTEXT (’‘USERENV’’, ‘‘SESSION_USER’’)=’‘JIM’’’
    3 EVALUATE PER SESSION;

三、启用/禁用审计策略

• 在连接到根容器或PDB时启用审计策略:

  • 适用于所有用户.

    SQL> AUDIT POLICY audit_mixed_pol1_CDB;

  • 仅适用于某些用户.

    SQL> CONNECT system@PDBC
    SQL> AUDIT POLICY audit_objpriv_pol2_PDB BY scott, oe;

    SQL> AUDIT POLICY audit_mixed_pol1_CDB;
    audit policy AUDIT_MIXED_POL1_CDB
    *
    ERROR at line 1:
    ORA-46357: Audit policy AUDIT_MIXED_POL1_CDB not found.

  • 根据失败或成功的操作产生审计记录.

    SQL> AUDIT POLICY audit_syspriv_pol1 WHENEVER SUCCESSFUL;

  • 通过使用NOAUDIT命令禁用审计策略.

    SQL> NOAUDIT POLICY auditpol;

四、查看审计政策

• 查看所有CDB或PDB的审计策略.

  SQL> CONNECT / AS SYSDBA
  SQL> SELECT POLICY_NAME,
  2 AUDIT_OPTION
  3 FROM AUDIT_UNIFIED_POLICIES;
  POLICY_NAME AUDIT_OPTION
  ----------- ------------------
  POL1_CDB DELETE
  POL2_CDB TRUNCATE TABLE

    #or
  

  SQL> CONNECT system@PDBC
  SQL> SELECT POLICY_NAME,
  2 AUDIT_OPTION
  3 FROM AUDIT_UNIFIED_POLICIES;
  POLICY_NAME AUDIT_OPTION
  ----------- ------------------
  POL1_PDBC CREATE VIEW
  POL2_PDBC CREATE SEQUENCE

• 查看启用审计策略的CDB或PDB.

  SQL> SELECT POLICY_NAME
  FROM AUDIT_UNIFIED_ENABLED_POLICIES;
  POLICY_NAME
  --------------------
  POL2_CDB

  SQL> SELECT POLICY_NAME
  FROM AUDIT_UNIFIED_ENABLED_POLICIES;
  POLICY_NAME
  --------------------
  POL2_PDBC

五、查看审计记录CDB_UNIFIED_AUDIT_TRAIL

• 审计记录是在每个容器中生成的，具有不同的DBID.
  • 连接到PDB1时，UNIFIED_AUDIT_TRAIL视图仅显示PDB1行。
  • 连接到PDB2时，UNIFIED_AUDIT_TRAIL视图仅显示PDB2行。
  • 连接到根容器后，UNIFIED_AUDIT_TRAIL视图仅显示根容器行。
  • 查询CDB_UNIFIED_AUDIT_TRAIL以获取所有容器审计行的合并视图

SQL> SELECT con_id, ACTION_NAME
2 FROM CDB_UNIFIED_AUDIT_TRAIL;
CON_ID ACTION_NAME
----------- ------------------
1 ALTER USER
3 CREATE VIEW
4 CREATE TABLE

六、删除审计策略

• 禁用审计策略.

  SQL> CONNECT / AS SYSDBA
  SQL> NOAUDIT POLICY CDB_pol1;

  或

  SQL> CONNECT system@PDBC
  SQL> NOAUDIT POLICY PDBC_pol1;

• 删除审计策略.

  SQL> DROP AUDIT POLICY
  2 CDB_pol1;

  SQL> DROP AUDIT POLICY
  2 PDBC_pol1;

七、审计清理

• 清理SYS.UNIFIED_AUDIT_TRAIL中的记录:

  • 安排自动清除作业以清除PDB记录.

    DBMS_AUDIT_MGMT.CREATE_PURGE_JOB
    (AUDIT_TRAIL_TYPE=> DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED, AUDIT_TRAIL_PURGE_INTERVAL => 12,
    AUDIT_TRAIL_PURGE_NAME => ‘Audit_Trail_PDB’,
    USE_LAST_ARCH_TIMESTAMP => TRUE, CONTAINER => DBMS_AUDIT_MGMT.CONTAINER_CURRENT);

  • 手动清除CDB的所有审计记录.

    DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL(
    AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED,
    CONTAINER => DBMS_AUDIT_MGMT.CONTAINER_ALL)