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