RE: Scripting the addition of a Default Role
- From: "Sam Bootsma" <sbootsma@xxxxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 19 Dec 2007 11:01:03 -0500
Hi Mark,
Dropping and recreating the role every night is my solution to a
business requirement.
Our ERP system (Banner) consists of a group of schemas that require
access to each others objects. This access was granted using the select
any table privilege. Our Developers login using these schemas to do
custom development.
A new business requirement stated Developers could no longer have access
to HR data, so I needed to revoke the select any table privilege and
instead devise a mechanism to grant select, update, delete, insert,
execute privileges on tables, views, and procedures owned by several
different users. My solution was to create roles that dynamically grant
these privileges every night. I decided a reliable solution would be to
just drop all the roles and regenerate them every night. An alternative
solution would be to regenerate all roles the first time only, then do
work to grant only new tables to the role in a script that is run at
night.
I know in Sql Server this could be done very simply and still preserve
the select any table privilege. This is because in Sql Server there
exists a "revoke" command that will over-ride the select any table
privilege. However, we have Oracle, and I want Oracle and I don't want
Sql Server.
Thanks,
Sam Bootsma
Oracle Database Administrator
Information Technology Services
George Brown College
Phone: 416-415-5000 x4933
Fax: 416-415-4836
E-mail: sbootsma@xxxxxxxxxxxxxx
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Powell, Mark D
Sent: December 19, 2007 9:36 AM
To: oracle-l@xxxxxxxxxxxxx
Subject: RE: Scripting the addition of a Default Role
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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
