AUDIT_SYS_OPERATIONS and top-level operation

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
total 2341264
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
8.5G    .

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
8184

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
8182

Expected feature

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.

So what?

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.