Auditing Read-Only Standbys
25th March 2015 Leave a comment
If your company has a passing interest in database security, you are probably running some sort of session auditing at the very least [audit session] (although this can also be useful for troubleshooting connectivity issues). There’s a reasonable chance you’re also running some level of object auditing, or even DML access auditing if your employer so dictates:
Check access/change of objects in the DB 1 select audit_option, success, failure 2 from dba_stmt_audit_opts 3 union 4 select privilege, success, failure 5 from dba_priv_audit_opts 6* order by 2,1
AUDIT_OPTION SUCCESS FAILURE -------------------------------------------------- ---------------------------------------- ----------------- ALTER ANY PROCEDURE BY ACCESS BY ACCESS ALTER ANY TABLE BY ACCESS BY ACCESS ALTER DATABASE BY ACCESS BY ACCESS ALTER PROFILE BY ACCESS BY ACCESS ALTER SYSTEM BY ACCESS BY ACCESS ALTER TABLE BY ACCESS BY ACCESS ALTER USER BY ACCESS BY ACCESS AUDIT SYSTEM BY ACCESS BY ACCESS CREATE ANY JOB BY ACCESS BY ACCESS CREATE ANY LIBRARY BY ACCESS BY ACCESS CREATE ANY PROCEDURE BY ACCESS BY ACCESS CREATE ANY TABLE BY ACCESS BY ACCESS CREATE EXTERNAL JOB BY ACCESS BY ACCESS CREATE LIBRARY BY ACCESS BY ACCESS CREATE PROCEDURE BY ACCESS BY ACCESS CREATE PUBLIC DATABASE LINK BY ACCESS BY ACCESS CREATE SESSION BY ACCESS BY ACCESS CREATE TABLE BY ACCESS BY ACCESS CREATE USER BY ACCESS BY ACCESS DATABASE LINK BY ACCESS BY ACCESS DIRECTORY BY ACCESS BY ACCESS DROP ANY PROCEDURE BY ACCESS BY ACCESS DROP ANY TABLE BY ACCESS BY ACCESS DROP PROFILE BY ACCESS BY ACCESS DROP USER BY ACCESS BY ACCESS EXEMPT ACCESS POLICY BY ACCESS BY ACCESS GRANT ANY OBJECT PRIVILEGE BY ACCESS BY ACCESS GRANT ANY PRIVILEGE BY ACCESS BY ACCESS GRANT ANY ROLE BY ACCESS BY ACCESS PROFILE BY ACCESS BY ACCESS PUBLIC DATABASE LINK BY ACCESS BY ACCESS PUBLIC SYNONYM BY ACCESS BY ACCESS ROLE BY ACCESS BY ACCESS SYSTEM AUDIT BY ACCESS BY ACCESS SYSTEM GRANT BY ACCESS BY ACCESS USER BY ACCESS BY ACCESS CREATE JOB BY SESSION BY SESSION
And you’re probably writing it into a database table [AUDIT_TRAIL=’DB’]
so how does that work if you open a Dataguard database read only? You are writing into sys.aud$ on the Primary, and that table is replicated to the Standby. So what happens?
From Oracle 11G, if you are running Dataguard and opening the standby up for read access, you may not notice the line in the ALERT log which reads:
AUDIT_TRAIL initialization parameter is changed to OS, as DB is NOT compatible for database opened with read-only access
So it starts writing down O/S audit trail files for all of your auditing options (well, the session connect and DML audit options – you can’t run DDL in a r/o DB). You might want to go and see just how many files it has written to [audit_file_dest], as you may be surprised at just how many are in there.
You may, one day, either run out of space or (more worryingly) have so many millions of files that it causes a performance problem when Oracle access the O/S directory. You might want to think about some sort of periodic clean-up job.