By Franck Pachot
SYSDBA is powerful. You should use it only when you need to. And you should audit what is done when connected SYSDBA. This is achieved by AUDIT_SYS_OPERATION. But do you have an idea how much audit log will be generated? Note that in 12c, AUDIT_SYS_OPERATION=true is the default.
The documentation says:
AUDIT_SYS_OPERATIONS enables or disables the auditing of top-level operations, which are SQL statements directly issued by users when connecting with the SYSASM, SYSBACKUP, SYSDBA, SYSDG, SYSKM, or SYSOPER privileges. (SQL statements run from within PL/SQL procedures or functions are not considered top-level.)
From the documentation, you can expect that only the statements that are issued by a SYSDBA user call are logged.
Not the recursive statements.
Not the scheduler jobs.
However, this is not what happens. Look at the following:
On Windows, the SYS Audit goes to the Event log and it is full of those kind of statements. Those are no statements that I’ce issued when connected as SYSDBA. Those are recursive statements from the automatic statistic gathering job that run everyday. Do I want to audit that? probably not. But it fills my system log.
On Unix, this is not better. Here is a database created a few days ago with nearly no activity since then:
[oracle@CDB adump]$ ls -alrt | head
drwxr-xr-x 5 oracle oinstall 4096 Jul 29 16:12 ..
-rw-r----- 1 oracle oinstall 2699 Aug 19 03:15 CDB_m000_10396_20160819031532925620143795.aud
-rw-r----- 1 oracle oinstall 1150 Aug 19 03:15 CDB_ora_10402_20160819031534799868143795.aud
-rw-r----- 1 oracle oinstall 1134 Aug 19 03:15 CDB_ora_10404_20160819031535178281143795.aud
-rw-r----- 1 oracle oinstallcat 1131 Aug 19 03:15 CDB_ora_10406_20160819031535558089143795.aud
-rw-r----- 1 oracle oinstall 1139 Aug 19 03:15 CDB_ora_10408_20160819031535936355143795.aud
-rw-r----- 1 oracle oinstall 1156 Aug 19 03:15 CDB_ora_10410_20160819031536306359143795.aud
-rw-r----- 1 oracle oinstall 1165 Aug 19 03:15 CDB_ora_10412_20160819031536679750143795.aud
[oracle@CDB adump]$ ls -alrt | tail
-rw-r----- 1 oracle oinstall 1243 Aug 23 08:31 CDB_ora_9044_20160823083113966954143795.aud
-rw-r----- 1 oracle oinstall 1243 Aug 23 08:31 CDB_ora_9047_20160823083114497136143795.aud
-rw-r----- 1 oracle oinstall 1243 Aug 23 08:31 CDB_ora_9048_20160823083114567197143795.aud
-rw-r----- 1 oracle oinstall 1243 Aug 23 08:31 CDB_ora_9049_20160823083115041317143795.aud
-rw-r----- 1 oracle oinstall 1243 Aug 23 08:31 CDB_ora_9050_20160823083115311603143795.aud
-rw-r----- 1 oracle oinstall 54568 Aug 23 08:31 CDB_ora_9036_20160823083113459749143795.aud
-rw-r----- 1 oracle oinstall 1243 Aug 23 08:31 CDB_ora_9052_20160823083115922822143795.aud
-rw-r----- 1 oracle oinstall 54568 Aug 23 08:31 CDB_ora_9045_20160823083114267635143795.aud
drwxr-xr-x 5 oracle oinstall 64274432 Aug 23 08:31 .
[oracle@CDB adump]$ du -hs
Yes, this is two million files. 8GB of text. What will you do with that?
That’s the problem with auditing. It’s activated by default to comply with security policies, but there’s nothing to purge or archive them by default. Size grows and you cannot do anything relevant with it.
If I look at a subset of files:
[oracle@CDB adump]$ ls CDB_ora_*_20160823*aud | wc -l
Only two of them are from user connections. The others have no terminal:
[oracle@CDB adump]$ grep -l "^CLIENT TERMINAL:\[0\]" CDB_ora_*_20160823*aud | wc -l
It’s not a bug. Oracle has decided to log everything in the SYS audit. Because if they don’t, a SYSDBA connection can run a procedure or a job that looks harmless when only the top-level call is logged. That procedure may be created by any user and so do not appear in the audit trail.
Whatever the default is, the audit trail is something to manage. On Windows, the event log has a maximum size. On Linux, it fills the adump directory (or syslog if you enabled it). So you should have housekeeping jobs. You can also disable the audit so only connections are logged, but not the statements. In this case you should control who has access to SYSDBA.