RE: Scripting the addition of a Default Role
- From: "Powell, Mark D" <mark.powell@xxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 19 Dec 2007 09:35:57 -0500
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
Other related posts:
