Home » SQL & PL/SQL » SQL & PL/SQL » snychronized role
snychronized role [message #208089] Fri, 08 December 2006 02:20 Go to next message
ehegagoka
Messages: 493
Registered: July 2005
Senior Member
hi!,
just want to ask, if a have a number of users, for example 4 users, and then i want all they're roles to be the same, is it possible in only sql to select all those roles not available to each of them then grant it to them? thanks =)


regards,
rhani


i tried this


SQL> select distinct 'grant '||a.granted_role||' to '||a.grantee||';'
  2  from dba_role_privs a, (select frus_code from ft_freight_user where frus_code
  3                           in ('M_ARMENTAN', 'EL_SANTIAG', 'L_ROYO')) b
  4  where  a.grantee = b.frus_code
  5  /

'GRANT'||A.GRANTED_ROLE||'TO
--------------------------------------------------------------------------------
grant CONNECT to EL_SANTIAG;
grant CONNECT to L_ROYO;
grant CONNECT to M_ARMENTAN;
grant FT_ALL_CDPS to EL_SANTIAG;
grant FT_ALL_CDPS to L_ROYO;
grant FT_ALL_CDPS to M_ARMENTAN;
grant FT_ALL_LAND_TRANS to EL_SANTIAG;
grant FT_ALL_LAND_TRANS to L_ROYO;
grant FT_ALL_LAND_TRANS to M_ARMENTAN;
grant FT_DOC_TRANS_ROLE to EL_SANTIAG;
grant FT_DOC_TRANS_ROLE to L_ROYO;
grant FT_DOC_TRANS_ROLE to M_ARMENTAN;
grant FT_LAND_TRANS_ROLE to EL_SANTIAG;
grant FT_LAND_TRANS_ROLE to L_ROYO;
grant FT_LAND_TRANS_ROLE to M_ARMENTAN;
grant FT_VING_VT_BOOK_ROLE to EL_SANTIAG;
grant FT_VING_VT_BOOK_ROLE to L_ROYO;
grant FT_VING_VT_BOOK_ROLE to M_ARMENTAN;
grant RESOURCE to EL_SANTIAG;
grant RESOURCE to L_ROYO;
grant RESOURCE to M_ARMENTAN;

21 rows selected





but it doesnt apply the extra role FT_AR_SUPERVISOR of EL_SANTIAG to the other user



SQL> select 'grant '||a.granted_role||' to '||a.grantee||';' as gr
  2  from dba_role_privs a, (select frus_code from ft_freight_user where frus_code
  3                               in ('M_ARMENTAN', 'EL_SANTIAG', 'L_ROYO')) b
  4  where  a.grantee = b.frus_code
  5  /

GR
--------------------------------------------------------------------------------
grant CONNECT to L_ROYO;
grant RESOURCE to L_ROYO;
grant FT_ALL_CDPS to L_ROYO;
grant FT_ALL_LAND_TRANS to L_ROYO;
grant FT_DOC_TRANS_ROLE to L_ROYO;
grant FT_LAND_TRANS_ROLE to L_ROYO;
grant FT_VING_VT_BOOK_ROLE to L_ROYO;
grant CONNECT to EL_SANTIAG;
grant RESOURCE to EL_SANTIAG;
grant FT_ALL_CDPS to EL_SANTIAG;
grant FT_AR_SUPERVISOR to EL_SANTIAG;
grant FT_ALL_LAND_TRANS to EL_SANTIAG;
grant FT_DOC_TRANS_ROLE to EL_SANTIAG;
grant FT_LAND_TRANS_ROLE to EL_SANTIAG;
grant FT_VING_VT_BOOK_ROLE to EL_SANTIAG;
grant CONNECT to M_ARMENTAN;
grant RESOURCE to M_ARMENTAN;
grant FT_ALL_CDPS to M_ARMENTAN;
grant FT_ALL_LAND_TRANS to M_ARMENTAN;
grant FT_DOC_TRANS_ROLE to M_ARMENTAN;

GR
--------------------------------------------------------------------------------
grant FT_LAND_TRANS_ROLE to M_ARMENTAN;
grant FT_VING_VT_BOOK_ROLE to M_ARMENTAN;

22 rows selected

SQL> 

[Updated on: Fri, 08 December 2006 02:45]

Report message to a moderator

Re: snychronized role [message #208129 is a reply to message #208089] Fri, 08 December 2006 05:01 Go to previous messageGo to next message
Littlefoot
Messages: 20888
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
If I understood you correctly, you'd like all your users to have identical privileges. Right?

If so, create a role (call it, for example, 'MY_DEFAULT_ROLE'). Then create a script (pure .SQL file) which would look like this:
GRANT CONNECT TO my_default_role;
GRANT ft_all_cdps TO my_default_role;
etc. etc.

Run it (@my_role_script.sql). Role will have all those privileges (hopefully).
Finally, GRANT my_default_role TO every_user_you_want.

Doing so, you will not have to worry who was granted what - just grant everything to everyone; there's no problem (i.e. it won't fail) if you re-grant any privilege once again (and again and again ...).
Re: snychronized role [message #208429 is a reply to message #208129] Sun, 10 December 2006 19:46 Go to previous message
ehegagoka
Messages: 493
Registered: July 2005
Senior Member
hi!,
thank you very much sir =)
Previous Topic: tigger
Next Topic: how to make use of save text.
Goto Forum:
  


Current Time: Fri Dec 02 12:41:59 CST 2016

Total time taken to generate the page: 0.05026 seconds