Re: Object Privilege for Database Link?

"sybrandb" <sybrandb_at_yahoo.com> wrote in message news:1152260237.726536.260620_at_s16g2000cws.googlegroups.com...
>
> thtsang_yh_at_yahoo.com.hk wrote:
> > I would like to allow a schema to access a remote database through a DB
> > link created by another schema. However, it seems there is no object
> > privilege for db links, so that I can't assign the right to that
> > schema. As an example, what I want is something like
> >
> > (From Schema 1)
> > create database link dblink1...;
> > grant execute on dblink1 to schema2;
> >
> > (From Schema 2)
> > select * from table_name_at_schema1.dblink1;
> >
> >
> > I know a public database link can be shared among different schemas.
> > However, I don't want to allow everyone to access the remote db through
> > the db link, but only selected schemas.

See option below

R.

> >
> > Am I missing something? Or this cannot be done yet?
>
> If you create the (public) link without username/password, the account
> information of the local database will be used to get into the remote
> database.
> Make sure you have all the users that need access have been set up in
> the remote database, and have the proper privileges, and you are there.
> If you don't want to do that, there is no other solution than setting
> up multiple private links
>
> --
> Sybrand Bakker
> Senior Oracle DBA
>

There is an other option (I think, but feel free to correct me if I'm wrong or incomplete).
Create an account (A) in your database with no schema objects, just a private database link to the remote database schema (B). No one has to know the password for this account nor the db link. Create views in schema A for the objects you want to expose from schema B (I even think synonyms will do, maybe Sybrand knows?) , and grant SELECT on these views/synonyms to the users you want to permit to see these objects (you'd rather use a role for that and grant this role to these users). I have seen constructs like this on many sites. For security, you might even consider to create a special account on the remote database, with access only to the objects you want to expose through the db link, and link to this schema in stead of schema B; this to avoid exposing more tables than just the ones selected.

R. Received on Fri Jul 07 2006 - 05:50:22 CDT