Unified Auditing, um dos novos recursos do Oracle Database 12c

Por Franky Weber,
Postado em Maio 2016

Revisado por Marcelo Pivovar - Solution Architect

Objetivo: Explicar e exemplificar como habilitar e por que usar o novo método de auditoria, mostrar seus benefícios e realizar alguns testes de desempenho para comprovar as melhorias.

Na versão 12c entrou em cena uma nova arquitetura para auditoria no banco de dados Oracle, a Unified Auditing. Até então tínhamos 3 métodos de auditoria no banco de dados: Fine Grained Auditing (FGA), Value Based Auditing (auditoria com triggers) e Standard Database Auditing (AUDIT command). Auditoria no Oracle sempre esteve relacionado a uma grande perda de desempenho devido a recursividade de operações. Com a Unified Auding há uma área de memória onde as informações são gravadas primeiro, basicamente tem o mesmo conceito do redo log buffer, primeiro grava em memória (unified_audit_sga_queue_size) e em seguida em disco nas tabelas do novo schema AUDSYS. Os demais métodos de auditoria continuam podendo ser utilizados e permanecem armazenados no schema SYS nas tabelas AUD$ e FGA_LOG$. Duas novas roles separam as funções de auditoria: AUDIT_ADMIN para configuração e administração e AUDIT_VIEWER para visualização e análise dos dados auditados.

A maioria dos DBAs odeia auditoria e os motivos são razoáveis: é chato configurar, impacta no desempenho do ambiente, não é tão seguro até o 12c devido aos dados de auditoria ficarem no schema SYS e não temos outra opção. Unified Auditing é realmente mais segura, pois um novo schema chamado AUDSYS armazena os dados de auditoria, é mais performático devido a área de memória alocada para este fim e substitui quase todos os métodos existentes de auditoria. Só isso já é um bom motivo para o upgrade para o 12c, mas esse não é o foco desse artigo.

Com a Unified Auditing, como o próprio nome já diz, Auditoria Unificada, os registros de auditoria são consultados em um só lugar, em uma única view chamada UNIFIED_AUDIT_TRAIL.

A Unified Auditing suporta os seguintes modelos de auditoria:

  • Standard database auditing;
  • SYS operations auditing;
  • Fine Grained Auditing (FGA);
  • Data Pump;
  • RMAN;
  • Label Security (OLS);
  • Database Vault (DV);
  • Real Application Security (RAS);
  • SQL*Loader Direct Load.

Por padrão a Unified Auditing vem inclusa na Enterprise Edition, sem necessidade de qualquer licenciamento adicional. Não há a necessidade de instalá-la, pois já está embutida no próprio Database, porém ela não vem completamente habilitada. Desta maneira é possível trabalhar com dois modos:

  • Mixed Mode: opção habilitada por padrão. Todas as funcionalidades de auditoria funcionam como antes da versão 12c, mas os novos recursos também estão disponíveis. Os dados de auditoria podem ser consultados tanto nas views como DBA_AUDIT_TRAIL ou DBA_FGA_AUDIT_TRAIL, já conhecidas até esta versão quanto na nova UNIFIED_AUDIT_TRAIL. Quando a auditoria de SYS está habilitada os logs continuam sendo gravados no diretório configurado através do parâmetro AUDIT_FILE_DEST.
  • Pure Mode: deve ser habilitado efetuando um relink do kernel e para tanto o banco de dados e demais componentes devem ser parados. Depois de habilitado o Pure, ou Full Mode como também é chamado, todas as configurações de auditoria anteriores à versão 12c são ignoradas e os dados auditados são armazenados utilizando o Oracle SecureFiles. Todos os dados de auditoria são encontrados consultando a view UNIFIED_AUDIT_TRAIL.

Exemplo da nova arquitetura apresentado em https://blogs.oracle.com/imc/entry/oracle_database_12c_new_unified:

Para saber se a Unified Auditing está habilitada execute a seguinte query:

SQL> col parameter for a30
SQL> col value for a10
SQL> select parameter, value from v$option where parameter='Unified Auditing';

PARAMETER                      VALUE
------------------------------ ----------
Unified Auditing               FALSE 

   

Constatamos que não está habilitada, pois o valor retornado foi FALSE. Isto significa que estamos utilizando o Mixed Mode, então agora vamos habilitá-la para usar o Pure Mode. Como já foi citado anteriormente temos que recompilar o Oracle Kernel e para isto temos que parar todos os componentes do Oracle. Neste exemplo o banco de dados está sobre um Standalone Server com Grid Infrastructure 12c.

[oracle@loredata-srv ~]$ lsnrctl status

LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 10-MAR-2016 22:44:34

