Unified Auditing was introduced with Oracle 12c 12.1.0.1 and one idea is to combine standard auditing (AUD$) with fine grained auditing (FGA_LOG$). AUD$ and FGA_LOG$ still exists and if you like you can use your existing and well accepted auditing procedures. Some documents stated that the same auditing which was introduced with Oracle 11g is still running but that’s not one hundred percent true. If you don’t change the standard database setup the “old” AUD$ table exist but will stay empty in contrast to 11g. A new partitioned table with a somewhat cryptic name (CLI_SWP$2dc5e8db$1$1) is populated in the schema AUDSYS and with the view unified_audit_trail you can query the audit information.
Many blogs are available talking about how to enable unified auditing by relinking the kernel. But actually that’s not necessary and even more some features like single column auding are not yet available with unified auditing but with fine grained auditing. So my approach is not to change the kernel but leave it as it is so I can use both methods simultaneously.
Fine grained auditing relies on “auditing policies” and you can have a look at the existing ones using Toad (guess since 12.0).
On the left hand you can see the pre created auditing policies as the come with 12c. On the right hand the first 6 lines are some descriptions and starting with line 7 you see actions to be audited. The first action type is an “object action” which says: execute on the procedure sys.dbms_rls will be audited. The next 16 actions are “Standard Actions”. So for example every usage of the command “ALTER DATABASE LINK” will be audited. At the bottom you see “System Privilege” actions. Those actions are audited if a database user executes a command due to one of these privileges.
The “Object Actions” and “Standard Actions” are easy to understand. But what is the difference between a “Standard Action” and “System Privilege”?
With this screenshot I used a filter (“Value LIKE %USER%) on the value of the policy info. As you can see there are three actions:
- – Standard Action ALTER USER: whenever a user executes “ALTER” user one audit record will be created.
- – System Privilege CREATE USER: whenever a user how has granted the privilege CREATE USER is executing the command one audit record will be created.
- – System Privilege DROP USER: same as the above.
For an ordinary user there is no difference in these three actions. Every time he’s executing CREATE, ALTER, or DROP USER a new audit record will be created. But if SYS is executing CREATE, ALTER, or DROP only the ALTER USER will be recorded as SYS does not have the privilege CREATE USER granted but is the owner of that privilege.
This is not a theoretical example but reality as the ORA_SECURECONFIG policy is enabled per default. It took me some time to find out why a CREATE USER hasn’t been recorded while an ALTER user was. Now I know!
So how can you find out which audit policies are being used. The tab “Policy Enablement” gives you some more information. One or more audit policies can be enabled though being active.
With the enablement you can add some values and the above example shows that the policy “ORA_LOGON_FAILURES” which by the way is a default as well is enabled “WHENEVER NOT SUCCESSFUL”. So in contrast to Oracle 11g where every logon and logoff was recorded with Oracle 12c only a failed logon will be recorded per default. If there is no “WHENEVER” clause (like with ORA_SECURECONFIG) a record will be created regardless if the command was successful or not.
Oracle 12c will create audit trails for several actions per default. But I must admit that I have no idea why they took these actions (e.g. record every execution of ALTER USER but record CREATE USER and DROP USER only if it isn’t SYS). So my approach is the following (I won’t call it best practice as I’ve only used it for one customer yet):
- Create a policy for logons. So like the ORA_LOGON_FAILURES but record fail and successful logins.
- Create a policy for critical DBA commands like CREATE, ALTER, DROP TABLESPACE, etc.
- Create a policy for all DDL commands not in the two above.
These three policies can be enabled or disabled as needed. For example policy 1 will be enabled for all databases. Policy two only databases where the DBA is responsible – dear DBA: don’t see this as a limitation or missing trust. It’s a safe guard for you because normally YOU are blamed if something goes wrong. And the third policy will be enabled for all preproduction, UAT (User Acceptance Testing) and production databases.
I would – as always – appreciate comments and hints.
And dear Toad PMs: It will be nice if you can see immediately which policies are enabled (a column to the left hand side should be sufficient).