This discussion is archived
4 Replies Latest reply: Aug 21, 2013 4:59 PM by davidp 2 RSS

Why the DR unit doesn't fire the schema trigger when invoked remotely?

dariyoosh Journeyer
Currently Being Moderated

Dear all,

 

I have a question about oracle schema triggers and I would appreciate if you could kindly give me a hand.

 

Oracle version:   11gR2 (11g Enterprise Edition Release 11.2.0.1.0 - 64bit)

OS:                      Linux Fedora Core 17 (X86_64)

 

I was reading the online documentation about Schema Triggers where oracle says:

Suppose that both user1 and user2 own schema triggers, and user1 invokes a DR unit owned by user2. Inside the DR unit, user2 is the current user. Therefore, if the DR unit initiates the triggering event of a schema trigger that user2 owns, then that trigger fires.

 

I wanted to see this behaviour in practice so I made up the following test case:

 

- There are two schemas:

  • testuser where I create a procedure with AUTHID DEFINER (therfore a DR unit) named createTab. This procedure takes as parameter a table name and if no table with that name already exists in the schema testuser, it will create a new table with that name having a single column of type NUMBER (well it's just an example for the purpose of this question, in practice I never create my tables this way)

  • training is therefore another schema to whom we grant the EXECUTE privilege on the above mentioned procedure createTab so that it may be possible to create tables on testuser schema by calling this remote procedure.

 

The idea behind the test is to create a schema trigger for testuser, so that whenever there is, for example, a table creation, a message is inserted into a log table (just an example to show a proof that the schema trigger was fired upon the table creation event). Now assuming that I grant the  EXECUTE privilege on the createTab procedure to the schema training, then any remote table creation has to fire the schema trigger, because according to the documentation, inside the DR unit, the user is not considered to be the invoking user (= training) but actually the owner (= testuser) who created the trigger and the procedure.

 

The problem is that I didn't manage to see this in my test. Consequently I'm going to write down here my test case so that you may have a look at it and kindly indicate where did I make mistake(s) and what did I misunderstand in the documentation.

 

Here is therefore what I created on the schema testuser

 

Code

SET SQLBLANKLINES ON;

ALTER SESSION SET PLSQL_WARNINGS='ENABLE:ALL';

SET SERVEROUTPUT ON;

 

 

-- A log table into which the schema trigger inserts messages
-- indicating that the schema trigger was fired (just as a proof)

CREATE TABLE tablog(logMsg VARCHAR2(100));

 

 

--The following is the procedure that updates the above defined (tablog) log table

--This procedure (autonomous transaction) is called by the schema trigger

CREATE OR REPLACE PROCEDURE updateLog(p_logMsg IN tablog.logMsg%TYPE)

AUTHID DEFINER

IS

    PRAGMA AUTONOMOUS_TRANSACTION;

BEGIN

    INSERT INTO tablog(logMsg) VALUES(p_logMsg);

    COMMIT;

END updateLog;

/

SHOW ERRORS;

 

 

-- So this is the procedure we use to create tables (which will be called

-- remotely from other schema -> training)

-- As it was said earlier, the procedure takes a table
-- name as parameter and creates a new table with a single NUMBER type column

-- only if no table with that name already exists

CREATE OR REPLACE PROCEDURE createTab

(

    p_tabName   IN  user_tables.table_name%TYPE

)

AUTHID DEFINER   -- So this is a DR unit as we specify explicitly AUTHID DEFINER

IS

BEGIN

    <<bk>>

    DECLARE

        tabName user_tables.table_name%TYPE;

    BEGIN

        -- Check to see whether a table with the name p_tabName
        -- already exists

        SELECT t1.table_name INTO bk.tabName

        FROM   user_tables t1

        WHERE  t1.table_name = upper(p_tabName);

 

        EXCEPTION

            -- No table with that name exists, so we create it now

            WHEN NO_DATA_FOUND THEN

                EXECUTE IMMEDIATE 'CREATE TABLE ' ||

                    p_tabName || '(n NUMBER)';        

    END;

END createTab;

/

SHOW ERRORS;

 

 

-- And finally this is the schema trigger for the schema 'testuser'

-- Any call of the above mentioned procedure createTab (if the procedure
-- creates a new table) fires the following trigger

CREATE OR REPLACE TRIGGER testuser_schema_tr

BEFORE CREATE ON testuser.schema

BEGIN

    -- Just inserting a message into the log table indicating the proof
    -- that our schema trigger was fired upon the CREATE TABLE
    -- statements

    updateLog

    (

        to_char(sysdate, 'DD-MON-YYYY HH24:Mi:ss') ||

                ': Schema trigger for testuser fired'

    );

END testuser_schema_tr;

/

SHOW ERRORS;

 

 

 

-- So here I grant the required privileges so that the user/schema training
-- may also be able to run remotely my procedure

GRANT EXECUTE ON createTab TO training;

GRANT SELECT  ON tablog    TO training;

 

 

First I tested the procedure createTab locally (therefore being connected as testuser schema, that is, the owner of the procedure and the trigger). Everything worked pretty well and upon each table creation the log table was updated by the trigger which showed that in fact upon each CREATE TABLE statement, the trigger was fired.

 

However, when I opened a new SQL*Plus session, this time being connected as training schema, I observed that, again, it was possible to create tables remotely on testuser schema, but the log table was not updated any more, which means that the trigger was not fired upon the CREATE TABLE statements which were issued remotely ( by calling remotely the createTab procedure).

 

Code

SQL> EXECUTE testuser.createTab('tmptab');

 

 

PL/SQL procedure successfully completed.

 

 

SQL> SELECT * FROM testuser.tablog;

 

 

no rows selected

 

 

SQL> SHOW USER

 

USER is "TRAINING"


SQL>

 

Any idea? Why the DR unit (createTab procedure) doesn't fire the schema trigger, unlike what documentation says, when called remotely?

 

Thanks in advance,

Dariyoosh

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points