Auditando DDL sem audit_trail com apenas um parâmetro
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 1048576Vou 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 TRUECrio 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/logDentro 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/ddlLá 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_objectsOs 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 VIEWO 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!
