[20180131]ORA-00942 and AUTHID CURRENT_USER.txt
--//偶尔写一个存储过程调用一些系统视图.经常遇到一些ORA-00942,有时候很烦.有时候加AUTHID CURRENT_USER可以过去,有一些不行.
1.环境:
SCOTT@book> @ &r/ver1
PORT_STRING VERSION BANNER
-------------------- ---------- ----------------------------------------------------------------------------
x86_64/Linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
--//scott 用户是dba,权限如下:
CREATE USER SCOTT
IDENTIFIED BY
DEFAULT TABLESPACE USERS
TEMPORARY TABLESPACE TEMP
PROFILE DEFAULT
ACCOUNT UNLOCK;
-- 3 Roles for SCOTT
GRANT CONNECT TO SCOTT;
GRANT DBA TO SCOTT;
GRANT RESOURCE TO SCOTT;
ALTER USER SCOTT DEFAULT ROLE ALL;
-- 5 System Privileges for SCOTT
GRANT CREATE DATABASE LINK TO SCOTT;
GRANT EXPORT FULL DATABASE TO SCOTT;
GRANT IMPORT FULL DATABASE TO SCOTT;
//GRANT SELECT ANY DICTIONARY TO SCOTT;
GRANT UNLIMITED TABLESPACE TO SCOTT;
-- 2 Object Privileges for SCOTT
GRANT EXECUTE ON SYS.DBMS_SESSION TO SCOTT;
GRANT EXECUTE, READ, WRITE ON DIRECTORY TOAD_BDUMP_DIR TO SCOTT WITH GRANT OPTION;
2.测试1:
SCOTT@book> select count(*) from v$session ;
COUNT(*)
----------
29
--//在scott用户下访问是ok的.
CREATE OR REPLACE PROCEDURE TEST2
AUTHID CURRENT_USER
IS
v_n number;
begin
select count(*) into v_n from v$session ;
dbms_output.put_line(v_n);
end;
/
SCOTT@book> show error
Errors for PROCEDURE TEST2:
LINE/COL ERROR
-------- -----------------------------------------------
6/3 PL/SQL: SQL Statement ignored
6/33 PL/SQL: ORA-00942: table or view does not exist
--//以前一些blog经常建议
SYS@book> grant select on v_$session to scott;
Grant succeeded.
SCOTT@book> set serverout on
SCOTT@book> exec test2
32
PL/SQL procedure successfully completed.
--//但是如果很多明显不是很方面.
3.测试3:
CREATE OR REPLACE PROCEDURE TEST3
AUTHID CURRENT_USER
IS
v_n number;
begin
select count(*) into v_n from &1;
dbms_output.put_line(v_n);
end;
/
--//比如如上我带入dba_objects就出问题,而使用all_objects就没有问题.两者的差别到底在那里.
--//而且all_objects的定义更加复杂!!
SYS@book> grant select any dictionary to scott;
Grant succeeded.
--//有了这个权限,过程test2也可以执行.
SYS@book> revoke select on v_$session from scott;
Revoke succeeded.
SCOTT@book> set serverout on
SCOTT@book> exec test2
31
PL/SQL procedure successfully completed.
