7. Januar 2013

PL/SQL Netzwerk-ACLs als SQL-Skript exportieren ...

Exporting PL/SQL Network ACLs as a PL/SQL script
Heute hatte ich eine kurze Mail-Korrespondenz mit Dietmar Aust. Es ging um die Frage, ob man die in einer Datenbank vorhandenen PL/SQL Netzwerk-ACLs, mit denen Netzwerkzugriffe durch eine Datenbanksession feingranular erlaubt oder verboten werden können, als Skript exportieren können. Die Antwort: Out-of-the-box geht es nicht. Man kann eine Netzwerk-ACL mit dem Package DBMS_NETWORK_ACL_ADMIN erzeugen und löschen, man kann sie mit den Dictionary Views DBA_NETWORK_ACLS und DBA_NETWORK_ACL_PRIVILEGES betrachten, aber es gibt nirgendwo eine Funktion zum Export.
Ein Skript zu generieren, ist nicht so schwer - und das könnten ja durchaus auch andere gebrauchen; also entschloß ich mich, eine kleine PL/SQL-Funktion zu schreiben, welche die Aufgabe erledigt. Dietmar war so freundlich, mich auf drei Fehler im Skript hinzuweisen, und nach Korrektur derselben möchte ich das Skript gerne teilen - vielleicht ist sie für den einen oder anderen ebenfalls nützlich.
create or replace function export_acls (
  p_generate_drop   number   default 0,
  p_principal       varchar2 default null
) return clob
 is
  v_sql varchar2(32767) := '';
  v_clob clob;

  procedure append(p_str in varchar2) is
  begin
    v_sql := v_sql || p_str || chr(10);
  end append;

  procedure generate_acl(p_acl in varchar2) is
  begin
    for aclprivs in (
      select 
        rownum zeile,
        replace(ACL, '/sys/acls/','') acl,
        PRINCIPAL,
        PRIVILEGE,
        IS_GRANT,
        INVERT,
        to_char(START_DATE, 'YYYY-MM-DD HH24:MI:SS.FFFFFF6 TZH:TZM') start_date, 
        to_char(END_DATE, 'YYYY-MM-DD HH24:MI:SS.FFFFFF6 TZH:TZM')   end_date,
        extractvalue(xdburitype(p_acl).getxml(), '/acl/@description') descr
      from dba_network_acl_privileges
      where acl = p_acl 
    ) loop
      if aclprivs.zeile = 1 then 
        if p_generate_drop = 1 then 
          append('begin');
          append('  sys.dbms_network_acl_admin.drop_acl(');
          append('    acl         => '''||aclprivs.acl||'''');
          append('  );');
          append('end;');
          append('/');
          append('sho err');
          append('');
        end if;
        append('begin');
        append('  sys.dbms_network_acl_admin.create_acl(');
        append('    acl         => '''||aclprivs.acl||'''');
        append('   ,description => '''||aclprivs.descr||'''');
        append('   ,principal   => '''||aclprivs.principal||'''');
        append('   ,is_grant    => '||aclprivs.is_grant||'');
        append('   ,privilege   => '''||aclprivs.privilege||'''');
        if aclprivs.start_date is not null then
          append('   ,start_date  => to_timestamp_tz('''||aclprivs.start_date||''',''YYYY-MM-DD HH24:MI:SS.FFFFFF6 TZH:TZM'')'); 
        end if;
        if aclprivs.end_date is not null then
          append('   ,end_date    => to_timestamp_tz('''||aclprivs.end_date||''',''YYYY-MM-DD HH24:MI:SS.FFFFFF6 TZH:TZM'')'); 
        end if;
        append('  );');
      else 
        append('  sys.dbms_network_acl_admin.add_privilege(');
        append('    acl         => '''||aclprivs.acl||'''');
        append('   ,principal   => '''||aclprivs.principal||'''');
        append('   ,is_grant    => '||aclprivs.is_grant||'');
        append('   ,privilege   => '''||aclprivs.privilege||'''');
        append('   ,position    => null');
        if aclprivs.start_date is not null then
          append('   ,start_date  => to_timestamp_tz('''||aclprivs.start_date||''',''YYYY-MM-DD HH24:MI:SS.FFFFFF6 TZH:TZM'')'); 
        end if;
        if aclprivs.end_date is not null then
          append('   ,end_date    => to_timestamp_tz('''||aclprivs.end_date||''',''YYYY-MM-DD HH24:MI:SS.FFFFFF6 TZH:TZM'')'); 
        end if;
        append('  );');
      end if;
    end loop;
    for aclhost in (
       select replace(acl, '/sys/acls/','') acl, host, to_char(lower_port) lower_port, to_char(upper_port) upper_port
       from dba_network_acls where acl = p_acl
    ) loop
      append('  sys.dbms_network_acl_admin.assign_acl(');
      append('    acl         => '''||aclhost.acl||'''');
      append('   ,host        => '''||aclhost.host||'''');
      append('   ,lower_port  => '||nvl(aclhost.lower_port, 'null')||'');
      append('   ,upper_port  => '||nvl(aclhost.upper_port, 'null'));
      append('  );');
    end loop;
    append('end;');
    append('/');
    append('sho err');
    append('');
  end generate_acl;
begin
  dbms_lob.createtemporary(v_clob, true, dbms_lob.call);
  v_sql := '';
  append('/********************************************************************');
  append(' * PL/SQL Network ACL');
  append(' *'); 
  append(' * exported as of '||to_char(sysdate, 'YYYY-MM-DD HH24:MI'));
  append(' *'); 
  append(' * Database hostname: '||utl_inaddr.get_host_name);
  append(' * Database host IP:  '||utl_inaddr.get_host_address);
  append(' * Database name      '||sys_context('userenv','DB_NAME'));
  append(' * Database domain    '||sys_context('userenv','DB_DOMAIN'));
  append(' ********************************************************************/');
  append('');
  dbms_lob.writeappend(v_clob, length(v_sql), v_sql);
  for aclhost in (
    select distinct a.acl
    from dba_network_acls a, dba_network_acl_privileges p
    where a.acl = p.acl and (p_principal is null or principal = upper(p_principal))
  ) loop
    v_sql := '';
    generate_acl(aclhost.acl);
    if v_sql is not null then 
      dbms_lob.writeappend(v_clob, length(v_sql), v_sql);
    end if;
  end loop;
  return v_clob;
end;
/
sho err
Die Funktion ist einfach zu nutzen und nimmt nur einen Parameter entgegen. Wird eine "1" übergeben, so werden auch DROP ACL-Anweisungen generiert, ansonsten nicht.
SQL> set long 20000
SQL> select export_acls from dual;

EXPORT_ACLS
--------------------------------------------------------------------------------
/********************************************************************
 * PL/SQL Network ACL
 *
 * exported as of 2013-01-07 16:47
 *
 * Database hostname: myhost.mydomain.com
 * Database host IP:  10.10.10.10
 * Database name      orcl
 * Database domain
 ********************************************************************/

begin
  sys.dbms_network_acl_admin.create_acl(
    acl         => 'all-network-PUBLIC.xml'
   ,description => null
   ,principal   => 'PUBLIC'
   ,is_grant    => true
   ,privilege   => 'connect'
  );
  sys.dbms_network_acl_admin.add_privilege(
    acl         => 'all-network-PUBLIC.xml'
   ,principal   => 'PUBLIC'
   ,is_grant    => true
   ,privilege   => 'resolve'
   ,position    => null
  );
  sys.dbms_network_acl_admin.assign_acl(
    acl         => 'all-network-PUBLIC.xml'
   ,host        => '*'
   ,lower_port  => null
   ,upper_port  => null
  );
end;
/
sho err
Viel Spaß damit.
Today, I had a short mail exchange with Dietmar Aust. It was about the question, whether a databases' PL/SQL Network ACLs, which allow to control network access from a database session in a fine-grained manner, could be easily exported as a script. The first an short answer is "no": We can create and delete network ACLs with the package DBMS_NETWORK_ACL_ADMIN, we can look into the dictionary views DBA_NETWORK_ACLS and DBA_NETWORK_ACL_PRIVILEGES, but there is no funktionality to export and import them.
But it's not so difficult to generate a SQL script - so I created a funktion which does the job. Dietmar was so kind to look into it and he found three mistakes - and after correcting them I'd like to share the script - perhaps someone will find it useful.
create or replace function export_acls (
  p_generate_drop   number   default 0,
  p_principal       varchar2 default null
) return clob
 is
  v_sql varchar2(32767) := '';
  v_clob clob;

  procedure append(p_str in varchar2) is
  begin
    v_sql := v_sql || p_str || chr(10);
  end append;

  procedure generate_acl(p_acl in varchar2) is
  begin
    for aclprivs in (
      select 
        rownum zeile,
        replace(ACL, '/sys/acls/','') acl,
        PRINCIPAL,
        PRIVILEGE,
        IS_GRANT,
        INVERT,
        to_char(START_DATE, 'YYYY-MM-DD HH24:MI:SS.FFFFFF6 TZH:TZM') start_date, 
        to_char(END_DATE, 'YYYY-MM-DD HH24:MI:SS.FFFFFF6 TZH:TZM')   end_date,
        extractvalue(xdburitype(p_acl).getxml(), '/acl/@description') descr
      from dba_network_acl_privileges
      where acl = p_acl 
    ) loop
      if aclprivs.zeile = 1 then 
        if p_generate_drop = 1 then 
          append('begin');
          append('  sys.dbms_network_acl_admin.drop_acl(');
          append('    acl         => '''||aclprivs.acl||'''');
          append('  );');
          append('end;');
          append('/');
          append('sho err');
          append('');
        end if;
        append('begin');
        append('  sys.dbms_network_acl_admin.create_acl(');
        append('    acl         => '''||aclprivs.acl||'''');
        append('   ,description => '''||aclprivs.descr||'''');
        append('   ,principal   => '''||aclprivs.principal||'''');
        append('   ,is_grant    => '||aclprivs.is_grant||'');
        append('   ,privilege   => '''||aclprivs.privilege||'''');
        if aclprivs.start_date is not null then
          append('   ,start_date  => to_timestamp_tz('''||aclprivs.start_date||''',''YYYY-MM-DD HH24:MI:SS.FFFFFF6 TZH:TZM'')'); 
        end if;
        if aclprivs.end_date is not null then
          append('   ,end_date    => to_timestamp_tz('''||aclprivs.end_date||''',''YYYY-MM-DD HH24:MI:SS.FFFFFF6 TZH:TZM'')'); 
        end if;
        append('  );');
      else 
        append('  sys.dbms_network_acl_admin.add_privilege(');
        append('    acl         => '''||aclprivs.acl||'''');
        append('   ,principal   => '''||aclprivs.principal||'''');
        append('   ,is_grant    => '||aclprivs.is_grant||'');
        append('   ,privilege   => '''||aclprivs.privilege||'''');
        append('   ,position    => null');
        if aclprivs.start_date is not null then
          append('   ,start_date  => to_timestamp_tz('''||aclprivs.start_date||''',''YYYY-MM-DD HH24:MI:SS.FFFFFF6 TZH:TZM'')'); 
        end if;
        if aclprivs.end_date is not null then
          append('   ,end_date    => to_timestamp_tz('''||aclprivs.end_date||''',''YYYY-MM-DD HH24:MI:SS.FFFFFF6 TZH:TZM'')'); 
        end if;
        append('  );');
      end if;
    end loop;
    for aclhost in (
       select replace(acl, '/sys/acls/','') acl, host, to_char(lower_port) lower_port, to_char(upper_port) upper_port
       from dba_network_acls where acl = p_acl
    ) loop
      append('  sys.dbms_network_acl_admin.assign_acl(');
      append('    acl         => '''||aclhost.acl||'''');
      append('   ,host        => '''||aclhost.host||'''');
      append('   ,lower_port  => '||nvl(aclhost.lower_port, 'null')||'');
      append('   ,upper_port  => '||nvl(aclhost.upper_port, 'null'));
      append('  );');
    end loop;
    append('end;');
    append('/');
    append('sho err');
    append('');
  end generate_acl;
begin
  dbms_lob.createtemporary(v_clob, true, dbms_lob.call);
  v_sql := '';
  append('/********************************************************************');
  append(' * PL/SQL Network ACL');
  append(' *'); 
  append(' * exported as of '||to_char(sysdate, 'YYYY-MM-DD HH24:MI'));
  append(' *'); 
  append(' * Database hostname: '||utl_inaddr.get_host_name);
  append(' * Database host IP:  '||utl_inaddr.get_host_address);
  append(' * Database name      '||sys_context('userenv','DB_NAME'));
  append(' * Database domain    '||sys_context('userenv','DB_DOMAIN'));
  append(' ********************************************************************/');
  append('');
  dbms_lob.writeappend(v_clob, length(v_sql), v_sql);
  for aclhost in (
    select distinct a.acl
    from dba_network_acls a, dba_network_acl_privileges p
    where a.acl = p.acl and (p_principal is null or principal = upper(p_principal))
  ) loop
    v_sql := '';
    generate_acl(aclhost.acl);
    if v_sql is not null then 
      dbms_lob.writeappend(v_clob, length(v_sql), v_sql);
    end if;
  end loop;
  return v_clob;
end;
/
sho err
The function is very easy to use and takes only one parameter. If a "1" is provided, the resulting script will also contain statements to drop the ACLs before creating them.
SQL> set long 20000
SQL> select export_acls from dual;

EXPORT_ACLS
--------------------------------------------------------------------------------
/********************************************************************
 * PL/SQL Network ACL
 *
 * exported as of 2013-01-07 16:47
 *
 * Database hostname: myhost.mydomain.com
 * Database host IP:  10.10.10.10
 * Database name      orcl
 * Database domain
 ********************************************************************/

begin
  sys.dbms_network_acl_admin.create_acl(
    acl         => 'all-network-PUBLIC.xml'
   ,description => null
   ,principal   => 'PUBLIC'
   ,is_grant    => true
   ,privilege   => 'connect'
  );
  sys.dbms_network_acl_admin.add_privilege(
    acl         => 'all-network-PUBLIC.xml'
   ,principal   => 'PUBLIC'
   ,is_grant    => true
   ,privilege   => 'resolve'
   ,position    => null
  );
  sys.dbms_network_acl_admin.assign_acl(
    acl         => 'all-network-PUBLIC.xml'
   ,host        => '*'
   ,lower_port  => null
   ,upper_port  => null
  );
end;
/
sho err
Have fun.

Beliebte Postings