BEGIN
-------------------------------------------------------------
-------------------------------------------------------------
-- Script to create an ACL to mimic pre-11g open access
-------------------------------------------------------------
-------------------------------------------------------------
$IF DBMS_DB_VERSION.ver_le_10
$THEN
NULL; -- If you happen to run this on 10g it will do nothing
$ELSE
-- If we've already created the acl, remove it to create
-- a clean slate
BEGIN
DBMS_NETWORK_ACL_ADMIN.drop_acl('Mimic_Pre11g_ACL.xml');
EXCEPTION
WHEN DBMS_NETWORK_ACL_ADMIN.acl_not_found
THEN
NULL; -- It didn't exist, that's ok
END;
-- Create the ACL and grant it to PUBLIC, allowing everyone to CONNECT to it.
DBMS_NETWORK_ACL_ADMIN.create_acl(
acl => 'Mimic_Pre11g_ACL.xml',
description => 'ACL that lets everyone connect to all ports of all servers',
principal => 'PUBLIC',
is_grant => TRUE,
privilege => 'connect'
);
-- Also add the RESOLVE permission for everybody.
-- This will allow look up of all hostnames and ip addresses.
DBMS_NETWORK_ACL_ADMIN.add_privilege(
acl => 'Mimic_Pre11g_ACL.xml',
principal => 'PUBLIC',
is_grant => TRUE,
privilege => 'resolve'
);
--Assign these privileges to all hosts and all ports
DBMS_NETWORK_ACL_ADMIN.assign_acl('Mimic_Pre11g_ACL.xml', '*');
COMMIT;
$END
END;
BEGIN
--DBMS_NETWORK_ACL_ADMIN.drop_acl('my_ip_lookup_acl.xml');
DBMS_NETWORK_ACL_ADMIN.create_acl(
acl => 'my_ip_lookup_acl.xml',
description => 'ACL that lets users do ip/name look ups for any host',
principal => 'SDS',
is_grant => TRUE,
privilege => 'resolve'
);
DBMS_NETWORK_ACL_ADMIN.assign_acl('my_ip_lookup_acl.xml', '*');
COMMIT;
END;
BEGIN
DBMS_NETWORK_ACL_ADMIN.add_privilege(
acl => 'my_ip_lookup_acl.xml',
principal => 'LOOKUP_USER_1',
is_grant => TRUE,
privilege => 'resolve'
);
DBMS_NETWORK_ACL_ADMIN.add_privilege(
acl => 'my_ip_lookup_acl.xml',
principal => 'LOOKUP_USER_2',
is_grant => TRUE,
privilege => 'resolve'
);
COMMIT;
END;
BEGIN
DBMS_NETWORK_ACL_ADMIN.create_acl(
acl => 'my_smtp_server_acl.xml',
description => 'ACL that lets me talk to the my email server',
principal => 'SDS',
is_grant => TRUE,
privilege => 'connect'
);
DBMS_NETWORK_ACL_ADMIN.assign_acl(
acl => 'my_smtp_server_acl.xml',
HOST => 'my_smtp_server.com',
lower_port => 25,
upper_port => 25
);
COMMIT;
END;
BEGIN
DBMS_NETWORK_ACL_ADMIN.add_privilege(
acl => 'my_smtp_server_acl.xml',
principal => 'SDS',
is_grant => TRUE,
privilege => 'connect',
start_date => timestamp '2011-11-01 00:00:00.00 US/Eastern',
end_date => timestamp '2011-11-01 23:59:59.999999 US/Eastern'
);
COMMIT;
END;
BEGIN
DBMS_NETWORK_ACL_ADMIN.delete_privilege(
acl => 'my_ip_lookup_acl.xml',
principal => 'LOOKUP_USER_1',
is_grant => TRUE,
privilege => 'resolve'
);
COMMIT;
END;
BEGIN
DBMS_NETWORK_ACL_ADMIN.unassign_acl(
acl => 'my_smtp_server_acl.xml',
HOST => 'my_smtp_server.com',
lower_port => 25,
upper_port => 25
);
COMMIT;
END;
BEGIN
DBMS_NETWORK_ACL_ADMIN.add_privilege(
acl => 'my_ip_lookup_acl.xml',
principal => 'BLOCKED_USER',
is_grant => FALSE,
privilege => 'resolve'
);
COMMIT;
END;
SELECT * FROM dba_network_acls;
HOST LOWER_PORT UPPER_PORT ACL ACLID
------------------------------------------------------------------------------------------------------
* /sys/acls/superusers.xml 1DA763004CEE455C959BD7966D901F9C
my_smtp_server.com 25 25 /sys/acls/my_smtp_server_acl.xml 65002399A99242AC829C612F769B7705
SELECT * FROM dba_network_acl_privileges;
ACL ACLID PRINCIPAL PRIVILEGE IS_GRANT INVERT START_DATE END_DATE
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
/sys/acls/my_smtp_server_acl.xml 65002399A99242AC829C612F769B7705 SDS resolve true false
/sys/acls/my_smtp_server_acl.xml 65002399A99242AC829C612F769B7705 SDS connect true false 2011/10/28 00:00:00.000000000 -04:00 2011/10/28 23:59:59.999999000 -04:00
/sys/acls/superusers.xml 1DA763004CEE455C959BD7966D901F9C SDS connect true false
/sys/acls/superusers.xml 1DA763004CEE455C959BD7966D901F9C SDS resolve true false
SELECT null
FROM xdb.xs$securityclass
WHERE EXTRACTVALUE(object_value, '/securityClass/@targetNamespace') = 'http://xmlns.oracle.com/plsql'
AND EXTRACTVALUE(object_value, '/securityClass/@name') = 'network';
Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.
Comments (2)
Commented:
This is good information. I hope can get the dba's help on this.
If I cannot, isn't there another email method that I can use that doesn't require SMTP?
How can I restructure the procedure and include the query data in an email for the other method?
Author
Commented:ACL's block all network traffic, so, even if you used a non smtp server, or moved to another port you would still need to have an ACL opened for you.
Your last comment seems to be about a question and not this article.