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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Remigiusz Sokolowski Sent: Wednesday, December 19, 2007 2:00 AM To: sbootsma@xxxxxxxxxxxxxx Cc: oracle-l@xxxxxxxxxxxxx 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@xxxxxxxx> 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) -- //www.freelists.org/webpage/oracle-l -- //www.freelists.org/webpage/oracle-l