Step by step Create strong password policy in Oracle 12c

#PasswordPolicy #Authentication #RAC #OracleRac

Sometimes as a part of implementing strong security in oracle databases,customer wants the password should match their requirement .As a sample let's say customer wants the below stringent password should be set for their application users .

1. Password complexity: Password should be at least 14 Char long with 2 Special char, 1 Number and 1 Uppercase,1 Lowercase.

Oracle Connection and Login Restrictions

2. Ensure 'FAILED_LOGIN_ATTEMPTS' Is Less than or Equal to '5'

3. Ensure 'PASSWORD_LOCK_TIME' Is Greater than or Equal to '1'

4. Ensure 'PASSWORD_LIFE_TIME' Is Less than or Equal to '90'

5. Ensure 'PASSWORD_REUSE_MAX' Is Greater than or Equal to '20'

6. Ensure 'PASSWORD_REUSE_TIME' Is Greater than or Equal to '365'

7. Ensure 'PASSWORD_GRACE_TIME' Is Less than or Equal to '5'

8. Ensure 'DBA_USERS.PASSWORD' Is Not Set to 'EXTERNAL' for Any User

9. Ensure 'PASSWORD_VERIFY_FUNCTION' Is Set for All Profiles

10. Ensure 'SESSIONS_PER_USER' Is Less than or Equal to '10'

11. Ensure the application Users Are Assigned the 'DEFAULT' Profile

For the #1:Password complexity :

we can have the default password policy of oracle12c .But as customer wants a customized password policy we have to create/define our own procedure .

CREATE OR REPLACE FUNCTION NEW_PASSWORD_VERIFY_FUN

(username varchar2,

password varchar2,

old_password varchar2)

return boolean IS

differ integer;

begin

if not ora_complexity_check(password, chars => 14, upper => 1, lower => 1,

digit => 1, special => 2) then

return(false);

end if;

-- Check if the password differs from the previous password by at least

-- 4 characters

if old_password is not null then

differ := ora_string_distance(old_password, password);

if differ < 4 then

raise_application_error(-20032, 'Password should differ from previous '

|| 'password by at least 4 characters');

end if;

end if;

return(true);

end;

/

2- Once the function is created we will create a new profile with the connection and login restrictions .

SQL> col COM for a10

SQL> col LIMIT for a10

SQL> col RESOURCE for a10

SQL> col com for a10

SQL> col RESOURCE for a20

SQL> col PROFILE for a10

Create profile NEW_PROFILE limit

FAILED_LOGIN_ATTEMPTS 5

PASSWORD_REUSE_MAX 20

PASSWORD_REUSE_TIME 365

PASSWORD_LIFE_TIME 90

PASSWORD_LOCK_TIME 1

PASSWORD_GRACE_TIME 5

SESSIONS_PER_USER 10

PASSWORD_VERIFY_FUNCTION NEW_PASSWORD_VERIFY_FUN;

3- Now we can assign the profile to the desired users .

alter user newuser profile NEW_PROFILE ;