Oracle Trigger on Schema not being triggered by other users besides the Schema owner

Question

I have the following table and trigger.

CREATE TABLE LogSchema (
    user varchar2(100),
    date_ date,
    operation varchar2(100),
    obj_type varchar2(100),
    obj_name varchar2(100)
);

CREATE OR REPLACE TRIGGER LogSchema
AFTER DDL ON USER1.SCHEMA 
/* can't use ON DATABASE or else it will log 
   everything that happens on all schemas*/
DECLARE
BEGIN
  INSERT INTO LogSchema VALUES
     (USER
       , SYSDATE
       , sys.sysevent
       , sys.dictionary_obj_type
       , sys.dictionary_obj_name);

END LogSchema;

I want to log every DDL action (create/drop table/procedure etc) that happens on USER1 schema. This trigger is only being executed if USER1 makes a DDL action, if USER2 makes a DDL action on USER1 schema like:

CREATE TABLE USER1.TEST (
        test varchar2(100)
);

it doesn't get logged. Anyone knows what is wrong with my trigger?

Answer 1

The "ON user.schema" clause doesn't work quite the way you think. From the 10gR2 SQL Reference:

"The trigger fires whenever any user connected as schema initiates the triggering event."

(emphasis mine)

So, the trigger only logs DDL issue when connected AS USER1, not when DDL is performed on the schema USER1.

Answer 2

Why do you 'build your own' and not use standard database functionality like 'Auditing'?