Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Jailing a schema from PUBLIC

RE: Jailing a schema from PUBLIC

From: Mercadante, Thomas F \(LABOR\) <>
Date: Wed, 14 Feb 2007 08:42:52 -0500
Message-ID: <ABB9D76E187C5146AB5683F5A07336FFE08B36@EXCNYSM0A1AJ.nysemail.nyenet>


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.


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

[] On Behalf Of Nigel Thomas Sent: Wednesday, February 14, 2007 3:46 AM To:; oracle-l Subject: Re: Jailing a schema from PUBLIC

Rich. Rjamya

>> wild idea,
>> since public access is visible through ALL_* views, you can create
>> ALL_* views in schema B. These would be essentially same as regular
>> 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 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)

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

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

Original text of this message