Re: Select Privileges
Date: Mon, 21 Nov 2011 10:46:50 -0800 (PST)
Message-ID: <fb2c7b2e-24bf-4c76-828c-eb7183fa23ff_at_d17g2000yql.googlegroups.com>
On Nov 17, 10:31 pm, ddf <orat..._at_msn.com> wrote:
> On Nov 17, 12:16 pm, ExecMan <artme..._at_yahoo.com> wrote:
>
>
>
>
>
>
>
>
>
> > Hi, I'm trying to get the following scenario to work. Maybe someone
> > can help?
>
> > We have the following small setup:
> > DATABASE: DB1
> > USERS: TOM & BILL, CHRIS, MARK, JOE
>
> > DATABASE: DB2
> > USERS: TOM
>
> > There is a trigger on a table in Tom's schema in DB1. That trigger
> > will insert data into a table in Tom's schema on DB2, over a DB link.
>
> > I want to grant SELECT access to CHRIS only so he can query the table
> > in Tom's schema on DB2. I do not anyone else to be able to select
> > from the table.
>
> > How can this be done, since the dblink logs in under a given user
> > account? Can permissions somehow be given to use of database links?
> > Or by source user?
>
> > Thanks
>
> No. You could create a read-only user in DB2 that can select from
> Tom's table then create a private db link in CHRIS' schema using that
> read-only user so he, and only he, can access that object.
>
> David Fitzjarrell
Dave,
I've got most things in place, but there is one small strange thing happening.
Here is how I did the set up:
DATABASE: DB1
USERS: TOM, BILL
DATABASE: DB2
USERS: TOM, BILL, JIM
The actual table will exist in the JIM schema on db2. Synonyms are
used to reference the object in JIM's schema.
On DB1, TOM will be able to INSERT into the table on DB2. I've created a private DB link granting INSERT privileges. On DB1, BILL will be able to SELECT from the table on DB2. I've created a private DB link granting SELECT privileges.
I can perform these operations fine from DB2. The privileges work as they should. TOM can only INSERT into the table and BILL can only SELECT from it.
However, from over the DB Link, the insert fails with 'insufficient privileges'. Is there something I am missing from going over the DB Link and objects not specifically owned by you? Received on Mon Nov 21 2011 - 12:46:50 CST