ORA-24247: network access denied by access control list (ACL)

Yesterday I was trying to send mail from my Oracle Database and I got below mentioned error :-

ERROR at line 1:
ORA-24247: network access denied by access control list (ACL)
ORA-06512: at "CINP01314", line 255
ORA-06512: at line 21

 In 11g Database , we need to create and configure ACL(access control list). In order to use PL/SQL network utility pakages like UTL_SMTP, UTL_MAIL, we have to configure an ACL file that provides fine grained access to external network services.

Steps to configure :-

1. Find out your SMTP outgoing mail server and configure the database parameter smtp_out_server.

SQL> alter system set smtp_out_server= '10.10.10.10' ; 
############### only for UTL_MAIL package this parameter needs to be set#################

system altered

2. Create an access control list file :-

begin
DBMS_NETWORK_ACL_ADMIN
.create_acl (
acl => 'utl_smtp.xml',
description => 'Enables mail to be sent',
principal => 'AMIT',   -- USERNAME to which access has to be granted
is_grant => true,
privilege => 'connect');
commit;
end;
/

PL/SQL procedure successfully completed.

--  Drop an access control list :-

BEGIN
  DBMS_NETWORK_ACL_ADMIN.drop_acl ( 
    acl         => '/sys/acls/utl_smtp.xml');
  COMMIT;
END;
/

PL/SQL procedure successfully completed.

3. Assign this ACL to your SMTP network host for your email server :-

begin
dbms_network_acl_admin.assign_acl (
acl => 'utl_smtp.xml',
host => '10.02.03.04',    -- SMTP network host
lower_port => 25);
commit;
end;
/

PL/SQL procedure successfully completed.

--  TO drop ACL assignments :-

begin
dbms_network_acl_admin.unassign_acl (
acl => 'utl_smtp.xml',
host => '10.02.03.04',    -- SMTP network host
lower_port => 25);
COMMIT;
end;
/

PL/SQL procedure successfully completed.

4. Grant permission to use ACL file :-

begin
DBMS_NETWORK_ACL_ADMIN.add_privilege (
acl => 'utl_smtp.xml',
principal => 'AMIT',
is_grant => TRUE,
privilege => 'connect');
COMMIT;
end;
/

PL/SQL procedure successfully completed.

-- If we want to delete or revoke this permission :-

BEGIN
  DBMS_NETWORK_ACL_ADMIN.delete_privilege ( 
    acl         => '/sys/acls/utl_smtp.xml', 
    principal   => 'AMIT',
    is_grant    => TRUE, 
    privilege   => 'connect');
  COMMIT;
END;
/

PL/SQL procedure successfully completed.

5. Check that for AMIT user permission has been set properly :-

SQL> col HOST for a45
SQL> SELECT host, lower_port, upper_port, privilege, status FROM   user_network_acl_privileges;

HOST                                                        LOWER_PORT UPPER_PORT PRIVILEGE             STATUS
---------------------------------------------       ----------------- ---------------- --------------------- ---------------------
10.02.03.04                                             25                    25               connect               GRANTED
<your smtp server host name or address>                                            connect               GRANTED
black                                                                                                  connect               GRANTED

--Status column must have value granted.

SQL> SELECT DECODE(
         DBMS_NETWORK_ACL_ADMIN.check_privilege('utl_smtp.xml', 'AMIT', 'connect'),
         1, 'GRANTED', 0, 'DENIED', NULL) privilege 
FROM dual;

PRIVILEGE
---------------------
GRANTED

SQL> COLUMN acl FORMAT A30
SQL> COLUMN principal FORMAT A30

SQL> SELECT acl,
       principal,
       privilege,
       is_grant,
       TO_CHAR(start_date, 'DD-MON-YYYY') AS start_date,
       TO_CHAR(end_date, 'DD-MON-YYYY') AS end_date
FROM   dba_network_acl_privileges;.

ACL                            PRINCIPAL                      PRIVILEGE             IS_GRANT        START_DATE                        END_DATE
------------------------------ ------------------------------ --------------------- --------------- --------------------------------- ---------------------------------
/sys/acls/utl_smtp.xml         AMIT                       connect               true

Once you got output like above queries. You can test you procedure to send mail, it will work.
As we have create ACL for utl_smtp.xml we can create for utl_mail.xml and utl_tcp.xml after creation of both these ACL's output of above query will be like below :-

