Introduction

Continuing with my series on TimesTen XE SQL Profiles, this blog gives you the tools to manage, troubleshoot, and control SQL profiles in TimesTen XE databases. You will learn how TimesTen XE uses system tables and views to manage the password lifetime and password history limits through targeted examples.

Password Parameters Syntax

Here is the syntax for the password parameters for the CREATE PROFILE and ALTER PROFILE SQL statements:

 

password_parameters::=

 

 

Password Lifetime

The password lifetime limits focus on the expiration time of a password. The password you use to connect to a TimesTen XE database is valid until the expiration time of the password is reached.

 

 

Password Lifetime Limits

The PASSWORD_LIFE_TIME and PASSWORD_GRACE_TIME parameters let you set the limits for a password’s lifetime.

• PASSWORD_LIFE_TIME: The number of days that a password, once set, is valid. Value is expressed in days.
• PASSWORD_GRACE_TIME: The number of days since your password has expired that you can continue to connect to a TimesTen XE database.

 

Password History

The password history limits focus on when you can reuse a password.

 

 

Password History Limits

The PASSWORD_REUSE_MAX and PASSWORD_REUSE_TIME parameters let you set the limits for a password’s history.

• PASSWORD_REUSE_MAX: The number of times you must change your current password before you can use this current password again.
• PASSWORD_REUSE_TIME: The time (expressed in days) that must pass before you can reuse a password.

These parameters work together and have specific conditions:

• If you set one of the parameters to UNLIMITED, TimesTen XE does not allow the password to be reused.
• If you set both parameters to UNLIMITED (the default), TimesTen XE ignores both  parameters and allows the password to be reused indefinitely.
• If you specify a value (other than UNLIMITED), TimesTen XE looks at the value of each parameter to determine if you can reuse a password.

The examples in Part 3 of this blog show how these parameters work together.

 

SYS.DBA_USERS System View

The SYS.DBA_USERS system view provides details about users of TimesTen XE databases.

 

The ACCOUNT_STATUS and EXPIRY_DATE columns are useful for the password lifetime limits. The examples in the blog show you how TimesTen XE populates these columns.

 

SYS.USER$ System View

The SYS.USER$ system table provides details on a user account.

 

 

These columns are of interest:

• CTIME: The password creation time. Set when you use the CREATE USER statement.
• PTIME: The password change time. Set when you use the CREATE USER or ALTER USER statement. Useful for password lifetime and password history limits.
• EXPTIME: The password expiration time. Populated if you define PASSWORD_GRACE_TIME. Useful for password lifetime limits if PASSWORD_GRACE_TIME is defined.

 

SYS.USER_HISTORY$ System View

The SYS.USER_HISTORY$ system table provides information about a password’s history. TimesTen XE does not populate this table if both PASSWORD_REUSE_MAX and PASSWORD_REUSE_TIME have a value of UNLIMITED.

 

 

The PASSWORD_DATE column stores the time the password was created or changed. Useful for password history limits.

 

 

Part 1: Experiment with Password Lifetime Limit

 

Task 1: Set Up

Let’s create a profile and set a password lifetime limit of 15 minutes. Then, let’s create a user and assign the user this newly created profile.

1. Create the profile1 profile and set password lifetime to 0.01041666666 (=15 minutes in a day = 15 / (60*24) = 0.01041666666).

 

2. Create the testuser1 user and assign the user the profile1 profile. Grant CONNECT privileges to the user.

 

Task 2: Review the System Table and Views

Next, let’s review the relevant metadata objects (system tables and views) to understand how TimesTen XE manages the PASSWORD_LIFE_TIME limit.

1. Use the SYS.USER$ system table to observe the password create date (ctime) and password change date (ptime).

The ctime (password creation time) and the ptime (password change time) are the same.

 

2. Use the SYS.DBA_USERS system view to observe the status of the user account (account_status) and the password expiration date (expiry_date).

The user account has a status of OPEN indicating the user can connect to the database. The testuser1 user can connect to the database until the expiry_date of 2022-05-31 16:29:08.227815. TimesTen XE derives the expiration date from the sum of ptime (2022-05-31 16:14:08.227815) plus the PASSWORD_LIFE_TIME limit (15 minutes) = 2022-05-31 16:29:08.227815.

 

Task 3: Test Lifetime Limit

1. Sleep 900 seconds (=15 minutes). Then, check the system time.

 

2. As testuser1, attempt to connect to the database.

 

3. As the ADMIN user, check the metadata objects.

