The main idea behind PFCLATK is to solve the problem that most sites I visit and also people I talk to do not have comprehensive audit trails for the database engine itself. Some may have very minimal audit for CREATE SESSION or even use audit for resource planning but very few actually use audit trails to understand who may access your Oracle database and what they might be doing. Included in this is the problem that audit in Oracle is actually complex when you get into the details.
What was needed was a simple "Turn it all on" - I mean "ALL" in a sense of all you want not all audit possible. Manage it at a policy level, provide events and alerts to assess if a breach or misuse has occurred and also provide centralised audit trails. All of this by simply running one SQL*Plus script in a database that becomes the central audit trail and then running the same script in each target database. Of course the central database is also audited by the same toolkit. Then running one simple configuration script to "add" each target database to the central database where it is then automatically managed. The central database PUL's the audit from each target and checksums and purges it from the remote source. Reporting can be done on the central database or the targets.
Anyway, I will talk in much more detail about the toolkit over coming weeks and show some demos and setup and show what it does. As part of the development and research into audit trails in Oracle for this tool and also for our one day class we discovered many nuances. I want to talk about purging / deleting SYS.AUD$. Here is a demo; First connect as SYS and create a user ATKP:
C:\____atk>sqlplus sys/oracle1@//192.168.1.39:1521/bofora.localdomain as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Tue Feb 28 10:26:55 2017
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Release 11.2.0.3.0 - 64bit Production
SQL>
Give ATKP the ability to connect and also the ability to delete records from AUD$:
SQL> create user atkp identified by atkp;
User created.
SQL> grant create session to atkp;
Grant succeeded.
SQL> grant delete on sys.aud$ to atkp;
Grant succeeded.
SQL>
Clear down AUD$ as SYSDBA - we are still connected as SYSDBA:
SQL> truncate table sys.aud$;
Table truncated.
SQL> commit;
Commit complete.
SQL>
Set up some formatting and check what audit is enabled:
SQL> col user_name for a20
SQL> col privilege for a30
SQL> set lines 220
SQL> select user_name,privilege,success,failure from dba_priv_audit_opts;
USER_NAME PRIVILEGE SUCCESS FAILURE
-------------------- ------------------------------ ---------- ----------
CREATE EXTERNAL JOB BY ACCESS BY ACCESS
CREATE ANY JOB BY ACCESS BY ACCESS
GRANT ANY OBJECT PRIVILEGE BY ACCESS BY ACCESS
EXEMPT ACCESS POLICY BY ACCESS BY ACCESS
ADMINISTER DATABASE TRIGGER BY ACCESS BY ACCESS
CREATE ANY LIBRARY BY ACCESS BY ACCESS
GRANT ANY PRIVILEGE BY ACCESS BY ACCESS
DROP PROFILE BY ACCESS BY ACCESS
ALTER PROFILE BY ACCESS BY ACCESS
DROP ANY TRIGGER BY ACCESS BY ACCESS
ALTER ANY TRIGGER BY ACCESS BY ACCESS
USER_NAME PRIVILEGE SUCCESS FAILURE
-------------------- ------------------------------ ---------- ----------
EXECUTE ANY PROCEDURE BY ACCESS BY ACCESS
DROP ANY PROCEDURE BY ACCESS BY ACCESS
ALTER ANY PROCEDURE BY ACCESS BY ACCESS
CREATE ANY PROCEDURE BY ACCESS BY ACCESS
ALTER DATABASE BY ACCESS BY ACCESS
AUDIT ANY BY ACCESS BY ACCESS
ALTER ANY ROLE BY ACCESS BY ACCESS
GRANT ANY ROLE BY ACCESS BY ACCESS
DROP ANY ROLE BY ACCESS BY ACCESS
CREATE ROLE BY ACCESS BY ACCESS
CREATE PUBLIC DATABASE LINK BY ACCESS BY ACCESS
USER_NAME PRIVILEGE SUCCESS FAILURE
-------------------- ------------------------------ ---------- ----------
DROP ANY TABLE BY ACCESS BY ACCESS
ALTER ANY TABLE BY ACCESS BY ACCESS
CREATE ANY TABLE BY ACCESS BY ACCESS
DROP USER BY ACCESS BY ACCESS
ALTER USER BY ACCESS BY ACCESS
BECOME USER BY ACCESS BY ACCESS
CREATE USER BY ACCESS BY ACCESS
CREATE SESSION BY ACCESS BY ACCESS
AUDIT SYSTEM BY ACCESS BY ACCESS
ALTER SYSTEM BY ACCESS BY ACCESS
32 rows selected.
SQL>
SQL> select user_name,audit_option,success,failure from dba_stmt_audit_opts;
USER_NAME AUDIT_OPTION SUCCESS FAILURE
-------------------- ---------------------------------------- ---------- ----------
CREATE EXTERNAL JOB BY ACCESS BY ACCESS
CREATE ANY JOB BY ACCESS BY ACCESS
GRANT ANY OBJECT PRIVILEGE BY ACCESS BY ACCESS
EXEMPT ACCESS POLICY BY ACCESS BY ACCESS
ADMINISTER DATABASE TRIGGER BY ACCESS BY ACCESS
CREATE ANY LIBRARY BY ACCESS BY ACCESS
GRANT ANY PRIVILEGE BY ACCESS BY ACCESS
DROP PROFILE BY ACCESS BY ACCESS
ALTER PROFILE BY ACCESS BY ACCESS
DROP ANY TRIGGER BY ACCESS BY ACCESS
ALTER ANY TRIGGER BY ACCESS BY ACCESS
USER_NAME AUDIT_OPTION SUCCESS FAILURE
-------------------- ---------------------------------------- ---------- ----------
EXECUTE ANY PROCEDURE BY ACCESS BY ACCESS
DROP ANY PROCEDURE BY ACCESS BY ACCESS
ALTER ANY PROCEDURE BY ACCESS BY ACCESS
CREATE ANY PROCEDURE BY ACCESS BY ACCESS
ALTER DATABASE BY ACCESS BY ACCESS
SYSTEM GRANT BY ACCESS BY ACCESS
AUDIT ANY BY ACCESS BY ACCESS
ALTER ANY ROLE BY ACCESS BY ACCESS
GRANT ANY ROLE BY ACCESS BY ACCESS
DROP ANY ROLE BY ACCESS BY ACCESS
CREATE ROLE BY ACCESS BY ACCESS
USER_NAME AUDIT_OPTION SUCCESS FAILURE
-------------------- ---------------------------------------- ---------- ----------
CREATE PUBLIC DATABASE LINK BY ACCESS BY ACCESS
DROP ANY TABLE BY ACCESS BY ACCESS
ALTER ANY TABLE BY ACCESS BY ACCESS
CREATE ANY TABLE BY ACCESS BY ACCESS
PROFILE BY ACCESS BY ACCESS
ROLE BY ACCESS BY ACCESS
DATABASE LINK BY ACCESS BY ACCESS
PUBLIC SYNONYM BY ACCESS BY ACCESS
DROP USER BY ACCESS BY ACCESS
ALTER USER BY ACCESS BY ACCESS
BECOME USER BY ACCESS BY ACCESS
USER_NAME AUDIT_OPTION SUCCESS FAILURE
-------------------- ---------------------------------------- ---------- ----------
CREATE USER BY ACCESS BY ACCESS
USER BY ACCESS BY ACCESS
CREATE SESSION BY ACCESS BY ACCESS
SYSTEM AUDIT BY ACCESS BY ACCESS
ALTER SYSTEM BY ACCESS BY ACCESS
38 rows selected.
SQL>
SQL> select * from dba_obj_audit_opts;
OWNER OBJECT_NAME OBJECT_TYPE ALT AUD COM DEL GRA IND INS LOC REN SEL UPD REF EXE CRE REA WRI FBK
------------------------------ ------------------------------ ----------------------- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- ---
SYS AUD$ 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
ORABLOG BIN$LO2rapM5UdbgUycBqMBEsQ==$0 TABLE -/- A/A -/- A/A -/- -/- A/A -/- -/- A/A A/A -/- -/- -/- -/- -/- -/-
ORABLOG BIN$LO3duR7cUlHgUycBqMBiIQ==$0 TABLE -/- A/A -/- A/A -/- -/- A/A -/- -/- A/A A/A -/- -/- -/- -/- -/- -/-
ORABLOG BIN$LO3mL8NUUn7gUycBqMBI4g==$0 TABLE -/- A/A -/- A/A -/- -/- A/A -/- -/- A/A A/A -/- -/- -/- -/- -/- -/-
ORABLOG BIN$LOt2+8lUTmrgUycBqMDJEg==$0 TABLE -/- A/A -/- A/A -/- -/- A/A -/- -/- A/A A/A -/- -/- -/- -/- -/- -/-
ORABLOG BIN$LOuOjTzSTqfgUycBqMDBbg==$0 TABLE -/- A/A -/- A/A -/- -/- A/A -/- -/- A/A A/A -/- -/- -/- -/- -/- -/-
ORABLOG BIN$LOunNR3oTuHgUycBqMDPcw==$0 TABLE -/- A/A -/- A/A -/- -/- A/A -/- -/- A/A A/A -/- -/- -/- -/- -/- -/-
ORABLOG CREDIT_CARD TABLE -/- A/A -/- A/A -/- -/- A/A -/- -/- S/S A/A -/- -/- -/- -/- -/- -/-
ORABLOG WP_USERMETA TABLE -/- -/- -/- A/A -/- -/- A/A -/- -/- A/A A/A -/- -/- -/- -/- -/- -/-
ORABLOG WP_USERS TABLE -/- -/- -/- A/A -/- -/- A/A -/- -/- A/A A/A -/- -/- -/- -/- -/- -/-
ATKD PFCLATK_ALERTS TABLE -/- -/- -/- -/- -/- -/- -/- -/- -/- A/A -/- -/- -/- -/- -/- -/- -/-
OWNER OBJECT_NAME OBJECT_TYPE ALT AUD COM DEL GRA IND INS LOC REN SEL UPD REF EXE CRE REA WRI FBK
------------------------------ ------------------------------ ----------------------- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- ---
ATKD PFCLATK_AUDIT TABLE -/- -/- -/- -/- -/- -/- -/- -/- -/- A/A -/- -/- -/- -/- -/- -/- -/-
ATKD PFCLATK_RULES TABLE -/- -/- -/- -/- -/- -/- -/- -/- -/- A/A -/- -/- -/- -/- -/- -/- -/-
ORABLOG RUN_SQL PROCEDURE -/- -/- -/- -/- -/- -/- -/- -/- -/- -/- -/- -/- A/A -/- -/- -/- -/-
ORABLOG ORABLOG_CRYPTO PROCEDURE -/- A/A -/- -/- -/- -/- -/- -/- -/- -/- -/- -/- A/A -/- -/- -/- -/-
ORABLOG CUSTA PROCEDURE -/- -/- -/- -/- -/- -/- -/- -/- -/- -/- -/- -/- A/A -/- -/- -/- -/-
16 rows selected.
SQL>
As you can see there are many audit settings in my database BUT the one i want to focus on is the audit on SYS.AUD$ itself. We are auditing select, insert, update, delete, audit and more. If i connect as SYSTEM and access AUD$ a couple of times I get:
SQL> connect system/oracle1@//192.168.1.39:1521/bofora.localdomain
Connected.
SQL> select * from sys.aud$;
...
SQL> select count(*) from sys.aud$;
COUNT(*)
----------
2
SQL>
If we check what is in AUD$ as SYSTEM we can see:
SQL> col userid for a20
SQL> col obj$name for a10
SQL> col action# for 9999
SQL> select userid, obj$name, action# from sys.aud$;
USERID OBJ$NAME ACTION#
-------------------- ---------- -------
SYSTEM 100
SYSTEM AUD$ 3
SYSTEM AUD$ 3
SYSTEM AUD$ 3
SQL>
So we have some records in AUD$ we can connect to ATKP and because ATKP has DELETE rights on SYS.AUD$ we can delete all of the records:
SQL> delete from sys.aud$;
8 rows deleted.
SQL> commit;
Commit complete.
SQL>
Check now as SYSTEM and see what's left:
SQL> select userid, obj$name, action# from sys.aud$;
USERID OBJ$NAME ACTION#
-------------------- ---------- -------
ATKP AUD$ 7
SQL>
This leaves one record in SYS.AUD$ with an action of 7; 7 means DELETE. This means that the DELETE on SYS.AUD$ was itself audited - Good! Interestingly also the select above on SYS.AUD$ by SYSTEM to see that the DELETE audit record also creates another audit records for action 3 - SELECT BUT of course we cannot see that now as its created by the SELECT. Now the issue; If we go back to the session at ATKP and DELETE from SYS.AUD$ again - we have permission to do that:
SQL> sho user
USER is "ATKP"
SQL> delete from sys.aud$;
1 row deleted.
SQL> commit
2 /
Commit complete.
SQL>
Lets now go to SYSTEM and check the contents of SYS.AUD$
SQL> select userid, obj$name, action# from sys.aud$;
USERID OBJ$NAME ACTION#
-------------------- ---------- -------
ATKP AUD$ 7
ATKP AUD$ 7
SQL>
So a user with DELETE rights on SYS.AUD$ can delete any audit records from SYS.AUD$ (including SELECT) BUT it cannot delete an audit record for DELETE on SYS.AUD$ itself. This is good in one sense but sort of not perfect in another. If we create an audit trail solution we want to create an AUDIT MANAGER type user who has the ability to manage the audit rails completely BUT this user has some restrictions (yes there are more restrictions but I will leave them for another post) in that it cannot manage the audit trails completely. In this case SYSDBA must empty AUD$ including the DELETE audit records for AUD$ itself.
I know there are solutions to this and I will also talk about some of these going forward in future blog posts along with some more related issues. Also watch out for the blog post coming soon on PFCLATK audit toolkit.
