Step by step Create strong password policy in Oracle 12c

Updated: Feb 11, 2021

#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 ;

64 views0 comments

Recent Posts

See All

#oracle #ASM #controlfile #multiplexing #oracle19c It is a best practice for any database to have controlfile as well as redolog file to be multiplexed . This helps during any disk failure to keep one

In the previous post we saw how to upgrade OMS from 13.2 to 13.4 version .(Click Here) Once OMS is upgraded we have to upgrade the agents running in the servers which are monitored by OEM . This whole