本帖最后由 macrowho 于 2014-2-19 11:35 编辑
最近使用到同义词，在做测试的时候发现SYNONYM针对underlying表权限方面有一个特别需要注意的地方，在这里贴出实验过程，方便大家回忆起这个特点。
测试步骤如下：
SYS@DB10G SQL> create user a identified by a ;
User created.
SYS@DB10G SQL> create user b identified by b;
User created.
SYS@DB10G SQL> grant connect,resource,create public synonym,drop public synonym to a;
Grant succeeded.
SYS@DB10G SQL> grant connect to b;
Grant succeeded.
登录A用户
A@DB10G SQL> create table t as select object_id,object_name from all_objects where rownum<11;
Table created.
A@DB10G SQL> select count(1) from t;
COUNT(1)
----------
10
A@DB10G SQL> create or replace public synonym syn_t for t;
Synonym created.
登录B用户
B@DB10G SQL> select count(1) from syn_t;
select count(1) from syn_t
*
ERROR at line 1:
ORA-00942: table or view does not exist
B@DB10G SQL> select count(1) from a.t;
select count(1) from a.t
*
ERROR at line 1:
ORA-00942: table or view does not exist
B@DB10G SQL> SELECT table_schema, table_name, privilege FROM all_tab_privs WHERE grantee = 'B';
no rows selected
B@DB10G SQL> select count(1) from t;
select count(1) from t
* ---B用户下并没有T表
ERROR at line 1:
ORA-00942: table or view does not exist
登录A用户
A@DB10G SQL> grant select on syn_t to b;
Grant succeeded.
登录B用户
B@DB10G SQL> SELECT table_schema, table_name, privilege FROM all_tab_privs WHERE grantee = 'B';
TABLE_SCHEMA TABLE_NAME PRIVILEGE
------------------------------ ------------------------------ ----------------------------------------
A T SELECT
解释如下：
Be aware that when you grant the synonym to another user, the grant applies to the underlying object （同义词的源表）that the synonym represents, not to the synonym itself.
B@DB10G SQL> select count(1) from syn_t;
COUNT(1)
----------
10
B@DB10G SQL> select count(1) from t;
select count(1) from t
*
ERROR at line 1:
ORA-00942: table or view does not exist
B@DB10G SQL> select count(1) from a.t;
COUNT(1)
----------
10
登录A用户
A@DB10G SQL> drop public synonym syn_t; --A用户删除同义词
Synonym dropped.
登录B用户
B@DB10G SQL> select count(1) from syn_t;
select count(1) from syn_t
*
ERROR at line 1:
ORA-00942: table or view does not exist
B@DB10G SQL> select count(1) from a.t;
COUNT(1) --这次实验的重点就在这里
----------
10
让我们来回忆一下实验步骤：
A用户创建了一个同义词syn_t指向A schema下的表T，并赋予了B用户针对同义词SYN_T的SELECT权限;
此时B用户拥有的是对A schema下T表的select权限，而非public synonym syn_t，当我删除掉同义词SYN_T时，B用户对T表的SELECT权限任然存在的。
在日常工作中，如果我们对对象权限的粒度管理的比较细致的话，在删除同义词的时候记得revoke掉用户在underlying表上的权限，否则会悲剧的，尤其是敏感数据。
原贴地址：