RE: Jailing a schema from PUBLIC

From: Mercadante, Thomas F $LABOR$ <Thomas.Mercadante_at_labor.state.ny.us>
Date: Wed, 14 Feb 2007 08:42:52 -0500
Message-ID: <ABB9D76E187C5146AB5683F5A07336FFE08B36@EXCNYSM0A1AJ.nysemail.nyenet>

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_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Nigel Thomas Sent: Wednesday, February 14, 2007 3:46 AM To: rjoralist_at_society.servebeer.com; 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

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_at_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

--
http://www.freelists.org/webpage/oracle-l
--
http://www.freelists.org/webpage/oracle-l

Received on Wed Feb 14 2007 - 07:42:52 CST