Home » SQL & PL/SQL » SQL & PL/SQL » privileges needed for execute immediate 'grant select...'?
privileges needed for execute immediate 'grant select...'? [message #179215] Mon, 26 June 2006 07:01 Go to next message
hellcat
Messages: 84
Registered: January 2006
Location: Zug, Switzerland
Member
hi

I've got a proc. looking like this:
create or replace procedure grant_table(p_table in varchar2, p_privilege in varchar2, p_to_user in varchar2, p_grantoption in boolean)
authid current_user
is
begin
  execute immediate 'grant ' || p_privilege || ' on ' || p_table || ' to ' || p_to_user || case when p_grantoption then ' with grant option' else '' end;
end grant_table;


unfortunately I get a runtime error (ORA-01031: insufficient privileges). what privilege do I need to grant directly to this account that it can successfully grant the privs. ?

thanks
Re: privileges needed for execute immediate 'grant select...'? [message #179218 is a reply to message #179215] Mon, 26 June 2006 07:15 Go to previous messageGo to next message
Art Metzer
Messages: 2480
Registered: December 2002
Senior Member
Re: privileges needed for execute immediate 'grant select...'? [message #179221 is a reply to message #179218] Mon, 26 June 2006 07:22 Go to previous messageGo to next message
hellcat
Messages: 84
Registered: January 2006
Location: Zug, Switzerland
Member
thanks, I know this. that's why I asked which priv. I have to grant directly (not through a role)...
Re: privileges needed for execute immediate 'grant select...'? [message #179226 is a reply to message #179221] Mon, 26 June 2006 07:38 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
You will have to have whichever privilege you are attempting to grant, with the grant option.

Why do all these users need to have these privs directly?
Can you not use Roles for this?
Re: privileges needed for execute immediate 'grant select...'? [message #179254 is a reply to message #179226] Mon, 26 June 2006 08:51 Go to previous message
hellcat
Messages: 84
Registered: January 2006
Location: Zug, Switzerland
Member
it's part of a deployment script used from dev. to test environment.

the problem was: authid current_user

I made this proc. to be able to grant tables from a different schema in which I obvously should not use authid current_user...

thanks
Previous Topic: how do i select data from master detail table using single cursor
Next Topic: Diffrence
Goto Forum:
  


Current Time: Sat Aug 30 03:08:13 CDT 2025