Re: How to transfer roles between instances.

From: Yann Chevriaux <chevriaux_at_theleme.com>
Date: 2000/06/29
Message-ID: <8jfio6$34o$1@wanadoo.fr>#1/1

I guess you want to retrofit role declaration and privileges to a SQL script.

Well, I'm just doing that by now ....

The function exportRole retrofit one or more roles to SQL scripts:

It suppose you declare in the initialisation file: UTL_FILE_DIR = <root_dir>
UTL_FILE_DIR = <root_dir>\Roles
(contact your DBA)

Of course you can adapt the funtion to your needs ... It has been customed for mine ;)

function exportRole(

    motif_nom in varchar2,
    root_dir in varchar2)
  return integer
  is
    cursor c_rol( v_motif varchar2) is

      select role, password_required
      from dba_roles
      where role like v_motif;
    cursor c_r2r (v_role varchar2) is
      select  GRANTEE, GRANTED_ROLE, ADMIN_OPTION, DEFAULT_ROLE
        from dba_role_privs
        where grantee = v_role;

    cursor c_prv (v_role varchar2) is
      select GRANTEE, OWNER, TABLE_NAME, GRANTOR, PRIVILEGE, GRANTABLE
        from dba_tab_privs
        where grantee=v_role;

    fichier_motif utl_file.file_type;
    fichier utl_file.file_type;
  begin

    fichier_motif := utl_file.fopen(root_dir, 'ROLES_'||motif_nom||'.sql','w');

    for vr in c_rol(motif_nom) loop
      dbms_output.put_line('Script du role '||vr.role);

      fichier := utl_file.fopen(root_dir||'\Roles', vr.role||'.sql', 'w');
      utl_file.put_line(fichier_motif, 'start Roles\'||vr.role||'.sql');
      utl_file.new_line(fichier_motif);

      utl_file.put_line(fichier, 'Create Role '||vr.role);
      utl_file.put_line(fichier,'/');
      utl_file.new_line(fichier);

      -- Les roles alloués à ce role
      for v2 in c_r2r(vr.role) loop
        utl_file.put_line(fichier, 'Grant '||v2.granted_role||' to


'||v2.grantee);

        utl_file.put_line(fichier,'/');
        utl_file.new_line(fichier);
      end loop;

      -- Les privileges
      for vp in c_prv(vr.role) loop
        utl_file.put_line(fichier, 'Grant '||vp.privilege||' on


'||vp.owner||'.'||vp.table_name||' to '||vp.grantee);

        utl_file.put_line(fichier,'/');
        utl_file.new_line(fichier);
      end loop;

      utl_file.fclose(fichier);

    end loop;

    utl_file.fclose(fichier_motif);

    return 0;

end exportRole;

Good luck. Received on Thu Jun 29 2000 - 00:00:00 CDT