Solved

A Trigger For All Deletes In A Schema

Posted on 2004-03-25
9
498 Views
Last Modified: 2010-08-05
Hello,

I want to create a trigger that captures user,object,event(which would be a delete) and most importantly ip_address when a user performs a delete on any table within a schema.

I created a trigger to execute on the database(see below) when a DDL event takes place:

CREATE TRIGGER DDLTRIGGER AFTER DDL ON DATABASE
DECLARE
ip_addr    VARCHAR2(30);

BEGIN

SELECT sys_context ('USERENV', 'IP_ADDRESS')
  INTO ip_addr
  FROM dual;

INSERT INTO DDLEvents
 (UserName, DDLDate, DDLObjType, DDLObjOwner, DDLObjName, DDLEvent,IP_ADDR)
 VALUES
 (ora_login_user, sysdate, ora_dict_obj_type,ora_dict_obj_owner, ora_dict_obj_name, ora_sysevent,ip_addr);
END;

Is there a way to create a trigger that will insert basically the same information above into a table when a delete occurs on any table within a schema?

Thanks
Michele
0
Comment
Question by:mmemon
9 Comments
 
LVL 47

Expert Comment

by:schwertner
ID: 10677908
Normal triggers cannot be based on a schema or a database.
So you can trigger only schema or database specific events.  
0
 
LVL 8

Expert Comment

by:baonguyen1
ID: 10678607
Add more to schwertner.

You have five type of triggers:

1. Statement triggers: on particular statement or statement that act on a table, view
2. Row triggers: fire on each row affected
3. Instead of triggers: execute in place of actual issuing statenment
4.System Event Trigger: fire when db startup, shutdown, server errors.
5. User Event triggers:user logon/off, DDL, DML actions.

So you can not create a DATABASE trigger as you specified but you can create a trigger to fire when a specific schema do some actions on objects ...

Hope this helps
0
 

Author Comment

by:mmemon
ID: 10678769
Thank you for your responses.

I was trying to avoid writting a trigger for each table. I would like to store some information when a delete happens on any table in the schema. I guess that is not possible.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 8

Accepted Solution

by:
baonguyen1 earned 125 total points
ID: 10679006
Yes, using user event trigger you can only trigger on the statements: CREATE, ALTER, DROP, ANALYZE, RENAME, TRUNCATE on a object other than INSERT, UPDATE, DELETE ... For example, you can log every object name, type that the user A drop:

create trigger drop_objs
before drop on a.schema
begin
insert into table values(ora_dict_obj_name, ora_dict_obj_type,
ora_client_ip_address, ....);
end;

HTH


0
 

Expert Comment

by:mickyfinn
ID: 10681955
SELECT
'create trigger catch_del_'||table_name||' after delete on'||owner||'.'||table_name||'
begin
insert into history_tables values (ora_dict_obj_name, ora_dict_obj_type,
ora_client_ip_address, ....); end;'
FROM ALL_TABLES

cut and paste output from above back into SQLPlus.  You'll need to edit the insert to match whatever your log table looks like.

Micky
0
 
LVL 2

Expert Comment

by:aabbas
ID: 10686525
Yes, probably this will reduce stuff to type. But the above script will create triggers for all the tables that exist within the database (i.e. for all schemas).

You will need to change it as follows, so catch just a specific schema.

select
'create trigger catch_del_' || table_name || ' after delete on ' || table_name || '
begin
insert into Del_Events
 (UserName, Del_Date, Table_Name, IP_ADDR)
 values
 (user, sysdate, ora_dict_obj_name, sys_context (''USERENV'', ''IP_ADDRESS''));
end;'
from user_tables;

Hope it will help.

Best of luck.
0
 
LVL 3

Expert Comment

by:rajnadimpalli
ID: 10697247
Are we forgetting that Oracle Audit trails can able to capture IP_address/USER_HOST/USER Terminal information ...along with user actions on objects...This is must better compared to triggers with very low foot print too...

See below.....

SQL> show parameter audit

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
audit_sys_operations                 boolean     FALSE
audit_trail                          string      DB <---- Database Audit Trails..
transaction_auditing                 boolean     TRUE
SQL> select count(*) from sys.aud$;

  COUNT(*)
----------
         0

SQL> select * from dba_audit_trail;

no rows selected

SQL> AUDIT DELETE TABLE BY SCOTT;

Audit succeeded.

SQL>
================

SQL> delete from  employees where rownum < 2;

1 row deleted.

SQL> commit;

Commit complete.

SQL> show user
USER is "SCOTT"
SQL>
================


SQL>@hsel
enter statement : select * from sys.aud$

SESSIONID                     : 140
ENTRYID                         : 5
STATEMENT                     : 16
TIMESTAMP#                    : 27-mar-2004 13:14:50
USERID                        : SCOTT             <----- Use
USERHOST                      :     <------------------ Some cases this will be populated IP address
TERMINAL                      : RAJ-R29YPPRS7WU   <----- User Terminal Name/User PC name/IP address
ACTION#                       : 103    <--- Event (103 = Delete)
RETURNCODE                    : 0
OBJ$CREATOR                   : SCOTT  
OBJ$NAME                      : EMPLOYEES         <---- Table name
AUTH$PRIVILEGES               :
AUTH$GRANTEE                  :
NEW$OWNER                     :
NEW$NAME                      :
SES$ACTIONS                   : ---S------------  <--- Event
SES$TID                       : 5924
LOGOFF$LREAD                  :
LOGOFF$PREAD                  :
LOGOFF$LWRITE                 :
LOGOFF$DEAD                   :
LOGOFF$TIME                   :
COMMENT$TEXT                  :
CLIENTID                      :
SPARE1                        : RAJ-R29YPPRS7WU\Administrator
SPARE2                        :
OBJ$LABEL                     :
SES$LABEL                     :
PRIV$USED                     :
SESSIONCPU                    :
-----------------

SQL> desc sys.AUD$
 Name                          Null?    Type
 ----------------------------- -------- --------------------
 SESSIONID                     NOT NULL NUMBER
 ENTRYID                       NOT NULL NUMBER
 STATEMENT                     NOT NULL NUMBER
 TIMESTAMP#                    NOT NULL DATE
 USERID                                 VARCHAR2(30)
 USERHOST                               VARCHAR2(128)
 TERMINAL                               VARCHAR2(255)
 ACTION#                       NOT NULL NUMBER
 RETURNCODE                    NOT NULL NUMBER
 OBJ$CREATOR                            VARCHAR2(30)
 OBJ$NAME                               VARCHAR2(128)
 AUTH$PRIVILEGES                        VARCHAR2(16)
 AUTH$GRANTEE                           VARCHAR2(30)
 NEW$OWNER                              VARCHAR2(30)
 NEW$NAME                               VARCHAR2(128)
 SES$ACTIONS                            VARCHAR2(19)
 SES$TID                                NUMBER
 LOGOFF$LREAD                           NUMBER
 LOGOFF$PREAD                           NUMBER
 LOGOFF$LWRITE                          NUMBER
 LOGOFF$DEAD                            NUMBER
 LOGOFF$TIME                            DATE
 COMMENT$TEXT                           VARCHAR2(4000)
 CLIENTID                               VARCHAR2(64)
 SPARE1                                 VARCHAR2(255)
 SPARE2                                 NUMBER
 OBJ$LABEL                              RAW(255)
 SES$LABEL                              RAW(255)
 PRIV$USED                              NUMBER
 SESSIONCPU                             NUMBER

SQL>

-R

0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now