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

Problem:

----Using SCOTT Database user getting the following error---

select utl_inaddr.get_host_address,utl_inaddr.get_host_name
from dual

ORA-24247: network access denied by access control list (ACL)
ORA-06512: at "SYS.UTL_INADDR", line 19
ORA-06512: at "SYS.UTL_INADDR", line 40
ORA-06512: at line 1

Solution

----execute the follwoing process using SYS user---

SQL> exec dbms_network_acl_admin.create_acl(acl => 'resolve.xml',description => 'resolve acl', principal =>'SCOTT', is_grant => true, privilege => 'resolve', start_date   => SYSTIMESTAMP);

PL/SQL procedure successfully completed.

SQL> exec dbms_network_acl_admin.assign_acl(acl => 'resolve.xml', host =>'*');

PL/SQL procedure successfully completed.

SQL> COMMIT;

Commit complete.

SQL> exec DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(acl => 'resolve.xml',principal => 'SCOTT',is_grant => true,privilege => 'connect');


----try to connect using SCOTT user----

select utl_inaddr.get_host_address,utl_inaddr.get_host_name
from dual


SELECT host, lower_port, upper_port, acl FROM dba_network_acls;


----Reference Query---

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;


------DELETE Privilege from ACL--------------

BEGIN
  DBMS_NETWORK_ACL_ADMIN.DELETE_PRIVILEGE(
        acl         => 'resolve.xml',
        principal   => 'SCOTT');
END;

------DROP ACL---------------
DBMS_NETWORK_ACL_ADMIN.DROP_ACL (
   acl           IN VARCHAR2);
   
BEGIN
   DBMS_NETWORK_ACL_ADMIN.DROP_ACL(
      acl => 'resolve.xml');
END;   

BEGIN
   DBMS_NETWORK_ACL_ADMIN.UNASSIGN_ACL(
     host        => '*.us.oracle.com',
     lower_port  => 80);
END;

Revoke All Privileges from User/Role/Public in Oracle

Revoke All Privileges from User/Role/Public in Oracle

REVOKE ALL FROM {user, | role, |PUBLIC}
   
example: REVOKE ALL FROM SYSTEM;   

REVOKE ALL ON employee FROM SCOTT;

Find Roles Granted to PUBLIC

select * from dba_role_privs where GRANTEE = 'PUBLIC';

Find DBA Roles Granted to Users

select GRANTEE, ADMIN_OPTION, DEFAULT_ROLE 
from dba_role_privs 
where GRANTED_ROLE = 'DBA';

Example CREATE PROFILE Password Security Script

 The following SQL script can be used to create an example Oracle profile used for password security.


connect sys/manager as sysdba
Connected.

