Re: "revoking" privileges granted to public

In article <1103495539.747238.132220_at_z14g2000cwz.googlegroups.com>, premmehrotra_at_hotmail.com says...
>
>I am using Oracle 8.1.6.2 on HP UNIX 11i.
>I have a third party application which has a schema "marc" which has
>many tables, views, stored procedures etc. Vendor has granted select,
>insert, delete, execute, update, insert on these objects to public.
>
>I want to create a read only database user for marc schema, i.e.,
>marcread, Is there anyway to revoke insert, delete, update privileges
>from marcread which were indirectly granted via public. I have
>not yet found a way.
>
>I did try granting only connect role to marcread (i.e., no resource),
>yet it
>could insert/delete/update rows in marc.
>
>I know in SQL SERVER 2000, there is something called "deny" which can
>deny privileges granted to public from a specific user, but
>I have not been able to find equivalent in Oracle.
>Appreciate any ideas.
>
>
>Prem
>

you could use fine grained access control for this, just create a policy function that looks something like:

....
is
  if ( user = 'READ_ONLY_DUDE' )
  then

     return 1=0;
  else

     return null;
  end if;
end;
/

add that on the relevant tables for insert/update/delete. that user will not be able to insert, won't be able to update anything, cannot delete anything.

or you can add a before trigger on each table that just says:

if user = 'READ_ONLY_DUDE'
then

   raise_application_error( -20001, 'No, you cannot do that' ); end if;

...
>Vendor has granted select,
>insert, delete, execute, update, insert on these objects to public.
>...

glad to see vendor thought about this for a while... hmm.

-- 
Thomas Kyte
Oracle Public Sector
http://asktom.oracle.com/
opinions are my own and may not reflect those of Oracle Corporation