ITPub博客
首页 博客 专栏 学院 社区 IT新媒体评选
发博

首页 > 数据库 > Oracle > ORACLE中存储过程的权限问题

ORACLE中存储过程的权限问题

原创 Oracle 作者：goodlatch 时间：2015-03-16 20:56:06 0 删除 编辑
或许你也遇到过同样的问题:
用sql语句建表没问题，但是放到存储过程里执行就报没有权限;
用sql查询或dml操作也是没问题，放到存储过程就报错权限不足,或表不存在.

在官方文档上看到有这么一段话 ,文档地址 http://docs.oracle.com/cd/E11882_01/network.112/e36292/authorization.htm#i1007304 

How Roles Work in PL/SQL Blocks
The use of roles in a PL/SQL block depends on whether it is an anonymous block or a named block (stored procedure, function, or trigger), and whether it executes with definer's rights or invoker's rights.
Roles Used in Named Blocks with Definer's Rights
All roles are disabled in any named PL/SQL block (stored procedure, function, or trigger) that executes with definer's rights. Roles are not used for privilege checking and you cannot set roles within a definer's rights procedure.
The SESSION_ROLES view shows all roles that are currently enabled. If a named PL/SQL block that executes with definer's rights queries SESSION_ROLES, then the query does not return any rows.
Roles Used in Named Blocks with Invoker's Rights and Anonymous PL/SQL Blocks
Named PL/SQL blocks that execute with invoker's rights and anonymous PL/SQL blocks are executed based on privileges granted through enabled roles. Current roles are used for privilege checking within an invoker's rights PL/SQL block. You can use dynamic SQL to set a role in the session.


其大意就是说用户角色的权限在命名的pl/sql块里是不可用的，包括存储过程、触发器、函数，但是在匿名的pl/sql块里是没问题的。
什么是角色的权限呢?
可以通过dba_roles查看所有角色:select * from dba_roles;
可以通过user_role_privs及role_sys_privs查看用户有那些角色，及角色具有的权限。 用户在这两个视图中的权限在存储过程、触发器、函数中是不起作用的。


下面演示DDL的实验，用scott用户建一个存储过程来删除表和创建表

  1. SQL> set linesize 1000
  2. SQL> show user;
  3. USER is "SCOTT"
  4. SQL> col text format a80
  5. SQL> select line,text from user_source where type='PROCEDURE' and name='P_EMP1' ORDER BY LINE;

  7.       LINE TEXT
  8. ---------- --------------------------------------------------------------------------------
  9.      1 procedure p_emp1
  10.      2 as
  11.      3 begin
  12.      4 execute immediate 'drop table emp1';
  13.      5 execute immediate 'create table emp1 as select * from emp';
  14.      6 end;

  16. 6 rows selected.

  18. SQL> drop table emp1;

  20. Table dropped.

  22. SQL> create table emp1 as select * from emp;

  24. Table created.

  26. SQL>
  27. SQL> exec p_emp1;
  28. BEGIN p_emp1; END;

  30. *
  31. ERROR at line 1:
  32. ORA-01031: insufficient privileges
  33. ORA-06512: at "SCOTT.P_EMP1", line 5
  34. ORA-06512: at line 1
发现用sql语句直接运行create table emp1 as select * from emp 是没有问题的，但是在存储过程中提示权限不足。
来查一下scott用户有哪些系统权限


  1. SQL> select * from session_roles;

  3. ROLE
  4. ------------------------------
  5. CONNECT
  6. RESOURCE

  8. SQL> select * from role_sys_privs order by role;

  10. ROLE             PRIVILEGE                ADM
  11. ------------------------------ ---------------------------------------- ---
  12. CONNECT          CREATE SESSION                NO
  13. RESOURCE         CREATE CLUSTER                NO
  14. RESOURCE         CREATE INDEXTYPE             NO
  15. RESOURCE         CREATE OPERATOR                NO
  16. RESOURCE         CREATE PROCEDURE             NO
  17. RESOURCE         CREATE SEQUENCE                NO
  18. RESOURCE         CREATE TABLE                NO
  19. RESOURCE         CREATE TRIGGER                NO
  20. RESOURCE         CREATE TYPE                NO

  22. 9 rows selected.

  24. SQL> select * from user_sys_privs;

  26. USERNAME         PRIVILEGE                ADM
  27. ------------------------------ ---------------------------------------- ---
  28. SCOTT             UNLIMITED TABLESPACE            NO
