-
how to grant role in function o procedure ?
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.
-
Code:
EXECUTE IMMEDIATE 'grant role to user';
HTH
Jim
Oracle Certified Professional
"Build your reputation by helping other people build theirs."
"Sarcasm may be the lowest form of wit but its still funny"
Click HERE to vist my website!
-
it's not running in procedure
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 ra-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.
Jim
Oracle Certified Professional
"Build your reputation by helping other people build theirs."
"Sarcasm may be the lowest form of wit but its still funny"
Click HERE to vist my website!
-
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 ra-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?
Jim
Oracle Certified Professional
"Build your reputation by helping other people build theirs."
"Sarcasm may be the lowest form of wit but its still funny"
Click HERE to vist my website!
-
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
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|