Re: Roles

"g3000" <carlton_gregory_at_yahoo.com> a écrit dans le message de news:1109092207.966284.189790_at_l41g2000cwc.googlegroups.com...
| 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.

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