ACL                            PRINCIPAL                      PRIVILEGE             IS_GRANT        START_DATE                        END_DATE
------------------------------ ------------------------------ --------------------- --------------- --------------------------------- ---------------------------------
/sys/acls/utl_mail.xml          AMIT                         connect               true
/sys/acls/utl_smtp.xml         AMIT                         connect               true
/sys/acls/utl_tcp.xml           AMIT                         connect               true

TO enable this ACL permission for a different USER other than above user(AMIT)  :-

Connect with TEST user and run below mentioned :-

begin
DBMS_NETWORK_ACL_ADMIN.add_privilege (
acl => 'utl_smtp.xml',
principal => 'TEST',
is_grant => FALSE,
privilege => 'connect');
commit;
end;
/

PL/SQL procedure successfully completed.

begin
dbms_network_acl_admin.assign_acl (
acl => 'utl_smtp.xml',
host => '10.02.03.04',
lower_port => 25);
end;
/

PL/SQL procedure successfully completed.

SQL> COLUMN acl FORMAT A30
SQL> COLUMN principal FORMAT A30

SQL> SELECT acl,
       principal,
       privilege,
       is_grant,
       TO_CHAR(start_date, 'DD-MON-YYYY') AS start_date,
       TO_CHAR(end_date, 'DD-MON-YYYY') AS end_date
FROM   dba_network_acl_privileges;.

ACL                            PRINCIPAL                      PRIVILEGE             IS_GRANT        START_DATE                        END_DATE
------------------------------ ------------------------------ --------------------- --------------- --------------------------------- ---------------------------------
/sys/acls/utl_smtp.xml         AMIT                       connect               true
/sys/acls/utl_smtp.xml         TEST                       connect               false

-- This query has to be run with TEST user and Status column should have value "GRANTED"

SQL> SELECT host, lower_port, upper_port, privilege, status FROM   user_network_acl_privileges;

HOST                                                        LOWER_PORT UPPER_PORT PRIVILEGE             STATUS
---------------------------------------------       ----------------- ---------------- --------------------- ---------------------
10.02.03.04                                             25                    25               connect               GRANTED
<your smtp server host name or address>                                            connect               GRANTED
black                                                                                                  connect               GRANTED


Now check your procedure to send mail through test user , it will work.

I hope this article helped you.

Regards,
Amit Rath

How to setup password never expire for oracle user in Oracle

Sometimes for a test user in oracle , we often get this error :-

SQL> conn scott/tiger
ERROR:
ORA-28002: the password  will expire in 7 days
Connected

We want that as its a test user, its password never expire. We have to make a change in the profile associated with this user.

SQL> select profile from dba_users where username='SCOTT';

PROFILE
------------------------------------------------------------------------------------------
PROFILE

SQL> select * from dba_profiles where profile='PROFILE' and RESOURCE_NAME='PASSWORD_LIFE_TIME';

PROFILE                   RESOURCE_NAME                  RESOURCE_TYPE            LIMIT

------------------------- ------------------------------ ------------------------ ------------------------------

PROFILE                   PASSWORD_LIFE_TIME             PASSWORD                 10

we need to change the limit of password_life_time parameter to unlimited.

SQL> alter profile PROFILE limit PASSWORD_LIFE_TIME unlimited;

Profile altered.

SQL> select * from dba_profiles where profile='PROFILE' and RESOURCE_NAME='PASSWORD_LIFE_TIME';

PROFILE                   RESOURCE_NAME                  RESOURCE_TYPE            LIMIT

------------------------- ------------------------------ ------------------------ ------------------------------

PROFILE                   PASSWORD_LIFE_TIME             PASSWORD                 UNLIMITED

Check that the expirary date column is null of desired user in DBA_USERS view

SQL> select USERNAME,ACCOUNT_STATUS,EXPIRY_DATE,PROFILE from  dba_users where USERNAME='SCOTT';

USERNAME                       ACCOUNT_STATUS            EXPIRY_DATE        PROFILE

------------------------------ ------------------------- ------------------ -------------------------

SCOTT                            OPEN                                                         PROFILE

Similarly if we want failed login attempts has to be unlimited for a test user then alter the profile associated with the test user :-

SQL> alter profile default limit FAILED_LOGIN_ATTEMPTS unlimited;

Profile altered

I hope this article helped you.

Regards,

Amit Rath