Copyright (c) 1991, 2014, Oracle.  All rights reserved.

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 12.1.0.2.0 - Production
Start Date                10-MAR-2016 22:17:22
Uptime                    0 days 0 hr. 27 min. 12 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/12.1.0.2/grid/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/loredata-srv/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=loredata-srv.localdomain)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary...
Service "+ASM" has 1 instance(s).
Instance "+ASM", status READY, has 1 handler(s) for this service...
Service "loredb.localdomain" has 1 instance(s).
Instance "loredb", status READY, has 1 handler(s) for this service...
Service "mypdb.localdomain" has 1 instance(s).
Instance "loredb", status READY, has 1 handler(s) for this service...

The command completed successfully 
[oracle@loredata-srv ~]$ srvctl stop database -db loredb
[oracle@loredata-srv ~]$ srvctl status database -db loredb

O banco de dados não está em execução.
[oracle@loredata-srv ~]$ srvctl stop listener -listener listener
[oracle@loredata-srv ~]$ srvctl status listener
O Listener LISTENER está ativado O Listener LISTENER não está em execução

Não há a necessidade de parar a instancia do ASM, pois é outro binário, outro ORACLE_HOME.

Agora só precisamos compilar o kernel. Primeiro certifique suas variáveis de ambiente:

[oracle@loredata-srv ~]$ . oraenv ORACLE_SID = [loredb] ? loredb
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@loredata-srv ~]$ cd $ORACLE_HOME/rdbms/lib/
[oracle@loredata-srv lib]$ pwd
/u01/app/oracle/product/12.1.0.2/dbhome_1/rdbms/lib/

Então faça o relink com a opção uniaud_on (o retorno vai ser bastante bagunçado como apresentado abaixo):

[oracle@loredata-srv lib]$ make -f ins_rdbms.mk uniaud_on ioracle
/usr/bin/ar d /u01/app/oracle/product/12.1.0.2/dbhome_1/rdbms/lib/libknlopt.a kzanang.o

/usr/bin/ar cr /u01/app/oracle/product/12.1.0.2/dbhome_1/rdbms/lib/libknlopt.a 
/u01/app/oracle/product/12.1.0.2/dbhome_1/rdbms/lib/kzaiang.o

chmod 755 /u01/app/oracle/product/12.1.0.2/dbhome_1/bin

