trigger to audit select,insert,update and delete statements

941949 Dec 23, 2012 8:52 PM

Currently Being Moderated

hii everybody,,
how can i audit all sql statements (SELECT,INSERT,UPDATE,DELETE) run by the users
in my oracle database USING TRIGGER only .your help is highly appreciated.

thanks and regards,
xyz.

Edited by: 938946 on Dec 23, 2012 8:52 PM

1. Re: trigger to audit select,insert,update and delete statements

sb92075 Dec 23, 2012 8:55 PM (in response to 941949)

Currently Being Moderated

938946 wrote:
hii everybody,,
how can i audit all sql statements (SELECT,INSERT,UPDATE,DELETE) run by the users
in my oracle database USING TRIGGER only .your help is highly appreciated.
Then start writing TRIGGERs; but it would be simpler if you decided to use AUDIT already built into the DB.
Report Abuse

Like (0)
2. Re: trigger to audit select,insert,update and delete statements

jeneesh Dec 23, 2012 8:56 PM (in response to 941949)

Currently Being Moderated

You cannot have trigger for SELECT statements....

How to monitor DML acticity in a database
Report Abuse

Like (0)
3. Re: trigger to audit select,insert,update and delete statements

user639304 Dec 23, 2012 8:57 PM (in response to 941949)

Currently Being Moderated

Hi,

Using trigger only, you can audit update, insert and delete statements, but cannot audit select statements.
Report Abuse

Like (0)
4. Re: trigger to audit select,insert,update and delete statements

941949 Dec 23, 2012 9:16 PM (in response to jeneesh)

Currently Being Moderated

i dnt want to do standard auditing because it will affect the system performance,for this reason i need trigger to audit all sql statement of a specific user who login to the oracle database.your help is apppreciated.

thanks and regards.
xyz
Report Abuse

Like (0)
5. Re: trigger to audit select,insert,update and delete statements

sb92075 Dec 23, 2012 9:20 PM (in response to 941949)

Currently Being Moderated

938946 wrote:
i dnt want to do standard auditing because it will affect the system performance,for this reason i need trigger to audit all sql statement of a specific user who login to the oracle database.your help is apppreciated.

thanks and regards.
xyz
Why do you believe that you can implement better & more efficient than Oracle;
because this is what you imply?

Please explain how you plan on tracking SELECT statements?
Report Abuse

Like (0)
6. Re: trigger to audit select,insert,update and delete statements

941949 Dec 23, 2012 9:47 PM (in response to sb92075)

Currently Being Moderated

we can track 'select' statements using FGA and we can add FGA to a trigger. m i right?

Edited by: 938946 on Dec 23, 2012 9:46 PM
Report Abuse

Like (0)
7. Re: trigger to audit select,insert,update and delete statements

user639304 Dec 23, 2012 9:57 PM (in response to 941949)
Currently Being Moderated

If you track select statements using FGA, then you are not using triggers only. That is why I wrote, earlier, that using trigger only, you cannot audit select statements.

On other hand, you wrote:
i dnt want to do standard auditing because it will affect the system performance,for this reason i need trigger to audit all sql statement of a specific user who login to the oracle database.your help is apppreciated. thanks and regards.
You should know that the auditing built into the database allows you to audit sql statements of a specific user
Report Abuse

Like (0)
8. Re: trigger to audit select,insert,update and delete statements

941949 Dec 23, 2012 10:24 PM (in response to user639304)

Currently Being Moderated

how to do specific user auditing using oracle built in auditing? will it affect the performance of the database?
Report Abuse

Like (0)
9. Re: trigger to audit select,insert,update and delete statements

jeneesh Dec 23, 2012 10:32 PM (in response to 941949)

Currently Being Moderated

938946 wrote:
how to do specific user auditing using oracle built in auditing?
Did you go through the documents?

will it affect the performance of the database?
Auditing require EXTRA work..

Which WILL affect the performance - Whatever method you choose..
Report Abuse

Like (0)
10. Re: trigger to audit select,insert,update and delete statements

EdStevens Dec 24, 2012 5:49 AM (in response to 941949)

Currently Being Moderated

938946 wrote:
i dnt want to do standard auditing because it will affect the system performance
REALLY?!?! And you think your home-grown trigger WON'T affect performance? Please explain the theory behind that.

,for this reason i need trigger to audit all sql statement of a specific user who login to the oracle database.your help is apppreciated.
I'd suggest you actually READ the documentation about auditing. Start here: http://docs.oracle.com/cd/E11882_01/network.112/e16543/auditing.htm#DBSEG006

thanks and regards.
xyz
Report Abuse

Like (0)
11. Re: trigger to audit select,insert,update and delete statements

washington_peres Feb 8, 2013 6:08 AM (in response to EdStevens)

Currently Being Moderated

Ok. AUDIT and all the benefits should be used. Great!

Now, for my question:

Audit on ACCESS ...
SQL> select * from sys.dba_stmt_audit_opts
2 order by user_name, proxy_name, audit_option;

User name Proxy name Audit Option SUCCESS FAILURE
DIRECTORY BY ACCESS BY ACCESS

ALL tables like:
...
OWNER OBJECT_NAM OBJECT ALT AUD COM DEL GRA IND INS LOC REN SEL UPD REF EXE
XYZ abc TABLE -/- -/- -/- -/- -/- -/- -/- -/- -/- A/A -/- -/- -/-
...

All "ACTION_NAME" choose...SELECT

Let say, using dba_audit_trail OR aud$. how can I tell:
- Username, object_name and program used every time SELECT statement occurs?

Thanks
Report Abuse

Like (0)

Go to original post

Actions

Legend

Correct Answers - 10 points
Helpful Answers - 5 points