RE: Jailing a schema from PUBLIC
- From: "Mercadante, Thomas F \(LABOR\)" <Thomas.Mercadante@xxxxxxxxxxxxxxxxx>
- To: <nigel_cl_thomas@xxxxxxxxx>, <rjoralist@xxxxxxxxxxxxxxxxxxxxx>, "oracle-l" <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 14 Feb 2007 08:42:52 -0500
Rich,
All of these are good ideas. But let me give you another thought.
Why not fix the original problem. Create a role in the original
database and grant select, insert, update & delete to all the objects in
schema A.
Grant this role to the users that connect to the database who need it.
Revoke the same grants from ALL and be done with it.
Create another role granting access to just the tables you need for the
new user. Grant this role to the new user.
My guess is that you would spend less time doing it the right way rather
than trying to patch all the holes that "grant all" opens.
Just my 2 cents.
Tom
--------------------------------------------------------
This transmission may contain confidential, proprietary, or privileged
information which is intended solely for use by the individual or entity to
whom it is addressed. If you are not the intended recipient, you are hereby
notified that any disclosure, dissemination, copying or distribution of this
transmission or its attachments is strictly prohibited. In addition,
unauthorized access to this transmission may violate federal or State law,
including the Electronic Communications Privacy Act of 1985. If you have
received this transmission in error, please notify the sender immediately by
return e-mail and delete the transmission and its attachments.
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Nigel Thomas
Sent: Wednesday, February 14, 2007 3:46 AM
To: rjoralist@xxxxxxxxxxxxxxxxxxxxx; oracle-l
Subject: Re: Jailing a schema from PUBLIC
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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
