A small but remarkable change in Oracle Database 12c is the default value of AUDIT_SYS_OPERATIONS has changed to TRUE now. In other words, all actions done by the superuser sys are being audited now by default!
[oracle@uhesse ~]$ sqlplus / as sysdba SQL*Plus: Release 12.1.0.2.0 Production on Fri Jul 24 15:23:10 2015 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> select name,value from v$spparameter where isspecified='TRUE'; NAME VALUE ---------------------------------------- -------------------------------------------------- memory_target 1073741824 control_files /u01/app/oracle/oradata/prima/control01.ctl db_block_size 8192 compatible 12.1.0.2 db_recovery_file_dest /u02/fra db_recovery_file_dest_size 2147483648 undo_management auto undo_tablespace undotbs1 remote_login_passwordfile exclusive db_name prima diagnostic_dest /u01/app/oracle 11 rows selected. SQL> show parameter sys_oper NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ audit_sys_operations boolean TRUE SQL> select count(*) from scott.dept; COUNT(*) ---------- 4 SQL> show parameter audit_file NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ audit_file_dest string /u01/app/oracle/product/12.1.0 /dbhome_1/rdbms/audit SQL> exit Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options [oracle@uhesse ~]$ cd /u01/app/oracle/product/12.1.0/dbhome_1/rdbms/audit [oracle@uhesse audit]$ cat prima_ora_6204_20150724152310753136143795.aud Audit file /u01/app/oracle/product/12.1.0/dbhome_1/rdbms/audit/prima_ora_6204_20150724152310753136143795.aud Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options ORACLE_HOME = /u01/app/oracle/product/12.1.0/dbhome_1 System name: Linux Node name: uhesse Release: 3.8.13-68.2.2.el7uek.x86_64 Version: #2 SMP Tue May 12 14:38:58 PDT 2015 Machine: x86_64 Instance name: prima Redo thread mounted by this instance: 1 Oracle process number: 41 Unix process pid: 6204, image: oracle@uhesse (TNS V1-V3) Fri Jul 24 15:23:10 2015 +02:00 LENGTH : '160' ACTION :[7] 'CONNECT' DATABASE USER:[1] '/' PRIVILEGE :[6] 'SYSDBA' CLIENT USER:[6] 'oracle' CLIENT TERMINAL:[5] 'pts/1' STATUS:[1] '0' DBID:[10] '2113606181' [Output shortened...] Fri Jul 24 15:23:56 2015 +02:00 LENGTH : '185' ACTION :[31] 'select count(*) from scott.dept' DATABASE USER:[1] '/' PRIVILEGE :[6] 'SYSDBA' CLIENT USER:[6] 'oracle' CLIENT TERMINAL:[5] 'pts/1' STATUS:[1] '0' DBID:[10] '2113606181'
Something you might need to know as a DBA, don’t you think? 🙂
#1 von Dave Dawson am Juli 24, 2015 - 19:24
Hi Uwe
Is this dependant on how the database is created as when I was creating a custom database using dbca on 12.1.0.2 today the parameter was set to false when I checked the Initialisation Parameters in the GUI.
So I set this to true before commencing the database creation but one down side of this was the huge amount of sys audit files created during the database creation which aren’t really of much use.
So a better approach may be to set this to true only after DB creation is completed?
Thanks
Dave
#2 von Uwe Hesse am Juli 28, 2015 - 08:54
Dave, when I say DEFAULT, I refer to what is used if nothing is specified explicitly. I do not mean what is used by GUIs like the DBCA. It makes perfect sense to have AUDIT_SYS_OPERATIONS=FALSE upon database creation as you pointed out correctly. Actually, I consider it questionable to turn it to TRUE afterwards, but that is something you need to decide at your site. All I wanted was to highlight that the default has changed here, I did not want to give a recommendation. My database was created on the command line, by the way, and I was quite surprised to learn that the parameter was turned on 🙂