scott有CONNECT和RESOURCE两个角色的权限，还有一个UNLIMITED TABLESPACE的系统权限。
接下来，给scott分配create table的权限，在看看效果


  1. SQL> conn /as sysdba
  2. Connected.
  3. SQL> grant create table to scott;

  5. Grant succeeded.

  7. SQL> conn scott/scott
  8. Connected.
  9. SQL> select * from user_sys_privs;

  11. USERNAME         PRIVILEGE                ADM
  12. ------------------------------ ---------------------------------------- ---
  13. SCOTT             UNLIMITED TABLESPACE            NO
  14. SCOTT             CREATE TABLE                NO

  16. SQL> exec p_emp1;
  17. BEGIN p_emp1; END;

  19. *
  20. ERROR at line 1:
  21. ORA-00942: table or view does not exist
  22. ORA-06512: at "SCOTT.P_EMP1", line 4
  23. ORA-06512: at line 1
  24. --这里报错是因为刚刚运行存储过程的时候已经drop掉emp1表了，但是create时候报错，所以这是会报表不存在

  26. SQL> create table emp1 as select * from emp;

  28. Table created.

  30. SQL> exec p_emp1;

  32. PL/SQL procedure successfully completed.
给scott分批create table的权限后，我们可以通过user_sys_privs视图看到scott拥有的权限，这时候存储过程正常了。

*********************************************************************************************************


