问题
存过中有查询dba_相关的数据字典视图,编译时却提示 ‘表或视图不存在’,编译不通过。
但是报错的语句在sql中确实可以查到的,说明问题并不是提出给出的’表或视图不存在’导致的。
这里需要注意sql权限和存过的权限是不同的。
我们查询的是sys用户下的数据字典,需要显示授权
虽然cc用户也是DBA用户。
select * from dba_role_privs where grantee='CC';
解决办法
oracle存储过程默认定义者权限,但ROLE对存储过程不可见。
因此需要给cc用户显示赋权。 使用authid current_user也不行。
使用sys用户
grant select any dictionary to cc;
重新编译,通过
知识引申
oracle存储过程分两种,DR(Definer’s Rights ) Procedure和IR(Invoker’s Rights ) Procedure。
在执行存储过程时,我们可能会遇到权限问题
- AUTHID DEFINER (定义者权限):指编译存储对象的所有者。也是默认权限模式。
- AUTHID CURRENT_USER(调用者权限):指拥有当前会话权限的模式,这可能和当前登录用户相同或不同(alter session set current_schema 可以改变调用者Schema)
在数据库中创建存储过程时,定义者权限是缺省模式。
当指定AUTHID CURRENT_USER关键字后,便是调用者权限存储过程.
它们之间最根本的差异在于role能否在存储过程中生效
定义者权限存储过程问题
定义者权限存储过程role无效,必须要有显式授权。即便是拥有dba role,还是不能访问不同用户的表。
有时候可能需要进行非常多的授权才能执行存储过程,稍显麻烦。
oracle给我们提供了在存储过程中使用role权限的方法:
修改存储过程,加入Authid Current_User时存储过程可以使用role权限(调用者权限)。
栗子
编译通过
执行失败。
方法一:使用sys用户赋权限
sql> conn sys/****** as sysdba;
Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.4.0
Connected as sys@cc AS SYSDBA
sql> grant create table to cc;
Grant succeeded
重新执行
sql> exec p_test_proc_priv;
PL/sql procedure successfully completed
方法二 :修改存储过程,加入Authid Current_User时存储过程可以使用role权限。
现在先回收掉权限,改用另外的方式
sql> conn sys/****** as sysdba;
Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.4.0
Connected as sys@cc AS SYSDBA
sql> revoke create table from cc;
Revoke succeeded
重新执行后:
sql> exec p_test_proc_priv;
begin p_test_proc_priv; end;
ORA-01031: insufficient privileges
ORA-06512: at "CC.P_TEST_PROC_PRIV",line 4
ORA-06512: at line 2
直白的说,就是让这个存过拥有当前用户的权限,如果这个用户可以create table,那么这个存过通过authid current_user,也获得该用户的权限。
修改存过
记得先把已经 exec 执行存过创建的表 drop掉,否则报错。
ORACLE权限相关
查看一个用户的所有系统权限(包含角色的系统权限)
select privilege from dba_sys_privs where grantee='DATAUSER' union select privilege from dba_sys_privs where grantee in (select granted_role from dba_role_privs where grantee='DATAUSER' );
查看Oracle提供的系统权限
select name from sys.system_privilege_map a order by a.name ;
oracle 11g中 209条数据