Skip to Main Content
  • Questions

  • Make a simple audit of table connections

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Xavier.

Asked: October 27, 2017 - 2:55 pm UTC

Last updated: November 02, 2017 - 11:35 am UTC

Version: 11G Release 11.2.0.3.0

Viewed 1000+ times

You Asked

Dear Tom.

I want to know which tables in my Oracle schema are impacted by any software, web application or direct queries.

An provider said me : " Make an audit on all tables (and views) of your schemas is not a good idea because your DB will works very more slowly that's why we could do this test on one day maximum and the results will be poor" .

With this positive point of view, I decided, for the moment,to work on the table V$SQL. I read all queries and I read all tables one by one (SQL_TEXT).
In fact V$SQL not seems to get all informations of connecting objects.

So, do you know how can I get this informations ? (And maybe more simply)

Great thanks for your help.

Geo-x

and Chris said...

You can't rely on v$sql to capture all queries against your tables. This only contains statements currently in the cursor cache. These can be aged or forced out. So there's no guarantee you'll see every statement.

If you want to audit when your application queries your tables, just run:

audit select table;


This will then capture details in dba_audit_trail. e.g.:

audit select table;

select count(*) from t;

COUNT(*)   
     10000 

select username, timestamp from dba_audit_trail 
where  obj_name = 'T'
and    timestamp > systimestamp - interval '1' minute;

USERNAME   TIMESTAMP              
CHRIS      30-OCT-2017 11:13:31


You can increase the auditing to include insert, update and delete and DDL if you want.

Now, clearly the more you audit the more overhead there will be. But if you want to capture this kind of information then there's no getting away from this. You need the space to store the audit data and time to process it.

This whitepaper discusses auditing options and their associated overhead:

http://www.oracle.com/technetwork/products/audit-vault/learnmore/twp-security-auditperformance-166655.pdf

Or for independent analysis, see:

https://www.trivadis.com/sites/default/files/downloads/ttc_oracle_auditing_report_ami_june2011-final_02.pdf

But the exact overheads will be very application specific. So there are two things you need to do:

1: Define exactly what you want to audit (and why)
2: Load test your application with this level of auditing to see if it meets your performance requirements

If everything is fine at step 2, you're good to go. If not, you need to reasses the level of auditing you need and/or how you're doing it.

Rating

  (6 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

For complete your answer (and my question by the same way)

Xavier LALANDE, October 30, 2017 - 2:32 pm UTC

Thank you very much for your precious (Like Gollum) answer Chris, it's perfect.

Just for complete your answer, how can I stop the audit ?

Thank you very much.
Chris Saxon
October 30, 2017 - 2:34 pm UTC 

noaudit select table;

And for see program in my DBA_AUDIT_TRAIL

Xavier LALANDE, October 31, 2017 - 9:58 am UTC

It's perfect Chris, Thank you.

And for complete this subject, it'is possible to see the program used by users in DBA_AUDIT_TRAIL ?

Thank you.
Chris Saxon
October 31, 2017 - 1:07 pm UTC

You can find details of all the information returned by this view in the docs:

https://docs.oracle.com/database/122/REFRN/DBA_AUDIT_TRAIL.htm#REFRN23023

noaudit doesn't works

Xavier, October 31, 2017 - 6:09 pm UTC

Thank you Chris.

I tried noaudit like you said and like the documentation, but in fact I see in dba_audit_trail my audit continue.

So I tried to stop audit with :
ALTER SYSTEM SET AUDIT_TRAIL=NONE SCOPE=SPFILE;

But I received the message : System altered.

And my audit always continue, what can I do ?
Connor McDonald
November 01, 2017 - 2:05 am UTC

scope=spfile means *next* time you restart the database, we will no longer do the auditing. It will continue until then.

So either bounce the database, or turn off any auditing with the noaudit command.

If I understand

Xavier, November 01, 2017 - 9:02 am UTC

Ok thank you so if I want to stop the audit right now I've to do :
ALTER SYSTEM SET AUDIT_TRAIL=NONE SCOPE=MEMORY;

Or

ALTER SYSTEM SET AUDIT_TRAIL=NONE SCOPE=BOTH;

Like I said dba_audit_trail continue to record informations with : noaudit select table
Chris Saxon
November 01, 2017 - 4:19 pm UTC

Yes, that will disable auditing provided you're using traditional auditing. It has no
effect on unified auditing (introduced in 12c). Use scope=both to disable it now and keep this setting after a restart.

Like I said dba_audit_trail continue to record informations with : noaudit select table

Have you enabled auditing for any other operations?

Noaudit

Xavier, November 01, 2017 - 4:33 pm UTC

Have you enabled auditing for any other operations?

I only did what you said : 
audit select table;

That Works !

Xavier, November 02, 2017 - 8:26 am UTC

I see this morning, my audit stopped yesterday I think when I leave my DB Manager software TOAD.

So, if I do that : 
ALTER SYSTEM SET AUDIT_TRAIL=NONE SCOPE=SPFILE;


That seem to working.

For information, when I try this : 
ALTER SYSTEM SET AUDIT_TRAIL=NONE SCOPE=MEMORY;


Or 

ALTER SYSTEM SET AUDIT_TRAIL=NONE SCOPE=BOTH;


I've an error system popup in TOAD code ORA-02095.

Thank you for your help Chris and Connor and congratulations for your website.
Chris Saxon
November 02, 2017 - 11:35 am UTC

Note that changing audit/noaudit only comes into effect when you reconnect:

select count(*) from t;

COUNT(*)   
     50000 

select username, timestamp from dba_audit_trail
where  timestamp > systimestamp - interval '30' second
and    obj_name = 'T'
order  by 2 desc;

no rows selected

audit select table;

select count(*) from t;

COUNT(*)   
     50000 

select username, timestamp from dba_audit_trail
where  timestamp > systimestamp - interval '30' second
and    obj_name = 'T'
order  by 2 desc;

no rows selected

conn chris/chris

select count(*) from t;

COUNT(*)   
     50000 

select username, timestamp from dba_audit_trail
where  timestamp > systimestamp - interval '30' second
and    obj_name = 'T'
order  by 2 desc;

USERNAME   TIMESTAMP   
CHRIS      02-NOV-17  

noaudit select table;

select count(*) from t;

COUNT(*)   
     50000 

select username, timestamp from dba_audit_trail
where  timestamp > systimestamp - interval '30' second
and    obj_name = 'T'
order  by 2 desc;

USERNAME   TIMESTAMP   
CHRIS      02-NOV-17   
CHRIS      02-NOV-17   

conn chris/chris

select count(*) from t;

COUNT(*)   
     50000 

select username, timestamp from dba_audit_trail
where  timestamp > systimestamp - interval '30' second
and    obj_name = 'T'
order  by 2 desc;

USERNAME   TIMESTAMP   
CHRIS      02-NOV-17   
CHRIS      02-NOV-17


Notice that after I run audit select table, the query still audit trail returns nothing until I reconnect. And after running noaudit select table, the query is still audited until the new connection.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Security

All of the vital components for a secure database are covered in the Security guide.