转载

关于视图和存储过程的权限问题探究

今天在处理一个工单的时候发现了一个奇怪的现象，开发同学需要创建一个存储过程，目前的架构类似这样的形式
关于视图和存储过程的权限问题探究

数据库中存在一个属主用户，表，存储过程等对象都创建在这个用户上，而另外有一些连接用户，根据业务和功能可能访问的对象权限也有所不同。所以就会出现一个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
而赋予了对象权限之后，视图的创建就引刃而解了。

小结

所以对于视图而言，在连接用户上创建视图需要对象权限而角色权限会有限制。
所以推荐的架构方式为：
关于视图和存储过程的权限问题探究