A Quick Hit on Database Auditing Support

thatjeffsmith SQL Developer 4 Comments

Tell Others About This Story:

View – DBA – Security – Auditing

There's quite a few pages here to investigate.

There’s quite a few pages here to investigate.

A user asked how to use SQL Developer to audit user XYZ. And to be honest, it’s not really set up to work that way. You define operations or objects you want audited. Any user that does XYZ to object ABC will get logged.

There’s an exception to this – you can setup SYSDBA users to be audited. This will grab the SYSDBA-level type operations that happen. Check out the latest docs here.

A Quick Example

I want to keep an eye on EMPLOYEES – the table. I trust my actual employees, of course.

So I go to the ‘Audited Objects’ page.

I want to add HR.EMPLOYEES..

I want to add HR.EMPLOYEES..

And this brings up a dialog to let me do just that:

You have a few options here - the 'by session' means if you query the table once or 10x in your session, there will be only 1 entry. If i log failures and successes, there's more to be recorded there as well.

You have a few options here – the ‘by session’ means if you query the table once or 10x in your session, there will be only 1 entry. If i log failures and successes, there’s more to be recorded there as well.

The more you ask for, the more it costs. Keeping track of this stuff creates work for the database, so ask for what you need.

Once I have the EMPLOYEES table set to be ‘watched’, I only need to look at the Objects Trail.

So I go in as users HR and JSON and run a couple of SELECTS..then come back to my audit trail, and refresh the report.

Ta-da! I can see who looked at my peeps and when.

Ta-da! I can see who looked at my peeps and when.

Once this table gets fairly large, you’ll want to at least use FILTERs – if not perform maintenance on the audit logs themselves, a topic that’s not for this domain.

Related Posts

Tell Others About This Story:

Comments 4

    1. thatjeffsmith Post
      Author
  1. Hi Jeff. I can’t to find how I can disable/delete audit on the table via SQL Developer 4.1.0.18. 🙁 Is there exist this opportunity?

    1. thatjeffsmith Post
      Author

Leave a Reply

Your email address will not be published. Required fields are marked *