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 18.104.22.168.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
SET SQLBLANKLINES ON;
ALTER SESSION SET PLSQL_WARNINGS='ENABLE:ALL';
SET SERVEROUTPUT ON;
-- A log table into which the schema trigger inserts messages
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)
INSERT INTO tablog(logMsg) VALUES(p_logMsg);
-- 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
-- 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
-- Check to see whether a table with the name p_tabName
SELECT t1.table_name INTO bk.tabName
FROM user_tables t1
WHERE t1.table_name = upper(p_tabName);
-- No table with that name exists, so we create it now
WHEN NO_DATA_FOUND THEN
EXECUTE IMMEDIATE 'CREATE TABLE ' ||
p_tabName || '(n NUMBER)';
-- And finally this is the schema trigger for the schema 'testuser'
-- Any call of the above mentioned procedure createTab (if the procedure
CREATE OR REPLACE TRIGGER testuser_schema_tr
BEFORE CREATE ON testuser.schema
-- Just inserting a message into the log table indicating the proof
to_char(sysdate, 'DD-MON-YYYY HH24:Mi:ss') ||
': Schema trigger for testuser fired'
-- So here I grant the required privileges so that the user/schema training
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).
SQL> EXECUTE testuser.createTab('tmptab');
PL/SQL procedure successfully completed.
SQL> SELECT * FROM testuser.tablog;
no rows selected
SQL> SHOW USER
USER is "TRAINING"
Any idea? Why the DR unit (createTab procedure) doesn't fire the schema trigger, unlike what documentation says, when called remotely?
Thanks in advance,