Home » RDBMS Server » Security » how to grant role using EXECUTE IMMEDIATE (oracle 11gr2 linux 6.4)
how to grant role using EXECUTE IMMEDIATE [message #604534] Tue, 31 December 2013 16:13 Go to next message
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>
Re: how to grant role using EXECUTE IMMEDIATE [message #604535 is a reply to message #604534] Tue, 31 December 2013 17:46 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
The standard advice when (ab)using EXECUTE IMMEDIATE is to compose the SQL statement in a single VARCHAR2 variable
Then print the variable before passing it to EXECUTE IMMEDIATE.
COPY the statement & PASTE into sqlplus to validate its correctness.

> x1 := 'grant '||item.granted_role||' to TEST_USER1 with admin option;';

what is ITEM.GRANTED_ROLE?
Re: how to grant role using EXECUTE IMMEDIATE [message #604549 is a reply to message #604534] Wed, 01 January 2014 01:39 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Remove the ";" of your execute immediate parameter.
x1 := 'grant '||item.granted_role||' to TEST_USER1 with admin option;';
Re: how to grant role using EXECUTE IMMEDIATE [message #605705 is a reply to message #604549] Mon, 13 January 2014 13:38 Go to previous message
kesavansundaram
Messages: 183
Registered: October 2007
Location: MUMBAI
Senior Member
Hi Michel,

I am sorry for late response. Yes, it works if i remove ";" as per your above instruction.
Thank you very much and it helped me a lot in my working env.

Thanks again !!

Kesavan
Previous Topic: password verify function
Next Topic: password expiration date as per profile attached
Goto Forum:
  


Current Time: Thu Mar 28 09:42:07 CDT 2024