Scripting the addition of a Default Role

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.  

Thanks for any suggestions.      

Sam Bootsma

Oracle Database Administrator

Information Technology Services
George Brown College

Phone: 416-415-5000 x4933
Fax: 416-415-4836
E-mail: sbootsma_at_georgebrown.ca <mailto:sbootsma_at_georgebrown.ca>  

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