No Oracle Database 11g foi introduzido o parâmetro enable_ddl_logging onde habilita a auditoria de comandos DDL no banco de dados. O seu valor default é FALSE e para habilitar basta seta-lo para TRUE. Não é necessário utilizar a auditoria do banco de dados para isso ou realizar stop/start.
[oracle@liverpool variaveis orcl12c]$s SQL*Plus: Release 12.1.0.2.0 Production on Tue Jan 19 15:58:42 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, OLAP, Advanced Analytics and Real Application Testing options SQL> show parameter ddl NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ ddl_lock_timeout integer 0 enable_ddl_logging boolean FALSE SQL> show parameter audit NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ audit_file_dest string /u01/app/oracle/admin/orcl12c/ adump audit_sys_operations boolean TRUE audit_syslog_level string audit_trail string DB unified_audit_sga_queue_size integer 1048576
Vou desligar a auditoria que está como DB.
SQL> alter system set audit_trail='none' scope=spfile; System altered. SQL> shut immediate; Database closed. Database dismounted. ORACLE instance shut down. SQL> startup ORACLE instance started. Total System Global Area 549453824 bytes Fixed Size 2926616 bytes Variable Size 469764072 bytes Database Buffers 71303168 bytes Redo Buffers 5459968 bytes Database mounted. Database opened. SQL> show parameter audit NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ audit_file_dest string /u01/app/oracle/admin/orcl12c/ adump audit_sys_operations boolean TRUE audit_syslog_level string audit_trail string NONE unified_audit_sga_queue_size integer 1048576 SQL> show parameter ddl NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ ddl_lock_timeout integer 0 enable_ddl_logging boolean FALSE SQL> alter system set enable_ddl_logging=TRUE scope=spfile; System altered. SQL> show parameter audit NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ audit_file_dest string /u01/app/oracle/admin/orcl12c/ adump audit_sys_operations boolean TRUE audit_syslog_level string audit_trail string NONE unified_audit_sga_queue_size integer 1048576 SQL> show parameter ddl NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ ddl_lock_timeout integer 0 enable_ddl_logging boolean TRUE
Crio um usuário para teste e dou grant de DBA para ele (o grant de DBA é apenas para poder ver objetos de DBA, não é necessário esse nível de permissão)
SQL> create user gabriel identified by gabriel; User created. SQL> grant dba to gabriel; Grant succeeded. SQL> conn gabriel/gabriel Connected. SQL> create table t1 as select * from all_objects; Table created.
A grande diferença da versão 12c para a 11g é que na versão 12c o arquivo com o conteúdo dos comandos DDL que estão sendo auditado é um arquivo XML, na versão 11g ele ficava no alert log.
SQL> show parameter background_dump_dest NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ background_dump_dest string /u01/app/oracle/product/12.1.0 .2/dbhome_1/rdbms/log
Dentro do backuground_dump_dest existe um novo diretório dentro do diretório log chamada ddl.
[oracle@liverpool ddl orcl12c]$pwd /u01/app/oracle/diag/rdbms/orcl12c/orcl12c/log/ddl
Lá está o arquivo de auditoria
[oracle@liverpool ddl orcl12c]$ls -ltrh total 4.0K -rw-r-----. 1 oracle oinstall 542 Jan 19 16:07 log.xml [oracle@liverpool ddl orcl12c]$cat log.xml <msg time='2016-01-19T16:06:34.691-02:00' org_id='oracle' comp_id='rdbms' msg_id='opiexe:4383:2946163730' type='UNKNOWN' group='diag_adl' level='16' host_id='liverpool.localdomain' host_addr='192.168.56.120' version='1'> <txt>ALTER DATABASE OPEN </txt> </msg> <msg time='2016-01-19T16:07:45.883-02:00' org_id='oracle' comp_id='rdbms' msg_id='opiexe:4383:2946163730' type='UNKNOWN' group='diag_adl' level='16' host_id='liverpool.localdomain' host_addr='192.168.56.120'> <txt>create table t1 as select * from all_objects </txt> </msg>
No 11G o procedimento é o mesmo:
[oracle@liverpool ddl orcl11g]$s SQL*Plus: Release 11.2.0.4.0 Production on Tue Jan 19 16:18:56 2016 Copyright (c) 1982, 2013, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> show parameter audit NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ audit_file_dest string /u01/app/oracle/admin/orcl11g/ adump audit_sys_operations boolean FALSE audit_syslog_level string audit_trail string DB SQL> alter system set audit_trail='none' scope=spfile; System altered. SQL> show parameter ddl NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ ddl_lock_timeout integer 0 enable_ddl_logging boolean FALSE SQL> alter system set enable_ddl_logging=TRUE; System altered. SQL> shut immediate; Database closed. Database dismounted. ORACLE instance shut down. SQL> startup ORACLE instance started. Total System Global Area 546992128 bytes Fixed Size 2255072 bytes Variable Size 390072096 bytes Database Buffers 150994944 bytes Redo Buffers 3670016 bytes Database mounted. Database opened. SQL> create table t2 as select * from all_objects; Table created. SQL> exit Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options [oracle@liverpool trace orcl11g]$tail -2 alert_orcl11g.log Tue Jan 19 16:23:29 2016 create table t2 as select * from all_objects
Os comandos auditados são:
ALTER/CREATE/DROP VIEW ALTER/CREATE/DROP/TRUNCATE CLUSTER ALTER/CREATE/DROP FUNCTION ALTER/CREATE/DROP INDEX ALTER/CREATE/DROP OUTLINE ALTER/CREATE/DROP PACKAGE ALTER/CREATE/DROP PACKAGE BODY ALTER/CREATE/DROP PROCEDURE ALTER/CREATE/DROP PROFILE ALTER/CREATE/DROP SEQUENCE CREATE/DROP SYNONYM ALTER/CREATE/DROP/RENAME/TRUNCATE TABLE ALTER/CREATE/DROP TRIGGER ALTER/CREATE/DROP TYPE ALTER/CREATE/DROP TYPE BODY DROP USER ALTER/CREATE/DROP VIEW
O nível de detalhes não é profundo, mas já ajuda a saber se houve ou não alteração em objetos no banco de dados.
Espero ter ajudado, até logo!
Gabriel Comenale, formado em banco de dados pela FIAP possui 6 anos de experiência na área de técnologia trabalhando com ambientes de pequeno, médio e grande porte nas plataformas Linux e Windows de segmentos variados de clientes como Odebrecht, Livraria Cultura, AES Eletropaulo, Renner, Sindicato dos Comerciários, Tigre – Tubos e Conexões, Unimed Seguros, Fidelity Investments, Alelo entre outros. Possui as certificações OCA 11g e ITIL V3 além de já ter estudado no Canadá e ministrado treinamentos em banco de dados Oracle. Atualmente trabalha como DBA Oracle na TIVIT.
