ORA-01031 权限不足-过程中DBA 角色用户无法执行DDL

Oracle的存储过程，是我们使用数据库应用开发的重要工具手段。在存储过程中，我们大部分应用场景都是使用DML语句进行数据增删改操作。本篇中，我们一起探讨一下数据定义语句DDL在存储过程中使用的细节和要点。

 

1、“借道而行”的DDL

 

从Oracle PL/SQL和存储过程程序开发原则上，应该是不鼓励在SP中使用DDL语句的。首先一个表现，就是Oracle在编译时就不允许直接在SP中使用DDL语句。下面我们使用Oracle 10gR2作为实验环境。

 

 

SQL> select * from v$version;

 

BANNER

----------------------------------------------------------------

Oracle Database10gEnterpriseEdition Release10.2.0.1.0 - Prod

PL/SQL Release10.2.0.1.0 - Production

CORE    10.2.0.1.0      Production

 

TNS for 32-bit Windows: Version10.2.0.1.0 - Production

NLSRTL Version10.2.0.1.0 – Production

 

 

建立存储过程p_test_nc，进行简单的数据表创建。

 

 

SQL> create or replace procedure P_TEST_NC

 2 is

 3 begin

 4   create table t (id number);

 5 end P_TEST_NC;

 6 /

 

Warning: Procedure created with compilation errors

 

SQL> select name, text from user_errors;

 

NAME      TEXT

---------- --------------------------------------------------------------------------------

P_TEST_NC PLS-00103:出现符号"CREATE"在需要下列之一时：

           begin case declare exit

             for goto if loop mod null pragma raise return select update

             while with <an identifier>

             <a double-quoted delimited-identifier> <a bind variable> <<

             close current delete fetch lock insert open rollback

             savepoint set sql execute commit forall merge pipe

 

 

显然，在编译时Oracle就报错不允许存储过程创建。之后的实验drop和truncate table操作，也都是不允许直接在存储过程中书写DDL语句。说明起码使用直接的DDL语句，存储过程是不能编译通过的。

 

那么，有没有什么折中的方法呢？我们说是有的，就是借助“execute immediate”方法，“绕过”编译过程中对DDL的屏蔽。我们使用truncate table DDL语句实验。

 

 

SQL> create or replace procedure P_TEST_NC

 2 is

 3 begin

  4   execute immediate'truncate table t';

 5 end P_TEST_NC;

 6 /

Procedure created

 

 

编译通过了，DDL语句以一个字符串的形式避开了编译时Oracle的语法检查，编译成功。那么，执行起来会不会报运行时错误呢？

 

 

SQL> exec p_test_nc;

PL/SQL procedure successfully completed

 

 

执行成功，说明：在Oracle存储过程中，可以使用exectue immediate语句绕开编译时对DDL语句的检查，生成运行代码。

 

 

2、SP中DDL权限

 

任何程序编译执行都会伴随着语法语义的一系列检查。使用execute immediate虽然可以回避编译时检查，但是SQL语句还是面临着运行时检查的问题。下面看实验的例子。

 

--在scott用户下进行试验；

SQL> create or replace procedure P_TEST_NC

 2 is

 3 begin

 4   execute immediate 'create table t(id number)';

 5 end P_TEST_NC;

 6 /

Procedure created–编译时通过；

 

SQL> exec p_test_nc;

 

begin p_test_nc; end;

 

ORA-01031:权限不足

ORA-06512:在"SCOTT.P_TEST_NC", line 4

ORA-06512:在line 1

 

 

在用户自己的schema下创建数据表，难道是不允许的吗？显然不是。

 

 

SQL> create table m (id number);

Table created

 

 

单独创建是允许的，说明是由于权限机制导致的问题。我们切换到sys用户上，提高scott用户权限。

 

 

Connected as SYS

--赋予最高创建数据表的系统权限；

SQL> grant create any table to scott;

Grant succeeded

 

 

切换回scott用户，继续实验。

 

 

SQL> conn scott/tiger@orcl;

Connected to Oracle Database10gEnterpriseEdition Release10.2.0.1.0

Connected as scott

 

SQL> exec p_test_nc;

PL/SQL procedure successfully completed

 

SQL> select * from t;

       ID

----------

 

