Re: "revoking" privileges granted to public

premmehrotra_at_hotmail.com wrote:

> 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

Oracle has no equivalent to deny but does provide at least two mechanisms I can think of that would accomplish the goal.

1. Look at how vendor create the public privileges and on a test box, as this will require very complete testing, revoke those privs and create a similar mechanism that give the privileges, privately, to those that actually need them.
2. Look at Fine Grained Access Control provided by the DBMS_RLS built-in package. They are also refered to in the docs as Virtual Private Database and FGAC. But I note you are on 8.1.6.2 which hasn't been fully supported by Oracle during the current millenium. Can you upgrade to a supported version such as 9i or 10g, or if not at least to 8.1.7.4? Because I'm not sure how well FGAC worked back in the neolithic period.

Either way ... find another vendor as this one is clearly clueless about Oracle and the concepts of both security and data having value.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace 'x' with 'u' to respond)


-----------== Posted via Newsfeed.Com - Uncensored Usenet News ==----------
   http://www.newsfeed.com       The #1 Newsgroup Service in the World!
-----= Over 100,000 Newsgroups - Unlimited Fast Downloads - 19 Servers =-----