OraFAQ Forum: Performance Tuning » logging DDL statements, etc?

Home » RDBMS Server » Performance Tuning » logging DDL statements, etc?

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

logging DDL statements, etc? [message #64957]

Thu, 18 March 2004 00:23

Akira Kenshin
Messages: 1
Registered: March 2004

Junior Member

i just came up with this hypothetical situation (it may, or may not happen in real life). and if it does, i would like how to solve it:

imagine that a table has been deleted (say maliciously by a certain user)...

is there a way (an sql srcipt or something) that i can use to find out when, what DDL statement was used, probably user name, ip address (most imporatnt!), etc.?

oracle just has too many dictionary/views, that im at a lost on how to make sense of the information to get what i need.

thanks in avanced.

Report message to a moderator

Re: logging DDL statements, etc? [message #64961 is a reply to message #64957]

Thu, 18 March 2004 04:18

Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

You can enable auditing, which pretty gives u the information about 'who did wat'?
but why dont u just prevent anyone 'doing any harm'?
something like this

mag@mutation_mutation > truncate table emp;
truncate table emp
               *
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-20111: truncate is not allowed .... table is protected by DBA
ORA-06512: at line 7

Report message to a moderator

Re: logging DDL statements, etc? [message #64962 is a reply to message #64957]

Thu, 18 March 2004 04:21

Frank Naude
Messages: 4581
Registered: April 1998

Senior Member

Hi,

You can write a database or schema level trigger to fire ON DROP and log all the required info (with IP Address) to a separate table.

The scripts section of this site contains some database level trigger examples.

Best regards.

Frank

Report message to a moderator

Re: logging DDL statements, etc? [message #64967 is a reply to message #64957]

Thu, 18 March 2004 15:36

Vijay
Messages: 116
Registered: September 1999

Senior Member

I am assuming that your database is 8i or above and you can do 2 things to get this information.

1) create a logon trigger for the whole database and log the username,machinename , last sql statement issued, logon and logoff time into a table. I am not sure if you can log the IP address.

2) Use logminer to find out the username, DDL statement issued and time issued. With logminer you can undo the changes too.

Once you find when a statement was issued (for e.g delete from x ) from the logminer, you can cross check with the audit table in step 1 and see who logged in at that time and get the machine name.

Report message to a moderator

Previous Topic:	performance tuning novice
Next Topic:	Memmory

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Nov 29 20:58:21 CST 2024