My Oracle Support Banner

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 27, 2019

Applies to:

Oracle Database - Enterprise Edition - Version 10.2.0.4 to 11.2.0.2.0 [Release 10.2 to 11.2]
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.

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: <USER_NAME>
Enter password:
ERROR:
ORA-01045: user <USER_NAME> lacks CREATE SESSION privilege; logon denied

<USER_NAME> had the following roles assigned to her:
Role Admin Option Default
<ROLE_NAME1> N Y
<ROLE_NAME2> N N
<ROLE_NAME3> N Y
<ROLE_NAME4> N N
<ROLE_NAME5> N Y

The <ROLE_NAME3> role which is a default role has the following:
<ROLE_NAME1>
<ROLE_NAME6>
<ROLE_NAME7>
<ROLE_NAME4>
<ROLE_NAME5>

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 <ROLE_NAME3> which is a default role assigned to <USER_NAME>, why does a sqlplus session to the oracle 11 database as <USER_NAME> generate the "ORA-01045: user <USER_NAME> lacks CREATE SESSION privilege; logon denied" error?

As a side note, in the Oracle 10g database, created a new account <USER_NAME> and this account is identical to <USER_NAME>, 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

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
Symptoms
Changes
Cause
Solution
References


My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.