通过对象权限和dblink给用户授权

有时候外包工程师需要查询一些表，而生产环境数据库可能是他们没法直接访问的，这时候可以用dblink加对象权限的办法来实现。结合同义词还能简化查询操作。

首先在生产环境建立新的外包用户，并把要查询的表的select权限赋给该用户：

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 Miningoptions

SQL> 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.

然后在外包工程师的开发环境建立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 options

SQL>

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查询，而不能进行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和对象权限没关系，不过我看这里却是可以的：

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