- Linking Oracle
rm -f /u01/app/oracle/product/12.1.0.2/dbhome_1/rdbms/lib/oracle
/u01/app/oracle/product/12.1.0.2/dbhome_1/bin/orald -o 
/u01/app/oracle/product/12.1.0.2/dbhome_1/rdbms/lib/oracle -m64 -z noexecstack -Wl,--disable-new-dtags -L
/u01/app/oracle/product/12.1.0.2/dbhome_1/rdbms/lib/ -L
/u01/app/oracle/product/12.1.0.2/dbhome_1/lib/ -L
/u01/app/oracle/product/12.1.0.2/dbhome_1/lib/stubs/ -Wl,-E 
/u01/app/oracle/product/12.1.0.2/dbhome_1/rdbms/lib/opimai.o 
/u01/app/oracle/product/12.1.0.2/dbhome_1/rdbms/lib/ssoraed.o 
/u01/app/oracle/product/12.1.0.2/dbhome_1/rdbms/lib/ttcsoi.o 
-Wl,--whole-archive -lperfsrv12 -Wl,--no-whole-archive /u01/app/oracle/product/12.1.0.2/dbhome_1/lib/nautab.o 
/u01/app/oracle/product/12.1.0.2/dbhome_1/lib/naeet.o 
/u01/app/oracle/product/12.1.0.2/dbhome_1/lib/naect.o 
/u01/app/oracle/product/12.1.0.2/dbhome_1/lib/naedhs.o /u01/app/oracle/product/12.1.0.2/dbhome_1/rdbms/lib/config.o 
-lserver12 -lodm12 -lcell12 -lnnet12 -lskgxp12 -lsnls12 -lnls12 -lcore12 -lsnls12 -lnls12 -lcore12 -lsnls12 -lnls12 
-lxml12 -lcore12 -lunls12 -lsnls12 -lnls12 -lcore12 -lnls12 -lclient12 -lvsn12 -lcommon12 -lgeneric12 -lknlopt 
`if /usr/bin/ar tv /u01/app/oracle/product/12.1.0.2/dbhome_1/rdbms/lib/libknlopt.a | grep xsyeolap.o > /dev/null 
2>&1 ; then echo "-loraolap12" ; fi` -lskjcx12 -lslax12 -lpls12 -lrt -lplp12 -lserver12 -lclient12 -lvsn12 
-lcommon12 -lgeneric12 `if [ -f /u01/app/oracle/product/12.1.0.2/dbhome_1/lib/libavserver12.a ] ; 
then echo "-lavserver12" ; else echo "-lavstub12"; fi` `if [ -f 
/u01/app/oracle/product/12.1.0.2/dbhome_1/lib/libavclient12.a ] ; 
then echo "-lavclient12" ; fi` -lknlopt -lslax12 -lpls12 -lrt 
-lplp12 -ljavavm12 -lserver12 -lwwg `cat /u01/app/oracle/product/12.1.0.2/dbhome_1/lib/ldflags` -lncrypt12 -lnsgr12 
-lnzjs12 -ln12 -lnl12 -lnro12 `cat /u01/app/oracle/product/12.1.0.2/dbhome_1/lib/ldflags` -lncrypt12 -lnsgr12 
-lnzjs12 -ln12 -lnl12 -lnnzst12 -lzt12 -lztkg12 -lmm -lsnls12 -lnls12 -lcore12 -lsnls12 -lnls12 -lcore12 -lsnls12 
-lnls12 -lxml12 -lcore12 -lunls12 -lsnls12 -lnls12 -lcore12 -lnls12 -lztkg12 `cat 
/u01/app/oracle/product/12.1.0.2/dbhome_1/lib/ldflags` -lncrypt12 -lnsgr12 -lnzjs12 -ln12 -lnl12 -lnro12 `cat 
/u01/app/oracle/product/12.1.0.2/dbhome_1/lib/ldflags` -lncrypt12 -lnsgr12 -lnzjs12 -ln12 -lnl12 -lnnzst12 -lzt12 
-lztkg12 -lsnls12 -lnls12 -lcore12 -lsnls12 -lnls12 -lcore12 -lsnls12 -lnls12 -lxml12 -lcore12 -lunls12 -lsnls12 
-lnls12 -lcore12 -lnls12 `if /usr/bin/ar tv 
/u01/app/oracle/product/12.1.0.2/dbhome_1/rdbms/lib/libknlopt.a | grep "kxmnsd.o" > 
/dev/null 2>&1 ; then echo " " ; else echo "-lordsdo12 -lserver12"; 
fi` -L/u01/app/oracle/product/12.1.0.2/dbhome_1/ctx/lib/ 
-lctxc12 -lctx12 -lzx12 -lgx12 -lctx12 -lzx12 -lgx12 -lordimt12 -lclsra12 -ldbcfg12 -lhasgen12 -lskgxn2 -lnnzst12 
-lzt12 -lxml12 -locr12 -locrb12 -locrutl12 -lhasgen12 -lskgxn2 -lnnzst12 -lzt12 -lxml12 -lgeneric12 -loraz -llzopro 
-lorabz2 -lipp_z -lipp_bz2 -lippdcemerged -lippsemerged -lippdcmerged -lippsmerged -lippcore -lippcpemerged 
-lippcpmerged -lsnls12 -lnls12 -lcore12 -lsnls12 -lnls12 -lcore12 -lsnls12 -lnls12 -lxml12 -lcore12 -lunls12 -lsnls12 
-lnls12 -lcore12 -lnls12 -lsnls12 -lunls12 -lsnls12 -lnls12 -lcore12 -lsnls12 -lnls12 -lcore12 -lsnls12 -lnls12 
-lxml12 -lcore12 -lunls12 -lsnls12 -lnls12 -lcore12 -lnls12 -lasmclnt12 -lcommon12 -lcore12 -laio -lons 
`cat /u01/app/oracle/product/12.1.0.2/dbhome_1/lib/sysliblist` -Wl,-rpath,
/u01/app/oracle/product/12.1.0.2/dbhome_1/lib -lm `cat /u01/app/oracle/product/12.1.0.2/dbhome_1/lib/sysliblist` 
-ldl -lm -L/u01/app/oracle/product/12.1.0.2/dbhome_1/lib
test ! -f /u01/app/oracle/product/12.1.0.2/dbhome_1/bin/oracle ||\
mv -f /u01/app/oracle/product/12.1.0.2/dbhome_1/bin/oracle /u01/app/oracle/product/12.1.0.2/dbhome_1/bin/oracleO
mv /u01/app/oracle/product/12.1.0.2/dbhome_1/rdbms/lib/oracle /u01/app/oracle/product/12.1.0.2/dbhome_1/bin/oracle
chmod 6751 /u01/app/oracle/product/12.1.0.2/dbhome_1/bin/oracle

Depois de compilado é só iniciar os componentes novamente:

[oracle@loredata-srv ~]$ srvctl start listener -listener listener
[oracle@loredata-srv ~]$ srvctl status listener -listener listener

O Listener LISTENER está ativado
O Listener LISTENER está em execução no(s) nó(s): loredata-srv

[oracle@loredata-srv ~]$ srvctl start database -db loredb
[oracle@loredata-srv ~]$ srvctl status database -db loredb

O banco de dados está em execução.

Vamos conferir se a alteração realmente surtiu efeito e habilitou a Unified Auditing:

SQL> col parameter for a30
SQL> col value for a10
SQL> select parameter, value from v$option where parameter='Unified Auditing';

PARAMETER                      VALUE
------------------------------ ----------
Unified Auditing               TRUE

É, pelo visto deu certo. Estamos definitivamente usando o Pure Mode. Vamos agora verificar algumas políticas existentes:

SQL> col policy_name for a50
SQL> select distinct policy_name from sys.audit_unified_policies order by 1;

