Extracting details of defined roles

[upthorpe]

Is there any was to extract details of defined roles from an Oracle 9i database. I need to recreate them exactly in another (similar database).

I've been looking at various parameters of the export utility, but I don't think I can achieve it this way.

 

[SantaMufasa]

Up,

I believe what you are asking appears in the data dictionary view DBA_, ALL_, or USER_ROLE_PRIVS. Here is a describe of DBA_ROLE_PRIVS:

SQL> desc dba_role_privs
 Name                    Null?    Type
 ----------------------- -------- ------------
 GRANTEE                          VARCHAR2(30)
 GRANTED_ROLE            NOT NULL VARCHAR2(30)
 ADMIN_OPTION                     VARCHAR2(3)
 DEFAULT_ROLE                     VARCHAR2(3)

...and similar columns are in the other views, as well.

Let us know if this is what you wanted.

Mufasa
(aka Dave of Sandy, Utah, USA @ 16:31 (02Sep04) UTC (aka "GMT" and "Zulu"), 09:31 (02Sep04) Mountain Time)

 

[upthorpe]

Not quite I think, I have an Oracle 9i forms 6i system where various roles have been defined, such as CX_ACCOUNTS which allow access to accountancy type functions (theres others like CX_STOCK, CX_SUPERUSR etc).

I can find what roles are granted to each user in the way that you suggest, but I actually want to find the details of what CX_ACCOUNTS (and the rest) actually contain so I can recreate these defined roles exactly in the new database.

I know some of the basics of Oracle, but by no means am expert - I've kind of been dropped in the deep end here.

 

[SantaMufasa]

Sorry, Up, I should have also mentioned two other sets of views: DBA_, ALL_, or USER_SYS_PRIVS and DBA_, ALL_, or USER_TAB_PRIVS. In those views, GRANTEE contains the ROLE name(s) in which you are interested.

Let us know if the addition of these views solves your problem.

Mufasa
(aka Dave of Sandy, Utah, USA @ 16:59 (02Sep04) UTC (aka "GMT" and "Zulu"), 09:59 (02Sep04) Mountain Time)

 

[upthorpe]

Well I've done it! I ended up doing a full export from the original database, reimporting into a blank database with SHOW=Y and logging to a text file.

I then used notepad to edit and extract the individual SQL grants for my roles into another text file which I then ran with SQLPlus to create the roles in the new database.

Perhaps not a very elegant solution, but it worked!

Thanks for all the assistance.