Setting up Network ACLs in Oracle 11g… For Dummies
Jul 24, 2009 / By Don Seiler
Having recently performed a test upgrade for a client from Oracle RDBMS 10g to 11g, I can tell you that one of the big changes that will likely require action on your part as DBA is the new fine-grained access control for the packages UTL_SMTP, UTL_TCP, UTL_MAIL, UTL_HTTP and UTL_INADDR. Part of the Oracle 11g pre-upgrade tool will notify you of users that will require new privileges.
Of course, Oracle’s post-upgrade network ACL setup documentation is much more confusing than it needs to be, at least for simple minds like me. A colleague stepped forward with a simple set of commands for a basic setup that even the tired and stressed can understand.
I’ll share that here, with some basic explanation:
BEGIN
-- Create the new ACL, naming it "netacl.xml", with a description.
-- Also, provide one starter privilege, granting user FOO
-- the privilege to connect.
DBMS_NETWORK_ACL_ADMIN.CREATE_ACL('netacl.xml',
'Allow usage to the UTL network packages', 'FOO', TRUE, 'connect');
-- Now grant privilege to resolve DNS names for FOO,
-- and then grant connect and resolve to user BAR
DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE('netacl.xml' ,'FOO', TRUE, 'resolve');
DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE('netacl.xml' ,'BAR', TRUE, 'connect');
DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE('netacl.xml' ,'BAR', TRUE, 'resolve');
-- Specify which hosts this ACL applies to,
-- for simplicity, we're saying all (*)
-- You might want to specify certain hosts to lock this down.
DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL('netacl.xml','*');
END;
/
As you can see, this example will let the FOO and BAR database users connect and resolve to any host. The ASSIGN_ACL section in the full package documentation (see link below) details how this can be used to lock down a user’s ability to make outside connections.
Of course, nothing beats reading the Oracle 11g DBMS_NETWORK_ACL_ADMIN documentation, where you can see some examples of stricter ACL setups.
Pingback: Blogroll Report 24/07/2009 – 31/07/2009 « Coskan’s Approach to Oracle
Pingback: Setting up Network ACLs in Oracle 11g… For Dummies « die Seilerwerks
Thanks! That’s just what I needed. Simple and straight forward. Not too much brain power expended.
:)
What i’m i doing wrong??
DB version is 11.2.0.1.0
Creation code (as dba-user):
begin
— Create the new ACL, naming it “netacl.xml”, with a description.
— Also, provide one starter privilege, granting user ACLTEST
— the privilege to connect.
DBMS_NETWORK_ACL_ADMIN.CREATE_ACL(‘netacl.xml’,
‘Allow usage to the UTL network packages’, ‘ACLTEST’, TRUE, ‘connect’);
— Now grant privilege to resolve DNS names for ACLTEST,
DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(‘netacl.xml’ ,’ACLTEST’, TRUE, ‘resolve’);
— Specify which hosts this ACL applies to,
— for simplicity, we’re saying all (*)
DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL(‘netacl.xml’,\'*’);
commit;
end;
Excecution code (as acltest):
declare
l_conn UTL_TCP.connection;
v_file ftp.TStringTable;
l_list ftp.t_string_table;
begin
l_conn := ftp.Logind(‘DOMAIN’, 21, ‘USERNAME’, ‘PASSWORD’);
FTP.LIST(l_conn,’\',l_list);
ftp.logout(l_conn);
end;
Error stack
ORA-24247: network access denied by access control list (ACL)
ORA-06512: at “SYS.UTL_TCP”, line 17
ORA-06512: at “SYS.UTL_TCP”, line 246
ORA-06512: at “COMMON.FTP”, line 784
ORA-06512: at line 7
Thanks man, you are a life saver.
I’ve been wondering for a while to resolve the PDF printing issue in Apex. After executing your script it worked fine.
I just want to add a note that it is better to add commit statement at the end.
@Mohammad, interesting, I’ll see if the COMMIT is necessary.
@John, did you use a / at the end of the begin/end block to execute the PL/SQL? Either way, use the DBMS_NETWORK_ACL_ADMIN.CHECK_PRIVILEGE procedure to verify:
http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28419/d_networkacl_adm.htm#CHDFJHHJ
Pingback: Scanning for listening ports on other servers with PL/SQL | David Cox's Oracle and Things Blog
When I use ip address, I have need to put de ip address on the /etc/hosts file