Setting up Network ACLs in Oracle 11g… For Dummies

Jul 24, 2009 / By Don Seiler

Tags:

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.

9 Responses to “Setting up Network ACLs in Oracle 11g… For Dummies”

  • […] Seiler – Setting up Network ACLs in Oracle 11g… For Dummies Leave a […]

  • Setting up Network ACLs in Oracle 11g… For Dummies « die Seilerwerks says:

    […] leave a comment » Originally posted on The Pythian Group blog. […]

  • Christoph says:

    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 http://ftp.TStringTable;
    l_list http://ftp.t_string_table;

    begin
    l_conn := http://ftp.Logind(‘DOMAIN’, 21, ‘USERNAME’, ‘PASSWORD’);
    http://FTP.LIST(l_conn,’\’,l_list);
    http://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

  • Mohammad says:

    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.

  • Don Seiler says:

    @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

  • […] figuring out the permissions, a change for 11g, so I got some help at Zahid Karim’s blog or Don Sieler’s post at Pythian. You should read it if you are unfamiliar with the Oracle network connection […]

  • JOSE LUNA says:

    When I use ip address, I have need to put de ip address on the /etc/hosts file

  • Brian says:

    I have a oracle wallet and a ACL created for a use that I have provided connect and resolve permissions. However, the only way I can get the user to connect to the URL needed is to set the user to have the DBA role. Once I do that the utl_http commands work. however then the role is remove I receive a message indicating that the utl_http provided a bad certifcate. any ideas what permission I need to grant my user other than DBA?

Leave a Reply

  • (will not be published)

XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>