Forum Stats

  • 3,769,698 Users
  • 2,253,010 Discussions
  • 7,875,155 Comments

Discussions

how to get ddl of users and roles?

654808
654808 Member Posts: 169
edited Nov 11, 2008 9:40AM in General Database Discussions
Subj. How to do it manualy with out toad? like i did with tables using dbms_metadata.get_ddl ?

Answers

  • 181444
    181444 Member Posts: 4,022
    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 --
  • Enrique Orbegozo
    Enrique Orbegozo Member Posts: 594 Silver Badge
    Use dbms_metadata, read the documentation: http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_metada.htm#i1015856
    [email protected] > 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"
    
    [email protected] > 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"
    
    [email protected] > 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"
    
    [email protected] > 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"
    
    [email protected] >  SELECT dbms_metadata.get_ddl('ROLE','RESOURCE') from dual;
    
    DBMS_METADATA.GET_DDL('ROLE','RESOURCE')
    --------------------------------------------------------------------------------
       CREATE ROLE "RESOURCE"
    
    [email protected] > 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
  • 654808
    654808 Member Posts: 169
    edited Nov 11, 2008 5:21AM
    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?
  • Pavan Kumar
    Pavan Kumar Member Posts: 11,904 Gold Crown
    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
  • Enrique Orbegozo
    Enrique Orbegozo Member Posts: 594 Silver Badge
    Perhaps it's a problem with "long":
    ...
    
       GRANT "SELECT_CATALOG_ROLE" TO "DBA" WITH ADMIN OPTION
    
       GRANT "EXECUTE_C
    
    
    22 rows selected.
    
    [email protected] > show long
    long 80
    [email protected] > set long 4000
    [email protected] > /
    . . .
       GRANT "SELECT_CATALOG_ROLE" TO "DBA" WITH ADMIN OPTION
    
       GRANT "EXECUTE_CATALOG_ROLE" TO "IMP_FULL_DATABASE"{code}
    
    Enrique                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
  • Pavan Kumar
    Pavan Kumar Member Posts: 11,904 Gold Crown
    edited Nov 11, 2008 7:50AM
    Hi,

    Reverting back my question..

    - Pavan Kumar N

    Edited by: Pavan Kumar on Nov 11, 2008 6:20 PM
  • 181444
    181444 Member Posts: 4,022
    Junior DBA, did you get past the issues you ran into or do you have more questions?

    HTH -- Mark D Powell --
This discussion has been closed.