Assim como \u00e9 feito em bancos non-CDBs, a arquitetura Multitenant nos permite utilizar o recurso de pol\u00edticas de auditoria tamb\u00e9m a n\u00edvel de PDBs. Neste artigo vamos explorar alguns exemplos simples de como implementar isso.<\/p>\n\n\n\n
Realizando reconhecimento do ambiente de laborat\u00f3rio:<\/p>\n\n\n
\n[oracle@quiasma ~]$ sqlplus \/ as sysdba\n \nSQL*Plus: Release 18.0.0.0.0 - Production on Fri Jun 18 05:21:28 2021\nVersion 18.13.0.0.0\n \nCopyright (c) 1982, 2018, Oracle. All rights reserved.\n \n \nConnected to:\nOracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production\nVersion 18.13.0.0.0\n \nSQL> SELECT NAME,OPEN_MODE,CDB FROM V$DATABASE;\n \nNAME OPEN_MODE CDB\n--------- -------------------- ---\nASWAN READ WRITE YES\n \nSQL> SHOW PDBS;\n \n CON_ID CON_NAME OPEN MODE RESTRICTED\n---------- ------------------------------ ---------- ----------\n 2 PDB$SEED READ ONLY NO\n 3 HIPOFISE2 READ WRITE NO\n 4 HIPOFISE1 READ WRITE NO\n<\/pre><\/div>\n\n\nNo PDB HIPOFISE1, vamos criar uma pol\u00edtica de auditoria chamada BSS, que coletar\u00e1 os eventos de cria\u00e7\u00e3o de tablespace:<\/p>\n\n\n
\n[oracle@quiasma ~]$ sqlplus system\/oracle@HIPOFISE1\n \nSQL*Plus: Release 18.0.0.0.0 - Production on Fri Jun 18 05:29:37 2021\nVersion 18.13.0.0.0\n \nCopyright (c) 1982, 2018, Oracle. All rights reserved.\n \nLast Successful login time: Thu Jun 10 2021 06:06:02 -03:00\n \nConnected to:\nOracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production\nVersion 18.13.0.0.0\n \nSQL> CREATE AUDIT POLICY BSS ACTIONS CREATE TABLESPACE;\n \nAudit policy created.\n \nSQL> AUDIT POLICY BSS;\n \nAudit succeeded.\n \nSQL> col user_name format A10\nSQL> col policy_name format A10\nSQL> col entity_name format a10\nSQL> SELECT * FROM AUDIT_UNIFIED_ENABLED_POLICIES WHERE POLICY_NAME='BSS';\n \nUSER_NAME POLICY_NAM ENABLED ENABLED_OPTION ENTITY_NAM ENTITY_ SUC FAI\n---------- ---------- ------- --------------- ---------- ------- --- ---\nALL USERS BSS BY BY USER ALL USERS USER YES YES\n<\/pre><\/div>\n\n\nCriando tablespace:<\/p>\n\n\n
\nSQL> CREATE TABLESPACE TESTE;\n \nTablespace created.\n<\/pre><\/div>\n\n\nValidando log da opera\u00e7\u00e3o:<\/p>\n\n\n
\nSQL> col dbusername format a12\nSQL> col action_name format a20\nSQL> col object_name format a12\nSQL> col event_time format a19\nSQL> 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;\n \nDBUSERNAME ACTION_NAME OBJECT_NAME EVENT_TIME\n------------ -------------------- ------------ -------------------\nSYSTEM CREATE TABLESPACE TESTE 18-JUN-21 05:30 AM\n<\/pre><\/div>\n\n\nDropando tablespace e pol\u00edtica:<\/p>\n\n\n
\nSQL> DROP TABLESPACE TESTE INCLUDING CONTENTS AND DATAFILES;\n \nTablespace dropped.\n \nSQL> NOAUDIT POLICY BSS;\n \nNoaudit succeeded.\n \nSQL> DROP AUDIT POLICY BSS;\n \nAudit Policy dropped.\n<\/pre><\/div>\n\n\nAgora vamos verificar as pol\u00edticas nativas, que j\u00e1 vem implementadas nos PDBs:<\/p>\n\n\n
\nSQL> col policy_name format a20\nSQL> col user_name format a15\nSQL> SELECT POLICY_NAME, USER_NAME FROM AUDIT_UNIFIED_ENABLED_POLICIES ORDER BY 1;\n \nPOLICY_NAME USER_NAME\n-------------------- ---------------\nORA_LOGON_FAILURES ALL USERS\nORA_SECURECONFIG ALL USERS\n<\/pre><\/div>\n\n\nVamos validar as opera\u00e7\u00f5es que s\u00e3o auditadas da pol\u00edtica ORA_SECURECONFIG:<\/p>\n\n\n
\nSQL> col audit_option format a40\nSQL> SELECT AUDIT_OPTION FROM AUDIT_UNIFIED_POLICIES WHERE POLICY_NAME ='ORA_SECURECONFIG' ORDER BY 1;\n \nAUDIT_OPTION\n----------------------------------------\nADMINISTER KEY MANAGEMENT\nALTER ANY PROCEDURE\nALTER ANY SQL TRANSLATION PROFILE\nALTER ANY TABLE\nALTER DATABASE\nALTER DATABASE DICTIONARY\nALTER DATABASE LINK\nALTER PLUGGABLE DATABASE\nALTER PROFILE\nALTER ROLE\nALTER SYSTEM\n \nAUDIT_OPTION\n----------------------------------------\nALTER USER\nAUDIT SYSTEM\nBECOME USER\nCREATE ANY JOB\nCREATE ANY LIBRARY\nCREATE ANY PROCEDURE\nCREATE ANY SQL TRANSLATION PROFILE\nCREATE ANY TABLE\nCREATE DATABASE LINK\nCREATE DIRECTORY\nCREATE EXTERNAL JOB\n \nAUDIT_OPTION\n----------------------------------------\nCREATE PLUGGABLE DATABASE\nCREATE PROFILE\nCREATE PUBLIC SYNONYM\nCREATE ROLE\nCREATE SQL TRANSLATION PROFILE\nCREATE USER\nDROP ANY PROCEDURE\nDROP ANY SQL TRANSLATION PROFILE\nDROP ANY TABLE\nDROP DATABASE LINK\nDROP DIRECTORY\n \nAUDIT_OPTION\n----------------------------------------\nDROP PLUGGABLE DATABASE\nDROP PROFILE\nDROP PUBLIC SYNONYM\nDROP ROLE\nDROP USER\nEXECUTE\nEXECUTE\nEXEMPT ACCESS POLICY\nEXEMPT REDACTION POLICY\nGRANT ANY OBJECT PRIVILEGE\nGRANT ANY PRIVILEGE\n \nAUDIT_OPTION\n----------------------------------------\nGRANT ANY ROLE\nLOGMINING\nPURGE DBA_RECYCLEBIN\nSET ROLE\nTRANSLATE ANY SQL\n \n49 rows selected.\n<\/pre><\/div>\n\n\nCriando e deletando um local user dentro do PDB:<\/p>\n\n\n
\nSQL> CREATE USER BRAZIL IDENTIFIED BY oracle;\n \nUser created.\n \nSQL> DROP USER BRAZIL CASCADE;\n \nUser dropped.\n<\/pre><\/div>\n\n\nRealizando consulta:<\/p>\n\n\n
\nSQL> col dbusername format a12\nSQL> col action_name format a20\nSQL> col object_name format a12\nSQL> col event_time format a19\nSQL> 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;\n \nDBUSERNAME ACTION_NAME OBJECT_NAME EVENT_TIME\n------------ -------------------- ------------ -------------------\nSYSTEM CREATE USER BRAZIL 18-JUN-21 05:45 AM\n<\/pre><\/div>\n\n\nCaso precisemos realizar a consulta a partir do CDB$ROOT, basta usarmos a mesma query, mas usando a view CDB_UNIFIED_AUDIT_TRAIL:<\/p>\n\n\n
\nSQL> conn \/ as sysdba\nConnected.\nSQL> SHOW CON_NAME;\n \nCON_NAME\n------------------------------\nCDB$ROOT\nSQL> col dbusername format a12\nSQL> col action_name format a20\nSQL> col object_name format a12\nSQL> col event_time format a19\nSQL> 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;\n \nDBUSERNAME ACTION_NAME OBJECT_NAME EVENT_TIME\n------------ -------------------- ------------ -------------------\nSYSTEM CREATE USER BRAZIL 18-JUN-21 05:45 AM\n<\/pre><\/div>\n\n\nObs: Este procedimento foi criado pelo senhor Ahmed Baraka (www.ahmedbaraka.com) e foi apenas reproduzido por mim em um laborat\u00f3rio pessoal para fins de aprendizado.<\/p>\n","protected":false},"excerpt":{"rendered":"
Assim como \u00e9 feito em bancos non-CDBs, a arquitetura Multitenant nos permite utilizar o recurso de pol\u00edticas de auditoria tamb\u00e9m a n\u00edvel de PDBs. Neste artigo vamos explorar alguns exemplos simples de como implementar isso. Realizando reconhecimento do ambiente de laborat\u00f3rio: No PDB HIPOFISE1, vamos criar uma pol\u00edtica de auditoria chamada BSS, que coletar\u00e1 os […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[8],"tags":[],"class_list":["post-5497","post","type-post","status-publish","format-standard","hentry","category-multitenant"],"_links":{"self":[{"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/posts\/5497","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/comments?post=5497"}],"version-history":[{"count":1,"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/posts\/5497\/revisions"}],"predecessor-version":[{"id":9098,"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/posts\/5497\/revisions\/9098"}],"wp:attachment":[{"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/media?parent=5497"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/categories?post=5497"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/tags?post=5497"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}