Detecting breaches in an Oracle database – with a honeypot

SQL Injection is one of the most popular, if not the most popular, hacking method. It’s in most cases an application-related problem. So what’s a DBA to do? In an ideal world DBAs can assign minimal privileges to the users that applications login with. I tell students on my “Hack Je Eigen Database” (Hack Your Own Database) training that. “But, but, but”, they say, “our suppliers won’t cooperate. They keep asking for ridiculous privileges, like the DBA role, to run their sofware. What are we to do?”

Personally, I’d like to discuss these ridiculous specs with those suppliers and I have my methods detecting what an application really needs (setting events on ORA-942 and 1001, SQL tracing and the like). But that’s for another blogpost. What I would like to discuss, is this: whatever the security of the application or anything else, can you detect a hacker nosing around on your database?

According to Verizon Business’ Data Breach Investigations Reports, breaches are often detected after weeks, months or even years. Detecting breaches happening right now would of course be very desirable. You could try to find out who is trying to breach your systems.

The check I’m thinking of, is a honeypot. System operators sometimes use unused systems as honey pots. If someone is trying to access it, the system operator knows someone is intruding. Like Admiral Ackbar would say, “It’s a trap!”. All alarms go off.

A database honeypot could consist of a table that is audited with Oracle’s standard auditing. Then you need notification when any action on the table is detected. The goal is to make the table look like it’s part of the application schema.

So let’s start with a table:
CREATE TABLE app_owner.fake_authentication
(auth_id     NUMBER(8),
username     VARCHAR2(40),
password     VARCHAR2(40))
TABLESPACE app_data;

(Obviously you wouldn’t call the table fake_authentication. Give it a name in lieu with the names of other tables in the schema.)

Now you need some data. It isn’t necessary for auditing, but more for keeping the hacker busy with the wrong data and it will make him/her less suspicious. Also, it makes the table statistics look like there are rows in the table and therefor it isn’t a table of some part of the application that is never used.

You could generate data with tools like Datagenerator by Dominic Gilles or online data generators, like But you could also get creative with dbms_random or something. Maybe the table should also have an index or a foreign key to a different honeypot table. Get as creative as you want.

Now making Oracle detect any action on a table, select, insert, delete or updates, is just a matter of setting up standard auditing. To audit select statements, for example, you can run this as sys:
AUDIT SELECT ON app_owner.fake_authentication BY ACCESS;

Don’t forget to make the audit_trail parameter something else than “none” of course.

Let’s see if it works. We log in with app_owner:

CONNECT app_owner/C0mplexPw

SELECT * FROM fake_authentication;

Now let’s log in as sys to see if we have detected that:

CONNECT sys AS sysdba

SELECT os_username, username, action_name, obj_name, extended_timestamp
FROM dba_audit_trail
AND owner='APP_OWNER';

---------------- ---------- ----------- -------------------- ---------------------------------
CLIENT123\user1  APP_OWNER  SELECT      FAKE_AUTHENTICATION  13-05-11 11:17:17,593000 +02:00

Yep, that works fine. You might want change to the audit_trail parameter to extended auditing (db_extended or xml_extended), if you want to find the SQL statements that were used by a hacker.

SELECT username, sql_text, extended_timestamp
FROM dba_audit_trail
AND owner='APP_OWNER';

---------- --------------------------------- ---------------------------------
APP_OWNER  select * from fake_authentication 13-05-11 11:17:17,593000 +02:00

So that works well. Know that when this record is written, that it will also be readable for the hacker in user_audit_trail:

SELECT action_name, obj_name, sql_text, extended_timestamp
FROM user_audit_trail

ACTION_NAME OBJ_NAME            SQL_TEXT                          EXTENDED_TIMESTAMP
----------- ------------------- --------------------------------- -------------------------------
SELECT      FAKE_AUTHENTICATION select * from fake_authentication 13-05-11 11:17:17,593000 +02:00

But by then, it is too late. For this reason you might want to clean up the audit information, so a hacker can’t see other hacking attempts.

Now all you need, is notification. For this you might use this query as basis:

SELECT username, sql_text, extended_timestamp
FROM dba_audit_trail
AND owner='APP_OWNER';

The wonderful thing of this method is, that it uses the natural curious nature of humans. It isn’t waterproof, but it’s better than you’ve had.


About Marcel-Jan Krijgsman

Ever since I started working with Oracle, I had an interest in Oracle database performance tuning. This led, eventually, to a four day training I made and gave for customers of Transfer Solutions. Since 2012 I work for Rabobank Nederland. A few years ago I also became interested in Oracle database security. All technology aside, it is my experience that security usually plays out on a political level. I'm a Oracle certified professional for the 8i, 9i, 10g and 11g databases and Oracle Database 11g Performance Tuning Certified Expert.
This entry was posted in Oracle security and tagged , , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s