After Export And Import The Error ORA-942 Is Raised While Accessing Tables (Doc ID 947974.1)

Last updated on NOVEMBER 14, 2011

Applies to:

Oracle Server - Enterprise Edition - Version: 11.1.0.7 and later   [Release: 11.1 and later ]
Information in this document applies to any platform.

Symptoms

After exporting a 9.2 database and then importing into an 11.1 database you find that privileges that are granted via a role not working for the user as they did in 9.2.

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 11.1.0.7, 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:

connect SCOTT
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.

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms