[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.