Updated: Feb 11, 2021
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
return boolean IS
if not ora_complexity_check(password, chars => 14, upper => 1, lower => 1,
digit => 1, special => 2) then
-- 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');
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
3- Now we can assign the profile to the desired users .
alter user newuser profile NEW_PROFILE ;