Re: Jailing a schema from PUBLIC

Rich,

Configuring a Virtual Private Database (VPD) -- Fine Grainedi Access Control -- functionality is one sure way to solve your problem. See * http://tinyurl.com/28j79r *for all the details. With 100s of tables it will take a little bit of work to implement, but that work should be open to significant optimization opportunities.

On 2/13/07, Rich Jesse <rjoralist_at_society.servebeer.com> wrote:
>
> 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
>
>
>

-- 
Rumpi Gravenstein

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