Managing Roles with Password Profiles: Part 2

In Part 1, I have explained how FAILED_LOGIN_ATTEMPTS and PASSWORD_LOCK_TIME can be used to record user logins. In this post, I will explain how to manage a password including its life time, grace time along with enforcing certain rules on the password phrase.

PASSWORD_LIFE_TIME and PASSWORD_GRACE_TIME

PASSWORD_LIFE_TIME specifies the number of days that the current password is valid and usable. After this period, the password expires and the user needs to change their password. PASSWORD_GRACE_TIME specifies the number of days an old password can still be used (after the period specified by PASSWORD_LIFE_TIME elapses) to log on to the system. Post this period, the user can only log on to the system but won’t be able to execute any query other than the one which changes his/her own password. Note that when a password is within the grace period, a warning will be issued at every user login.

Let's demonstrate this by altering our profile's PASSWORD_LIFE_TIME to seven days and PASSWORD_GRACE_TIME to one day. This means, once a new password is created it will remain valid for seven days plus a one-day grace period.

ALTER PROFILE myprofile LIMIT
  PASSWORD_LIFE_TIME 7
  PASSWORD_GRACE_TIME 1;

SELECT prfname, prfpasswordlifetime, prfpasswordgracetime
FROM edb_profile WHERE prfname = 'myprofile';
  prfname  | prfpasswordlifetime | prfpasswordgracetime
-----------+---------------------+----------------------
 myprofile |              604800 |                86400
(1 row)

\c - myuser
Password for user myuser:
You are now connected to database "edb" as user "myuser".

ALTER USER myuser IDENTIFIED BY mypassword;

SELECT rolname, rolprofile, edb_get_role_status(oid), rolpasswordexpire
FROM pg_roles WHERE rolname = 'myuser';
 rolname | rolprofile | edb_get_role_status | rolpasswordexpire
---------+------------+---------------------+-------------------
 myuser  | myprofile  | OPEN                |
(1 row)

So far so good… Now after seven days, if you try to log on, you will see the following:

\c - myuser
Password for user myuser:
WARNING:  the account will expire soon; please change your password
DETAIL:  Your password will expire in 1.000000 days.
HINT:  Use ALTER ROLE to change your password.
You are now connected to database "edb" as user "myuser".

Since the password is older than seven days, when you log on, the system detects that the current password has expired; but as there is a grace period specified, the user is able to log on and use the system as a normal user. Also, have a look at the DETAIL line which specifies how many more days the same password can be used, after which time, the password will expire.

-- Check the role status
SELECT rolname, rolprofile, edb_get_role_status(oid), rolpasswordexpire
FROM pg_roles WHERE rolname = 'myuser';
 rolname | rolprofile | edb_get_role_status |        rolpasswordexpire         
---------+------------+---------------------+----------------------------------
 myuser  | myuser     | EXPIRED(GRACE)      | 21-NOV-18 12:28:50.351837 +05:30
(1 row)

The role status now says EXPIRED(GRACE), which means the user password is expired, but it’s still usable within the grace period. rolpasswordexpire gives the final date after which a password will expire. So, let's move one day ahead without changing the password and see what happens.

-- After one more day, try to login with the same password
\c - myuser
Password for user myuser:
WARNING:  password expired
DETAIL:  You must change your password before executing any other SQL statement.
HINT:  Use ALTER ROLE to change your password.
You are now connected to database "edb" as user "myuser".

Now, you get a warning saying the password has expired. But the system did allow you to log on to change your password. You were able to log on successfully, but will you be able to execute any queries? Let's try to select from a dual table:

SELECT * FROM dual;
ERROR:  password expired

Oops! You got an error saying "password expired" and can't execute any queries. Well, that is exactly what the DETAIL line in the error message explained when you logged in, right? It also has a HINT as to how to change the password. So, let's go ahead and change the password to get back to the normal login mode. But, before that, let's have a look over the role status (you need to go back to your superuser account to see that as you can't run any other query).

-- Check the role status
SELECT rolname, rolprofile, edb_get_role_status(oid), rolpasswordexpire
FROM pg_roles WHERE rolname = 'myuser';
 rolname | rolprofile | edb_get_role_status |        rolpasswordexpire         
---------+------------+---------------------+----------------------------------
 myuser  | myuser     | EXPIRED             | 21-NOV-18 12:28:50.351837 +05:30
(1 row)

As with the ACCOUNT LOCK clause which I have explained earlier, a superuser can explicitly set any role's password status as EXPIRED using:

ALTER USER myuser PASSWORD EXPIRE;

SELECT rolname, rolprofile, edb_get_role_status(oid), rolpasswordexpire
FROM pg_roles WHERE rolname = 'myuser';
 rolname | rolprofile | edb_get_role_status |        rolpasswordexpire         
---------+------------+---------------------+----------------------------------
 myuser  | myprofile  | EXPIRED             | 21-NOV-18 12:30:56.662828 +05:30
(1 row)

And then the user will be prompted to change their password to restore full access to the system.

PASSWORD_VERIFY_FUNCTION

To enforce certain rules on a password string, such as “password should be eight characters long” or “password must have one special character”, the password verifier function should be used. A PL/SQL function with sufficient password complexity needs to be attached to this parameter. So, if PASSWORD_VERIFY_FUNCTION is set, then it will be executed every time a normal user changes their password.

So, before setting this password profile parameter, create the password verifier function first.

CREATE FUNCTION sys.mypvf(user_name varchar2, new_password varchar2, old_password varchar2)
  RETURN boolean IMMUTABLE IS
BEGIN
  IF (length(new_password) < 8) THEN
    raise_application_error(-20001, 'password too short');
  END IF;
  IF new_password = old_password THEN
    raise_application_error(-20002, 'password cannot be same as existing password');
  END IF;

  RETURN true;
END;

Note that the password verifier function must reside in the SYS schema and must accept the user_name, new and old passwords as parameters that are declared as VARCHAR2. It will then return a BOOLEAN value, which will read as TRUE when all the conditions of having a valid password are met. If a FALSE value is returned, it will generate an error saying "ERROR:  password verification for the specified password failed".

Now, we have the password verifier function that makes sure the password length is a minimum of eight characters long and is different from the existing password. Attach it to the profile.

ALTER PROFILE myprofile LIMIT
  PASSWORD_VERIFY_FUNCTION mypvf;

-- Check the details in the catalog table
SELECT prfname, prfpasswordverifyfunc::regproc
FROM edb_profile WHERE prfname = 'myprofile';
  prfname  | prfpasswordverifyfunc
-----------+-----------------------
 myprofile | mypvf
(1 row)

Now, all the users with the profile myprofile must observe these rules while setting the password. So, let's test that. Log on using myuser and try to change the password. Note that the existing password is "mypassword", which you need to provide using a REPLACE clause.

ALTER USER myuser IDENTIFIED BY mypassword REPLACE mypassword;
ERROR:  EDB-20002: password cannot be same as existing password

Since the new password is the same as the existing password, we get an error. Similarly, you will get an error if you have a new password that contains fewer than 8 characters.

ALTER USER myuser IDENTIFIED BY mynew REPLACE mypassword;
ERROR:  EDB-20001: password too short

However, following command works and changes the password to "mynewpassword" as all the rules are followed.

ALTER USER myuser IDENTIFIED BY mynewpassword REPLACE mypassword;

If a superuser tries to change any other user's password, then providing the old password using the REPLACE clause is optional. However, the password verifier function is still executed for the superuser.

Watch this space for part three, which will explain the remaining password profile parameters.