Re: Scripting the addition of a Default Role

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

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