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.
Kommentare:
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.
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
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
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.
jetzt brauchen wir noch den Port auf 12c, vgl. https://blogs.oracle.com/UPGRADE/entry/network_acls_and_database_upgrade
Kommentar veröffentlichen