After Export And Import The Error ORA-942 Is Raised While Accessing Tables
(Doc ID 947974.1)
Last updated on APRIL 03, 2020
Applies to:Oracle Database - Enterprise Edition - Version 184.108.40.206 and later
Oracle Database Cloud Schema Service - Version N/A and later
Oracle Database Exadata Cloud Machine - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Oracle Database Backup Service - Version N/A and later
Information in this document applies to any platform.
A simple select on a table that has privileges granted via a role will return an ORA-942.
The issue is that after import into 220.127.116.11, a user receives ORA-942 when doing a SELECT from a table in another schema, even though the user was granted a role that in turn has SELECT on the table.
This behavior only occurs immediately after import.
NOTE: Problem does NOT occur after dropping/recreating the user and re-granting the role.
Notice that there is no active roles in SCOTT session even if this user has two roles granted.
Looking for "active" roles immediately after login:
select * from session_roles;
no rows selected
This implies that we expect the role to be a default role (i.e. enabled upon login). This can be verified in DBA_ROLE_PRIVS:
connect / as sysdba
select * from dba_role_privs where grantee=SCOTT';
GRANTEE GRANTED_ROLE ADM DEF
------------------------------ ------------------------------ --- ---
SCOTT READ NO NO
SCOTT UPDATE NO YES
When a role is initially granted (as when the role is recreated the user and re-granted the role), the role is enabled at that time -- which explains why it works when we drop/recreate the user and re-grants the role.
The role will need to be enabled after every login using the SET ROLE command. If the role is supposed to be default, then use ALTER USER to set the desired role(s) as default.
To view full details, sign in with your My Oracle Support account.
Don't have a My Oracle Support account? Click to get started!
In this Document