Call: +44 (0)1904 557620 Call
Blog

Pete Finnigan's Oracle Security Weblog

This is the weblog for Pete Finnigan. Pete works in the area of Oracle security and he specialises in auditing Oracle databases for security issues. This weblog is aimed squarely at those interested in the security of their Oracle databases.

Delete from AUD$

We have been working on a new audit trail toolkit / product for some time now which is called PFCLATK - The PFCL means "P"ete"F"innigan."C"om "L"imited so most of our tools products end up with a consistent name starting PFCL. I will talk about the audit trail toolkit and show some demo's over the coming weeks. PFCLATK will (is) a free PL/SQL and SQL toolkit that is used to provide a layer over standard audit. It is currently around 11k lines of PL/SQL and SQL presently and is being implemented by some sites into their Oracle databases with our help. Core audit is supported now and its trivial to also support Unified Audit when there is a demand. The toolkit will be free BUT we plan to create a dashboard / admin interface most likely in our Oracle vulnerability scanner PFCLScan so that a "link" can be created between security auditing of a database and audit trails of a database. The toolkit will be free but probably will not be available as a download - just ask me for it if you would like to implement it. We also give the same toolkit away as a free download for students of my one day class "Designing Practical Audit Trails for Oracle".

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.



Fourteenth Anniversary For PeteFinnigan.com Limited And New Website

Wow, has it really been fourteen years since I started PeteFinnigan.com Limited? - Time has gone so fast and business is getting better and better. We have great customers, great Oracle Security trainings and consulting projects meeting new people and we love passing on knowledge. We also have great customers of our software PFCLScan - our database security scanner for Oracle and also PFCLObfuscate our product to help people protect their PL/SQL.

We have a number of new products in the wings most notably PFCLATK our audit trail toolkit - More on this soon in this blog, I promise!!!. We have some customers implementing PFCLATK already and we would really like to get a couple more customers to implement and test it with us. As I said more details on this great new audit trail toolkit soon but if you are interested to test let me know.

I hope that visitors to our company website PeteFinnigan.com have also noticed that the home page style has somewhat changed. Thanks to Kariba Web Design in Harrogate here in the UK. They have designed a new look for us based on initial designs by me. They provided a HTML5, JavaScript and CSS set of samples styles and we have converted that to fit the main site code structure. The home page and a few other static pages have been live for a few weeks but the blog has only just gone live this week on the new style and design. The blog was a big effort to shoehorn a new modern HTML5 / CSS design into GreyMatter which we have used for many years. Greymatter was one of the first blog software packages from 2001 (written initially by Noah Grey) and then in the early years modded and updated by people like FlippedCracker, Linear, Hazelorb and others. Then around 11 - 12 years ago myself, Coldstone and Carlos Phelps took the reigns and updated and released a number of new versions. The last official version was 1.8.2 in May 2008 BUT... I also created after that (September 2009) a version 1.8.3 that included some new bug fixes and some new features BUT by that time no one was interested in Greymatter blogging anymore and only one or two people tested 1.8.3.

As part of re-learning the templates and some other features to update to our new website design I have also made a couple of small changes to the Greymatter blog code to remove the matrix calendar and include inline links of the current months posts and also to add HTML titles from the blog itself. This will be added to a new Greymatter 1.8.4 version. If anyone is interested in 1.8.4 then please let me know BUT there will not be an upgrade script from 1.8.2 as it is just not worth it as there are so few people using it now. That said I do get people from time to time asking if they can have 1.8.3. Version 1.8.3 has over 60 changes to the code base of 1.8.2 and also some structural changes to the storage.

The main site still has quite a few pages in the old style and indeed I captured a screen shot of the old front page and saved this to the design page for posterity and also you will notice that the new logo is a caricature of me from the old site!! The remaining pages but probably not the forum will be updated to the new style as soon as possible - Maybe the forum if I can find a way to do it without major effort.

There may also be a 1.9 or possibly version 2.0 of Greymatter Blog coming soon at least in this blog. I want to add some features to my blog that do not exist in 1.8.2 or earlier; these changes will likely not be fully integrated in Perl and may be add-ons in php. After all back in 2002-2005 that is how most of the mods for 1.2 were done!!

Welcome to the new look blog and also now watch out for new blog posts on Oracle Security; I have a backlog of things to write about now so a few posts are coming.