ORA-00942 and "AUTHID CURRENT_USER"

Many non experienced developers and DBAs could fall in the ORA-00492 errors in some very strange situations where Oracle security behave in a little different way then they would expect.

This example is not connected to any specific Oracle version but mostly on fixed tables/views which are placed in sys schema. Let me explain how sometimes things might look crazy on first look and might happened even to schema owner with DBA roles.

Reproduction of the problem

Let us create one powerful user with DBA role. This example is also one nice trick how to create a user in just one line.

grant dba to scott_dba identified by tiger;

Resulting create user script in Oracle looks like:

CREATE USER SCOTT_DBA
  IDENTIFIED BY 
  DEFAULT TABLESPACE USERS
  TEMPORARY TABLESPACE TEMP
  PROFILE DEFAULT
  ACCOUNT UNLOCK;
  -- 1 Role for SCOTT_DBA 
  GRANT DBA TO SCOTT_DBA;
  -- 1 System Privilege for SCOTT_DBA 
  GRANT UNLIMITED TABLESPACE TO SCOTT_DBA;

It is imported to see that all what SCOTT_DBA can do is enabled through DBA role only ("unlimited tablespace" has no influence in this terms).

Quick prove that SCOTT_DBA can select two objects (views) which we will use later in example:

SQL> select count(*) from gv$event_name;

  COUNT(*)
----------
       995

SQL> select count(*) from gv$active_session_history;

  COUNT(*)
----------
       277

Create procedure which will use before tested two views and retrieve sql_id column from one of them. BTW, this query shows top 10 interesting queries from certain period (it works from 10g and afterward) but any other example with fixed views/tables behave in the same way.

SQL> CREATE OR REPLACE PROCEDURE TEST
  2  AUTHID CURRENT_USER
  3  IS
  4    CURSOR C IS
  5     SELECT sql_id
  6      FROM gv$active_session_history ash,
  7            gv$event_name evt
  8      WHERE ash.event_id = evt.event_id
  9        AND evt.wait_class = 'User I/O'
 10      GROUP BY sql_id
 11      having COUNT(*) >= 10
 12    ;
 13  begin
 14    for i in c loop
 15      dbms_output.put_line('Queries are: ' || i.sql_id);
 16    end loop;
 17  end;
 18  /

Warning: Procedure created with compilation errors.

SQL> show errors;
Errors for PROCEDURE TEST:

LINE/COL ERROR
-------- -----------------------------------------------------------------
5/4      PL/SQL: SQL Statement ignored
7/11     PL/SQL: ORA-00942: table or view does not exist
15/5     PL/SQL: Statement ignored
15/45    PLS-00364: loop index variable 'I' use is invalid
SQL>

Oooups! In line 7 Oracle says that gv$event_name doesn't exist! Other error (ORA-00364) is just circumstance of the first one which invalidate cursor.

Regardless SCOTT_DBA user has through DBA role many object privileges it looks like this is not enough of them to compile one simple procedure!??

And this is true in this case!

Cause

Any stored procedure in Oracle runs either with the rights of the caller (authid current_user) or with the rights of the procedure's owner (authid definer) what is default option if you do not specify anything.

Obviously, our example was using first (non default) way.

Brief explanation

For such a cases explanation is that a procedure with authid current_user "disables all roles" that are granted to the creator in the compile time. This can lead to ORA-00942 errors in compiling packages-what is our case!

Let us look a little bit closer what is gv$event_name object?

SQL> select OWNER,OBJECT_NAME,OBJECT_TYPE from dba_objects where object_name='GV$EVENT_NAME';

OWNER              OBJECT_NAME                    OBJECT_TYPE
------------------ ------------------------------ -------------------
PUBLIC             GV$EVENT_NAME                  SYNONYM

SQL> select OWNER,SYNONYM_NAME, TABLE_OWNER, TABLE_NAME from dba_synonyms where synonym_name='GV$EVENT_NAME';

OWNER              SYNONYM_NAME                   TABLE_OWNER                    TABLE_NAME
------------------ ------------------------------ ------------------------------ ------------------------------
PUBLIC             GV$EVENT_NAME                  SYS                            GV_$EVENT_NAME

SQL> select OWNER,OBJECT_NAME,OBJECT_TYPE from dba_objects where object_name='GV_$EVENT_NAME';

OWNER              OBJECT_NAME                    OBJECT_TYPE
------------------ ------------------------------ -------------------
SYS                GV_$EVENT_NAME                 VIEW

SQL>

How GV$EVENT_NAME is defined may be retrieved through

select VIEW_DEFINITION
from V$FIXED_VIEW_DEFINITION
where view_name='GV$EVENT_NAME';

Formatted result of previous query looks like:

SELECT inst_id,
       indx,
       ksledhash,
       kslednam,
       ksledp1,
       ksledp2,
       ksledp3,
       ksledclassid,
       ksledclass#,
       ksledclass
  FROM x$ksled 

