Don’t trust our slides – only believe what you’ve verified by yourself 😉
Actually one of our slides gives a parameter recommendation to set AUDIT_TRAIL since Oracle 11g explicitly to the value you want as otherwise it may switch to “DB” and you may not be aware of it. In conjunction with this setting we explain the new Oracle Database 12c feature Unified Auditing – which is not linked into the kernel and therefore should be off.
Should be … well … thanks to Marco Patzwahl who asked me why he still has over 100 audit records in V$UNIFIED_AUDIT_TRAIL? Good question – and I’ve had no answer. But Carol, my manager, knew the right person to ask. And Naveen replied within minutes (thanks!!!).
Here are the facts:
- Unified Auditing is not linked into the Oracle 12c kernel by default to offer people the choice to use it and to avoid conflicts in case somebody has auditing ON already – so neither during an upgrade nor with a fresh 12c database you’ll see it included into the kernel. It will have to be linked in manually (see our slides)
- Check if Unified Auditing is present in your environment:
SQL> select VALUE from V$OPTION where
PARAMETER=’Unified Auditing’; - In case you’ll link it into the kernel
cd $ORACLE_HOME/rdbms/lib
make -f ins_rdbms.mk uniaud_on ioracle
ORACLE_HOME=$ORACLE_HOME
make sure you set AUDIT_TRAIL=NONE afterwards as otherwise both auditing mechanisms will run concurrently
- Check if Unified Auditing is present in your environment:
- But even though it is not linked into the kernel a bit of Unified Auditing is ON by default in MIXED MODE when you create a fresh Oracle 12c database.
- MIXED MODE auditing?
- See the documentation for further information
- Just two policies are enabled by default: ORA_SECURECONFIG and ORA_LOGON_FAILURES
- MIXED MODE auditing?
- Turn Unfiied Auditing OFF?
- If is has been linked in into the kernel, unlink it:
cd $ORACLE_HOME/rdbms/lib
make -f ins_rdbms.mk uniaud_off ioracle ORACLE_HOME=$ORACLE_HOME - Disable the two default policies – this will turn off any Unified Auditing features:
SQL> noaudit policy ORA_SECURECONFIG;
Noaudit succeeded.
SQL> noaudit policy ORA_LOGON_FAILURES;
Noaudit succeeded.
- If is has been linked in into the kernel, unlink it:
Update – 4-MAR-2016 – thanks to Marco Patzwahl, MuniQsoft:
- If parameter audit_trail=NONE and select VALUE from V$OPTION where PARAMETER=’Unified Auditing’ == FALSE then no Unified Audit Trail will be written
- If select VALUE from V$OPTION where PARAMETER=’Unified Auditing’ == TRUE then the parameter audit_trail has no meaning anymore
–Mike
Hi Mike,
The database was hung up when we execute the following command:
SQL> noaudit policy ORA_SECURECONFIG;
Should I mount the database rather than open it to execute that command?
Regards
Leo
Leo,
the database shouldn’t hang when you disable a policy.
Did the alert.log say anything while it was hanging?
How long did the hang take.
And clear NO, you don’t need to take the database in upgrade mode to switch on or off an auditing policy.
Thanks
Mike
Hello:
How can I move the "AUDSYS"."CLI_SWP—- Table to another tablespace ?
Best Regards
Juan
Juan,
MOS is your friend 😉
Oracle Support Document 1328239.1 (How To Move The DB Audit Trails To A New Tablespace Using DBMS_AUDIT_MGMT?) can be found at: https://support.oracle.com/epmos/faces/DocumentDisplay?id=1328239.1
Cheers
Mike
MOS is realy friend but theres a catch
Moving CLI.SWP possible only for Enterprise edition, for standard impossible due partitioning.
second joke: stoping or deleting records from unified_audit_trail doesnt release allocated table and LOB space, advice from Oracle recreate database and beware of uncontrolled growing unified_audit_trail table 🙂
Hi Mike,
I have 2 oracle 12c databases, exactly the same pameters for AUDIT, but in on of them has the table sys.aud$ no data, in other DB over 32 mili. records:
How can i finde the reason(s), why the table sys.aud$ has no records?
Cheers,
Lucas
SQL> show parameter audit
NAME TYPE VALUE
———————————— ———– ——————————
audit_file_dest string /u02/app/oracle/admin/NVI/adump
audit_sys_operations boolean TRUE
audit_syslog_level string
audit_trail string DB
unified_audit_sga_queue_size integer 1048576
SQL> select count(*) from sys.aud$;
COUNT(*)
———-
0
SQL>
Hi Lucas,
audit_trail=db and therefore your database is writing audit records. It could now be that somebody did (and does) truncate the AUD$ table on a regular basis in one DB – and this does not happen in the other DB. TRUNCATE is a very common way to get rid of audit records – and does not generate finger prints 😉
You may check with the various DBA_AUDIT% views if there are other things defined in addition.
To change audit_trail (for instance to "NONE") you’ll have to restart the database unfortunately.
Cheers
Mike
Hi Mike,
If unified_audit_tail is enabled and audit_trail parameter is OS, what ocurr. The audit records are save also in OS?
Regards
Hi Mike,
If unified_audit_tail is enabled and audit_trail parameter is OS, what ocurr. The audit records are save also in OS?
Regards
Hey Mike – I am running 12.1.0.2 Standard on Windows 2008R2. My datafile for auditing (Unified Auditing) grew to 10G and while I was able to cleanup the records for unified audit records, I’ve read that the database won’t allow you to shrink the data file. Some kind of internal safety feature. Is there a way around this? The result of the "select value from v$option where parameter=’Unified Auditing’ yields "False" as a result. What gives? Still getting auditing records collecting in my unified_audit_trail table yet this parameter is coming up "False"? I’d like to start over with this thing – is creating a new database the only way to do it?
Thanks! –Austin
Hi Austin,
you’ll have to disable the policies as well – or check first which policies are enabled. Furthermore make sure audit_trail=none as well.
And for a shrink operation please check back with Oracle Support. There may be a way to recreate specific objects in order to get the space freed up (or a shrink operation) – but Support should know better.
Cheers and sorry to hear that 🙁
Mike
@Umazzini:
Hi there,
when both are on, the database is writing both trails unfortunately. This is one pitfall we usually talk about in our workshops when time allows. You really can have BOTH on at the same time and get a ton of (most likely unwanted) audit information.
Either use audit_trail or Unified Auditing. But no mixed mode and not both on at the same time if possible.
Cheers
Mike
As for “2.If select VALUE from V$OPTION where PARAMETER=’Unified Auditing’ == TRUE then the parameter audit_trail has no meaning anymore”, does it conflict with Mixed mode?
What confuses me is that if unified auditing disables the parameter of audit_trail (i.e. audit_trail=none), only unified auditing is enabled. Therefore, audit_trail cannot enabled with unified auditing and there is no Mixed mode.
If you linked in UNIAUD then there is no mixed mode anymore. The mixed mode is sort of a strange creature which I personally never understood (ok, I understand the motivation of it, but I completely discourage such a setting/mode as it goes against all principals I learned in Software Engineering at the university a long time ago.
The old audit trail is disabled when you link UNIAUD into your kernel to prevent two audit trails active at the same time. This makes sense. But the mixed mode with the old audit trail and “some” audit activities in the new trail is weird.
Cheers
MIke
I also have concerns about mixed mode. I read somewhere in documentation that its purpose is to migrate old auditing to new unified auditing policies which means that both methods should be fully functional in this mode. I mean here that it should be possible to configure and test new unified auditing policies while having old auditing still working. Linking UNIAUD disables mixed mode and makes only unified auditing active. Is this understanding correct?
Yes, this is correct as far as my testing goes.
Cheers
Mike
Hi Mike, Your blog is indeed very useful and it has really nice tips to take care while doing things around database for eg in this what you said “make sure you set AUDIT_TRAIL=NONE afterwards as otherwise both auditing mechanisms will run concurrently” such tips are really helpful as these are mostly overlooked when things are explained generally but you are good at pointing those. With this reference and some other site i have finished setting up the auditing in oracle 12.2.0 version as you have mentioned only enabling the parameter. Would you mind to share on how to take care of policies hereafter…i mean to delete the data collected in unified_audit_trial after x amount of time/size is reached please. I will be looking forward to your mail. Take care.
Hi Vageeha,
see here please:
https://docs.oracle.com/en/database/oracle/oracle-database/19/dbseg/administering-the-audit-trail.html#GUID-9F298B8A-6196-4206-A889-A7CEB0924CF1
and
https://docs.oracle.com/en/database/oracle/oracle-database/19/dbseg/administering-the-audit-trail.html#GUID-9B891A44-3DF4-4B52-98D4-A931DBFAEC1D
And thanks for the kind feedback!
Cheers,
Mike
Mike,
I upgraded to 19c, enabled unified auditing and created a policy to audit selects made by a specific user:
CREATE AUDIT POLICY my_policy
ACTIONS SELECT ONLY TOPLEVEL;
AUDIT POLICY my_policy BY DEV1;
The issue I see is lots of records in the trail associated to SYS even though I am using ONLY TOPLEVEL.
I get audit rows related to selects issued by DEV1 but I also get a bunch of SYS statements which seem related to parsing and not issued directly by someone connected as SYS.
However, if I create a policy by listing a specific set of objects to audit then I will NOT get SYS rows which is what I would expect. This is how I create the policy that works correctly:
CREATE AUDIT POLICY my_policy
ACTIONS SELECT ON hr.employee
ONLY TOPLEVEL;
AUDIT POLICY my_policy BY DEV1;
In this case when dev1 queries hr.employee I only get one row in the trail –which is what I’d expect.
Is this a bug related to ONLY TOPLEVEL not working correctly?
Roberto.
Hi Roberto,
thanks for the example – and I can’t tell you seriously whether there is an issue with ONLY TOPLEVEL, or what exactly is expected since I am not a UNIAUD specialist. So you please may need to open an SR if you haven’t opened one already, and check with Oracle Support.
Thanks,
Mike