how to grant role using EXECUTE IMMEDIATE [message #604534] |
Tue, 31 December 2013 16:13  |
kesavansundaram
Messages: 183 Registered: October 2007 Location: MUMBAI
|
Senior Member |
|
|
Team,
I request you to guide me in achieving below scenario:
i have a master user TEST_ADMIN with below 9 roles granted to it. I need to grant those roles programmatically to other users.
I am able to print the value using dbms_output.put_line. but I am not able to use that in the GRANT statement.
Could you please help me. This will help me a lot.
Thank you very much
SQL> select * from DBA_ROLE_PRIVS where grantee = 'TEST_ADMIN';
GRANTEE GRANTED_ROLE ADM DEF
------------------------------ ------------------------------ --- ---
TEST_ADMIN AQ_USER_ROLE YES YES
TEST_ADMIN RECOVERY_CATALOG_OWNER YES YES
TEST_ADMIN CTXAPP YES YES
TEST_ADMIN AQ_ADMINISTRATOR_ROLE YES YES
TEST_ADMIN DBA YES YES
TEST_ADMIN DEVELOPER_WRITE_ROLE YES YES
TEST_ADMIN DEVELOPER_READ_ROLE YES YES
TEST_ADMIN CONNECT YES YES
TEST_ADMIN RESOURCE YES YES
9 rows selected.
SQL> declare
2 x1 varchar2(250);
3 cursor c1 is
4 SELECT GRANTED_ROLE FROM DBA_ROLE_PRIVS WHERE grantee = 'TEST_ADMIN';
5 begin
6 for item in c1
7 loop
8 dbms_output.put_line(item.granted_role);
9 x1 := 'grant '||item.granted_role||' to TEST_USER1 with admin option;';
10 execute immediate x1;
11 end loop;
12 end;
13 /
AQ_USER_ROLE
declare
*
ERROR at line 1:
ORA-00911: invalid character
ORA-06512: at line 10
SQL>
|
|
|
|
|
|