Home » RDBMS Server » Server Administration » EXECUTE IMMEDIATE fails with insufficient privilege
EXECUTE IMMEDIATE fails with insufficient privilege [message #369960] Mon, 20 November 2000 16:47 Go to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous message
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:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Sat May 04 05:10:46 CDT 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.