接下来测试下dml操作,给scott用户dba的角色,然后让scott用户操作test用户下的表


  1. SQL> conn /as sysdba
  2. Connected.
  3. SQL> grant dba to scott;

  5. Grant succeeded.

  7. SQL> conn scott/scott
  8. Connected.
  9. SQL>
  10. SQL> select * from role_sys_privs order by role;

  12. ROLE             PRIVILEGE                ADM
  13. ------------------------------ ---------------------------------------- ---
  14. CONNECT          CREATE SESSION                NO
  15. DATAPUMP_EXP_FULL_DATABASE CREATE SESSION                NO
  16. DATAPUMP_EXP_FULL_DATABASE CREATE TABLE                NO
  17. DATAPUMP_IMP_FULL_DATABASE ALTER DATABASE                NO
  18. DATAPUMP_IMP_FULL_DATABASE ALTER PROFILE                NO
  19. DATAPUMP_IMP_FULL_DATABASE ALTER RESOURCE COST            NO
  20. DATAPUMP_IMP_FULL_DATABASE ALTER USER                NO
  21. DATAPUMP_IMP_FULL_DATABASE AUDIT ANY                NO
  22. DATAPUMP_IMP_FULL_DATABASE AUDIT SYSTEM                NO
  23. DATAPUMP_IMP_FULL_DATABASE CREATE PROFILE                NO
  24. DATAPUMP_IMP_FULL_DATABASE CREATE SESSION                NO

  26. ROLE             PRIVILEGE                ADM
  27. ------------------------------ ---------------------------------------- ---
  28. DATAPUMP_IMP_FULL_DATABASE DELETE ANY TABLE             NO
  29. DATAPUMP_IMP_FULL_DATABASE EXECUTE ANY OPERATOR            NO
  30. DATAPUMP_IMP_FULL_DATABASE GRANT ANY OBJECT PRIVILEGE        NO
  31. DATAPUMP_IMP_FULL_DATABASE GRANT ANY PRIVILEGE            NO
  32. DATAPUMP_IMP_FULL_DATABASE GRANT ANY ROLE                NO
  33. DATAPUMP_IMP_FULL_DATABASE SELECT ANY TABLE             NO
  34. DBA             ADMINISTER ANY SQL TUNING SET        YES
  35. DBA             ADMINISTER DATABASE TRIGGER        YES
  36. DBA             ADMINISTER RESOURCE MANAGER        YES
  37. DBA             ADMINISTER SQL MANAGEMENT OBJECT     YES
  38. DBA             ADMINISTER SQL TUNING SET        YES
  39. ...
  40. --下边有很多就不显示了，我们可以看到dba角色的权限已经有了


  43. SQL> set linesize 100
  44. SQL> desc test.t;
  45.  Name                         Null?    Type
  46.  ----------------------------------------------------- -------- ------------------------------------
  47.  ID                                NUMBER

  49. SQL> select * from test.t;

  51.     ID
  52. ----------
  53.      1

  55. SQL>

  57. SQL> select line,text from user_source where type='PROCEDURE' and name='P_INSERT_T' ORDER BY LINE;

  59.       LINE TEXT
  60. ---------- --------------------------------------------------------------------------------
  61.      1 procedure p_insert_t as
  62.      2 begin
  63.      3 insert into test.t values (2);
  64.      4 end;

  66. SQL> insert into test.t values (2);   --可以看到直接insert是没有问题的

  68. 1 row created.

  70. SQL> select * from test.t;

  72.     ID
  73. ----------
  74.      1
  75.      2

  77. SQL> rollback;

  79. Rollback complete.

  81. SQL> exec p_insert_t;
  82. BEGIN p_insert_t; END;

  84.       *
  85. ERROR at line 1:
  86. ORA-06550: line 1, column 7:
  87. PLS-00905: object SCOTT.P_INSERT_T is invalid
  88. ORA-06550: line 1, column 7:
  89. PL/SQL: Statement ignored

  91. --这里报了sql无效，我们编译下存储过程看看报什么错误

  93. SQL> alter procedure p_insert_t compile;

  95. Warning: Procedure altered with compilation errors.

  97. SQL> show errors;
  98. Errors for PROCEDURE P_INSERT_T:

  100. LINE/COL ERROR
  101. -------- -----------------------------------------------------------------
  102. 3/3     PL/SQL: SQL Statement ignored
  103. 3/20     PL/SQL: ORA-00942: table or view does not exist
  104. --这里报表不存在，说明没有对test用户的t表的select权限，我们来赋下权限
  1. SQL>
  2. SQL> conn test/test
  3. Connected.
  4. SQL> grant select on t to scott;

  6. Grant succeeded.

  8. SQL> conn scott/scott
  9. Connected.
  10. SQL> alter procedure p_insert_t compile;

  12. Warning: Procedure altered with compilation errors.

  14. SQL> show errors;
  15. Errors for PROCEDURE P_INSERT_T:

  17. LINE/COL ERROR
  18. -------- -----------------------------------------------------------------
  19. 3/3     PL/SQL: SQL Statement ignored
  20. 3/20     PL/SQL: ORA-01031: insufficient privileges
  21. --这时候开始报没有insert权限，继续赋权
  22. SQL>
  23. SQL>
  24. SQL> conn test/test
  25. Connected.
  26. SQL> grant insert on t to scott;

  28. Grant succeeded.

  30. SQL> conn scott/scott
  31. Connected.
  32. SQL> alter procedure p_insert_t compile;

  34. Procedure altered.

  36. SQL> select * from test.t;

  38.     ID
  39. ----------
  40.      1

  42. SQL> exec p_insert_t;

  44. PL/SQL procedure successfully completed.

  46. SQL> select * from test.t;

  48.     ID
  49. ----------
  50.      1
  51.      2
  52. --最后我们查下scott都有那些表才操作权限
  53. SQL> select * from user_tab_privs;

  55. GRANTEE          OWNER             TABLE_NAME         GRANTOR             PRIVILEGE                 GRA HIE
  56. ------------------------------ ------------------------------ ------------------------------ ------------------------------ ---------------------------------------- --- ---
  57. SCOTT             TEST             T              TEST             INSERT                 NO NO
  58. SCOTT             TEST             T              TEST             SELECT                 NO NO

 所以在处理存储过程的时候要注意这些权限问题，不要只看用户的角色。