POLICY_NAME
--------------------------------------------------
ORA_ACCOUNT_MGMT
ORA_CIS_RECOMMENDATIONS
ORA_DATABASE_PARAMETER
ORA_DV_AUDPOL
ORA_LOGON_FAILURES
ORA_RAS_POLICY_MGMT
ORA_RAS_SESSION_MGMT
ORA_SECURECONFIG

8 rows selected.

As seguintes opções de auditoria se fazem valer através da política ORA_SECURECONFIG:

SQL> col POLICY_NAME format A20
SQL> col AUDIT_OPTION format A40
SQL> set PAGES 100
SQL> select POLICY_NAME, AUDIT_OPTION
    from   AUDIT_UNIFIED_POLICIES
     where  policy_name =  'ORA_SECURECONFIG'  order by 2;

POLICY_NAME        AUDIT_OPTION
-------------------- -----------------------------------
ORA_SECURECONFIG      ADMINISTER KEY MANAGEMENT
ORA_SECURECONFIG     ALTER ANY PROCEDURE
ORA_SECURECONFIG     ALTER ANY SQL TRANSLATION PROFILE
ORA_SECURECONFIG     ALTER ANY TABLE
ORA_SECURECONFIG     ALTER DATABASE
ORA_SECURECONFIG     ALTER DATABASE LINK
ORA_SECURECONFIG     ALTER PLUGGABLE DATABASE
ORA_SECURECONFIG     ALTER PROFILE
ORA_SECURECONFIG     ALTER ROLE
ORA_SECURECONFIG     ALTER SYSTEM
ORA_SECURECONFIG     ALTER USER
ORA_SECURECONFIG     AUDIT SYSTEM
ORA_SECURECONFIG     CREATE ANY JOB
ORA_SECURECONFIG     CREATE ANY LIBRARY
ORA_SECURECONFIG     CREATE ANY PROCEDURE
ORA_SECURECONFIG     CREATE ANY SQL TRANSLATION PROFILE
ORA_SECURECONFIG     CREATE ANY TABLE
ORA_SECURECONFIG     CREATE DATABASE LINK
ORA_SECURECONFIG     CREATE DIRECTORY
ORA_SECURECONFIG     CREATE EXTERNAL JOB
ORA_SECURECONFIG     CREATE PLUGGABLE DATABASE
ORA_SECURECONFIG     CREATE PROFILE
ORA_SECURECONFIG     CREATE PUBLIC SYNONYM
ORA_SECURECONFIG     CREATE ROLE
ORA_SECURECONFIG     CREATE SQL TRANSLATION PROFILE
ORA_SECURECONFIG     CREATE USER
ORA_SECURECONFIG     DROP ANY PROCEDURE
ORA_SECURECONFIG     DROP ANY SQL TRANSLATION PROFILE
ORA_SECURECONFIG     DROP ANY TABLE
ORA_SECURECONFIG     DROP DATABASE LINK
ORA_SECURECONFIG     DROP DIRECTORY
ORA_SECURECONFIG     DROP PLUGGABLE DATABASE
ORA_SECURECONFIG     DROP PROFILE
ORA_SECURECONFIG     DROP PUBLIC SYNONYM
ORA_SECURECONFIG     DROP ROLE
ORA_SECURECONFIG     DROP USER
ORA_SECURECONFIG     EXECUTE
ORA_SECURECONFIG     EXEMPT ACCESS POLICY
ORA_SECURECONFIG     EXEMPT REDACTION POLICY
ORA_SECURECONFIG     GRANT ANY OBJECT PRIVILEGE
ORA_SECURECONFIG     GRANT ANY PRIVILEGE
ORA_SECURECONFIG     GRANT ANY ROLE
ORA_SECURECONFIG     LOGMINING
ORA_SECURECONFIG     PURGE DBA_RECYCLEBIN
ORA_SECURECONFIG     SET ROLE
ORA_SECURECONFIG     TRANSLATE ANY SQL

46 rows selected.

Para conferir as políticas já habilitadas vamos executar a seguinte query:

SQL> col user_name for a20
SQL> SELECT * FROM SYS.AUDIT_UNIFIED_ENABLED_POLICIES;

USER_NAME       POLICY_NAME                ENABLED_ SUC FAI
--------------- ------------------------------ -------- --- ---
ALL USERS       ORA_SECURECONFIG               BY       YES YES
ALL USERS       ORA_LOGON_FAILURES              BY       NO  YES

2 rows selected.

Por padrão os registros de auditoria são armazenados no schema AUDSYS que tem seus segmentos na tablespace SYSAUX:

SQL> select distinct owner, tablespace_name from dba_segments where owner='AUDSYS'

OWNER  TABLESPACE_NAME
------ ------------------------------
AUDSYS SYSAUX

1 row selected.

Vamos consultar o que já temos de informações até o momento consultando somente as primeiras 5 linhas:

