RE: Scripting the addition of a Default Role

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_at_georgebrown.ca

-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Powell, Mark D Sent: December 19, 2007 9:36 AM
To: oracle-l_at_freelists.org
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_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Remigiusz Sokolowski Sent: Wednesday, December 19, 2007 2:00 AM To: sbootsma_at_georgebrown.ca
Cc: oracle-l_at_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_at_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


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