Sam, Could you configure FGA to exclude access to the HR schema and still preserve the "select any" priv? Finn On 12/19/07, Sam Bootsma <sbootsma@xxxxxxxxxxxxxx> wrote: > > 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 > > >