SQL> set pages 200 lin 200
SQL> col object_name for a30
SQL> col action_name for a20
SQL> col object_schema for a10
SQL> col sql_text for a70
SQL> col EVENT_TIMESTAMP for a30
SQL> select OS_USERNAME, TERMINAL, DBUSERNAME, ACTION_NAME, OBJECT_SCHEMA,
OBJECT_NAME, EVENT_TIMESTAMP, AUDIT_OPTION 
from unified_audit_trail 
order by EVENT_TIMESTAMP 
fetch first 5 rows only;

OS_USERNAME   TERMINAL  DBUSERNAME  ACTION_NAME       OBJECT_SCH OBJECT_NAME EVENT_TIMESTAMP     AUDIT_OPTION
------------- --------- ----------- ---------------- ---------- ----------- ---------------------------- ------------
oracle      SYS      ALTER USER       SYSTEM        16-JAN-16 05.06.51.700546 PM
oracle      SYS      CREATE PLUGGABLE DATA  PDB$SEED    16-JAN-16 05.08.12.825891 PM ABASE
oracle      SYS      ALTER PLUGGABLE  DATA  PDB$SEED    16-JAN-16 05.08.24.888508 PM BASE
oracle      SYS      ALTER PLUGGABLE  DATA  PDB$SEED    16-JAN-16 05.10.42.029258 PM BASE
oracle      SYS      ALTER PLUGGABLE  DATA  PDB$SEED    16-JAN-16 05.11.42.806513 PM BASE

5 rows selected.

Vamos ver na sequência como criar essas políticas, mas antes gostaria de deixar uma dica…

Muitas políticas podem ser criadas no banco de dados, mas o melhor é limitar a quantidade de políticas habilitadas.

A sintaxe utilizada para criar as políticas da Unified Auditing nos permite criar uma política que cobre todas as configurações de auditoria que precisamos para o nosso banco de dados. Você pode criar poucas grandes políticas ao invés de muitas pequenas, isso facilita o gerenciamento e tem as seguintes vantagens:

• Reduz o overhead associado ao logon de usuários, pois cada política habilitada precisa ser carregada na UGA da sessão. Quanto menos políticas habilitadas menor é o tempo para carregar informações das mesmas;

• Consequentemente reduz também o consumo da área de memória UGA por sessão;

• Poucas políticas facilitam a checagem interna do Oracle para saber onde será gravada a trilha de auditoria.

A cláusula para criação das políticas é a seguinte:

CREATE AUDIT POLICY policy_name
   { {privilege_audit_clause [action_audit_clause ] [role_audit_clause ]}
   | { action_audit_clause  [role_audit_clause ] } 
   | { role_audit_clause }
   } 
[WHEN audit_condition EVALUATE PER {STATEMENT|SESSION|INSTANCE}] 
[CONTAINER = {CURRENT | ALL}];

Vamos ver um exemplo...

Se você ainda não está acostumado com os Pluggable Databases já aproveite para praticar neste exemplo.

Primeiro vou definir minha sessão atual para o MYPDB:

SQL> alter session set container=MYPDB;
Session altered.

Se o seu PDB ainda não estiver aberto execute o seguinte comando:

SQL> ALTER PLUGGABLE DATABASE OPEN;
Pluggable database altered.

Então crie o usuário e conceda privilégios a ele:

SQL> create user franky identified by oracle container=CURRENT;
User created.

SQL> grant create session, resource to franky;
Grant succeeded.

SQL> alter user franky quota unlimited on USERS;
User altered.

Vamos conectar com nosso novo usuário e criar uma tabela:

[oracle@loredata-srv ~]$ sqlplus franky/oracle@loredata-srv.localdomain:1521/mypdb.localdomain
SQL*Plus: Release 12.1.0.2.0 Production on Fri Mar 11 23:09:02 2016
Copyright (c) 1982, 2014, Oracle.  All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics,
Real Application Testing and Unified Auditing options

SQL> create table teste as select * from all_tables;
Table created.

Em outra sessão com o usuário SYS crie e habilite para todos os usuários a seguinte política:

SQL> CREATE AUDIT POLICY MYPDB_USERS_POL ACTIONS SELECT, INSERT, UPDATE, DELETE ON FRANKY.TESTE;
Audit policy created.

SQL> AUDIT POLICY MYPDB_USERS_POL;
Audit succeeded.

Vamos agora criar outro usuário para selecionar os dados da tabela TESTE.

Ainda na sessão do SYS crie este novo usuário:

SQL> create user JOHN identified by oracle container=current;
User created.

SQL> grant create session to john;
Grant succeeded.

SQL> grant all on franky.teste to john;
Grant succeeded.

Na outra sessão conecte-se com o usuário JOHN recém criado e consulte os dados da tabela TESTE.

[oracle@loredata-srv ~]$ sqlplus john/oracle@loredata-srv.localdomain:1521/mypdb.localdomain
SQL*Plus: Release 12.1.0.2.0 Production on Sat Mar 12 02:05:00 2016
Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics,
Real Application Testing and Unified Auditing options

