10g to 11gr2 upgrade causes SYS.UTL_HTTP malfunction

oracleupgrade

After a 10g to 11gr2 upgrade, we are getting ORA-29273 errors when we try to use the SYS.UTL_HTTP package. Our upgrade list requires ACL to be disabled.

How can we resolve this problem without enabling ACL?

Best Answer

I'm afraid your requirements cannot be met. If you want to use UTL_HTTP functions (and those of other network-related packages), you'll need to define ACLs in 11gR2. There is no alternative.

If you don't care about the security implications, the simplest thing to do would be to create an ACL that allows access to everything, and assign it to all the database users that need to use the functions.

The Fine-Grained Access to Network Services in Oracle Database 11g article has an example of how to do that:

BEGIN
  DBMS_NETWORK_ACL_ADMIN.create_acl (
    acl          => 'open_acl_file.xml', 
    description  => 'Allow access to all hosts',
    principal    => 'TEST',  -- put your user there
    is_grant     => TRUE, 
    privilege    => 'connect',
    start_date   => SYSTIMESTAMP,
    end_date     => NULL);

  DBMS_NETWORK_ACL_ADMIN.assign_acl (
    acl         => 'open_acl_file.xml',
    host        => '*',      -- this allows all hosts
    lower_port  => 1,        -- on ports 1 to 9999
    upper_port  => 9999); 

  COMMIT;
END;
/

If you need to assign that ACL to other users, use:

BEGIN
  DBMS_NETWORK_ACL_ADMIN.add_privilege ( 
    acl         => 'open_acl_file.xml', 
    principal   => 'TEST2',    -- your other user here
    is_grant    => TRUE, 
    privilege   => 'connect', 
    position    => NULL, 
    start_date  => NULL,
    end_date    => NULL);

  COMMIT;
END;
/
Related Question