ThatJeffSmith

Oracle SQL Developer and Database Auditing

Did you know Oracle SQL Developer allows you to configure Database Auditing and view your audit trails? Well, now you do!

This is not new for version 4.0, but the screenshots in this post are from version 4.0. This feature is available under the DBA panel, which can be found on the View menu.

Control what's logged and browse the logs all in one place.

Control what’s logged and browse the logs all in one place.

I won’t say whether Database Auditing is the right solution for you – I don’t know your requirements, but I WILL say this: I’m always surprised when folks build their own auditing system. I would advise you try what we’ve built for you first.

Anyways, if you want to see what’s going on in your database, open the Audit Settings node under the Security piece in the DBA tree.

It’s MORE than just the settings though, it’s all the trails.

The Failed Logins and Objects Audit Trails

The Failed Logins and Objects Audit Trails

Ok, so this feature is in v3.2, but what I’m showing above is a SQL Developer v4 feature – the cloned editor. This allows me to open the Audit Settings Document TWICE. After I clone it, I create a new document tab group so I can see both at the same time. Be sure to master the clone/tab group trick for maximum SQLDev experience points :)

Changing the Audit Settings

Maybe you want to add something to be tracked. Just right-click on the Audit Settings node and choose what you want.

Change what's being recorded.

Change what’s being recorded.

I want to record anytime the HR user issues a GRANT on a table…

Switch to the SQL panel is you want to see the command to be issued...

Switch to the SQL panel is you want to see the command to be issued…

Remember, nothing’s free.

I know that sounds weird, especially since we’re (SQL DEVELOPER) essentially a free product. But if you ask the database to log addditional information, that has a cost – I/O, CPU, whatever. Be sure to only ask for what you need. And read the advice on the auditing link I shared in the first paragraph of this post – it will help you minimize overhead on your systems.