SQL> select count(*) from franky.teste;

COUNT(*)
----------
101

Volte a sessão do SYS e consulte a UNIFIED_AUDIT_TRAIL para saber se a auditoria surtiu efeito.

SQL>  select UNIFIED_AUDIT_POLICIES, DBUSERNAME, ACTION_NAME,
SYSTEM_PRIVILEGE_USED,
to_char(EVENT_TIMESTAMP,'DD-MON-YY HH:MI') "DATE"
from unified_audit_trail
where  DBUSERNAME in ('JOHN')
order by 5;

UNIFIED_AUDIT_POLICIES DBUSERNA ACTION_N SYSTEM_PRIVILEGE_U DATE
---------------------- -------- -------- ------------------ ---------------
             JOHN     LOGON        12-MAR-16 01:49
           JOHN     LOGON        12-MAR-16 01:49
MYPDB_USERS_POL        JOHN    SELECT              12-MAR-16 01:50
MYPDB_USERS_POL        JOHN    SELECT              12-MAR-16 01:50
MYPDB_USERS_POL        JOHN    SELECT        12-MAR-16 01:50
MYPDB_USERS_POL        JOHN    SELECT        12-MAR-16 01:50
MYPDB_USERS_POL        JOHN    SELECT              12-MAR-16 01:50
MYPDB_USERS_POL        JOHN    SELECT        12-MAR-16 01:50
MYPDB_USERS_POL        JOHN    SELECT              12-MAR-16 01:51
MYPDB_USERS_POL        JOHN    SELECT              12-MAR-16 01:53
           JOHN     AUDIT            12-MAR-16 01:55
           JOHN     AUDIT            12-MAR-16 01:55
MYPDB_USERS_POL        JOHN    SELECT        12-MAR-16 01:56
MYPDB_USERS_POL        JOHN    SELECT        12-MAR-16 01:56
MYPDB_USERS_POL        JOHN    SELECT        12-MAR-16 02:00
MYPDB_USERS_POL        JOHN    SELECT        12-MAR-16 02:00
MYPDB_USERS_POL        JOHN    SELECT        12-MAR-16 02:00
MYPDB_USERS_POL        JOHN    SELECT        12-MAR-16 02:00
MYPDB_USERS_POL        JOHN    SELECT        12-MAR-16 02:00
MYPDB_USERS_POL        JOHN    SELECT        12-MAR-16 02:00
MYPDB_USERS_POL        JOHN    SELECT        12-MAR-16 02:00
MYPDB_USERS_POL        JOHN    SELECT        12-MAR-16 02:00
MYPDB_USERS_POL        JOHN    SELECT        12-MAR-16 02:00
MYPDB_USERS_POL        JOHN    SELECT        12-MAR-16 02:00
MYPDB_USERS_POL        JOHN    SELECT        12-MAR-16 02:00
MYPDB_USERS_POL        JOHN    SELECT        12-MAR-16 02:00
MYPDB_USERS_POL        JOHN    SELECT        12-MAR-16 02:00
MYPDB_USERS_POL        JOHN    SELECT        12-MAR-16 02:00
MYPDB_USERS_POL        JOHN    SELECT        12-MAR-16 02:00
MYPDB_USERS_POL        JOHN    SELECT        12-MAR-16 02:00
MYPDB_USERS_POL        JOHN    SELECT        12-MAR-16 02:00
MYPDB_USERS_POL        JOHN    SELECT        12-MAR-16 02:00
MYPDB_USERS_POL        JOHN    SELECT              12-MAR-16 02:00
MYPDB_USERS_POL        JOHN    SELECT        12-MAR-16 02:00
MYPDB_USERS_POL        JOHN    SELECT        13-MAR-16 10:04
MYPDB_USERS_POL        JOHN    SELECT        13-MAR-16 10:04
MYPDB_USERS_POL        JOHN    SELECT        13-MAR-16 10:04
MYPDB_USERS_POL        JOHN    SELECT        13-MAR-16 10:04
MYPDB_USERS_POL        JOHN    SELECT        13-MAR-16 10:04
MYPDB_USERS_POL        JOHN    SELECT        13-MAR-16 10:04
MYPDB_USERS_POL        JOHN    SELECT        13-MAR-16 10:04
MYPDB_USERS_POL        JOHN    SELECT        13-MAR-16 10:06
MYPDB_USERS_POL        JOHN    SELECT        13-MAR-16 10:07

43 rows selected.

Se os dados ainda estiverem em memória você deve executar a seguinte package para forçar a descarga dos dados para as tabelas:

SQL> EXEC SYS.DBMS_AUDIT_MGMT.FLUSH_UNIFIED_AUDIT_TRAIL
PL/SQL procedure successfully completed.

Para excluir a política que acabamos de utilizar primeiro temos que desabilitá-la e só então poderemos removê-la, vamos ver um exemplo:

