有時候外包工程師需要查詢一些表，而生產(chǎn)環(huán)境數(shù)據(jù)庫可能是他們沒法直接訪問的，這時候可以用dblink加對象權(quán)限的辦法來實現(xiàn)。結(jié)合同義詞還能簡化查詢操作。
首先在生產(chǎn)環(huán)境建立新的外包用戶，并把要查詢的表的select權(quán)限賦給該用戶：
ERPDB1@/home/oracle>sqlplus " / as sysdba"
SQL*Plus: Release 10.2.0.3.0 - Production on Mon Nov 2 09:25:43 2009
Copyright (c) 1982, 2006, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bitProduction
With the Partitioning, Real Application Clusters, OLAP and Data MiningoptionsSQL> create user waibao identified by queryonly default tablespaceERP temporary tablespace TEMP2 profile DEFAULT;
User created.
SQL>
SQL> grant connect to waibao;Grant succeeded.
SQL> grant select on erpuser.overdue to waibao;
Grant succeeded.
然后在外包工程師的開發(fā)環(huán)境建立dblink：
[oracle@erpdevdb ~]$ export ORACLE_SID=devdb
[oracle@erpdevdb ~]$ sqlplus "erpuser/banping"SQL*Plus: Release 10.2.0.1.0 - Production on Mon Nov 2 09:33:45 2009
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bitProduction
With the Partitioning, OLAP and Data Mining optionsSQL>
create public database link db100 connect to waibao identified byqueryonly using'(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=172.16.0.1)(PORT=1521))(CONNECT_DATA=(SID=ERPDB1)))';
此時可以通過dblink查詢，而不能進(jìn)行DML操作：
SQL> select count(*) from erpuser.overdue@db100;
COUNT(*)
----------
75930
建立別名，簡化訪問方式：
SQL> create synonym overdue100 forerpuser.overdue@db100;
Synonym created.
SQL> select count(*) from overdue100;
COUNT(*)
----------
75930
Oracle文檔里說dblink和對象權(quán)限沒關(guān)系，不過我看這里卻是可以的：
Some schema objects, such as clusters, indexes, triggers,and database links, do not have associated object privileges. Their useis controlled with system privileges. For example, to alter a cluster, auser must own the cluster or have the ALTER ANY CLUSTER systemprivilege.
鏈接如下：
http://download.oracle.com/docs/cd/B19306_01/network.102/b14266/authoriz.htm#DBSEG124
