Home » RDBMS Server » Security » Oracle Roles
Oracle Roles [message #64095] Tue, 14 December 2004 14:09 Go to next message
Nitin
Messages: 35
Registered: July 1999
Member
Hi all,

   I have a simple query. How to copy roles created in one database to the other database?

 
Re: Oracle Roles [message #64099 is a reply to message #64095] Wed, 15 December 2004 10:37 Go to previous messageGo to next message
croK
Messages: 170
Registered: April 2002
Senior Member
Simple question, simple answer: re-create it in target db as were created in source db.

Best luck.
Re: Oracle Roles [message #64100 is a reply to message #64095] Wed, 15 December 2004 11:05 Go to previous message
Frank Naude
Messages: 4579
Registered: April 1998
Senior Member
Hi,

You can extract the DDL statements for all roles on your source database and apply them on your target database.

This script (untested) should extract the required DDL:

set pages 0 line 132 rimspool on
spool cre8roles

exec dbms_metadata.set_transform_param( -
        dbms_metadata.session_transform, 'SQLTERMINATOR', true);

-- Create the roles
SELECT DBMS_METADATA.GET_DDL('ROLE', role)||';'
  FROM dba_roles
/

-- Roles which are granted to roles
SELECT DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT', role)
  FROM role_role_privs
/

-- System privileges granted to roles
SELECT DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT', role)
  FROM ROLE_SYS_PRIVS
/

-- Table privileges granted to roles
SELECT DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT', role)
  FROM ROLE_TAB_PRIVS
/

spool off


Best regards.

Frank
Previous Topic: Listing Oracle Roles
Next Topic: how to add a new user?
Goto Forum:
  


Current Time: Thu Mar 28 16:46:49 CDT 2024