Setting up Network ACLs in Oracle 11g… For Dummies

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.