oracle存储过程权限继承小结

一、问题描述：由于某种原因，我需要定期去从新编译某个schema下所有INVALID状态的试图，于是创建了如下存储过程： 1、首先单独创建一个用户,并授权dba权限;create user db_admin identified by "passwd";grant dba to db_admin; 2、使用db_admin用户登录数据库，创建如下存储过程;create or replace procedure compile_invalid_views(p_owner varchar2) as--编译某个用户下的无效视图str_sql varchar2(2000);beginfor invalid_views in (select object_name from all_OBJECTSwhere status = 'INVALID' and object_type = 'VIEW' and owner=upper(p_owner))loopalter_sql := 'alter view '||p_owner||'.' ||invalid_views.object_name || ' compile';beginexecute immediate alter_sql;end;end loop;end; 3、指定定时任务,定期调用存储过程来从新编译sysadm用户下的无效视图;*/5 * * * * sh /home/oracle2/scripts/compile_invalid_views.sh[oracle2@B-GH4-PSDB-01 ~]$ cat /home/oracle2/scripts/compile_invalid_views.sh#!/bin/bashsource /home/oracle2/.bash_profilesql_str=sqlplus -S db_admin/'passwd'@mz <echo $sql_str[oracle2@B-GH4-PSDB-01 ~]$然后过了几天后，发现有的无效视图并没有被从新编译，业务还是报错，太奇怪了，db_admin有dba权限，执行存储过程没有报错; 二、原因分析： 存储过程分成两种权限：1.定义者权限 AUTHID DEFINER2.调用者权限 AUTHID CURRENT_USER默认的情况下使用的是定义者权限，也就是存储过程内部的权限是继承的创建者的权限;1、定义者权限使用遇到的问题：当使用定义者权限时候，不管是你自己去call，还是其他用户去call，效果是一样的，都是用你的权限去执行。但是，不是你的全部权限，是把所有Roles的权限去掉了( 因为我只给db_admin授权了dba的权限，但是他是个角色，所以存储过程里面会去掉dba这个角色，这是问题的关键，一般不注意)，当去掉dba角色后，再查询all_OBJECTS是不能查到别的schema下视图信息的，所以也就无法从新编译别的用户下的视图了。2、调用者权限使用遇到的问题：当使用调用者权限时候，用什么用户去call存储过程，就用谁的权限，而且包括角色权限。请注意，用数据字典user_xxx, 这个是调用者的，所以建议不要用他，而用all_xxx或dba_xxxx. (当然是要求调用者有select_catalog_role)另外，涉及到create table, create index, ...要确认调用者是否具备在该存储过程所在的schema下有此权限(如果调用者就是你自己，肯定没问题)。 三、解决问题： 方法1：这样的话需要保证调用者的权限是足够的，调用者只有执行存储过程的权限是不行的!因为调用者是dba权限，所以如下这样让存储过程继承调用者的权限，是可以解决问题的!添加AUTHID CURRENT_USER 关键字，这样存储过程就继承执行者(调用者)的所有权限，包括角色权限!create or replace procedure compile_invalid_views(p_owner varchar2) AUTHID CURRENT_USER as--编译某个用户下的无效视图str_sql varchar2(2000);beginfor invalid_views in (select object_name from all_OBJECTSwhere status = 'INVALID' and object_type = 'VIEW' and owner=upper(p_owner))loopalter_sql := 'alter view '||p_owner||'.' ||invalid_views.object_name || ' compile';beginexecute immediate alter_sql;end;end loop;end; 方法2：建议这种的方式，存储过程保持默认继承定义者权限，依旧使用db_admin用户创建存储过程!如果db_admin只有dba权限，存储过程继承定义者权限的时候，会去掉其拥有的角色的权限，所以会去掉dba权限;所以需要给db_admin用户dba之外的权限，不会去掉的权限，如下所示：SQL> grant alter any table to db_admin; 因为需要存储过程需要执行alter view，Grant succeeded.SQL> grant select any table to db_admin; #因为需要能查看别的用户下的view后，才能在all_objects中查看到别的用户的视图信息!SQL> grant connect to db_admin; #因为后面需要收回dba，所以需要加connect权限最后还可以回收dba权限;并且由于存储过程是自己账户下面的，自己的账号是可以执行的SQL>revoke dba from db_admin; 方法3：这种方式不完美，因为只能从新编译sysadm用户的试图，当你传别的schema的时候，还是有问题!在sysadm下创建对应的存储过程，这样继承了sysadm这个用户的权限，存储过程中也是可以查到自己的下面的试图的，所以问题就解决了，但是一旦别的用户下的试图有问题也是不能从新编译的!create or replace procedure sysadm.compile_invalid_views(p_owner varchar2) as--编译某个用户下的无效视图str_sql varchar2(2000);beginfor invalid_views in (select object_name from all_OBJECTSwhere status = 'INVALID' and object_type = 'VIEW' and owner=upper(p_owner))loopalter_sql := 'alter view '||p_owner||'.' ||invalid_views.object_name || ' compile';beginexecute immediate alter_sql;end;end loop;end;然后使用db_admin用户去调用这个存储过程!exec sysadm.compile_invalid_views('sysadm'); 综上所述：Oracle创建存储过程的时候，存储过程默认继承的是定义者，也就是创建者的权限，但是会去掉定义者拥有的 角色 ，这个需要大家注意!