TimesTen XE changes account_status to EXPIRED.

 

Task 4: Change User Password

1. As the ADMIN user, modify the testuser1 password.

 

2. Check the metadata objects.

TimesTen XE does the following:

• Updates ptime with the new password change time (=16:42:53.399335).
• Sets account_status to OPEN.
• Sets new expiry_date of 2022-05-31 16:57:53.399335 (=2022-05-31 16:42:53.399335 + 15 minutes).

 

3. As the testuser1 user, connect to the database using the new password.

The connection succeeds.

 

Task 5: Clean up

1. As the user with ADMIN privileges, disconnect the testuser1 connection.

 

2. Drop the testuser1 user. Drop the profile1 profile. Then, exit from ttIsql.

 

 

Part 2: Experiment with Password Grace Time Limit

 

Task 1: Set Up

Let’s continue with the password lifetime limits by setting PASSWORD_LIFE_TIME limit to 20 minutes and PASSWORD_GRACE_TIME to 10 minutes for the profile1 profile.

1. Create the profile1 profile, setting PASSWORD_LIFE_TIME to 0.01388888888 (=20 minutes/1440 minutes in a day  and PASSWORD_GRACE_TIME to 0.00694444444 (=10 minutes/1440 minutes in a day).

 

2. Create the testuser1 user and assign the user the profile1 profile. Grant CONNECT privileges to the user.

 

Task 2: Review the System Tables and Views

Let’s review the relevant metadata objects to understand how TimesTen XE manages the PASSWORD_LIFE_TIME and PASSWORD_GRACE_TIME limits.

1. Use the SYS.USER$ system table to observe the password create date (ctime), password change date (ptime) and the date the password expired (exptime).

The password change date is 2022-06-01 01:20:04.212825.

 

2. Use the SYS.DBA_USERS system view to observe the status of the user account (account_status) and the password expiration date (expiry_date).

TimesTen XE derives  expiry_date (2022-06-01 01:40:04.212825) from the sum of ptime (2022-06-01 01:20:04.212825) plus PASSWORD_LIFE_TIME (20 minutes).

 

Task 3: Test Limits

1. Sleep 1200 seconds (=20 minutes). Then, check the system time.

 

2. As testuser1, attempt to connect to the database. Then disconnect.

Grace period starts when testuser1 first connects to the database after the password has expired. Grace period is 10 minutes (PASSWORD_GRACE_TIME).

 

3. As the ADMIN user, check the metadata objects.

TimesTen XE does the following:

• Updates exptime to 2022-06-01 01:51:54.937626. Grace period ends at this time.
• Updates account_status to EXPIRED(GRACE).
• Updates expiry_date to 2022-06-01 01:51:54.937626.

 

4. Sleep 120 seconds (=2 minutes). Check system time.

 

5. As testuser1, attempt to connect to the database. Then disconnect.

The connection succeeds Grace time is reducing.

 

6. Check system time.

The current time exceeds expiry_date of 2022-06-01 01:51:54.937626.

 

7. As testuser1, attempt to connect to the database.

The connection fails. The password has expired.

 

8. As the ADMIN user, check the metadata objects.

TimesTen XE updates account_status to EXPIRED.

 

Task 4: Change User Password

1. As the ADMIN user, modify the testuser1 password.

 

2. Check the metadata objects.

TimesTen XE does the following:

• Updates ptime with the new password change time (2022-06-01 02:04:52.879754).
• Updates account_status to OPEN.
• Updates expiry_date to 2022-06-01 02:24:52.879754 (=2022-06-01 02:04:52.879754 + 20 minutes).

 

3. As the testuser1 user, connect to the database using the new password.

The connection succeeds.

 

Task 5: Clean up

1. As the user with ADMIN privileges, disconnect the testuser1 connection.

 

2. Drop the testuser1 user. Drop the profile1 profile. Then, exit from ttIsql.

 

Part 3: Experiment with Password Reuse Max and Password Reuse Time

 

Task 1: Set Up

Let’s create the profile1 profile and set PASSWORD_REUSE_MAX to 2 and PASSWORD_REUSE_TIME to UNLIMITED.

1. Create the profile1 profile, setting PASSWORD_REUSE_MAX to 2 and PASSWORD_REUSE_TIME to UNLIMITED.

Since PASSWORD_REUSE_TIME has a value of UNLIMITED, a user who is assigned this profile cannot reuse a password.

 

2. Create the testuser1 user and assign the user the profile1 profile.

 

3. Review the SYS.USER$ system table.

The password creation time matches the password change time. Make a note that ptime for password1 is 2022-06-02 14:58:14.553621.

 

4. Review the SYS.USER_HISTORY$ system table.

There is one row in the table that contains data for testuser1(user# 30), including the password1 password and the password_date (2022-06-02 14:58:14.553621).

 

Task 2: Test the Limit

Let’s modify testuser1‘s password three times.

1. Modify testuser1‘s password for the first time. Then check the metadata objects.

 

Check SYS.USER$.

TimesTen XE updates ptime for testuser1. Make a note that ptime for password2 is 2022-06-02 15:04:34.981875.

 

Check SYS.USER_HISTORY$.

There are two rows in the table that contains data for password1 and password2.

 

2. Modify testuser1‘s password for a second time.

 

Check SYS.USER$.

TimesTen XE updates ptime. Make a note that ptime for password3 is 2022-06-02 15:11:53.966629 .

 

Check SYS.USER_HISTORY$.

There are three rows in the table that contains data for password1, password2, and password3.

 

3. Modify testuser1‘s password for a third time. Then check the metadata objects.

 

Check SYS.USER$.

TimesTen XE updates ptime. Make a note that ptime for password3 is 2022-06-02 15:23:24.475935 .

 

Check SYS.USER_HISTORY$.

There are four rows in the table that contains data for password1, password2, password3, and password4.

 

Task 3: Modify Reuse Time Limit

1. Attempt to reuse password1.

By changing the password1 password three times, you met the PASSWORD_REUSE_MAX limit of 2. However, since PASSWORD_REUSE_TIME is set to UNLIMITED, you cannot reuse the password.

 

2. Modify PASSWORD_REUSE_TIME for the profile1 profile.

The password reuse time is 5 minutes.

 

3. Check system time. Sleep 300 seconds (= 5 minutes). Check system time again.

 

4. Use SYS$USER_HISTORY to review the password dates for testuser1‘s passwords.

The system date of 2022-06-02 15:44:20 is at least 5 minutes greater than the dates of the four passwords. This meets the requirements of the PASSWORD_REUSE_TIME limit of 5 minutes, indicating that the four passwords can be reused if the password has been changed at least PASSWORD_REUSE_MAX times (=2). Note that password1 and password2 have been changed at least two times. However, password3 and password4 have not.

 

5. Attempt to reuse password3 and password4.

Since these passwords have not been changed at least 2 times, they cannot be reused.

 

6. Attempt to reuse password2.

The operation succeeds. Since you changed password2 at least 2 times, you can reuse it.

 

7. Use SYS.USER$ to observe the new password.

TimesTen XE updates ptime with the password change time.

 

8. Use SYS.USER_HISTORY$ to review the password history table.

TimesTen XE does the following:

• Deletes password1 from the table. You can reuse it.
• Adds the password2 password you just reused to the table (2022-06-02 15:55:56.361438).
• Makes no modifications to the password3 and password4 entries.

 

Task 4: Clean Up

1. Drop the testuser1 user.

 

2. Drop the profile1 profile.

 

3. Check SYS.USER_HISTORY$.

TimesTen XE correctly deleted all rows for testuser1.

 

Summary

In TimesTen XE SQL Profiles (Part 1) and this blog, you learned how to use SQL profiles to set password limits, increasing the level of security when accessing TimesTen XE databases.

 

Learn More about TimesTen XE SQL Profiles

• Blog: TimesTen XE SQL Profiles (Part 1)
• SQL statements in the Oracle TimesTen In-Memory Database SQL Reference. There are several examples in these sections.
  • CREATE PROFILE
  • ALTER PROFILE
  • DROP PROFILE
  • CREATE USER
  • ALTER USER

 

Learn More About TimesTen XE

• TimesTen XE Home Page
• TimesTen XE Download
• TimesTen XE Docker Container
• TimesTen Classic Home Page
• TimesTen Scaleout Home Page
• TimesTen VM with Hands On Labs
• TimesTen Documentation

 

Additional TimesTen XE Blogs

• What is TimesTen XE
• How fast is TimesTen XE
• How to install TimesTen XE
• How to create a database on TimesTen XE
• TimesTen XE SQL
• Using TimesTen XE on WSL
• Using client/server without config files on TimesTen XE
• Using client/server with config files on TimesTen XE

 

Disclaimer: These are my personal thoughts and do not represent Oracle’s official viewpoint in any way, shape, or form.

Feel free to like or share this article. Welcome your comments.