Oracle ACL

Databases

Oracle

Oracle Security

Create ACL

Drop ACL

Check

Useful Queries

Example - Changing Mail Server

Example - Revoking an ACL from a user

To view an XML ACL file

Useful Views

Bibliography

To avoid "ORA-24247: network access denied by access control list (ACL)" when executing UTL packages (Network related Packages), access has to be granted to user using these packages. Affected packages include:

UTL_TCP
UTL_SMTP
UTL_MAIL
UTL_HTTP
UTL_INADDR

Create ACL

At its simplest...

BEGIN

   DBMS_NETWORK_ACL_ADMIN.CREATE_ACL(acl => 'ACLNAME.xml',

                                     description => 'Description of this ACL',

                                     principal => 'MYUSER',

                                     is_grant => true,

                                     privilege => 'connect',

                                     start_date => null,

                                     end_date => null );

   COMMIT;

END;/

for everything except UTL_INADDR, privilege should be 'connect'. For UTL_INADDR you can also use 'resolve'.

The following example first looks for any ACL currently assigned to host_name. If one is found, then the example grants user_name the CONNECT privilege in the ACL only if that user does not already have it. If no ACL exists for host_name, then the example creates a new ACL called ACL_name, grants the CONNECT privilege to user_name, and assigns the ACL to host_name.

DECLARE

   ACL_PATH VARCHAR2(4000);

   ACL_ID   RAW(16);

BEGIN

   -- Look for the ACL currently assigned to '*' and give MYUSER

   -- the "connect" privilege if MYUSER does not have the privilege yet.

   SELECT ACL INTO ACL_PATH

   FROM   DBA_NETWORK_ACLS

   WHERE  HOST = '*'

   AND    LOWER_PORT IS NULL

   AND    UPPER_PORT IS NULL;

   -- Before checking the privilege, make sure that the ACL is valid

   -- (for example, does not contain stale references to dropped users).

   -- If it does, the following exception will be raised:

--

   -- ORA-44416: Invalid ACL: Unresolved principal 'MYUSER'

   -- ORA-06512: at "XDB.DBMS_XDBZ", line ...

   SELECT SYS_OP_R2O(extractValue(P.RES, '/Resource/XMLRef')) INTO ACL_ID

   FROM   XDB.XDB$ACL A,

          PATH_VIEW P

   WHERE  extractValue(P.RES, '/Resource/XMLRef') = REF(A)

   AND    EQUALS_PATH(P.RES, ACL_PATH) = 1;

   DBMS_XDBZ.ValidateACL(ACL_ID);

   IF DBMS_NETWORK_ACL_ADMIN.CHECK_PRIVILEGE(acl       => ACL_PATH,

                                             user      => 'MYUSER',

                                             privilege => 'connect') IS NULL

   THEN

      DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(acl       => ACL_PATH,

                                           principal => 'MYUSER',

                                           is_grant  => TRUE,

                                           privilege => 'connect');

   END IF;

EXCEPTION

   -- When no ACL has been assigned to '*'.

   WHEN NO_DATA_FOUND THEN

      DBMS_NETWORK_ACL_ADMIN.CREATE_ACL(acl         => 'aclname.xml',

                                        description => 'ACL description',

                                        principal   => 'MYUSER',

                                        is_grant    => TRUE,

                                        privilege   => 'connect');

      DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL(acl         => 'aclname.xml',

                                        host        => '*');

END;

COMMIT;

aclname.xml => Enter a name for the access control list XML file.ACL description => 'file description',principal => 'user_or_role',is_grant => TRUE|FALSE,privilege => 'connect|resolve',host_name => host name (e.g. * for everything, or something like mail.mydomain.com for that specific host). Can be domain name or IP.

Drop ACL

BEGIN

   DBMS_NETWORK_ACL_ADMIN.DROP_ACL(acl => 'ACLNAME');

END

Check

To list ACLs...

SELECT ACL

FROM   DBA_NETWORK_ACLS

To see which users have ACLs granted…

COLUMN acl FORMAT a30

COLUMN principal FORMAT a30

SELECT acl,

       principal,

       privilege

FROM   dba_network_acl_privileges

