Content-class: urn:content-classes:message
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: 8bit
Subject: RE: Scripting the addition of  a Default Role
Date: Wed, 19 Dec 2007 09:35:57 -0500
Message-ID: <D1DC33E67722D54A93F05F702C99E2A901BC66F9@usahm208.amer.corp.eds.com>
In-Reply-To: <4768C17D.7000504@wp-sa.pl>
Thread-Topic: Scripting the addition of  a Default Role
From: "Powell, Mark D" <mark.powell@eds.com>
To: <oracle-l@freelists.org>
Sender: oracle-l-bounce@freelists.org
Errors-to: oracle-l-bounce@freelists.org
Precedence: normal
Reply-to: mark.powell@eds.com

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