Re: Select Privileges

From: Peter Schneider <pschneider1968_at_googlemail.com>
Date: Fri, 18 Nov 2011 21:01:26 +0100
Message-ID: <ja6dhn$l4i$1_at_online.de>

Am 17.11.2011 21:16, schrieb ExecMan:
> 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?

In DB1, schema TOM, create a view which SELECTs * FROM Tom's table_at_DB2. Let Tom grant SELECT privilege for that view to Chris. In DB1, schema CHRIS, create a private synonym pointing to Tom's view, named as the original table from TOM in DB2.

Regards
Peter

-- 
The only way to keep your health is to eat what you don't want, drink what
you don't like, and do what you'd rather not. -- Mark Twain

Received on Fri Nov 18 2011 - 14:01:26 CST