【Oracle Database】DBA権限のみ付与された状態でDBA表を参照するようなPL/SQLを作成するとORA-00942でエラーになる

DBA権限だけでDBA表にselectできるのでPL/SQLでも参照可能かと思っていたらコンパイルエラーになる

SQL> grant dba to db_admin
  2  ;

Grant succeeded.

SQL> conn db_admin/db_admin
Connected.
SQL> desc dba_hist_snapshot
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 SNAP_ID                                   NOT NULL NUMBER
 DBID                                      NOT NULL NUMBER
 INSTANCE_NUMBER                           NOT NULL NUMBER
 STARTUP_TIME                              NOT NULL TIMESTAMP(3)
 BEGIN_INTERVAL_TIME                       NOT NULL TIMESTAMP(3)
 END_INTERVAL_TIME                         NOT NULL TIMESTAMP(3)
 FLUSH_ELAPSED                                      INTERVAL DAY(5) TO SECOND(1)
 SNAP_LEVEL                                         NUMBER
 ERROR_COUNT                                        NUMBER
 SNAP_FLAG                                          NUMBER
 SNAP_TIMEZONE                                      INTERVAL DAY(0) TO SECOND(0)
 CON_ID                                             NUMBER

SQL> select count(*) from dba_hist_snapshot;

  COUNT(*)
----------
        96

SQL> create or replace procedure db_admin.get_snap_id
  2  is
  v_begin_id number;
  3    4    v_end_id number;
begin
  5    6    select min(snap_id),max(snap_id) into v_begin_id,v_end_id from SYS.DBA_HIST_SNAPSHOT
    where end_interval_time between '2019-06-13 13.55.47' and '2019-06-13 14.55.47';
end;
  7    8    9  /

Warning: Procedure created with compilation errors.

SQL> show error
Errors for PROCEDURE DB_ADMIN.GET_SNAP_ID:

LINE/COL ERROR
-------- -----------------------------------------------------------------
6/3      PL/SQL: SQL Statement ignored
6/70     PL/SQL: ORA-00942: table or view does not exist

いやぁんばかぁん

個別にselect権限を付与してやるとコンパイルが通るようになる

SQL> conn / as sysdba
Connected.
SQL> grant select on dba_hist_snapshot to db_admin;

Grant succeeded.

SQL> conn db_admin/db_admin
Connected.
SQL> create or replace procedure db_admin.get_snap_id
  2  is
  v_begin_id number;
  3    4    v_end_id number;
begin
  5    6    select min(snap_id),max(snap_id) into v_begin_id,v_end_id from SYS.DBA_HIST_SNAPSHOT
    where end_interval_time between '2019-06-13 13.55.47' and '2019-06-13 14.55.47';
end;
  7    8    9  /

Procedure created.

チョットダケヨー

SELECT ANY TABLE権限ではだめ

SQL> conn / as sysdba
Connected.
SQL>
SQL> revoke select on dba_hist_snapshot from db_admin;

Revoke succeeded.

SQL> grant select any table to db_admin;

Grant succeeded.

SQL> conn db_admin/db_admin
Connected.
SQL> create or replace procedure db_admin.get_snap_id
  2  is
  v_begin_id number;
  3    4    v_end_id number;
begin
  5    6    select min(snap_id),max(snap_id) into v_begin_id,v_end_id from SYS.DBA_HIST_SNAPSHOT
    where end_interval_time between '2019-06-13 13.55.47' and '2019-06-13 14.55.47';
end;
  7    8    9  /

Warning: Procedure created with compilation errors.

SELECT ANY DICTIONARYならOK

SQL> conn / as sysdba
Connected.
SQL> revoke select any table from db_admin;

Revoke succeeded.

SQL> grant select any dictionary to db_admin;

Grant succeeded.

SQL> conn db_admin/db_admin
Connected.
SQL> create or replace procedure db_admin.get_snap_id
  2  is
  v_begin_id number;
  v_end_id number;
  3    4    5  begin
  select min(snap_id),max(snap_id) into v_begin_id,v_end_id from SYS.DBA_HIST_SNAPSHOT
    where end_interval_time between '2019-06-13 13.55.47' and '2019-06-13 14.55.47';
  6    7    8  end;
/  9

Procedure created.