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

今天在处理一个工单的时候发现了一个奇怪的现象,开发同学需要创建一个存储过程,目前的架构类似这样的形式


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

小结

所以对于视图而言,在连接用户上创建视图需要对象权限而角色权限会有限制。
所以推荐的架构方式为:


下面是我的公众号二维码,欢迎扫描关注。

本页内容版权归属为原作者,如有侵犯您的权益,请通知我们删除。

Oracle如何删除表中重复记录 - 2016-08-11 14:08:09

Oracle如何删除表中重复记录 1    引言 在对数据库进行操作过程中我们可能会遇到这种情况,表中的数据可能重复出现,使我们对数据库的操作过程中带来 读诸 多不便,那么怎么删除这些重复没有用的数据呢 ? 平时工作中可能会遇到当试图对库表中的某一列或几列创建唯一索引时,系统提示 ORA-01452  :不能创建唯一索引,发现重复记录。 2    处理过程 重复的数据可能有这样两种情况 : 第一种 是 表中只有某些字段一样,第二种是两行记录完全一样 。删除重复记录后的结果也分为 2 种, 第一种 是重复的

外连接转换为内连接的情况 - 2016-08-10 04:08:06

一般的情况下外连接如下a right join b on a.id=b.id 那么b一定要作为驱动表,原因在于只有b作为驱动表才能得到完整的结果集,如果a作为驱动,那么返回的结果集 可能不完整,但是在特殊的情况的,可能将外连接转换为内连接 考虑如下的情况 b    id  name   1   g1   1   g2   2   g3   2   g4 a   id name   2  gname2 使用如下语句: select b.id,a.id from  a right join b on a.id=
今天总算抽了些时间把半自动化的脚本完成了大半,目前还缺少两部分的脚本,一部分是安装前的检查脚本,可以做一个预检查。虽然目前来看还不是必须,但是这些是标准和规范的地方,这些条件不满足,失败的概率会加大。另外一部分是安装后的补充脚本,其实安装后还有很多需要注意的地方。 大体想了下,补充的脚本包含下面的部分。 配置crontab,目前的常用job是定期删除归档,定期检查监听的情况 配置iptables ,把主库的防火墙信息拷贝过来,或者作为静态备份,需要是启用 配置大页,这个可以在优化的基础上进行计算,在内核参数

MongoBD 日常操作小节 - 2016-08-09 17:08:09

一、开启Mongodb 密码验证功能 默认安装完mongodb是不用密码验证的,直接输入mongo就可以登入数据库进行相关操作,设置参数auth=true启动mongodb密码验证功能,开启改功能步骤如下: ①、 修改参数文件auth=false,并重启mongodb ②、登入数据库,创建管理员用户(默认是没有管理员账户的) [ root@mon godb ~]  # mongo user admin   db.createUser(     {       user: "admin",       pw

在Buffer Cache中自动大表缓存 - 2016-08-09 17:08:09

约翰,在A银行做DBA。凯特琳,新的首席技术官,她正在为数据库性能问题由于长的I/O响应时间而沮丧。为了提高性能,她希望增加数据库实例的缓冲区缓存,以便在缓存中缓存更多的数据,从而减少了频繁需要去读磁盘。戴比,系统开发工程师,经历了很多这样的问题在她的职业生涯中,她解释说,“我们的应用有很多全表扫描,数据库的全表扫描使用的是直接路径读取(DPR)在表中的数据块,不做缓冲区高速缓存。”因此,添加物理内存到服务器后,增加缓冲区缓存,是不会帮助,因为缓冲区没有用。应用程序可以通过加hits跳过DPRs,使用缓冲

MySQL案例-mysqld got signal 11 - 2016-08-09 14:08:35

-------------------------------------------------------------------------------------------------正文--------------------------------------------------------------------------------------------------------------- 背景: MySQL-5.7.12, debian 8核16G虚拟机 , 业务方反馈在某一
今天的技术问答是刘晨兄的一个问题,提问来自于我新书中的一个实验,刘晨兄非常认真,对我书中的很多细节都进行了测试。 看到这个错误,如果出现end-of-file这类的错误信息,基本可以断定数据库实例是宕了。 找到刘晨兄提到的页码标示,原来和我书中的测试结果有一些差别。 我书中的结果类似这样的形式: 错误代码也完全不同,这个问题该怎么解释呢,这个应该是一个很细节的问题。 首先网络上关于这个错误有很多种说法,很多我不认同。 我们先来复现一下问题,找了一套11.2.0.3的环境测试了一下。 先初始化数据 然后复现问
【故障处理】分布式事务 ORA-01591 错误解决 1    BLOG 文档结构图       2    前言部分 2.1    导读和注意事项 各位技术爱好者,看完本文后,你可以掌握如下的技能,也可以学到一些其它你所不知道的知识,~O(∩_∩)O~ : ①  分布式事务的简单概念         ②  ORA-01591 错误解决   Tips: ① 本文在 ITpub ( http://blog.itpub.net/26736162 )、博客园 ( http://www.cnblogs.com/lh
我们有几个项目使用了Windows Server 2008+ ROSE HA+Oracle 的组合方案,但是最近遇到了一个不大不小的麻烦。 甲方在进行故障测试时,断掉了一个网络交换机 的电源 (共有两个网络交换机,对应两个冗余的业务网络),Oracle服务竟然停止了,导致所有的客户端数据库连接中断。 首先,这个测试结果甲方是无法接受的; 其次,这个结果也出乎我们的意料,在另外一个交换机正常工作的情况下,ROSE HA 停止了 Oracle服务。 那么,如何给甲方一个交代呢?在和 ROSE售后经过多次沟通后,
故障现象: 用服务器上面的(客户端)sql server management  stutio 去连接本服务器上的sql server 数据库,如下图:点击连接不报任何错,就是连接不上,一直处于等待状态; 但是通过windows 身份验证是可以连接的,并且是可以正常操作的,如下图:服务器名字不在是个IP,要选择local. 通过这个现象可判断出 数据库本身的服务(通过任务管理器中也可以看出来)是没有问题,只是连接方面出现问题:网络,端口或者是监听之类的问题。 这是网上查到的一篇文档:经验证我的这里是 没有