Oracle FAQ Your Portal to the Oracle Knowledge Grid

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

Re: Roles

From: Michel Cadot <micadot{at}altern{dot}org>
Date: Tue, 22 Feb 2005 19:54:12 +0100
Message-ID: <421b7fa1$0$8103$>

"g3000" <> a écrit dans le message de
| 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
| user1.
| 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.

When you drop a user all privileges granted on his objects are also dropped. You have to regrant them after the import.

Michel Cadot Received on Tue Feb 22 2005 - 12:54:12 CST

Original text of this message