Access Control List(ACL) in Oracle 11G

With ACL’s, Oracle offers more fine-grained access control for users to access external network resources.

The packages UTL_MAIL, UTL_SMTP, UTL_HTTP, UTL_TCP etc. allow communication beyond the database server to the outside world, but when granted access, all hosts can be accessed. This can be interpreted as a security flaw as no login is required when using UTL_TCP for example. DBA’s are advised to revoke the execute privileges from public on these kind of packages.

Since Oracle 11g, the Access Control List is introduced. You not only can control who has access to these packages by granting, but now you can also control which resources they can call.We can control accessibility based on host and port number.

Create ACL and assign host,port and privileges.

1.

BEGIN

DBMS_NETWORK_ACL_ADMIN.create_acl (

acl => 'jaydba.xml',

description => 'jaydba',

principal => 'APPS',

is_grant => TRUE,

privilege => 'connect',

start_date => SYSTIMESTAMP,

end_date => NULL);

COMMIT;

END;

/

2.

BEGIN

DBMS_NETWORK_ACL_ADMIN.assign_acl (

acl => 'jaydba.xml',

host => 'jaydba.blogspot.com',

lower_port => 443,

upper_port => 443);

END;

/

3.

BEGIN

DBMS_NETWORK_ACL_ADMIN.add_privilege (

acl => 'jaydba.xml',

principal => 'APPS',

is_grant => FALSE,

privilege => 'resolve',

position => NULL,

start_date => NULL,

end_date => NULL);

COMMIT;

END;

/

4.Drop ACL.

begin

DBMS_NETWORK_ACL_ADMIN.drop_acl (acl => 'jaydba.xml');

COMMIT;

END;

/

5.Unassign ACL:

begin

dbms_network_acl_admin.unassign_acl(

acl        => 'jaydba.xml',

host       => 'jaydba.blogspot.com',

lower_port => 443,

upper_port => 443

  );

end;

/

6.Delete Privilege:

begin

dbms_network_acl_admin.delete_privilege('jaydba.xml', 'APPS', NULL, 'connect');

end;

/

In Case ,if we would like to change host name or port ,we just have to unassign and assign(5 & 2),No need to drop acl and recreate.

Accessing https sites:

IF the externeal network we try access is ssl protected,then we need to create the wallet and import the certificate,pls make sure to import the certificate chains properly(main,root,intermediate certs)

orapki wallet create -wallet /home/oracle/wallet -pwd password123 -auto_login

orapki wallet add -wallet /home/oracle/wallet -trusted_cert -cert verisignclass3.cer -pwd password123

orapki wallet add -wallet /home/oracle/wallet -trusted_cert -cert www.verisign.com.cer -pwd password123

orapki wallet add -wallet /home/oracle/wallet -trusted_cert -cert jaydba.cer -pwd password123

orapki wallet display -wallet /home/oracle

To verify:

select   UTL_HTTP.request('url',proxy,'wallet',wallet password) from dual;

proxy            -- (Optional) Specifies a proxy server to use when making the HTTP request

wallet_password  -- (Optional) Specifies the password required to open the wallet. 

ex:

select   UTL_HTTP.request('https://jaydba.blogspot.com',null,'file:/home/oracle/wallet ',null) from dual;