【转自ITPUB】SYNONYM关于underlying table权限的小小发现
来源:互联网 发布:linux squid 启动 编辑:程序博客网 时间:2024/05/01 04:37
本帖最后由 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权限任然存在的。
最近使用到同义词,在做测试的时候发现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表上的权限,否则会悲剧的,尤其是敏感数据。
原贴地址:点击打开链接
0 0
- 【转自ITPUB】SYNONYM关于underlying table权限的小小发现
- 【翻译自mos文章】为table 的partition 建立synonym
- 袁崇焕的头颅(转自ITPUB)
- 关于strncpy 的一个小小发现
- 关于认证的看法(整理自ItPub)
- 某小小女生的2006世界杯十大最新发现(转自世界杯吧)
- 闲话三国(转自ITPUB)
- 关于synonym的简单说明
- 饮茶讲故事----楚腰(转自ITPUB)
- hadoop1.2.1 eclipse插件(转自Itpub)
- localstorage的一个小小发现
- 关于truncate table需要的系统权限
- SYNONYM和权限用法
- 小小发现
- 从西天取经看ERP的实施哲学 - (转自ITPUB)
- GL, SLA, SOB, COA, BSV, CCID, LE 概念的简单介绍----转自Itpub[goodhawk]
- 国内软件项目的典型历程 - 转自 http://space.itpub.net/17007506
- 国内软件项目失败的根源分析 - 转自 http://space.itpub.net/17007506
- OPENCV2.4.9的配置
- hdu 4906 Our happy ending
- php(mysql)
- HDU2601,An easy problem
- c++头文件
- 【转自ITPUB】SYNONYM关于underlying table权限的小小发现
- Android 多线程断点下载源码
- (P)EXCEL 常用工作薄函数和VBA函数
- java_web用户的自动登录模块的实现
- 常见异常汇总
- MVVM设计模式教程 - tutorial with ReactiveCocoa
- 10.jQuery 高级事件
- UINavigationController
- HDU 1020 Encoding