Import of 10.1.0.5 into 11.1 or 11.2 does not retain the privileges of any account (Doc ID 1066762.1)

Last updated on FEBRUARY 10, 2011

Applies to:

Oracle Server - Enterprise Edition - Version: 10.2.0.4 to 11.2.0.2.0 - Release: 10.2 to 11.2
Information in this document applies to any platform.

Symptoms


1. The following error is reported when trying to access the account:

SQL*Plus: Release 11.2.0.1.0 Production on Fri Dec 11 10:09:21 2009
Copyright (c) 1982, 2009, Oracle. All rights reserved.

Enter user-name: bdai
Enter password:
ERROR:
ORA-01045: user BDAI lacks CREATE SESSION privilege; logon denied

BDAI had the following roles assigned to her:
Role Admin Option Default
CMS_WASTE_ACCUM N Y
CTS_UPDATE N N
CTS_VIEW N Y
MSDS_UPDATE N N
MSDS_VIEW N Y

The CTS_VIEW role which is a default role has the following:
CMS_WASTE_ACCUM
CTS_OBJ_VIEW
END_USER
MSDS_UPDATE
MSDS_VIEW

At the lowest level, END_USER has the following system privileges assigned to it:
ALTER SESSION
CREATE SEQUENCE
CREATE SESSION

So the question is, if end_user has create session and end_user is assigned to CTS-VIEW which is a default role assigned to bdai, why does a sqlplus session to the oracle 11 database as bdai generate the "ORA-01045: user BDAI lacks CREATE SESSION privilege; logon denied" error?

As a side note, in the Oracle 10g database, created a new account bdai and this account is identical to bdai, and yet, it is able to log on to the new Oracle 11.2.0.1 database.

The export of 10.1.0.5 database and import it into a second 10.1.0.5 database, all of the privileges are preseved for each account. The export of 10.1.0.5 database and import it into an 11.1.0.7 or 11.2.0.1 database, all of the privileges are ignored. They appear to be assigned but are not recognized.

Also tried using dbua, reported with the same results. Granting select any priveleges nothing. The table is real and has data. The user has select access to the table and can't access the table.

Changes

Tried to migrate the database from 10g to 11g R1 and R2

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