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.

Kommentare:

Anonym hat gesagt…

Hi !
die fehlende "description" findest du in der View sys.XDS_ACL.


Hier mal meine SQL-Variante ohne Function:


COL x NOPRINT
COL y NOPRINT

SELECT acl x,
1 y,
'BEGIN'
|| CHR (10)
|| 'DBMS_NETWORK_ACL_ADMIN.create_acl ('
|| CHR (10)
|| ' acl => '''
|| SUBSTR (acl, (INSTR (acl, '/', -1) + 1))
|| ''','
|| CHR (10)
|| ' description => '''
|| b.description
|| ''','
|| CHR (10)
|| ' principal => '''
|| principal
|| ''','
|| CHR (10)
|| ' is_grant => '
|| is_grant
|| ','
|| CHR (10)
|| ' privilege => '''
|| privilege
|| ''','
|| CHR (10)
|| ' start_date => '
|| 'NULL'
|| ','
|| CHR (10)
|| ' end_date => '
|| 'NULL'
|| ');'
|| CHR (10)
acl
FROM dba_network_acl_privileges a, xds_acl b
WHERE a.aclid = b.aclid AND privilege = 'connect'
UNION ALL
SELECT acl,
2,
'DBMS_NETWORK_ACL_ADMIN.add_privilege ('
|| CHR (10)
|| ' acl => '''
|| SUBSTR (acl, (INSTR (acl, '/', -1) + 1))
|| ''','
|| CHR (10)
|| ' principal => '''
|| principal
|| ''','
|| CHR (10)
|| ' is_grant => '
|| is_grant
|| ','
|| CHR (10)
|| ' privilege => '''
|| privilege
|| ''');'
|| CHR (10)
|| 'END;'
|| CHR (10)
|| '/'
acl
FROM dba_network_acl_privileges
WHERE privilege != 'connect'
UNION ALL
SELECT acl,
3,
'BEGIN'
|| CHR (10)
|| 'DBMS_NETWORK_ACL_ADMIN.assign_acl('
|| CHR (10)
|| ' acl => '
|| ''''
|| SUBSTR (a.acl, (INSTR (a.acl, '/', -1) + 1))
|| ''','
|| CHR (10)
|| ' HOST => '''
|| a.HOST
|| ''','
|| CHR (10)
|| ' lower_port => '
|| lower_port
|| ','
|| CHR (10)
|| ' upper_port => '
|| upper_port
|| ');'
|| CHR (10)
|| 'END;'
|| CHR (10)
|| '/'
acl
FROM dba_network_acls a
ORDER BY 1, 2;


Grüße t.h.

Carsten Czarski hat gesagt…

Hallo zusammen,

kurz nach der Veröffentlichung bekam ich noch weitere Hinweise - das ist das schöne an der Community:

* Dietmar fand heraus, dass nach dem Löschen eines DB-Users ein ACL-"Torso" zurückbleibt - die View DBA_NETWORK_ACLs behält den Eintrag, DBA_NETWORK_ACL_PRIVILEGES hat keinen mehr. Damit muss die Funktion umgehen können.

* "T.H." hat mich auf die fehlende Description hingewiesen; die Funktion exportiert diese nun ebenfalls.

* Und schließlich habe ich noch einen Parameter hinzugefügt, um nur die ACLs für einen User oder eine Rolle zu exportieren.

Der Code im Posting ist aktualisiert.

Viel Spaß damit

-Carsten

Sokrates hat gesagt…

Carsten,

thanks for that, useful stuff, I missed that !

However, I received

sokrates@11.2 > select export_acls from dual;
ERROR:
ORA-24247: network access denied by access control list (ACL)
ORA-06512: at "SYS.UTL_INADDR", line 4
ORA-06512: at "SYS.UTL_INADDR", line 35
ORA-06512: at "SOKRATES.EXPORT_ACLS", line 100
ORA-06512: at line 1



no rows selected


So, I commented lines 100 and 101

Thanks
Matthias

Anonym hat gesagt…

Danke fur den code.
Ich hatte das heute gebraucht ich habe den sql query genutzt.
Um XDB errors zu vorkommen habe ich exec commands gebraucht.

set pages 0
set head off feedback off
set lines 400
spool exported_acls_&_CONNECT_IDENTIFIER..out
COL x NOPRINT
COL y NOPRINT

SELECT acl x,
1 y,
'EXEC DBMS_NETWORK_ACL_ADMIN.create_acl ('
|| ' acl => '''
|| SUBSTR (acl, (INSTR (acl, '/', -1) + 1))
|| ''','
|| ' description => '''
|| b.description
|| ''','
|| ' principal => '''
|| principal
|| ''','
|| ' is_grant => '
|| is_grant
|| ','
|| ' privilege => '''
|| privilege
|| ''','
|| ' start_date => '
|| 'NULL'
|| ','
|| ' end_date => '
|| 'NULL'
|| ');'
acl
FROM dba_network_acl_privileges a, xds_acl b
WHERE a.aclid = b.aclid AND privilege = 'connect'
UNION ALL
SELECT acl,
2,
'EXEC DBMS_NETWORK_ACL_ADMIN.add_privilege ('
|| ' acl => '''
|| SUBSTR (acl, (INSTR (acl, '/', -1) + 1))
|| ''','
|| ' principal => '''
|| principal
|| ''','
|| ' is_grant => '
|| is_grant
|| ','
|| ' privilege => '''
|| privilege
|| ''');'
acl
FROM dba_network_acl_privileges
-- WHERE privilege != 'connect'
UNION ALL
SELECT acl,
3,
'EXEC DBMS_NETWORK_ACL_ADMIN.assign_acl('
|| ' acl => '
|| ''''
|| SUBSTR (a.acl, (INSTR (a.acl, '/', -1) + 1))
|| ''','
|| ' HOST => '''
|| a.HOST
|| ''''
|| case nvl(lower_port,9999999999)
WHEN 9999999999 THEN ''
ELSE
', lower_port => ' || lower_port || ',' || ' upper_port => ' || upper_port
END
|| ');'
acl
FROM dba_network_acls a
ORDER BY 1, 2;
spool off
prompt output in exported_acls_&_CONNECT_IDENTIFIER..out

Grusse aus Holland.
Dik Pater.

Anonym hat gesagt…

jetzt brauchen wir noch den Port auf 12c, vgl. https://blogs.oracle.com/UPGRADE/entry/network_acls_and_database_upgrade

Beliebte Postings