CREATE OR REPLACE FUNCTION verify_user_password (  username VARCHAR2
                                                 , password VARCHAR2
                                                 , old_password VARCHAR2)
    RETURN boolean
    IS

        passwordMinLength   INTEGER;
        passwordLength      INTEGER;
        differ              INTEGER;
        differMinLength     INTEGER;
        isDigit             BOOLEAN;
        isChar              BOOLEAN;
        isPunct             BOOLEAN;
        digitArray          VARCHAR2(20);
        punctArray          VARCHAR2(25);
        charArray           VARCHAR2(52);

    BEGIN

        digitArray         := '0123456789';
        charArray          := 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';
        punctArray         := '!"#$%&()``*+,-/:;<=>?_';
        passwordMinLength  := 7;
        differMinLength    := 3;
        passwordLength     := LENGTH(password);
        isDigit            := FALSE;
        isChar             := FALSE;
        isPunct            := FALSE;

        -- +------------------------------------------------+
        -- | Check if the password is same as the username  |
        -- +------------------------------------------------+
        IF NLS_LOWER(password) = NLS_LOWER(username) THEN
            raise_application_error(-20001, 'Password same as or similar to user');
        END IF;


        -- +-------------------------------------------------+
        -- | Check that password is more than [x] characters |
        -- | in length.                                      |
        -- +-------------------------------------------------+
        IF (LENGTH(password) < passwordMinLength) THEN
            raise_application_error(-20002, 'Password must be greater than '
                                            ||
                                            passwordMinLength
                                            ||
                                            ' characters.');
        END IF;


        -- +----------------------------------------------------+
        -- | Check if the password is too simple. A dictionary  |
        -- | of words may be maintained and a check may be made |
        -- | so as not to allow the words that are too simple   |
        -- | for the password.                                  |
        -- +----------------------------------------------------+
        IF NLS_LOWER(password) IN (   'welcome'
                                    , 'database'
                                    , 'account'
                                    , 'user'
                                    , 'password'
                                    , 'oracle'
                                    , 'computer'
                                    , 'abcd') THEN
            raise_application_error(-20003, 'Password too simple');
        END IF;

        -- +-----------------------------------------------------+
        -- | Check if the password contains at least one letter, |
        -- | one digit and one punctuation mark.                 |
        -- +-----------------------------------------------------+

        -- +-----------------------------------------------------+
        -- | (1.) Check for the digit                            |
        -- +-----------------------------------------------------+
        FOR i IN 1..10 LOOP
            FOR j IN 1..passwordLength LOOP
                IF SUBSTR(password,j,1) = SUBSTR(digitArray,i,1) THEN
                    isDigit := TRUE;
                    GOTO findchar;
                END IF;
            END LOOP;
        END LOOP;

        IF isDigit = FALSE THEN
            raise_application_error(-20004, 'Password should contain at least '
                                             ||
                                             ' one digit,'
                                             ||
                                             ' one character and'
                                             ||
                                             ' one punctuation');
        END IF;

        -- +-----------------------------------------------------+
        -- | (2.) Check for the character                        |
        -- +-----------------------------------------------------+
        <>
        FOR i IN 1..LENGTH(charArray) LOOP
            FOR j IN 1..passwordLength LOOP
                IF SUBSTR(password,j,1) = SUBSTR(charArray,i,1) THEN
                    isChar := TRUE;
                    GOTO findpunct;
                END IF;
            END LOOP;
        END LOOP;

        IF isChar = FALSE THEN
            raise_application_error(-20004, 'Password should contain at least '
                                             ||
                                             ' one digit,'
                                             ||
                                             ' one character and'
                                             ||
                                             ' one punctuation');
        END IF;

        -- +-----------------------------------------------------+
        -- | (3.) Check for the punctuation                      |
        -- +-----------------------------------------------------+
        <>
        FOR i IN 1..LENGTH(punctArray) LOOP
            FOR j IN 1..passwordLength LOOP
                IF SUBSTR(password,j,1) = SUBSTR(punctArray,i,1) THEN
                    isPunct := TRUE;
                    GOTO endsearch;
                END IF;
            END LOOP;
        END LOOP;

        IF isPunct = FALSE THEN
            raise_application_error(-20004, 'Password should contain at least '
                                             ||
                                             ' one digit,'
                                             ||
                                             ' one character and'
                                             ||
                                             ' one punctuation');
        END IF;

        <>

        -- +-----------------------------------------------------+
        -- | Check that the new password is not null.            |
        -- +-----------------------------------------------------+
        IF old_password = '' THEN
            raise_application_error(-20005, 'Old password is null');
        END IF;


        -- +-----------------------------------------------------+
        -- | Check if the password differs from the previous     |
        -- | password by at least [x] letters.                   |
        -- +-----------------------------------------------------+
        differ := ABS(LENGTH(old_password) - LENGTH(password));

        IF differ < differMinLength THEN

            IF LENGTH(password) < LENGTH(old_password) THEN
                passwordLength := LENGTH(password);
            ELSE
                passwordLength := LENGTH(old_password);
            END IF;

            FOR i IN 1..passwordLength LOOP

                IF SUBSTR(password,i,1) != SUBSTR(old_password,i,1) THEN
                    differ := differ + 1;
                END IF;
            END LOOP;

            IF differ < differMinLength THEN
                raise_application_error(-20006, 'Password should differ by at least '
                                                ||
                                                differMinLength
                                                ||
                                                ' characters.');
            END IF;

        END IF;

        -- +-----------------------------------------------------+
        -- | Well, looks like we passed all of the requirements. |
        -- | Return 'true'.                                      |
        -- +-----------------------------------------------------+
        RETURN(true);

    END;
/

Function created.

CREATE PROFILE "USER_PROFILE"
    LIMIT   
    FAILED_LOGIN_ATTEMPTS 3
    PASSWORD_LOCK_TIME UNLIMITED
    PASSWORD_GRACE_TIME 5
    PASSWORD_LIFE_TIME 90
    PASSWORD_REUSE_MAX UNLIMITED
    PASSWORD_REUSE_TIME 120
    PASSWORD_VERIFY_FUNCTION verify_user_password
/

Profile created.