Home » SQL & PL/SQL » SQL & PL/SQL » can compile procedure but cannot execute it (Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi)
can compile procedure but cannot execute it [message #358867] Wed, 12 November 2008 14:59 Go to next message
rishg
Messages: 9
Registered: March 2008
Junior Member
Hello,
This problem really baffles me. I created a procedure, compiled it without errors and when I try executing it, I get an 'insufficient privileges' error message. I can run the same code without any errors as an anonymous block, but when I try executing the procedure, it errors. Also, I'm executing it under the same id that it was created in. Not sure if posting the code helps as the code works while running as an anonymous block. Any thoughts or comments? This id has the execute any procedure privilege.


Error report:
ORA-01031: insufficient privileges
ORA-06512: at SG365.QUESTIONABLE_TRANSFERS_LOG", line 62
ORA-06512: at line 2
01031. 00000 - "insufficient privileges"
*Cause: An attempt was made to change the current username or password
without the appropriate privilege. This error also occurs if
attempting to install a database without the necessary operating
system privileges.
When Trusted Oracle is configure in DBMS MAC, this error may occur
if the user was granted the necessary privilege at a higher label
than the current login.
*Action: Ask the database administrator to perform the operation or grant
the required privileges.
For Trusted Oracle users getting this error although granted the
the appropriate privilege at a higher label, ask the database
administrator to regrant the privilege at the appropriate label.
Re: can compile procedure but cannot execute it [message #358868 is a reply to message #358867] Wed, 12 November 2008 15:04 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
Privileges granted through roles do not work for procedures, functions, and packages.

Permissions must be granted explicitly to a user.
Re: can compile procedure but cannot execute it [message #358873 is a reply to message #358868] Wed, 12 November 2008 15:24 Go to previous messageGo to next message
rishg
Messages: 9
Registered: March 2008
Junior Member
So you are saying that rather than the execute any privilege, one should do a
'grant execute procedure_name to username' ?

I had the DBA grant the execute procedure_name to me and executing the procedure resulted in the same error message. This procedure does updates and inserts. what else am I missing?

[Updated on: Wed, 12 November 2008 15:41]

Report message to a moderator

Re: can compile procedure but cannot execute it [message #358877 is a reply to message #358873] Wed, 12 November 2008 16:01 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
Whatever tasks are being performed by the procedure, require specific privileges. These privileges must be granted explicitly.
Re: can compile procedure but cannot execute it [message #358881 is a reply to message #358877] Wed, 12 November 2008 16:48 Go to previous messageGo to next message
rishg
Messages: 9
Registered: March 2008
Junior Member
Pablolee,
So the procedure should be granted update/insert privileges? Something on the lines of
grant update on table T to procedure P ?

Re: can compile procedure but cannot execute it [message #358898 is a reply to message #358867] Wed, 12 November 2008 21:43 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

'grant execute procedure_name to username' will be enough.
Aslo ensure that your user have the DML privilege on the tables referred .

Smile
Rajuvan.

[Updated on: Wed, 12 November 2008 21:48]

Report message to a moderator

Re: can compile procedure but cannot execute it [message #358946 is a reply to message #358898] Thu, 13 November 2008 00:57 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
rajavu1 wrote on Thu, 13 November 2008 03:43
'grant execute procedure_name to username' will be enough.
Aslo ensure that your user have the DML privilege on the tables referred .

Smile
Rajuvan.

Surely your second statement proves your first to be wrong?
Re: can compile procedure but cannot execute it [message #358950 is a reply to message #358881] Thu, 13 November 2008 00:59 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
rishg wrote on Wed, 12 November 2008 22:48
Pablolee,
So the procedure should be granted update/insert privileges? Something on the lines of
grant update on table T to procedure P ?



No, privileges are not granted to a procedure. They are granted to users and/or roles.
If you want to execute procedure p that updates table t, the YOU must have the execute privilege on procedure p AND you must have the update privilege on table t granted to you EXPLICITLY (i.e. NOT via a role)
Re: can compile procedure but cannot execute it [message #358960 is a reply to message #358867] Thu, 13 November 2008 01:17 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

What I mean is .

Its enough to grant Execute Privilege to the procedure and no need to grant update on table to procedure . Becoz OP was wondering ..

Quote:
Something on the lines of
grant update on table T to procedure P ?


Smile
Rajuvan
Re: can compile procedure but cannot execute it [message #358988 is a reply to message #358960] Thu, 13 November 2008 03:14 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I'm pretty sure that if the person who compiled the procedure does not have explicit permission to access the objects referred to by the procedure, then it will still error on execute, regardless of whether you have Execute granted on it.
Re: can compile procedure but cannot execute it [message #359014 is a reply to message #358988] Thu, 13 November 2008 05:42 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
JRowbottom wrote on Thu, 13 November 2008 09:14
I'm pretty sure that if the person who compiled the procedure does not have explicit permission to access the objects referred to by the procedure, then it will still error on execute, regardless of whether you have Execute granted on it.

Of course, as usual, you have it right Smile (unless the proc is compiled with AUTHID current_user)

[Updated on: Thu, 13 November 2008 05:43]

Report message to a moderator

Re: can compile procedure but cannot execute it [message #359019 is a reply to message #359014] Thu, 13 November 2008 05:52 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Good point.
Re: can compile procedure but cannot execute it [message #359102 is a reply to message #358867] Thu, 13 November 2008 13:32 Go to previous messageGo to next message
rishg
Messages: 9
Registered: March 2008
Junior Member
Thank you all for the replies. I had the DBA explicitly grant the execute procedure privilege and update/insert privilege on the tables, yet it doesn't work. I looked up the table_privileges and I see the the SELECT_PRIV is set to 'N'. I guess i need exclusive select privileges too. I asked the DBA to grant the select privileges exclusively. Will post a reply if that finally works.

On a related note, does anyone know what the different flags for columns SELECT_PRIV
INSERT_PRIV
DELETE_PRIV
UPDATE_PRIV in the TABLE_PRIVILEGES stand for. I see values like A, N and Y. They intuitively seem to mean Allow, No and Yes, but I couldn't find any information about them.
Re: can compile procedure but cannot execute it [message #359118 is a reply to message #359102] Thu, 13 November 2008 15:27 Go to previous messageGo to next message
rishg
Messages: 9
Registered: March 2008
Junior Member
Finally, granting the explicit select worked. did not realize that even a select(apart from insert and update) had to be granted explicitly. Thanks all for your help.
Re: can compile procedure but cannot execute it [message #359121 is a reply to message #359118] Thu, 13 November 2008 15:37 Go to previous message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
SELECT has to be granted if you SELECT.
INSERT has to be granted if you INSERT.
UPDATE has to be granted if you UPDATE.
Previous Topic: doing a select on multiple databases in a single query
Next Topic: PL/SQL concatenating other attribute while one attribute is equal
Goto Forum:
  


Current Time: Tue Dec 06 04:49:49 CST 2016

Total time taken to generate the page: 0.11137 seconds