how to grant role in function o procedure ?

[tokienlua]

hi everybody,
in PL/SQL we can run command' grant role to user'
but in funtion or procedure can not do that.
error:ORA-01919
i want grant role to user in procedure
can you help me?
thanks alot.

[jovery]

EXECUTE IMMEDIATE 'grant role to user';

HTH

[tokienlua]

in procedure, it not running

[tokienlua]

in PL/SQL ,EXECUTE IMMEDIATE 'grant role to user' running
but in procedure below, it not run
create or replace procedure test as

begin


EXECUTE IMMEDIATE 'grant csr to lanntthso';


exception when others then
raise;
end;


error :ora-01919 role 'csr'doe not exist
but in BD there is role'csr'

[jovery]

Does the user who owns the procedure have the necessary privs? it works fine for me.

SQL> create role tester;

Role created.

SQL> create or replace procedure test
2 as
3 begin
4 execute immediate 'grant tester to scott';
5 exception when others then
6 raise;
7 end;
8 /

Procedure created.

SQL> exec test;

PL/SQL procedure successfully completed.

SQL> select granted_role
2 from dba_role_privs
3 where grantee = 'SCOTT';

GRANTED_ROLE
------------------------------
TESTER
CONNECT
RESOURCE

3 rows selected.

[tokienlua]

in my base, there many roles already so i write a proc to grant role to user
your example works, i've test it
but many roles in my base, i grant its, proc not work and error :ora-01919
role 'csr' does not exist
can you explain
in PL/SQL, i can execute :grant csr to lanntthso
but in proc, it does't work
help me!

[jovery]

Does the procedure owner have the necessary privileges to grant the role?

i.e GRANT ANY ROLE, or ADMIN OPTION on the role?

[slimdave]

Ah, does the user owning the procedure have the privilege to grant the role directly, or through a role? Confusing, eh? I they have the privilege to grant any role through a role then they can't make use of it in a procedure ... it has to be a privilege granted directly to the user.

[padders]

Unless of course the stored procedure is defined with invoker rights (AUTHID CURRENT_USER) and executed from a call stack executing entirely under invoker rights in which case roles WOULD be enabled and the GRANT ANY ROLE privilege (which was granted by role) would be sufficient to grant a role.

[tokienlua]

user have not privileges to grant the role
so grant role to user in procedure not work.
thanks everybody alot