执行成功！这个原因是什么呢？还是由于存储过程权限体系特点和DDL语句特点共同造成的。

 

在之前笔者的系列文章《所有者权限和调用者权限》（http://space.itpub.net/17203031/viewspace-692161）中，介绍了Oracle存储过程采用的两种权限体系方式和role权限在存储过程执行中的特殊性。

 

默认情况下，Oracle对存储过程是使用所有者权限，也就是说：如果用户B调用了用户A schema下的一个存储过程，其中使用的对象权限和系统权限，全部都是用户A的。如果用户A没有权限，用户B执行要报错。

 

同时，用户的角色权限在进入存储过程后，会被剥离掉，是不其效果的。

 

结合上面的实验，就好解释了：scott自身只拥有一个resource的角色权限，单独在SQL中使用没有问题。进入到SP之后，这个create table的权限就被剥离掉了。而该SP存在被其他用户调用生成数据表的可能。所以会在运行时报错权限不足。

 

当我们显示的赋予scott用户create any table/create table之后，系统权限就可以渗透到SP中起效果了。

 

这并不是解决该问题的唯一方法。此处我们可以使用调用者权限机制，改写SP代码。首先我们剔除掉scott的create any table权限。

 

 

Connected to Oracle Database10gEnterpriseEdition Release10.2.0.1.0

Connected as SYS

 

SQL> revoke create any table from scott;

Revoke succeeded

 

SQL> conn scott/tiger@orcl;

Connected to Oracle Database10gEnterpriseEdition Release10.2.0.1.0

Connected as scott

 

SQL> exec p_test_nc;

begin p_test_nc; end;

 

ORA-01031:权限不足

ORA-06512:在"SCOTT.P_TEST_NC", line 4

ORA-06512:在line 1

 

 

我们改写代码为：

 

 

SQL> create or replace procedure P_TEST_NC

 2 Authid Current_User

 3 is

 4 begin

 5   execute immediate 'create table t (id number)';

 6 end P_TEST_NC;

 7 /

Procedure created

 

SQL> exec p_test_nc;

PL/SQL procedure successfully completed

 

 

执行成功，这里使用“authid Current_user”将存储过程转化为调用者权限。每次调用存储过程，都是动态根据调用者的权限构成去判定是否有权限，这样就回避了该问题的出现。

 

总之：在使用DDL在存储过程中时，权限管理和使用的复杂度是在增加。

 

 

4、DDL对事务的提交影响

 

将DDL语句放置在存储过程中，潜在最大风险就是对事务管理的破坏。在Oracle中，如果调用一个DDL语句，潜藏效果就是将当前会话的未提交事务进行提交。这个过程显然是对原有的事务逻辑破坏。

 

 

SQL> create table m (id number);

Table created

 

SQL> select * from m;

       ID

----------

 

SQL> create or replace procedure P_TEST_NC

 2 is

 3 begin

 4   insert into m values (3);

 5   execute immediate 'truncate table t';

 6 

 7   rollback;

 8 end P_TEST_NC;

 9 /

 

Procedure created

 

--执行代码

SQL> exec p_test_nc;

PL/SQL procedure successfully completed

 

--事务提交

SQL> select * from m;

       ID

----------

        3

 

 

从上面的例子上，我们可以清楚的看到现象。由于中间的truncate table操作，引起数据表m的插入操作被提交commit。而真正的事务逻辑可能是一个rollback。

 

所以，在SP中使用DDL命令，可能引起业务逻辑的不可控提交和数据不一致，这个风险在任何应用中是不可以允许的。

 

那么，有没有方法回避这个过程呢？经一个同事提醒，的确可以使用手段回避。

 

 

5、DDL与自治事务

 

自治事务（AUTONOMOUS_TRANSACTION）是保证在事务进行过程中一段独立的事务过程。如果在DDL操作外套入一个自治事务过程，是否就可以回避问题了。

 

 

SQL> select * from m;

 

       ID

----------

 

SQL> create or replace procedure P_TEST_NC is

 2   procedure p_inner_test

 3   is

 4   PRAGMA AUTONOMOUS_TRANSACTION;

 5   begin

 6     --调用ddl

 7     execute immediate 'truncate table t';

 8   end;

 9 begin

 10   insert into m values (3);

 11   p_inner_test;

 12 

 13   rollback;

 14 end P_TEST_NC;

 15 /