ORDER BY principal

To check which ACL domains allow MYUSER to connect to mail.mydomain.com…

COLUMN host FORMAT a10

COLUMN acl  FORMAT a40

SET LINESIZE 132

SELECT  dna.host,

        dna.lower_port,

        dna.upper_port,

        dna.acl,

        DECODE(DBMS_NETWORK_ACL_ADMIN.CHECK_PRIVILEGE_ACLID(dna.aclid, 'MYUSER', 'connect'),

               1, 'GRANTED',

               0, 'DENIED',

               null) privilege

FROM    dba_network_acls dna

WHERE   host IN (SELECT  *

                 FROM    TABLE(DBMS_NETWORK_ACL_UTILITY.DOMAINS('mail.mydomain.com')))

ORDER BY DBMS_NETWORK_ACL_UTILITY.DOMAIN_LEVEL(host) desc,

         lower_port,

         upper_port

Useful Queries

Example - Changing Mail Server

If you need to change the your mail server for database outgoing mail...

(e.g. you need to change SMTP_OUT_SERVER from mail.mydomain.com to relay.newdomain.com)

First we need to identify how the ACLs are defined for the existing host...

SET LINESIZE 200

COLUMN host FORMAT a30

COLUMN acl FORMAT a60

SELECT host, acl, lower_port, upper_port

FROM dba_network_acls;

Any ACLs that explicitly point to the old mail server will need to be associated with the new mail server...

It is possible for an ACL to use a wildcard (e.g. * or *.domain.com). You should be mindful of giving non-privileged users access to wildcard ACLs.

BEGIN

   DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL (acl => '&ACLNAME', host => '&newhost', lower_port => &lowport, upper_port => &hiport);

   commit;

END;

At this point it should be possible to send mails through mail.mydomain.com or relay.newdomain.com dependent on your setting for SMTP_OUT_SERVER. Once the change is considered permanent, then you should remove any redundant ACL assignments...

BEGIN

   DBMS_NETWORK_ACL_ADMIN.UNASSIGN_ACL (acl => '&ACLNAME', host => '&oldhost', lower_port => &lowport, upper_port => &hiport);

   commit;

END;

Example - Revoking an ACL from a user

If you need to stop a user from sending mails using UTL_MAIL via a mail server when that user can currently do so

(e.g. you need to stop USER1 sending mails using UTL_MAIL via relay.newdomain.com, but you don't want to revoke access to UTL_MAIL)

BEGIN

  DBMS_NETWORK_ACL_ADMIN.DELETE_PRIVILEGE(acl       => 'ACLNAME',

                                          principal => 'USER1')

END

To view an XML ACL file

SET LONG 4000

SET PAGESIZE 4000

SELECT a.OBJECT_VALUE

FROM   RESOURCE_VIEW r,

       XDB.XDB$ACL a

WHERE  ref(a) = extractValue(r.RES, '/Resource/XMLRef')

AND    equals_path(r.RES, '/sys/acls/aclname.xml') = 1;

UTL_MAIL

Useful Views

DBA_NETWORK_ACLS
DBA_NETWORK_ACL_PRIVILEGES

Bibliography

https://dba12c.wordpress.com/2015/08/18/understanding-dbms_network_acl_admin-with-example/
Note 453786.1 ORA-24247 When Executing UTL_HTTP UTL_INADDR Packages1080105.1-Understanding DBMS_NETWORK_ACL_ADMIN With Example http://docs.oracle.com/cd/B28359_01/appdev.111/b28419/d_networkacl_adm.htm - Oracle® Database PL/SQL Packages and Types Reference 11g Release 1 (11.1)http://docs.oracle.com/cd/B28359_01/appdev.111/b28419/d_networkacl_adm.htm#BABCJDGChttp://docs.oracle.com/cd/B28359_01/network.111/b28531/authorization.htm#DBSEG40012 - Oracle® Database Security Guide 11g Release 1 (11.1)http://docs.oracle.com/cd/B28359_01/appdev.111/b28369/xdb21sec.htm#ADXDB2400 - Oracle® XML DB Developer's Guide 11g Release 1 (11.1)

Page updated

Google Sites

Report abuse