force ddl procedure to execute as calling user

Say you have a user DBAUSER with DBA rights. As DBAUSER you need to create a trigger in a different schema.
 
 
As DBAUSER you simply enter

CREATE OR REPLACE TRIGGER otherschema.my_trigger ..

without any problems.
 
 
You need to create the same trigger in a couple of different schema’s though so you decide to make a procedure to make the triggers without duplicating code

CREATE OR REPLACE PROCEDURE DBAUSER.create_trigger( schema IN VARCHAR2 )
IS
BEGIN
  EXECUTE IMMEDIATE 'CREATE OR REPLACE TRIGGER ' || schema || '.my_trigger............'

 
 
Looking at it, executing the following code as DBAUSER

BEGIN
  DBAUSER.create_trigger(schema => 'otherschema')
END;
/

shouldn’t give any problems either, right?
 
 
This is actually a situation a colleage handed me asking me to help him figure out why o why the last statement DID give an error being:

ORA-01031: insufficient privileges

He had been staring at it all afternoon trying to understand and I just stared some more.
 
 
Apparently Oracle didn’t get that the caller of the procedure also needed to be the executer of the command inside the procedure.
Solution:

CREATE OR REPLACE PROCEDURE DBAUSER.create_trigger( schema IN VARCHAR2 )
AUTHID CURRENT_USER
IS
BEGIN
  EXECUTE IMMEDIATE 'CREATE OR REPLACE TRIGGER ' || schema || '.my_trigger............'

 
This forces the DDL to be excuted ‘in’ the schema calling it.
So say you have a procedure in schema X to execute a DDL statement like creating a table (without schema prefix in the DDL command), you grant execute to public and execute it as schema Y. Without AUTHID the table is created in schema X, with AUTHID it is created in schema Y.
 
 
 
 
With thanks to my colleage T. Aalsma

1 comment to force ddl procedure to execute as calling user

Leave a Reply

 

 

 

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>