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.
Printable View
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.
HTHCode:EXECUTE IMMEDIATE 'grant role to user';
in procedure, it not running
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'
Does the user who owns the procedure have the necessary privs? it works fine for me.
Code: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.
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!
Does the procedure owner have the necessary privileges to grant the role?
i.e GRANT ANY ROLE, or ADMIN OPTION on the role?
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.
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.
user have not privileges to grant the role
so grant role to user in procedure not work.
thanks everybody alot