So x$ksled is fixed X$ view which we are quering in real case. And we cannot query it directly!

This is proven very easy. If you execute query ... you'll get an error:

SQL> desc x$ksled;
ERROR:
ORA-04043: object x$ksled does not exist

SQL> desc sys.x$ksled;
ERROR:
ORA-04043: object sys.x$ksled does not exist

SQL> 

Ability to query this fixed view is inherited from mentioned view through SELECT_CATALOG_ROLE role! This role is, as mentioned before, multiple part of DBA role privileges.

So let us prove what SCOTT_DBA user really own of roles and it's privileges. Once again all what SCOTT_DBA can do is inherited from DBA role only!

For that I'll use mine script user_roles .

SQL> @user_roles
Enter value for enter_username: SCOTT_DBA
old  12:       username = upper('&enter_username')
new  12:       username = upper('SCOTT_DBA')

User: roles
-----------------------------------------------------
  SCOTT_DBA
    DBA
      DATAPUMP_EXP_FULL_DATABASE
        EXP_FULL_DATABASE
          EXECUTE_CATALOG_ROLE
            HS_ADMIN_ROLE
          SELECT_CATALOG_ROLE
            HS_ADMIN_ROLE
      DATAPUMP_IMP_FULL_DATABASE
        EXP_FULL_DATABASE
          EXECUTE_CATALOG_ROLE
            HS_ADMIN_ROLE
          SELECT_CATALOG_ROLE
            HS_ADMIN_ROLE
        IMP_FULL_DATABASE
          EXECUTE_CATALOG_ROLE
            HS_ADMIN_ROLE
          SELECT_CATALOG_ROLE
            HS_ADMIN_ROLE
      DELETE_CATALOG_ROLE
      EXECUTE_CATALOG_ROLE
        HS_ADMIN_ROLE
      EXP_FULL_DATABASE
        EXECUTE_CATALOG_ROLE
          HS_ADMIN_ROLE
        SELECT_CATALOG_ROLE
          HS_ADMIN_ROLE
      GATHER_SYSTEM_STATISTICS
      IMP_FULL_DATABASE
        EXECUTE_CATALOG_ROLE
          HS_ADMIN_ROLE
        SELECT_CATALOG_ROLE
          HS_ADMIN_ROLE
      JAVA_ADMIN
      JAVA_DEPLOY
      OLAP_DBA
      OLAP_XS_ADMIN
      SCHEDULER_ADMIN
      SELECT_CATALOG_ROLE
        HS_ADMIN_ROLE
      WM_ADMIN_ROLE
      XDBADMIN
      XDB_SET_INVOKER

43 rows selected.

SQL> 

It is really easy to see that SELECT_CATALOG_ROLE is under DBA role and inherited from many other subroles that DBA role has.

So when AUTHID CURRENT_USER is used, according previously said, when all roles are temporary revoked in compile time, SCOTT_DBA really cannot compile that procedure because he has not a single privilege to do that!

Which views are involved also?

Not only X$ fixed views are involved in case like this. Much better known ordinary G$ and V$ fixed views are involved as well. Quick example on very often used v$session view:

SQL> CREATE OR REPLACE PROCEDURE TEST2
  2  AUTHID  CURRENT_USER
  3  IS
  4    v_SID varchar2(24);
  5  begin
  6    select SID into v_sid
  7      from v$session
  8     where rownum<=1
  9    ;
 10    dbms_output.put_line(v_sid);
 11  end;
 12  /

Warning: Procedure created with compilation errors.

SQL> show errors;
Errors for PROCEDURE SCOTT_DBA.TEST2:

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

To be general in statement all fixed views/tables suffer from this kind of error in a cases like shown.

Solution

To overcome ORA-00942 problems with AUTHID, grant select any dictionary privileges to user that own schema object with error.

So, SCOTT_DBA just need to get just one additional system privilege (not any role again!!):

SQL> grant select any dictionary to scott_dba;

Grant succeeded.

SQL>

Just to remind that particular grant select on fixed tables/views is not possible:

SQL> grant select on sys.x$ksled to scott_dba;
grant select on sys.x$ksled to scott_dba
*
ERROR at line 1:
ORA-02030: can only select from fixed tables/views

SQL> 

More on this view and problems on fixed X$ views can be found at nice blog post Granting Access to X$ Tables.

This will will be enough to compile problematic TEST procedure:

SQL> SQL> alter procedure TEST compile;

Procedure altered.

SQL>

For the end

Regardless SCOTT_DBA could not compile TEST procedure without additional privileges, executor could inherited rights from role ONLY! Here is the prove for that:

SQL> grant dba to dba2 identified by dba2;

Grant succeeded.

SQL> conn dba2/dba2
Connected.
SQL> set serveroutput on size unl;
SQL> execute scott_dba.test;
Queries are:
Queries are: 1h50ks4ncswfn

PL/SQL procedure successfully completed.

SQL>

Cheers!