Path: dp-news.maxwell.syr.edu!spool.maxwell.syr.edu!news-spur1.maxwell.syr.edu!news.maxwell.syr.edu!newsfeed.icl.net!newsfeed.fjserv.net!newsfeed.icl.net!proxad.net!216.239.36.134.MISMATCH!postnews.google.com!m79g2000cwm.googlegroups.com!not-for-mail From: "sybrandb" Newsgroups: comp.databases.oracle.server Subject: Re: Object Privilege for Database Link? Date: 7 Jul 2006 04:33:42 -0700 Organization: http://groups.google.com Lines: 70 Message-ID: <1152272022.180398.219740@m79g2000cwm.googlegroups.com> References: <1152258009.177590.75830@s16g2000cws.googlegroups.com> <1152260237.726536.260620@s16g2000cws.googlegroups.com> <44ae3d8f$0$31643$e4fe514c@news.xs4all.nl> NNTP-Posting-Host: 192.33.238.6 Mime-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" X-Trace: posting.google.com 1152272027 25710 127.0.0.1 (7 Jul 2006 11:33:47 GMT) X-Complaints-To: groups-abuse@google.com NNTP-Posting-Date: Fri, 7 Jul 2006 11:33:47 +0000 (UTC) In-Reply-To: <44ae3d8f$0$31643$e4fe514c@news.xs4all.nl> User-Agent: G2/0.2 X-HTTP-UserAgent: Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; SV1; .NET CLR 1.1.4322; .NET CLR 2.0.50727; InfoPath.1),gzip(gfe),gzip(gfe) X-HTTP-Via: 1.1 PXYBEBR003 Complaints-To: groups-abuse@google.com Injection-Info: m79g2000cwm.googlegroups.com; posting-host=192.33.238.6; posting-account=3-ZCkwwAAAAif1uTJMVWCyzt_MY2v5ol Xref: dp-news.maxwell.syr.edu comp.databases.oracle.server:270555 GuessWho wrote: > "sybrandb" wrote in message > news:1152260237.726536.260620@s16g2000cws.googlegroups.com... > > > > thtsang_yh@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@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. Synonyms won't work, as synonyms are aliases only, and you can't grant access on a a synonym. Also you have 'hybrid' (distributed) views, which can result in unpredictable execution paths. Your second suggestion is to be preferred, as in that scenario the views are 'local' to the remote database. -- Sybrand Bakker Senior Oracle DBA