Procedure created

 

SQL> exec p_test_nc;

PL/SQL procedure successfully completed

\

SQL> select * from m;

       ID

----------

 

 

实验成功，通过自治事务的确可以回避DDL的事务问题。

 

6、结论

 

DDL在SP中，与常规的DML操作差异很大。这种差异不仅仅是语法上，更多的是权限、事务等更深层次复杂的差异。所以，从Oracle的角度看，尽量少在SP中使用DDL语句，避免出现不可控的问题。

 

PLS-00157:   AUTHID only allowed on schema-level programs

查了下错误原因 An AUTHID clause was specified for a subprogram inside a package or type. These clauses are only supported for top-level stored procedures, packages, and types.

大致意思就是authid只能用在顶级的存储过程、包、类型上，不能用在包或类型的子程序上。

 

在包上加入authid，执行正常了。

create or replace package rule_execute
authid current_user

Oracle DB对于PLSQL程序提供两种不同的对象权限. 默认的(也是在8i以前唯一的)模式才能为定义者权限(definer rights, DR)
在这种模式下, PLSQL存储程序通过定义者的权限执行. 另外一种称为执行者权限(invoker rights, IR), 顾名思义, 即采用
执行者的权限来进行存储程序.

定义为定义者权限下的子程序称为DR Unit, 定义在执行者权限下的子程序称为IR Unit

匿名块默认执行在IR权限下, 触发器默认执行在DR权限下.

定义者权限(DR)
1. 任何外部引用的解析都发生在编译时, 通过对该用户(定义该子程序的用于)的直接赋予权限来解析.
2. 所有编译需要的权限必须通过直接授予的方式获得(e.g directly grant previliges), 
3. 数据库角色(roles) 在编译时将被完全忽略.
4. DR程序中SQL语句永远执行在创建该程序的用户权限之下.
5. 尽管你需要直接赋予的权限来编译该程序, 但你可以通过 grant EXECUTE 语句给其他用户运行该
   程序的权限.

DR的问题:
1. 比如在你的企业里面一种通常的做法是程序员会读写其他用户的某些表. 这些表的读写权限都
    被赋予给该程序员, 并且通过创建public synonyms来实现其他用户的信息隐藏.
    比如, 下面的语句可以顺利的在SQL PLUS下运行:
      select student_no, first_name, last_name
      from students;
    但如果你试图运行下面的子程序:
    create or replace procedure show_students
    is
    begin
          for rec in (select student_no, first_name, last_name from students)
          loop
             dbms_output.put_line(rec.first_name);
          end loop;
    end;
    / 
   你将会得到错误信息:
    PL/SQL: SQL Statement ignored
    PLS-00201: identifier 'STUDENTS' must be declared

第二种情况是通过子程序对数据库进行DDL操作. 这种情况下调用该子程序的对象由于采用
创建者的权限从而无法在自己的用户空间创建对象, 从而产生错误信息. 这种情况, 即使
在表命前加上用户名也会报权限不够的错误, 比如. hr.departments

综上, 有DDL操作的子程序不适合采用DR模式.

第二种权限模式是 IR模式, 这种模式采用当前用户权限去解析引用对象.
AUTHID CURRENT_USER
因此, 对于引用对象, 要求调用者具有与定义者相同的权限才能成功执行.

1) DR是默认选项, 不指定的情况下. 但匿名块是IR
2) IR与DR不同, DR在编译是用定义者权限解析引用对象, 而IR是在执行时(run-time)对引用对象
                     采用执行者权限去解析.
3) 对IR, 角色(role)权限在运行时是有效的, 只要这个IR子程序不是从一个DR的父程序进行的调用即可.
4) AUTHID 子句只能用于单独子程序的头部, 包的头部, 或者对象类型头部, 而不能用在包或者对象类型
   中单独的子程序的头部. 换句话说, 对于一个包而言, 整体要么是DR要么是IR
5) 在编译时,对于子程序或者对象类型的过程, 永远都是采用DR来解析外部引用.
6) DR的优先级高于IR
7) IR的缺点是在运行时会有对用户权限的检查过程, 会产生额外的开销从而对性能有所影响.