Turning on SQL Audit February 2, 2010
Posted by mwidlake in development, performance, Testing.Tags: audit, performance, security, Testing
trackback
I want to test out using the SQL AUDIT functionality.
The first thing you have to do (having read up on it a bit) is to enable it for the database. You do this by setting the initialization parameter AUDIT_TRAIL. On version 10.2 you have the options to write the audit entires to:
- The DB, by setting it to DB or DB_EXTENDED {not DB,EXTENDED as the manual says, that is the old format}. This puts the entries into the table SYS.AUD$
- The operating system, by setting it to OS, XML or XML_EXTENDED. If you set it to XML or XML_EXTENDED then the data is written out in XML format. You also optionally set AUDIT_FILE_DEST to where you want to data to be written to.
Writing the audit trail to the OS is potentially more secure, as you can stop those cunning and devious DBAs messing with the audit trail. {I’m not so sure that this really helps that much – if anyone knows of any DBAs caught out being naughty solely as a result of using the OS to store the SQL AUDIT records, it would be a fascinating comment}
I want to write to the DB as I want to be able to get at the audit data easily and I am not sure how I want to interrogate it. I’m faster with SQL than SED and AWK.
I also decided up front I wanted to use DB_EXTENDED so that the triggering SQL statement and all bind variables are caught, so I can see more about what it triggering the audit record. I am cautious of the impact of storing CLOBs though, which these two values are stored as. I’ve had performance issues moving lots of CLOBS around and I know from some old colleagues that Secure Files are a lot faster. If Secure Files are faster, that means CLOBs are slower :-). If the audit trail seems to add too much burden on my system, swapping back to just DB will be my first step.
Now for the bad news. You can’t just turn on AUDIT. That initialization parameter is not dynamic. You can’t even enable it for your session. It will need a restart of your database.
This tells me something. Oracle needs to do some setting up for SQL AUDIT when it starts the instance. Either start a new process, enable functionality in one of it’s regular processes or set up structures in memory to cope. Or a mixture thereof. I strongly suspect the need for memory structures {but this is only because, in reality, I have done some testing and I am writing this up afterwards}.
I should not really need to say this but DON’T go turning this on for a production system without extensive testing somewhere else first. There is not a lot “Out There” about the details of the performance impact of AUDIT but the general opinion is there is some; and that is reasonable given it is going to write database records for every action audited. Also, you have no idea yet of any knock-on effects. You know, things you did not expect that causes your database to lock or crash and you to get fired.
{Question, what happens if you alter the initialization file and restart only one node of a RAC database? I don’t know and so I should test that. My current test system is not RAC, but the final destination for this stuff is RAC}.
You probably also want to check that no one has gone and tried turning on SQL AUDIT on things already. You never know if someone else decided to have a play with this and issued a load of AUDIT statements only to find nothing happened – and left what they did in place “as nothing happened”. I already know of one example of this happening…
Here is a little script I knocked up to see what is currently set to be audited:
-- what_is_audited.sql -- Martin Widlake 11/01/10 -- simple listing of what auditing is currently set set pages 100 set pause on spool what_is_audited.lst select * from dba_priv_audit_opts order by user_name,privilege / select * from sys.dba_stmt_audit_opts order by user_name,audit_option / select * from DBA_OBJ_AUDIT_OPTS order by owner,object_name / spool off clear col -- -- EOF --
And some sample output. I’m not going to explain it in this post, but you can have a look though it.
DEV3> @what_is_audited USER_NAME PROXY_NAME ------------------------------ ------------------------------ PRIVILEGE SUCCESS FAILURE ---------------------------------------- ---------- ---------- MDW1 ALTER SYSTEM BY ACCESS BY ACCESS MDW1 AUDIT SYSTEM BY ACCESS BY ACCESS MDW1 CREATE SESSION BY ACCESS BY ACCESS ALTER SYSTEM BY ACCESS BY ACCESS AUDIT SYSTEM BY ACCESS BY ACCESS CREATE SESSION BY ACCESS BY ACCESS 6 rows selected. USER_NAME PROXY_NAME ------------------------------ ------------------------------ AUDIT_OPTION SUCCESS FAILURE ---------------------------------------- ---------- ---------- MDW1 ALTER SYSTEM BY ACCESS BY ACCESS MDW1 CLUSTER BY ACCESS BY ACCESS MDW1 CONTEXT BY ACCESS BY ACCESS MDW1 CREATE SESSION BY ACCESS BY ACCESS MDW1 DATABASE LINK BY ACCESS BY ACCESS MDW1 DELETE TABLE BY ACCESS BY ACCESS MDW1 ... TYPE BY ACCESS BY ACCESS MDW1 UPDATE TABLE BY ACCESS BY ACCESS MDW1 USER BY ACCESS BY ACCESS MDW1 VIEW BY ACCESS BY ACCESS ALTER SYSTEM BY ACCESS BY ACCESS CLUSTER BY ACCESS BY ACCESS CONTEXT BY ACCESS BY ACCESS CREATE SESSION BY ACCESS BY ACCESS DATABASE LINK BY ACCESS BY ACCESS DIMENSION BY ACCESS BY ACCESS DIRECTORY BY ACCESS BY ACCESS INDEX BY ACCESS BY ACCESS MATERIALIZED VIEW BY ACCESS BY ACCESS ... USER BY ACCESS BY ACCESS VIEW BY ACCESS BY ACCESS 56 rows selected. OWNER OBJECT_NAME OBJECT_TYPE ------------------------------ ------------------------------ -------------- ALT AUD COM DEL GRA IND INS LOC REN SEL ------- ------- ------- ------- ------- ------- ------- ------- ------- ---- UPD REF EXE CRE REA WRI FBK ------- --- ------- ------- ------- ------- ------- MWPERF FORN_M_SEQ SEQUENCE -/- -/- -/- -/- -/- -/- -/- -/- -/- A/A -/- -/- -/- -/- -/- -/- -/- MWPERF PERSON TABLE A/A A/A A/A A/A A/A A/A A/A A/A A/A A/A A/A -/- -/- -/- -/- -/- A/A MWPERF ROAD_TYPE TABLE -/- -/- -/- A/A -/- -/- A/A -/- -/- A/A A/A -/- -/- -/- -/- -/- -/-
If you discover you have a lot of things set to be audited, ESPECIALLY if they are auditing select access, think about turning some or all of it off before you enable AUDITING by setting that initialization parameter.
Once you have turned on the feature, you can start testing it…
Martin Widlake's Yet Another Oracle Blog 
The documentation is definitely confusing on this parameter, however the following should work for 10.2 ‘db’,’extended’ or db, extended , but not ‘db,extended’.
Thanks ebrian. This is what I set it to on my 10.2.0.3 test box
select version from v$instance
VERSION
—————–
10.2.0.3.0
@prm_lst
Search string for parameters (auto wildcarded)> audit
Parameter Name Parameter Value DLF TYP NUM
——————————– ———————————– — — —
audit_file_dest /a_dir_oracle_can_see_but_not_dbas N C 919
audit_sys_operations FALSE Y B 807
audit_syslog_level Y C 920
audit_trail DB_EXTENDED N C 938
Since 11g, the standard setting for the parameter AUDIT_TRAIL was changed to the value DB. Also, some system privileges are audited right from the creation of the Database – for example CREATE SESSION is audited, so that every connect (successful or not) is protocolled. That was a major change in the auditing policy of Oracle Databases, but it makes it easier to turn on additional auditing, because you don’t have to restart the instance to change AUDIT_TRAIL to DB anymore.
Hi Uwe,
Thanks for that, it’s great to have some information on 11g improvements. I know, 11g has been out a while now and it is what I use at home, but many oracle sites are still on 10 (including where I am spending my working days at present, so I spend most of my time working on it). Thus I tend to stick to 10. However, I might say something about 11Gs improvements to houskeeping the audit data in a future post.
For the RAC question (probably you already found the answer- I struggling to keep up with the blogs)
I tested it and if you run
alter system set audit_trail=DB scope=SPFILE;
on RAC1 and reboot RAC1 only
parameter is DB on RAC1 and NONE on RAC2
If you start auditing update on employees, when you run update a row on RAC1 it is being audited in dba_audit_trail,
but if you run same thing (update different row) on RAC2 it is not being audited.
Basically till you restart your other node you can only audit rebooted node
Hi Coskan,
I’m struggling to keep up with blogs as well (doing mine and reading others). I’ve got a list of things I’d like to say as long as my arm! So I appreciate you doing that test on RAC.
Your test shows it working how I would expect it to – the node that has not been restarted has not had a chance to set up what it needs to set up internally to handle auditing.
Thanks,
Martin