Thursday, June 7, 2012

Oracle 11g : Fine Grained Access to Network Services (ACL)


Configuring fine-grained access to network services:

Oracle Database 11g offers you fine-grained access control capability so you can control the access of users to external network services from within the Oracle database

Creating an Access Control List:

An access control list is simply a list of users and their privileges. Oracle allows access to external network services using several PL/SQL APIs (UTL_TCPUTL_SMTPUTL_MAILUTL_HTTP and UTL_INADDR).

The invoker of those packages needs additional privileges to connect to an external host or to resolve the name or the IP address of a host.The packages check the invoker for the necessary privileges only when the calls are made at runtime and raises an exception if the invoker lacks the privileges

This new security measure is implemented by the XML DB access control list (ACL) mechanism and, therefore, requires XML DB to be installed in order to use those packages.

To grant a user the privileges for the host, the database administrator should create an ACL, add the privileges to the ACL for the user, assign the ACL to the host and commit the changes using the DBMS_NETWORK_ACL_ADMIN PL/SQL package.

Before testing the ACL functionality, create two users in the database (USER1,USER2) and grant connect permissions accordingly.


Creating new ACL:


SQL> BEGIN
  2      DBMS_NETWORK_ACL_ADMIN.create_acl (
  3      acl          => 'test.xml',                      -- case sensitive
  4      description  => 'ACL permission functionality',
  5      principal    => 'USER1',                 --user or role the privilege is granted or denied (upper case)
  6      is_grant     => TRUE,                    --privilege is granted or denied
  7      privilege    => 'connect',                -- or 'resolve' (case sensitive)
  8      start_date   => SYSTIMESTAMP, -- when the access control entity ACE will be valid
  9      end_date     => NULL);        -- ACE expiration date (TIMESTAMP WITH TIMEZONE format)
 10
 11    COMMIT;
 12  END;
 13  /

PL/SQL procedure successfully completed.

Once created, the ACL is visible in the "http://host:port/sys/acls/" directory.
You can then query the RESOURCE_VIEW view to find the dba.xml ACL in the /sys/acls directory:

Add users or roles to the newly created ACL:

SQL> BEGIN
  2    DBMS_NETWORK_ACL_ADMIN.add_privilege (
  3      acl         => 'test.xml',
  4      principal   => 'USER2',
  5      is_grant    => FALSE, -- grant is denied to USER2 for testing purposes
  6      privilege   => 'connect',
  7      position    => NULL,
  8      start_date  => NULL,
  9      end_date    => NULL);
 10
 11    COMMIT;
 12  END;
 13  /

PL/SQL procedure successfully completed.

In above script, we have add the the user 'USER2' but have not granted the connect previledge

Removing a Privilege:

If the privilege is to be removed, use the below script

BEGIN
  DBMS_NETWORK_ACL_ADMIN.delete_privilege ( 
    acl         => 'test.xml', 
    principal   => 'USER1',
    is_grant    => FALSE,  -- 
    privilege   => 'connect');

  COMMIT;
END;
/

Droping the ACL

If the ACL needs to be droped, use the below script

BEGIN
  DBMS_NETWORK_ACL_ADMIN.drop_acl (  acl   => 'test.xml');
  COMMIT;
END;
/

Assigning an ACL to a Network:

BEGIN
DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL('test.xml','www.bash-dba.com', 80);
END;
/
PL/SQL procedure successfully completed.

Test the ACL:

conn / as sysdba

GRANT EXECUTE ON UTL_HTTP TO USER1, USER2;

 conn user1/user1

SQL> DECLARE
  2    v_url            VARCHAR2(50) := 'http://www.bash-dba.com:80';
  3    v_http_request   UTL_HTTP.req;
  4    v_http_response  UTL_HTTP.resp;
  5  BEGIN
  6    -- Make a HTTP request and get the response.
  7    v_http_request  := UTL_HTTP.begin_request(v_url);
  8    v_http_response := UTL_HTTP.get_response(v_http_request);
  9    UTL_HTTP.end_response(v_http_response);
 10  END;
 11  /

PL/SQL procedure successfully completed.

select UTL_HTTP.REQUEST('http://www.bash-dba.com') from dual;


CONN user2/user2

Since we set 'is_grant' to FALSE while adding the user in ACL, see the result below after executing the script 

SQL> DECLARE
  2    v_url            VARCHAR2(50) := 'http://www.bash-dba.com:80';
  3    v_http_request   UTL_HTTP.req;
  4    v_http_response  UTL_HTTP.resp;
  5  BEGIN
  6    -- Make a HTTP request and get the response.
  7    v_http_request  := UTL_HTTP.begin_request(v_url);
  8    v_http_response := UTL_HTTP.get_response(v_http_request);
  9    UTL_HTTP.end_response(v_http_response);
 10  END;
 11  /
DECLARE
*
ERROR at line 1:
ORA-29273: HTTP request failed
ORA-06512: at "SYS.UTL_HTTP", line 1130
ORA-24247: network access denied by access control list (ACL)
ORA-06512: at line 7

END;


The current ACL assignment to external hosts and the privileges currently defined in the ACLs are shown through the system catalog views DBA_NETWORK_ACLS and DBA_NETWORK_ACL_PRIVILEGES.

No comments:

Post a Comment