A user with SELECT ANY TABLE privilege gets ORA-00942: TABLE OR VIEW DOES NOT EXIST when quering any table.
SYMPTOMS

A user has SELECT ANY TABLE privilege but He/She gets ORA-00942: table or view does not exist error when querying any table.

SELECT ANY TABLE

Query tables, views, or materialized views in any schema except SYS,AUDSYS.
Obtain row locks using a SELECT ... FOR UPDATE.

CAUSE

A user does not have the direct SELECT ANY TABLE privilege.
The SELECT ANY TABLE privilege have been granted to a role, and the role has been granted to a user. The role is NOT DEFAULT user role.

SOLUTION

Set the role having SELECT ANY TABLE privilege as the DEFAULT user role.

DEMO
Create users for the demo
SQL> SHO USER
USER is "SYS"

-- Table owner and Table

CREATE USER DEMO_OWNER IDENTIFIED BY DEMO;
GRANT CREATE TABLE TO DEMO_OWNER;
ALTER USER  DEMO_OWNER QUOTA 10M ON USERS;

CREATE TABLE DEMO_OWNER.DUMMY_TABLE(COL1 VARCHAR2(10));
INSERT INTO DEMO_OWNER.DUMMY_TABLE VALUES ('Accessed');
COMMIT;

-- Grantee

CREATE USER DEMO_GRANTEE IDENTIFIED BY DEMO;
GRANT CONNECT TO DEMO_GRANTEE;

-- Role with required privilege 

CREATE ROLE DEMO_ROLE_1;
GRANT SELECT ANY TABLE TO DEMO_ROLE_1;
GRANT DEMO_ROLE_1 TO DEMO_GRANTEE;

-- Default roles for Grantee 

ALTER USER DEMO_GRANTEE DEFAULT ROLE CONNECT;
Get all Grantee roles
SET LINES 300
SET PAGES 99
COL GRANTED_ROLE FOR A12
COL GRANTEE FOR A12
COL DEFAULT_ROLE FOR A12
SELECT GRANTED_ROLE,GRANTEE,DEFAULT_ROLE FROM DBA_ROLE_PRIVS WHERE GRANTEE='DEMO_GRANTEE' ORDER BY DEFAULT_ROLE;
GRANTED_ROLE GRANTEE      DEFAULT_ROLE
------------ ------------ ------------
DEMO_ROLE_1  DEMO_GRANTEE NO
CONNECT      DEMO_GRANTEE YES
NOTE

Here is DEMO_ROLE_1 role is NOT DEFAULT role for Grantee user.

 

Connect as Grantee and select from Owner table
SQL> CONNECT DEMO_GRANTEE/DEMO
Connected.

SQL> SHOW USER
USER is "DEMO_GRANTEE"

SQL> SELECT * FROM DEMO_OWNER.DUMMY_TABLE;
SELECT * FROM DEMO_OWNER.DUMMY_TABLE
                         *
ERROR at line 1:
ORA-00942: table or view does not exist

It failed because Grantee user does not have DEMO_ROLE_1 role enabled by default

SQL> SHOW USER
USER is "DEMO_GRANTEE"

SQL> SELECT * FROM SESSION_ROLES;

ROLE
---------------
CONNECT
Enable the DEMO_ROLE_1 role in Grantee session and try again
SQL> SHOW USER
USER is "DEMO_GRANTEE"

SQL> SELECT * FROM SESSION_ROLES;

ROLE
---------------
CONNECT

SQL> SET ROLE DEMO_ROLE_1;

Role set.

SQL> SELECT * FROM SESSION_ROLES;

ROLE
---------------
DEMO_ROLE_1

SQL> SELECT * FROM DEMO_OWNER.DUMMY_TABLE;

COL1
----------
Accessed

When the role is enabled Grantee user is able to select from Owner table.

Set the DEMO_ROLE_1 role for Grantee user as the default role
SQL> SHOW USER
USER is "SYS"

SQL> ALTER USER DEMO_GRANTEE DEFAULT ROLE CONNECT, DEMO_ROLE_1;

User altered.

SQL> SELECT GRANTED_ROLE,GRANTEE,DEFAULT_ROLE FROM DBA_ROLE_PRIVS WHERE GRANTEE='DEMO_GRANTEE' ORDER BY DEFAULT_ROLE;

GRANTED_ROLE GRANTEE      DEFAULT_ROLE
------------ ------------ ------------
DEMO_ROLE_1  DEMO_GRANTEE YES
CONNECT      DEMO_GRANTEE YES


SQL> CONNECT DEMO_GRANTEE/DEMO
Connected.

SQL> SHO USER
USER is "DEMO_GRANTEE"

SQL> SELECT * FROM SESSION_ROLES;

ROLE
---------------
CONNECT
DEMO_ROLE_1

SQL> SELECT * FROM DEMO_OWNER.DUMMY_TABLE;

COL1
----------
Accessed

That's it. Drop Grantee and Owner users.

SQL> SHOW USER
USER is "SYS"

SQL> DROP USER DEMO_OWNER CASCADE;

User dropped.

SQL> DROP USER DEMO_GRANTEE CASCADE;

User dropped.

SQL> DROP ROLE DEMO_ROLE_1;

Role dropped.

Written At
22 JUNE 202223:00
Red Hat Release
7.x x64
Database Release
19.13.0.0.0