Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Usenet -> c.d.o.server -> Roles


From: g3000 <>
Date: 22 Feb 2005 09:10:07 -0800
Message-ID: <>

I have the following scenario that I need guidance on.

I have a database user (user1) that I want to export and later import the user into the same schema name.

The user1 has had system privileges granted to it by SYS.

There are roles created that give access to the objects user1 owns.

All users who have access to user1 schema via roles also must have a profile created other then the default.
This profile obviously has some different settings. One of those settings is a password function which is owned by SYS.

When I export user1 I also identify everyone who has the profile assigned to them and I capture and export their username and the roles assigned to them that allow them to access user1's schema.

I do this to recreate the users with the same access once I import user1 schema.

When I import user1 back I know I have to reissue all the system privileges granted by SYS to
I also have to issue the roles to user1 even though they are for its schema.
I also have to recreate the users I exported and reissue the roles to them ( which should still exist )
Users who log in to the application execute packages owned by user1 AS user1 ( definer rights )
But user1 has to have those roles to grant them to users who execute his packages.

My question is this.

Role1 is created.
Privilege granted to Role1 on user1.table1 or user1.package1 User1 is dropped with a cascade

What happens to the privileges granted to Role1? Im thinking they should remain valid so when I recreate
User1 with
no objects in the schema then import User1 all the roles should be in there right state

Problem is when I try to use one of those users that is recreated the user cant log into the application.
It has something to do with a role but I cant figure it out. Received on Tue Feb 22 2005 - 11:10:07 CST

Original text of this message