SQL> drop audit policy MYPDB_USERS_POL;
drop audit policy MYPDB_USERS_POL
 *
ERROR at line 1:
ORA-46361: Audit policy cannot be dropped as it is currently enabled.

SQL> noaudit policy MYPDB_USERS_POL;
Noaudit succeeded.

SQL> drop audit policy MYPDB_USERS_POL;
Audit Policy dropped.

Para conferir se a política ainda existe podemos consultar novamente a view:

SQL> col policy_name for a30
SQL> select distinct policy_name from sys.audit_unified_policies order by 1;

POLICY_NAME
------------------------------
ORA_ACCOUNT_MGMT
ORA_CIS_RECOMMENDATIONS
ORA_DATABASE_PARAMETER
ORA_DV_AUDPOL
ORA_LOGON_FAILURES
ORA_RAS_POLICY_MGMT
ORA_RAS_SESSION_MGMT
ORA_SECURECONFIG

8 rows selected.

Chegou a hora de fazermos uma limpeza nas trilhas de auditoria. Para isso vamos ver quantos registros auditados já temos:

SQL> select count(*) from unified_audit_trail;

COUNT(*)
-----------
519916

Para efetuar a limpeza primeiro vamos arquivar os últimos registros e depois efetuar o purge:

SQL> exec DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP ( -
AUDIT_TRAIL_TYPE  => DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED, -
LAST_ARCHIVE_TIME => sysdate) 

PL/SQL procedure successfully completed. 
   
SQL> exec DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL( -
AUDIT_TRAIL_TYPE        => DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED, - 
USE_LAST_ARCH_TIMESTAMP => TRUE)
   
PL/SQL procedure successfully completed.

Vamos verificar se os registros foram realmente removidos:

SQL> select count(*) from unified_audit_trail;

COUNT(*)
-----------
519473

Podemos ver que poucos registros foram removidos, isso se deve ao uso da opção USE_LAST_ARCH_TIMESTAMP como TRUE, o que garante os registros salvos depois do último arquivamento.

A documentação explica:

USE_LAST_ARCH_TIMESTAMP accepts either of the following settings:

• TRUE deletes audit records created before the last archive timestamp. To check the last recorded timestamp, query the LAST_ARCHIVE_TS column of the DBA_AUDIT_MGMT_LAST_ARCH_TS data dictionary view for read-write databases and the

DBMS_AUDIT_MGMT.GET_LAST_ARCHIVE_TIMESTAMP function for read-only databases. The default value is TRUE. Oracle recommends that you set USE_LAST_ARCH_TIMESTAMP to TRUE.

• FALSE deletes all audit records without considering last archive timestamp. Be careful about using this setting, in case you inadvertently delete audit records that should have been deleted.

Se fizermos com esta opção como FALSE vamos ver realmente uma remoção das linhas:

SQL> exec DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL( -
AUDIT_TRAIL_TYPE        => DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED, -
USE_LAST_ARCH_TIMESTAMP => FALSE)

PL/SQL procedure successfully completed.

SQL> select count(*) from unified_audit_trail;

COUNT(*)
----------
20

Quando utilizamos a Standard Auditing os registros de auditoria são gravados diretamente nas tabelas AUD$ e FGA_LOG$. Por esse motivo auditoria sempre esteve relacionada a perda de desempenho no Oracle, porém na versão 12c o Oracle Database usa uma área de memória da SGA onde os registros são armazenados temporariamente numa espécie de fila e quando esta área estiver cheia acontece o flush (descarga) para as tabelas através do processo GEN0. É uma arquitetura similar a do log buffer. Nesta nova arquitetura existe a possiblidade de perda de registros caso haja a queda da instância, pois podem existir registros em memória que não foram descarregados para as tabelas. Se a auditoria for mais importante do que o desempenho podemos utilizar a Unified Auditing com gravação imediata e desta maneira não teremos perda.

Vamos ver como habilitar a gravação imediata, observe a procedure AUDIT_TRAIL_IMMEDIATE_WRITE:

SQL> EXEC DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_PROPERTY(-
DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED,-
DBMS_AUDIT_MGMT.AUDIT_TRAIL_WRITE_MODE,-
DBMS_AUDIT_MGMT.AUDIT_TRAIL_IMMEDIATE_WRITE);

PL/SQL procedure successfully completed.

Para habilitar novamente o uso da SGA para enfileiramento, observe a procedure AUDIT_TRAIL_QUEUED_WRITE:

SQL> EXEC DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_PROPERTY(-
DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED,-
DBMS_AUDIT_MGMT.AUDIT_TRAIL_WRITE_MODE,-
DBMS_AUDIT_MGMT.AUDIT_TRAIL_QUEUED_WRITE);

PL/SQL procedure successfully completed.

TESTES DE DESEMPENHO

Utilizei o Swingbench para demonstrar o desempenho dos métodos de auditoria.

Sem qualquer tipo de auditoria (em mixed mode):

