Assim como é feito em bancos non-CDBs, a arquitetura Multitenant nos permite utilizar o recurso de políticas de auditoria também a nível de PDBs. Neste artigo vamos explorar alguns exemplos simples de como implementar isso.
Realizando reconhecimento do ambiente de laboratório:
[oracle@quiasma ~]$ sqlplus / as sysdba
SQL*Plus: Release 18.0.0.0.0 - Production on Fri Jun 18 05:21:28 2021
Version 18.13.0.0.0
Copyright (c) 1982, 2018, Oracle. All rights reserved.
Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.13.0.0.0
SQL> SELECT NAME,OPEN_MODE,CDB FROM V$DATABASE;
NAME OPEN_MODE CDB
--------- -------------------- ---
ASWAN READ WRITE YES
SQL> SHOW PDBS;
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 HIPOFISE2 READ WRITE NO
4 HIPOFISE1 READ WRITE NO
No PDB HIPOFISE1, vamos criar uma política de auditoria chamada BSS, que coletará os eventos de criação de tablespace:
[oracle@quiasma ~]$ sqlplus system/oracle@HIPOFISE1
SQL*Plus: Release 18.0.0.0.0 - Production on Fri Jun 18 05:29:37 2021
Version 18.13.0.0.0
Copyright (c) 1982, 2018, Oracle. All rights reserved.
Last Successful login time: Thu Jun 10 2021 06:06:02 -03:00
Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.13.0.0.0
SQL> CREATE AUDIT POLICY BSS ACTIONS CREATE TABLESPACE;
Audit policy created.
SQL> AUDIT POLICY BSS;
Audit succeeded.
SQL> col user_name format A10
SQL> col policy_name format A10
SQL> col entity_name format a10
SQL> SELECT * FROM AUDIT_UNIFIED_ENABLED_POLICIES WHERE POLICY_NAME='BSS';
USER_NAME POLICY_NAM ENABLED ENABLED_OPTION ENTITY_NAM ENTITY_ SUC FAI
---------- ---------- ------- --------------- ---------- ------- --- ---
ALL USERS BSS BY BY USER ALL USERS USER YES YES
Criando tablespace:
SQL> CREATE TABLESPACE TESTE;
Tablespace created.
Validando log da operação:
SQL> col dbusername format a12
SQL> col action_name format a20
SQL> col object_name format a12
SQL> col event_time format a19
SQL> SELECT DBUSERNAME, ACTION_NAME, OBJECT_NAME, to_char(EVENT_TIMESTAMP,'DD-MON-RR HH12:MI AM') EVENT_TIME FROM UNIFIED_AUDIT_TRAIL WHERE ACTION_NAME LIKE '%TABLESPACE%' ORDER BY EVENT_TIMESTAMP;
DBUSERNAME ACTION_NAME OBJECT_NAME EVENT_TIME
------------ -------------------- ------------ -------------------
SYSTEM CREATE TABLESPACE TESTE 18-JUN-21 05:30 AM
Dropando tablespace e política:
SQL> DROP TABLESPACE TESTE INCLUDING CONTENTS AND DATAFILES;
Tablespace dropped.
SQL> NOAUDIT POLICY BSS;
Noaudit succeeded.
SQL> DROP AUDIT POLICY BSS;
Audit Policy dropped.
Agora vamos verificar as políticas nativas, que já vem implementadas nos PDBs:
SQL> col policy_name format a20
SQL> col user_name format a15
SQL> SELECT POLICY_NAME, USER_NAME FROM AUDIT_UNIFIED_ENABLED_POLICIES ORDER BY 1;
POLICY_NAME USER_NAME
-------------------- ---------------
ORA_LOGON_FAILURES ALL USERS
ORA_SECURECONFIG ALL USERS
Vamos validar as operações que são auditadas da política ORA_SECURECONFIG:
SQL> col audit_option format a40
SQL> SELECT AUDIT_OPTION FROM AUDIT_UNIFIED_POLICIES WHERE POLICY_NAME ='ORA_SECURECONFIG' ORDER BY 1;
AUDIT_OPTION
----------------------------------------
ADMINISTER KEY MANAGEMENT
ALTER ANY PROCEDURE
ALTER ANY SQL TRANSLATION PROFILE
ALTER ANY TABLE
ALTER DATABASE
ALTER DATABASE DICTIONARY
ALTER DATABASE LINK
ALTER PLUGGABLE DATABASE
ALTER PROFILE
ALTER ROLE
ALTER SYSTEM
AUDIT_OPTION
----------------------------------------
ALTER USER
AUDIT SYSTEM
BECOME USER
CREATE ANY JOB
CREATE ANY LIBRARY
CREATE ANY PROCEDURE
CREATE ANY SQL TRANSLATION PROFILE
CREATE ANY TABLE
CREATE DATABASE LINK
CREATE DIRECTORY
CREATE EXTERNAL JOB
AUDIT_OPTION
----------------------------------------
CREATE PLUGGABLE DATABASE
CREATE PROFILE
CREATE PUBLIC SYNONYM
CREATE ROLE
CREATE SQL TRANSLATION PROFILE
CREATE USER
DROP ANY PROCEDURE
DROP ANY SQL TRANSLATION PROFILE
DROP ANY TABLE
DROP DATABASE LINK
DROP DIRECTORY
AUDIT_OPTION
----------------------------------------
DROP PLUGGABLE DATABASE
DROP PROFILE
DROP PUBLIC SYNONYM
DROP ROLE
DROP USER
EXECUTE
EXECUTE
EXEMPT ACCESS POLICY
EXEMPT REDACTION POLICY
GRANT ANY OBJECT PRIVILEGE
GRANT ANY PRIVILEGE
AUDIT_OPTION
----------------------------------------
GRANT ANY ROLE
LOGMINING
PURGE DBA_RECYCLEBIN
SET ROLE
TRANSLATE ANY SQL
49 rows selected.
Criando e deletando um local user dentro do PDB:
SQL> CREATE USER BRAZIL IDENTIFIED BY oracle;
User created.
SQL> DROP USER BRAZIL CASCADE;
User dropped.
Realizando consulta:
SQL> col dbusername format a12
SQL> col action_name format a20
SQL> col object_name format a12
SQL> col event_time format a19
SQL> SELECT DBUSERNAME, ACTION_NAME, OBJECT_NAME, to_char(EVENT_TIMESTAMP,'DD-MON-RR HH12:MI AM') EVENT_TIME FROM UNIFIED_AUDIT_TRAIL WHERE ACTION_NAME = 'CREATE USER' AND OBJECT_NAME='BRAZIL' ORDER BY EVENT_TIMESTAMP;
DBUSERNAME ACTION_NAME OBJECT_NAME EVENT_TIME
------------ -------------------- ------------ -------------------
SYSTEM CREATE USER BRAZIL 18-JUN-21 05:45 AM
Caso precisemos realizar a consulta a partir do CDB$ROOT, basta usarmos a mesma query, mas usando a view CDB_UNIFIED_AUDIT_TRAIL:
SQL> conn / as sysdba
Connected.
SQL> SHOW CON_NAME;
CON_NAME
------------------------------
CDB$ROOT
SQL> col dbusername format a12
SQL> col action_name format a20
SQL> col object_name format a12
SQL> col event_time format a19
SQL> SELECT DBUSERNAME, ACTION_NAME, OBJECT_NAME, to_char(EVENT_TIMESTAMP,'DD-MON-RR HH12:MI AM') EVENT_TIME FROM CDB_UNIFIED_AUDIT_TRAIL WHERE ACTION_NAME = 'CREATE USER' AND OBJECT_NAME='BRAZIL' ORDER BY EVENT_TIMESTAMP;
DBUSERNAME ACTION_NAME OBJECT_NAME EVENT_TIME
------------ -------------------- ------------ -------------------
SYSTEM CREATE USER BRAZIL 18-JUN-21 05:45 AM
Obs: Este procedimento foi criado pelo senhor Ahmed Baraka (www.ahmedbaraka.com) e foi apenas reproduzido por mim em um laboratório pessoal para fins de aprendizado.
Portuguese
English
Spanish