Re: Setting roles, but NOT as

In article <2.289.234.0N72A4A1_at_active.ch>, robert.jung_at_active.ch (Robert Jung) wrote:

> Subject: Setting roles, but NOT as a default role ...
> OM>(I love roles; they're one of my favorite Oracle7 Server features,
> OM>along with:
> OM>stored procedures, functions, packages
> OM>enhanced pl/sql
> OM>triggers
> OM>referential integrity and other constraints enforced in the database
> OM>engine
>
> Scott,
>
> maybe you can give me an idea on how to do the following:
>
> I wish to grant roles xx,yy,zz to user scott. These roles have a password
> attached to them, and the user doesn't know the passwords, but the program
> he/she uses does. These roles should therefore NOT be the default roles,
> which is what they are after being granted to the user. SO, how do I make
> then DEFAULT=NO for the user? I tried the "alter user scott enable all
> except xx,yy,zz;", but any time a new role, say ww comes in, I need to do the
> "alter user ...." all over again. I'd like to do this also from within a
> program. Any suggestions would be greatly appreciated.
>
> TIA and
>
> Regards
> +=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=+=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=+
> | Real Life : Robert Jung | The opinions expressed here are my own. |
> | Compuserve: 100064,1474_at_compuserve.com |
> | Internet : robert.jung_at_active.ch <<< Internet preferred, CI$ second |
> +=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=+=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=+
>
> * 1st 2.00b #2037 * Dogs come when you call. Cats have answering machines.

Robert
according to the SQL ref man (alter user), a user gets at logon all roles granted to him as default roles. So you have to set the default roles for a user explicitly
alter user rjung default role cc,ff,rr

Hope this helps
LM

-- 
Leo Mannhart
Planning Office
University of Zurich               phone: ++41 1 257 23 34
Kuenstlergasse 15                    fax: ++41 1 257 22 12
CH-8001 Zurich, Switzerland        eMail: mannhart_at_zuv.unizh.ch