OraFAQ Forum: Server Administration » EXECUTE IMMEDIATE fails with insufficient privilege

Home » RDBMS Server » Server Administration » EXECUTE IMMEDIATE fails with insufficient privilege

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

EXECUTE IMMEDIATE fails with insufficient privilege [message #369960]

Mon, 20 November 2000 16:47

Chetan
Messages: 21
Registered: November 2000

Junior Member

I can EXECUTE IMMEDIATE 'CREATE GLOBAL TEMPORARY TABLE TEMP_TAB(TNAME VARCHAR2(30))' from SQL*Plus. But when the same code is executed from within the Stored Procedure, it fails with ORA-01031: insufficient privileges.

Report message to a moderator

dbms_sql.execute fails with insufficient privilege -- similar [message #369964 is a reply to message #369960]

Tue, 21 November 2000 02:28

Tarun Chhichhia
Messages: 6
Registered: November 2000

Junior Member

I am having similar problems with dbms_sql,

Please let me know if you find a solution to his.

thanks
Tarun

Report message to a moderator

Re: dbms_sql.execute fails with insufficient privilege -- similar [message #369968 is a reply to message #369960]

Tue, 21 November 2000 03:14

John R
Messages: 156
Registered: March 2000

Senior Member

It sounds like the user executing the package does not have Execute privilege on the DBMS_SQL package. This needs to be granted explicitly, not via a role.

Report message to a moderator

Re: dbms_sql.execute fails with insufficient privilege -- similar [message #369969 is a reply to message #369960]

Tue, 21 November 2000 03:25

Tarun Chhichhia
Messages: 6
Registered: November 2000

Junior Member

How would one explain the fact that the same user executing the dbms_sql, via an anonymous PL/SQL block has no access problems however only when they execute it as a part of a package created by themself...

tarun

Report message to a moderator

Re: EXECUTE IMMEDIATE fails with insufficient privilege [message #369976 is a reply to message #369960]

Tue, 21 November 2000 15:00

Chetan
Messages: 21
Registered: November 2000

Junior Member

I found that all roles are disabled in any named PL/SQL block that executes with definer rights. Named PL/SQL blocks that execute with invoker rights are executed based on privileges granted through enabled roles. Hence, I executed the stored procedure with invoker rights and current roles were used for privilege checking within an invoker rights PL/SQL block. I was thus able to EXECUTE IMMEDIATE from the stored procedure. Thank You guys for your response.

Report message to a moderator

Re: dbms_sql.execute fails with insufficient privilege -- similar [message #369990 is a reply to message #369968]

Thu, 23 November 2000 03:16

John R
Messages: 156
Registered: March 2000

Senior Member

One would explain it by pointing out that roles are suspended within database procedures. An amonymous Pl/Sql block is processed differently.

Try it if you believe otherwise.

Report message to a moderator

Thanks [message #369991 is a reply to message #369960]

Thu, 23 November 2000 03:47

Tarun Chhichhia
Messages: 6
Registered: November 2000

Junior Member

Thanks John..

that explains..I did not mean to respond in a rude way ..

Tarun

Report message to a moderator

Previous Topic:	Specifying a timeout for a query
Next Topic:	How do i select an element of a type object in SQL

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun Dec 22 16:30:56 CST 2024