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.

You cannot have trigger for SELECT statements....

How to monitor DML acticity in a database

Hi,

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

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?

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

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..

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

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