Re: Jailing a schema from PUBLIC
- From: Nigel Thomas <nigel_cl_thomas@xxxxxxxxx>
- To: rjoralist@xxxxxxxxxxxxxxxxxxxxx, oracle-l <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 14 Feb 2007 00:46:11 -0800 (PST)
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:
