Oracle Database Discussions

The Oracle provided package dbms_metadata can also be used to generate role DDL.

You can use the export/import utilities to genertate create user and role DDL.

You can use SQL to generate SQL (DDL) by querying the rdbms dictionary views to accomplish this task.

HTH -- Mark D Powell --

Use dbms_metadata, read the documentation: http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_metada.htm#i1015856

SYS@orcl > SELECT dbms_metadata.get_ddl('USER','SCOTT') FROM dual;

DBMS_METADATA.GET_DDL('USER','SCOTT')
--------------------------------------------------------------------------------
   CREATE USER "SCOTT" IDENTIFIED BY VALUES 'F894844C34402B67'
      DEFAULT TABLESPACE "USERS"
      TEMPORARY TABLESPACE "TEMP"

SYS@orcl > SELECT DBMS_METADATA.GET_GRANTED_DDL(
  2 'ROLE_GRANT','SCOTT') from dual;

DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT','SCOTT')
--------------------------------------------------------------------------------
   GRANT "CONNECT" TO "SCOTT"
   GRANT "RESOURCE" TO "SCOTT"

SYS@orcl > SELECT DBMS_METADATA.GET_GRANTED_DDL(
  2   'OBJECT_GRANT','SCOTT') from dual;

DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT','SCOTT')
--------------------------------------------------------------------------------
  GRANT SELECT ON "HR"."EMPLOYEES" TO "SCOTT"

SYS@orcl > SELECT DBMS_METADATA.GET_GRANTED_DDL(
  2  'SYSTEM_GRANT','SCOTT') from dual;

DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT','SCOTT')
--------------------------------------------------------------------------------
  GRANT UNLIMITED TABLESPACE TO "SCOTT"

SYS@orcl >  SELECT dbms_metadata.get_ddl('ROLE','RESOURCE') from dual;

DBMS_METADATA.GET_DDL('ROLE','RESOURCE')
--------------------------------------------------------------------------------
   CREATE ROLE "RESOURCE"

SYS@orcl > SELECT DBMS_METADATA.GET_GRANTED_DDL(
  2  'SYSTEM_GRANT','RESOURCE') from dual;

DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT','RESOURCE')
--------------------------------------------------------------------------------
  GRANT CREATE INDEXTYPE TO "RESOURCE"
  GRANT CREATE OPERATOR TO "RESOURCE"
  GRANT CREATE TYPE TO "RESOURCE"
  GRANT CREATE TRIGGER TO "RESOURCE"
  GRANT CREATE PROCEDURE TO "RESOURCE"
  GRANT CREATE SEQUENCE TO "RESOURCE"
  GRANT CREATE CLUSTER TO "RESOURCE"

HTH

Enrique

Hi again. I need it for all roles i did the folowing:

spool on
set heading off

exec dbms_metadata.set_transform_param(dbms_metadata.session_transform, 'SQLTERMINATOR', true);
spool my_created_roles1.sql

SELECT DBMS_METADATA.GET_DDL('ROLE', role)||';' FROM dba_roles;
spool my_created_roles2.sql

SELECT DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT', role) FROM role_role_privs;
spool my_created_roles3.sql

SELECT DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT', role) FROM ROLE_SYS_PRIVS;
spool my_created_roles4.sql

SELECT DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT', role) FROM ROLE_TAB_PRIVS;
spool off






but i have the folowing output in file3:



GRANT SELECT ON "OUTLN"."OL$" TO "SELECT_CATALOG_ROLE";

GRANT SELECT ON "

GRANT SELECT ON "OUTLN"."OL$" TO "SELECT_CATALOG_ROLE";

GRANT SELECT ON "



what am i doing wrong?

Hi,

Try with dba_users but not with "role_role_privs" and further try to use the "exists " operator, I mean the subquery to check for User Roles.

For Demo, Check the
http://www.oraclealchemist.com/news/trick-1-copying-users-the-right-way/
http://www.dbalifeline.com/2008/02/25/oracle-scripts-to-generate-ddl-for-roles-and-users/

- Pavan Kumar N

Perhaps it's a problem with "long":

...

   GRANT "SELECT_CATALOG_ROLE" TO "DBA" WITH ADMIN OPTION

   GRANT "EXECUTE_C


22 rows selected.

SYS@orcl > show long
long 80
SYS@orcl > set long 4000
SYS@orcl > /
. . .
   GRANT "SELECT_CATALOG_ROLE" TO "DBA" WITH ADMIN OPTION

   GRANT "EXECUTE_CATALOG_ROLE" TO "IMP_FULL_DATABASE"{code}

Enrique                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                

Hi,

Reverting back my question..

- Pavan Kumar N

Edited by: Pavan Kumar on Nov 11, 2008 6:20 PM

Junior DBA, did you get past the issues you ran into or do you have more questions?

HTH -- Mark D Powell --