Re: Jailing a schema from PUBLIC
- From: "Rich Jesse" <rjoralist@xxxxxxxxxxxxxxxxxxxxx>
- To: "Nigel Thomas" <nigel_cl_thomas@xxxxxxxxx>
- Date: Wed, 14 Feb 2007 11:28:06 -0600 (CST)
Hey Nigel,
Now that it's not Midnight (this cold's kicking my ASCII), that makes
perfect sense. After all, Oracle doesn't *need* everything in catalog.sql
run to use it, but it sure makes it handier for us DBA-types.
I need to draw this out on a whiteboard -- I don't yet see how to provide
the DML jail, but maybe a picture will help. I've been trying to get to it
all morning, but other stuff is in the way.
Also, Tom, I would love to revoke the PUBLIC, but since we're less than a
month from go-live for this ERP system, there's a whole lot of unknowns in
that proposal and I don't know that I can pull in the resources I need to
answer them at this time. For the long-term solution post-go-live,
absolutely!
Thanks all! It's great being back on The List. Ahhhhhhhhhh...
Rich
> Rich. Rjamya
>
>>> wild idea,
>>> since public access is visible through ALL_* views, you can create local
ALL_* views in schema B. These would be essentially same as regular
views
>>> but you'd filter out owner A.
>
>
> The dictionary views are just that: views for your convenience. You can
tell
> they aren't used by the SQL engine to do name and privilege resolution -
just look at the recursive SQL in a trace file which always refers
directly
> to the underlying dictionary tables (eg OBJ$, TAB$, SEG$, PRV$ etc etc).
You can't spoof it into giving you more (or fewer) privileges. So if
you've
> granted S/I/U/D to public on your first database, the only ways to close
the
> door are
>
> 1) You can use your two remote schemas - ie use a database that isn't
'infected' by the public grants
>
> Remote schema RA owns the views
> Remote schema RB is Vendor B's schema to use
>
> -- Create a db link owned by remote user RA
> create database link LINKA connect to A identified by X using
> 'servicename';
> -- And of course, *don't* create a public link, else RB can use it
directly
> and get full SIUD access.
>
> -- Create remote views in RA on your original vendor A schema; they will
use
> the DB link
> create view X as select * from X@LINKA;
>
> -- Grant required (**limited**) select. insert, update privileges to
schema
> RB
> grant select, update on X to RB;
>
> -- Create synonyms for the views
> -- Either create public synonyms in the remote database
> create PUBLIC synonym X for RA.X;
>
> -- or create private synonyms as RB
>
> create synonym X for RA.X;
>
> Now you have full control over what RB can see, and what DML (if any) he
can
> do. (This doesn't include any PL/SQL API access... yet)
>
> 2) Or (as you already indicated)
>
> - revoke all public grants of User A objects
> - replace them with appropriate private grants to all legitimate
Application
> A users
> - Now add user B, and do the right thing
>
> Writing a script for (2) is a fairly trivial exercise; weeding out users
who
> don't need App A (eg SYS, SYSTEM etc) is tedious (but not strictly
necessary). You don't need to destroy the public synonyms; they'll be no
use
> to B without the grants.
>
> Option 2 is likely to be easier to set up and maintain...
>
> HTH
>
> Regards Nigel
>
--
//www.freelists.org/webpage/oracle-l
Other related posts:
