【转自ITPUB】SYNONYM关于underlying table权限的小小发现

本帖最后由 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表上的权限，否则会悲剧的，尤其是敏感数据。

原贴地址：点击打开链接