RE: Scripting the addition of a Default Role

Sam, what is the point in dropping and recreating role F every night? I do not understand what benefit there is do this action.

• Mark D Powell -- Phone (313) 592-5148

-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Remigiusz Sokolowski Sent: Wednesday, December 19, 2007 2:00 AM To: sbootsma_at_georgebrown.ca
Cc: oracle-l_at_freelists.org
Subject: Re: Scripting the addition of a Default Role

Sam Bootsma wrote:
>
> Hello all,
>
> Can anybody tell me if there is a simple way to script the addition of

> a role as a default role? Without breaking future additions of default

> or non-default roles? Here is an example to show what I mean:
>
> User X has been granted roles A,B,C,D, and E. Roles A,B, and C are
> default roles; roles D and E are non-default.
>
> If I add a new role F that I want to give user X as a default role I
> can do it by issuing: alter user X default role all except D,E;
>
> This works. In fact, I regenerate role F every night by dropping the
> role, recreating it, granting privileges to the role, and then
> granting it to the appropriate users, including user X. And, I have
> this scripted (of course) and scheduled to run every night.
>
> This works fine until six months later somebody else grants user X an
> additional role G as a non-default role. Oops, the script runs at
> night and all of a sudden user X has role G as a default role.
>
> Can anybody tell me if there is a simple way to work-around this
> problem? I know I can change the script to be "alter user default role

> A,B,C;", but this doesn't really solve my problem either. All it does
> is cause a newly added default role to not be a default role the next
day.
>

I think You could simply change Your set of roles into something more dynamic 1. ask dba_role_privs about non-default roles (Your script needs much privileges anyway, so I assume it is not a problem) 2. concatenate them into comma-separated string 3. issue execute immediate 'alter user X default role all except '||my_string;

Am I miss anything?

-- 

------------------------------------------------------------------------
Remigiusz Sokolowski <rems_at_wp-sa.pl>
WP/PTI/DIP/ZAB (+04858) 52 15 770
MySQL v04.x,05.x; Oracle v10.x

Zastrzezenia:
1. Wylaczenie danej funkcjonalnosci oznacza, ze niezwlocznie przystapimy
   lub juz pracujemy nad jej uruchomieniem 2. Niniejsza wiadomosc
stanowi jedynie wyraz prywatnych pogladow autora 
   i nie jest w zadnym wypadku zwiazana ze stanowiskiem przedsiebiorstwa

   Wirtualna Polska S.A.
------------------------------------------------------------------------



WIRTUALNA  POLSKA  SA, ul. Traugutta 115c, 80-226 Gdansk; NIP:
957-07-51-216; Sad Rejonowy Gdansk-Polnoc KRS 0000068548, kapital
zakladowy 62.880.024 zlotych (w calosci wplacony)
--
http://www.freelists.org/webpage/oracle-l


--
http://www.freelists.org/webpage/oracle-l