Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

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

Jailing a schema from PUBLIC

From: Rich Jesse <rjoralist_at_society.servebeer.com>
Date: Tue, 13 Feb 2007 16:12:28 -0600 (CST)
Message-ID: <19791.12.17.117.251.1171404748.squirrel@12.17.117.251>


Hi all,

I've been handed a vendor (let's call them "A") 10.1.0.5 database that has a schema wide open with GRANT SELECT, INSERT, UPDATE, DELETE TO PUBLIC on all of the hundreds of tables. Now I need to get vendor "B" read-only access to one of those tables and read/write access to a few more, but I don't want
"B" to have any access to any of the other tables in "A". And, yes, I'm
being generic on purpose. :)

My first test was to create two new schemas in another DB for "B" -- one for the vendor to connect to and another to create a DBLINK to the database for
"A". I can then create VIEWs in the new DBLINK schema and GRANT SELECT on
them to manage read-only access for "B".

But without creating packages for an API, how do I handle DML security? I basically want to "jail" vendor "B" from getting at vendor "A"'s objects without revoking the PUBLIC GRANTs. Yes, I believe I will eventually be able to REVOKE them and GRANT only to what needs it, but right now I can't. It would be perfect if I could GRANT to a synonym in the remote DB, but that doesn't seem to work like I hoped it would.

Thoughts anyone?

TIA!
Rich

p.s. Yes, this is a new email address for me...

--
http://www.freelists.org/webpage/oracle-l
Received on Tue Feb 13 2007 - 16:12:28 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US