今天在处理一个工单的时候发现了一个奇怪的现象,开发同学需要创建一个存储过程,目前的架构类似这样的形式
数据库中存在一个属主用户,表,存储过程等对象都创建在这个用户上,而另外有一些连接用户,根据业务和功能可能访问的对象权限也有所不同。所以就会出现一个owner,多个connect user的情况。这种方式可以减少很多误操作,权限控制更为细粒度。
现在的问题是在owner用户上创建存储过程,存储过程会引用若干张表,都在owner用户下,而connect user下则没有这些表相关的任何同义词。看起来好像是不大合理啊,至少感觉信息不够完整,于是和开发的同学进行了确认,他们反馈这个存储过程一直是connect user执行,没有任何问题,当然在处理完之后,我还是带着疑惑测试了一遍,发现果真如此,开始让我有一种毁三观的感觉。
我们来测试一下,步骤很明确,先来初始化数据,创建两个用户,一个owner,一个connect user,然后创建一个存储过程,模拟当时的问题。
create user testo identified by oracle;
create user testc identified by oracle;
grant connect to testc;
grant connect,resource to testo;
alter session set current_schema=testo;
创建表test_bind,需要在后面的存储过程中引用,保证表中有存储过程调用合适的数据。
create table test_bind as select object_id cn,object_id cid from all_objects;
update test_bind set cn=100 where rownum<2;
update testo.test_bind set cn=101 where cn=100 and rownum<2 ;
SQL> select count(*) from testo.test_bind where cn=100;
COUNT(*)
----------
1
创建存储过程
create or replace procedure test_proc
AS
tmp_cid test_BIND.CID%TYPE;
BEGIN
SELECT CID INTO tmp_cid
FROM test_BIND WHERE CN = 100;
dbms_output.put_line(tmp_cid);
END;
/
赋予权限,创建存储过程的同义词,注意此处是没有创建表的同义词
grant execute on testo.test_proc to testc;
create synonym testc.test_proc for testo.test_proc;
开始复现问题:
alter session set current_schema=testc;
set serveroutput on
存储过程调用没有问题
SQL> exec test_proc;
100
PL/SQL procedure successfully completed.
查看test_bind这个表是否可访问
SQL> desc testc.test_BIND
ERROR:
ORA-04043: object testc.test_BIND does not exist
小结 由此可以看出,owner用户上的存储过程,里面涉及的表在connect 用户上没有对应的同义词时,存储过程调用没有问题。可见存储过程的执行是完全基于owner用户的。
当然存储过程的权限问题了解了,我的印象中视图似乎也有点矫情,有时候权限的要求比较高。在此一并矫正一下错误的观点。
我们创建一个新的connect用户testc2,然后测试视图的情况。
如果我们在owner用户上创建视图,测试一下是否权限也会有类似的问题。
create user testc2 identified by oracle;
grant connect to testc2;
alter session set current_schema=testo;
创建角色testo_role,所有的权限都通过testo_role来控制
create role testo_role;
创建视图
create view view_test_bind as select *from test_bind;
给角色testo_role赋予权限
grant select on view_test_bind to testo_role;
角色赋予connect用户testc2
grant testo_role to testc2;
alter session set current_schema=testc2;
创建同义词
create synonym testc2.view_test_bind for testo.view_test_bind;
查看视图的结构
desc testc2.view_test_bind
Name Null? Type
----------------------------------------- -------- ----------------------------
CN NOT NULL NUMBER
CID NOT NULL NUMBER
查看owner下的表test_bind是否可以在testc2下访问
SQL> desc testc2.test_bind
ERROR:
ORA-04043: object testc2.test_bind does not exist
小结
由此可以看出情况和存储过程是类似的
那么关于视图还有什么矫情的问题呢。印象中是有的。
我们在connect用户上创建视图
先把访问testo.test_bind的权限给角色testo_role
grant select on testo.test_bind to testo_role;
然后创建同义词
create synonym testc2.test_bind for testo.test_bind;
alter session set current_schema=testc2;
在connect用户下创建视图
SQL> create view view_test_bind as select *from testc2.test_bind;
create view view_test_bind as select *from testc2.test_bind
*
ERROR at line 1:
ORA-01031: insufficient privileges
而赋予了对象权限之后,视图的创建就引刃而解了。
小结
所以对于视图而言,在连接用户上创建视图需要对象权限而角色权限会有限制。
所以推荐的架构方式为:
下面是我的公众号二维码,欢迎扫描关注。
![](http://img.blog.itpub.net/blog/attachment/201608/10/23718752_1470841380rqzJ.png?x-oss-process=style/bb)
数据库中存在一个属主用户,表,存储过程等对象都创建在这个用户上,而另外有一些连接用户,根据业务和功能可能访问的对象权限也有所不同。所以就会出现一个owner,多个connect user的情况。这种方式可以减少很多误操作,权限控制更为细粒度。
现在的问题是在owner用户上创建存储过程,存储过程会引用若干张表,都在owner用户下,而connect user下则没有这些表相关的任何同义词。看起来好像是不大合理啊,至少感觉信息不够完整,于是和开发的同学进行了确认,他们反馈这个存储过程一直是connect user执行,没有任何问题,当然在处理完之后,我还是带着疑惑测试了一遍,发现果真如此,开始让我有一种毁三观的感觉。
我们来测试一下,步骤很明确,先来初始化数据,创建两个用户,一个owner,一个connect user,然后创建一个存储过程,模拟当时的问题。
create user testo identified by oracle;
create user testc identified by oracle;
grant connect to testc;
grant connect,resource to testo;
alter session set current_schema=testo;
创建表test_bind,需要在后面的存储过程中引用,保证表中有存储过程调用合适的数据。
create table test_bind as select object_id cn,object_id cid from all_objects;
update test_bind set cn=100 where rownum<2;
update testo.test_bind set cn=101 where cn=100 and rownum<2 ;
SQL> select count(*) from testo.test_bind where cn=100;
COUNT(*)
----------
1
创建存储过程
create or replace procedure test_proc
AS
tmp_cid test_BIND.CID%TYPE;
BEGIN
SELECT CID INTO tmp_cid
FROM test_BIND WHERE CN = 100;
dbms_output.put_line(tmp_cid);
END;
/
赋予权限,创建存储过程的同义词,注意此处是没有创建表的同义词
grant execute on testo.test_proc to testc;
create synonym testc.test_proc for testo.test_proc;
开始复现问题:
alter session set current_schema=testc;
set serveroutput on
存储过程调用没有问题
SQL> exec test_proc;
100
PL/SQL procedure successfully completed.
查看test_bind这个表是否可访问
SQL> desc testc.test_BIND
ERROR:
ORA-04043: object testc.test_BIND does not exist
小结 由此可以看出,owner用户上的存储过程,里面涉及的表在connect 用户上没有对应的同义词时,存储过程调用没有问题。可见存储过程的执行是完全基于owner用户的。
当然存储过程的权限问题了解了,我的印象中视图似乎也有点矫情,有时候权限的要求比较高。在此一并矫正一下错误的观点。
我们创建一个新的connect用户testc2,然后测试视图的情况。
如果我们在owner用户上创建视图,测试一下是否权限也会有类似的问题。
create user testc2 identified by oracle;
grant connect to testc2;
alter session set current_schema=testo;
创建角色testo_role,所有的权限都通过testo_role来控制
create role testo_role;
创建视图
create view view_test_bind as select *from test_bind;
给角色testo_role赋予权限
grant select on view_test_bind to testo_role;
角色赋予connect用户testc2
grant testo_role to testc2;
alter session set current_schema=testc2;
创建同义词
create synonym testc2.view_test_bind for testo.view_test_bind;
查看视图的结构
desc testc2.view_test_bind
Name Null? Type
----------------------------------------- -------- ----------------------------
CN NOT NULL NUMBER
CID NOT NULL NUMBER
查看owner下的表test_bind是否可以在testc2下访问
SQL> desc testc2.test_bind
ERROR:
ORA-04043: object testc2.test_bind does not exist
小结
由此可以看出情况和存储过程是类似的
那么关于视图还有什么矫情的问题呢。印象中是有的。
我们在connect用户上创建视图
先把访问testo.test_bind的权限给角色testo_role
grant select on testo.test_bind to testo_role;
然后创建同义词
create synonym testc2.test_bind for testo.test_bind;
alter session set current_schema=testc2;
在connect用户下创建视图
SQL> create view view_test_bind as select *from testc2.test_bind;
create view view_test_bind as select *from testc2.test_bind
*
ERROR at line 1:
ORA-01031: insufficient privileges
而赋予了对象权限之后,视图的创建就引刃而解了。
小结
所以对于视图而言,在连接用户上创建视图需要对象权限而角色权限会有限制。
所以推荐的架构方式为:
![](http://img.blog.itpub.net/blog/attachment/201608/10/23718752_1470842655mwxZ.png?x-oss-process=style/bb)
下面是我的公众号二维码,欢迎扫描关注。
![](http://img.blog.itpub.net/blog/attachment/201608/10/23718752_1470840343sNsi.gif?x-oss-process=style/bb)
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/23718752/viewspace-2123297/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/23718752/viewspace-2123297/