Wednesday, 30 May 2012

Oracle HTTP Requests: Set up the ACL

In order to use utl_http requests from Oracle 11g onward (or any network services for that matter), you need to set up an ACL. Two generic permissions to grant are connect and resolve, where * can be used as a wild card.

A good template I commonly use, just replacing the file name and schema name. This should be executed as a user with dba privileges.



DECLARE

 l_filename varchar2(200) := 'file_test_http.xml';
 l_schema varchar2(200) := 'FILE_TEST';

BEGIN

        BEGIN
            DBMS_NETWORK_ACL_ADMIN.DROP_ACL(
                acl => l_filename
            );

  EXCEPTION WHEN OTHERS THEN
      NULL; -- ACL does not exist yet
 END;

        DBMS_NETWORK_ACL_ADMIN.CREATE_ACL(
              acl           => l_filename
            , description   => 'All requests to test utl_http'
            , principal     => l_schema -- schema name
            , is_grant      => TRUE
            , privilege     => 'connect'
        );

        DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(
              acl       => l_filename
            , principal => l_schema -- schema name
            , is_grant  => TRUE
            , privilege => 'resolve'
        );

        DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL(
              acl   =>l_filename
            , host  => '*'
        );
END;
/
commit;

No comments:

Post a Comment