Figure 1 - 10 usuários, média de 7153 TPM, tempo médio de reposta 11ms.

Standard Auditing - Auditoria habilitada para todas as operações DML do usuário SOE:

SQL> alter session set container=mypdb;
Session altered.

SQL> AUDIT ALL STATEMENTS BY soe BY ACCESS;
Audit succeeded.

Figure 2 - 10 usuários, média de 7028 TPM, tempo médio de resposta 13ms. 

Unified Auditing - Política habilitada para todas as operações DML do usuário SOE e em Queued Write:

SQL> CREATE AUDIT POLICY SOE_POL
ACTIONS DELETE, INSERT, UPDATE, SELECT
 WHEN 'SYS_CONTEXT(''USERENV'', ''SESSION_USER'') = ''SOE'''
EVALUATE PER STATEMENT
CONTAINER = ALL;
Audit policy created.

SQL> AUDIT POLICY SOE_POL;
Audit succeeded.

Figure 3 - 10 usuários, média de 7499 TPM, tempo médio de reposta 7ms.

Unified Auditing - Política habilitada para todas as operações DML do usuário SOE em Immediate Write:

SQL> EXEC DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_PROPERTY(-
DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED,-
DBMS_AUDIT_MGMT.AUDIT_TRAIL_WRITE_MODE,-
DBMS_AUDIT_MGMT.AUDIT_TRAIL_IMMEDIATE_WRITE);
PL/SQL procedure successfully completed.

Figure 4 - 10 usuários, média de 7326 TMP, tempo médio de resposta 7ms.

O Swingbench foi utilizado com todos os parâmetros padrões e 10 usuários conectados no mesmo banco de dados e com as mesmas condições durante aproximadamente 10 minutos onde tirei a média de transações do período. Após cada execução fiz logout e login novamente através da ferramenta.

Sumarizado:

Tipo de auditoria

Tempo de resposta (ms)

Média de transações por minuto (10 usuários)

Duração do teste

Sem auditoria (banco em mixed mode)

11

7153

10 minutos

Standard Auditing

13

7028

10 minutos

Unified Auditing com queued write

7

7499

10 minutos

Unified Auditing com immediate write

7

7326

10 minutos

O Oracle Database 12c tem vários recursos novos, nós vimos um deles que é a Unified Auditing. Aprendemos que podemos criar políticas de auditoria para auditarmos o que acharmos necessário sem prejudicar o desempenho do banco de dados e também consultar esses dados em uma view que centraliza tudo. Ainda conseguimos auditar as mesmas operações assim como em versões anteriores à 12c, mas agora com mais benefícios. Durante os testes não foi possível verificar com precisão o ganho de desempenho, pois o ambiente não estava sobrecarregado e a concorrência de usuários era baixa, mas mesmo assim foi possível identificar um bom ganho em relação à arquitetura anterior à versão 12c.

Infraestrutura utilizada nos testes:

Host

Processador: Intel® Core™ i7 3610QM

Memória RAM: Kingston HyperX Impact Black 16GB 1600MHz DDR3L

SSD: Kingston V300 240GB

S.O.: Oracle Enterprise Linux 6.6 x86_64

Guest

Hypervisor: Oracle VM VirtualBox 5.0.16

Processadores: 4 CPUs

Memória RAM: 4GB

S.O.: Oracle Enterprise Linux 6.6 x86_64

Softwares Oracle: Oracle Grid Infrastructure 12.1.0.2, Oracle Database 12.1.0.2 Enterprise Edition e Oracle ASMLib 2.0.4

Observações:

• Foi coletado estatísticas de todo o banco de dados utilizando o padrão da procedure gather_database_stats.

• Foi coletado estatísticas de sistema somente em noworkload.

• Foi criado um diskgroup CONFIG para os arquivos do Grid Infrastructure.

• O banco de dados loredb estava armazenado exclusivamente no diskgroup DATA.

• Havia 5 grupos de redolog files com 500MB cada um para evitar contenção.

• O parâmetro sga_target estava definido com 768M e o pga_aggregate_target com 256M.

• Para cada média tirada nos testes o Swingbench ficou 10 minutos em execução com 10 usuários.

Franky Weber Faust; atua como administrador de banco de dados Oracle e MySQL no PagSeguro, tem 26 anos, é graduado em Tecnologia em Bancos de Dados e iniciou sua carreira trabalhando num projeto internacional da Volkswagen com os bancos de dados DB2 da IBM, SQL Server da Microsoft e também com o Oracle e desde o início direcionou seus estudos para as tecnologias Oracle. É especialista em tecnologias de Alta Disponibilidade como RAC, Dataguard e GoldenGate e compartilha seus conhecimentos no blog loredata.com.br. Possui as certificações OCE SQL, OCA 11g, OCP 12c, OCS RAC 12c e OCS Linux 6.

Este artigo foi revisto pela equipe de produtos Oracle e está em conformidade com as normas e práticas para o uso de produtos Oracle.