How to Verify Privileges In Procedures Or Functions Or Package

By on 2011/12/30 at 6:58 上午 in dba

默认情况存储过程或函数中对对象的权限验证和直接在SQL语句运行时对权限的验证方式是不同的。

存储过程的权限验证包括两个阶段：创建编译时的权限检查；运行时权限检查。

创建的时候除了验证CREATE PROCEDURE/CREATE FUNCTION/CREATE PACKAGE权限之外，还要验证是否对存储过程中所涉及的对象具有可操作的权限，比如DML或者DDL权限。如果存储过程中包含了动态SQL，比如：EXECUTE IMMEDIATE或者DBMS_SQL包装的SQL语句，那么类似的权限验证只有在存储过程运行时才会检查。

运行时验证EXECUTE PROCEDURE权限，如果存储过程中包含了动态SQL那么这时对存储过程才进行编译并对SQL中包含的对象进行相应的权限检查。

数据库版本：

 Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Prod

user2具有下列权限：

[oracle@iQueDB1 ~]$ tbs getprivs user2

Enter value for schems:
Privs                          Obj.Owner       Obj.Tab              Grantor
------------------------------ --------------- -------------------- --------------------
SYS---UNLIMITED TABLESPACE
Role--DBA
Role--RESOURCE
Role--CONNECT

DBA role中包含了SELECT ANY TABLE 权限。所以下面的SQL在user2中可以执行：

[oracle@iQueDB1 ~]$ sqlplus user2/user2

SQL*Plus: Release 10.2.0.4.0 - Production on Fri Dec 30 16:56:53 2011

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select count(*) from user1.test1;

  COUNT(*)
----------
     31786

但如果包含在存储过程中呢？

SQL> connect user2/user2
Connected.
SQL> CREATE OR REPLACE PROCEDURE p1
IS
 p_count NUMBER;
BEGIN
  EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM user1.test1'
     INTO p_count;
  DBMS_OUTPUT.PUT_LINE('Remote Users Records:'||to_char(p_count));
END;
/  2    3    4    5    6    7    8    9  

Procedure created.

SQL> CREATE OR REPLACE PROCEDURE p3
IS
 p_count NUMBER;
BEGIN
   SELECT COUNT(*) INTO p_count FROM user1.test1;
  DBMS_OUTPUT.PUT_LINE('Remote Users Records:'||to_char(p_count));
END;
/  2    3    4    5    6    7    8  

Warning: Procedure created with compilation errors.

SQL> show errors;
Errors for PROCEDURE P3:

LINE/COL ERROR
-------- -----------------------------------------------------------------
5/4      PL/SQL: SQL Statement ignored
5/44     PL/SQL: ORA-00942: table or view does not exist
SQL>

同样的存储过程P1用了EXECUTE IMMEDIATE包装，P3用了静态SQL,P3在编译的时候直接出错了。

执行P1呢？

SQL> set serveroutput on
SQL> exec p1;
BEGIN p1; END;

*
ERROR at line 1:
ORA-00942: table or view does not exist
ORA-06512: at "USER2.P1", line 5
ORA-06512: at line 1

 

也报错了，同样的SQL中为什么包含在存储过程中就报错呢？因为默认情况下存储过程或函数都是以DEFINER模式创建，就是所谓的definer-rights procedure,这种模式下存储过程在编译时必须对程序所涉及的对象具有直接的操作权限，包括对象权限和系统权限，而不是通过ROLE的方式授权，所以P1和P3都出错了。

除非创建invoker-rights procedure, 用AUTHID CURRENT_USER创建存储过程或者函数。invoker模式就是程序执行时如果用户直接具有某种权限或者以其他方式继承或间接具有了操作该对象的权限那么程序就能顺利编译执行。

看看invoke-right procedure的官方说明:

The invoker_rights_clause lets you specify whether the procedure executes with the privileges and in the schema of the user who owns it or with the privileges and in the schema of CURRENT_USER.

This clause also determines how the database resolves external names in queries, DML operations, and dynamic SQL statements in the procedure.

Specify CURRENT_USER to indicate that the procedure executes with the privileges of CURRENT_USER. This clause creates an invoker-rights procedure.

这就意味着只要对p1存储过程用CURRENT_USER模式创建就可以顺利执行了，包含静态SQL的p3还是不能执行。

SQL> CREATE OR REPLACE PROCEDURE p2
 AUTHID CURRENT_USER
IS
 p_count NUMBER;
BEGIN
  EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM user1.test1'
     INTO p_count;
  DBMS_OUTPUT.PUT_LINE('Remote Users Records:'||to_char(p_count));
END;
/  2    3    4    5    6    7    8    9   10  

Procedure created.

SQL> CREATE OR REPLACE PROCEDURE p4
 AUTHID CURRENT_USER
IS
 p_count NUMBER;
BEGIN
   SELECT COUNT(*) INTO p_count FROM user1.test1;
  DBMS_OUTPUT.PUT_LINE('Remote Users Records:'||to_char(p_count));
END;
/  2    3    4    5    6    7    8    9  

Warning: Procedure created with compilation errors.

SQL> show errors;
Errors for PROCEDURE P4:

LINE/COL ERROR
-------- -----------------------------------------------------------------
6/4      PL/SQL: SQL Statement ignored
6/44     PL/SQL: ORA-00942: table or view does not exist
SQL> 

SQL> exec p2;
Remote Users Records:31786

PL/SQL procedure successfully completed.

用CURRENT_USER创建的P2可以顺利执行；用CURRENT_USER创建的P４还是不能编译通过。

SQL> DECLARE
  p_count NUMBER;
BEGIN
  SELECT COUNT(*) INTO p_count  FROM user1.test1;
  DBMS_OUTPUT.PUT_LINE('Remote Users Records:'||to_char(p_count));
END;
/  2    3    4    5    6    7
Remote Users Records:31786

PL/SQL procedure successfully completed.

SQL> DECLARE
  p_count NUMBER;
BEGIN
  EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM user1.test1'
     INTO p_count;
  DBMS_OUTPUT.PUT_LINE('Remote Users Records:'||to_char(p_count));
END;
/  2    3    4    5    6    7    8
Remote Users Records:31786

PL/SQL procedure successfully completed.

PLSQL Block和普通SQL的解析模式一样，所以不存在问题。

如果直接GRANT SELECT ANY TABLE 权限给USER2呢？

SQL> grant select any table to user2;

Grant succeeded.

SQL> connect user2/user2
Connected.
SQL> set serveroutput on
SQL> exec p1;
Remote Users Records:31786

PL/SQL procedure successfully completed.

SQL> exec p2;
Remote Users Records:31786

PL/SQL procedure successfully completed.

SQL> exec p3;
Remote Users Records:31786

PL/SQL procedure successfully completed.

SQL> exec p4;
Remote Users Records:31786

PL/SQL procedure successfully completed.

p1,p2,p3,p4全部顺利执行。

结论：

  • 存储过程具有两阶段的权限验证；
  • 存储过程具有AUTHID DEFINER\AUTHID CURRENT_USER两种创建模式；
  • 动态SQL是在运行时进行编译的；

Relative Posts

Tags: ,

Leave a Reply