******************************************************************************************************************

我们总是想偷偷懒，每次都要赋权限感觉好麻烦，有没有其他好办法呢？答案是：有。oracle提供了一个authid的属性

  1. SQL> conn /as sysdba
  2. Connected.
  3. SQL> revoke create table from scott;

  5. Revoke succeeded.

  7. SQL> conn scott/scott
  8. Connected.

  10. SQL> select * from user_sys_privs;

  12. USERNAME         PRIVILEGE                ADM
  13. ------------------------------ ---------------------------------------- ---
  14. SCOTT             UNLIMITED TABLESPACE            NO

  16. SQL> select line,text from user_source where type='PROCEDURE' and name='P_EMP1' ORDER BY LINE;

  18.       LINE TEXT
  19. ---------- --------------------------------------------------------------------------------
  20.      1 procedure p_emp1
  21.      2 authid CURRENT_USER  --注意这里，多了一句
  22.      3 as
  23.      4 begin
  24.      5 execute immediate 'drop table emp1';
  25.      6 execute immediate 'create table emp1 as select * from emp';
  26.      7 end;

  28. 7 rows selected.

  30. SQL> exec p_emp1;

  32. PL/SQL procedure successfully completed.

添加一个authid CURRENT_USER就可以了，当然这么做也有一点风险，就是如果scott和test用户都有一个emp表，那么当test用户执行的时候，新建的emp1表会在test用户下


  1. SQL> select line,text from user_source where type='PROCEDURE' and name='P_EMP1' ORDER BY LINE;

  3.       LINE TEXT
  4. ---------- --------------------------------------------------------------------------------
  5.      1 procedure p_emp1
  6.      2 authid CURRENT_USER
  7.      3 as
  8.      4 begin
  9.      5 execute immediate 'create table emp1 as select * from emp'; --这里我们去掉了drop的语句
  10.      6 end;

  12. 6 rows selected.

  14. SQL> conn / as sysdba
  15. Connected.
  16. SQL>
  17. SQL>
  18. SQL> select owner,table_name from dba_tables where table_name='EMP1';

  20. OWNER             TABLE_NAME
  21. ------------------------------ ------------------------------
  22. SCOTT             EMP1

  24. SQL> drop table scott.emp1;

  26. Table dropped.

  28. SQL> grant resource to test;

  30. Grant succeeded.

  32. SQL> grant execute on scott.p_emp1 to test;

  34. Grant succeeded.

  36. SQL> conn test/test;
  37. Connected.
  38. SQL> create table emp (id number) ;

  40. Table created.

  42. SQL> exec scott.p_emp1;

  44. PL/SQL procedure successfully completed.

  46. SQL> conn /as sysdba
  47. Connected.

  48.
  49. SQL> select owner,table_name from dba_tables where table_name='EMP1';

  51. OWNER             TABLE_NAME
  52. ------------------------------ ------------------------------
  53. TEST             EMP1

来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/29339009/viewspace-1462017/ ，如需转载，请注明出处，否则将追究法律责任。

0
0
上一篇： oracle静默安装
下一篇： oracle删除用户后的恢复测试
请登录后发表评论 登录
全部评论
goodlatch

注册时间：2013-11-27

  • 博文量
    6
  • 访问量
    22055

最新文章

支持我们 作者招募 用户协议 FAQ Contact Us

北京盛拓优讯信息技术有限公司. 版权所有  京ICP备16024965号-8 北京市公安局海淀分局网监中心备案编号：11010802021510 niuxiaotong@pcpop.com 17352615567 未成年人举报专区

广播电视节目制作经营许可证(京) 字第1234号 中国互联网协会会员

违法和不良信息举报电话:18101376593