7 Replies Latest reply: Nov 11, 2008 8:40 AM by 181444 RSS

    how to get ddl of users and roles?

    654808
      Subj. How to do it manualy with out toad? like i did with tables using dbms_metadata.get_ddl ?
        • 1. Re: how to get ddl of users and roles?
          181444
          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 --
          • 2. Re: how to get ddl of users and roles?
            Enrique Orbegozo
            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
            • 3. Re: how to get ddl of users and roles?
              654808
              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?
              • 4. Re: how to get ddl of users and roles?
                Pavan Kumar
                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
                • 5. Re: how to get ddl of users and roles?
                  Enrique Orbegozo
                  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                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
                  • 6. Re: how to get ddl of users and roles?
                    Pavan Kumar
                    Hi,

                    Reverting back my question..

                    - Pavan Kumar N

                    Edited by: Pavan Kumar on Nov 11, 2008 6:20 PM
                    • 7. Re: how to get ddl of users and roles?
                      181444
                      Junior DBA, did you get past the issues you ran into or do you have more questions?

                      HTH -- Mark D Powell --