Skip to Main Content
  • Questions
  • insufficient privilege issue using execute immediate

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Michael.

Asked: January 27, 2005 - 1:24 pm UTC

Last updated: January 27, 2005 - 10:41 pm UTC

Version: 9.2.0

Viewed 1000+ times

You Asked

Hi Tom,

Thanks for this great site!

I wrote a stored procedure which dynamically disables constraints:


CREATE OR REPLACE PROCEDURE COMMON.PRC_DSBL_PART_CNSTR_TEST("PAYOR_IN" IN VARCHAR2) IS
DSBLCTR_sql1 VARCHAR2(100) := 0 ;
SCHEMA_OWNER VARCHAR2(10);

BEGIN
SELECT USER INTO SCHEMA_OWNER FROM DUAL ;


DSBLCTR_sql1 := 'ALTER Table ' || SCHEMA_OWNER || '.' || PAYOR_IN || '_CLAIM_CURPART DISABLE CONSTRAINT ' || PAYOR_IN || '_CLAIM_CURPART_PK' ;

dbms_output.put_line('SCHEMA '||SCHEMA_OWNER);
dbms_output.put_line('DSBL1 '||DSBLCTR_sql1);


EXECUTE IMMEDIATE DSBLCTR_sql1 ;

END PRC_DSBL_PART_CNSTR_TEST ;

This procedure is designed to be used by multiple schemas that have similar table structures. The user provides the payer and everything else is automatic for the schema.

When I run this procedure in a different schema I get the following
error:

SQL> show user;
USER is "BCBSTGT1"
SQL> execute common.prc_dsbl_part_cnstr_test('BCBS');
BEGIN common.prc_dsbl_part_cnstr_test('BCBS'); END;

*
ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at "COMMON.PRC_DSBL_PART_CNSTR_TEST", line 24
ORA-06512: at line 1

However, when I run the command directly I do not get the error:
SQL> SHOW USER;
USER is "COMMON"
SQL> ALTER TABLE BCBSTGT1.BCBS_CLAIM_CURPART DISABLE CONSTRAINT BCBS_CLAIM_CURPA
RT_PK ;

Table altered.

Any idea why this is happening? Thanks in advance.






and Tom said...

</code> http://asktom.oracle.com/Misc/RolesAndProcedures.html <code>


SELECT USER INTO SCHEMA_OWNER FROM DUAL ;
should just be

schema_owner := user;

and schema_owner should probably be a varchar2(30) since that is how big a schema name can be.


You *probably* want this to be an INVOKERS rights routine (using authid current_user) -- meaning, if GEORGE runs this, it will run with GEORGES privileges (roles and all). If MARY runs it, it'll run with her privs (roles and all)

Otherwise you (the owner of this procedure) will need the extremely powerful "ALL" types privileges granted directly to you.

If you have "Expert one on one Oracle" -- I go into great detail on this topic

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Thanks Tom

Michael, January 27, 2005 - 10:41 pm UTC

Thanks for a great answer!

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library