CREATE PROFILE

The CREATE PROFILE statement creates a profile, which is a set of limits on the database resources. If you assign a profile to a user, that user cannot exceed the limits specified in the profile.

Required Privilege

ADMIN

Usage with TimesTen Scaleout

This statement is supported with TimesTen Scaleout.

SQL Syntax

CREATE PROFILE profile LIMIT password_parameters

password_parameters::=
[FAILED_LOGIN_ATTEMPTS password_parameter_options]
[PASSWORD_LIFE_TIME password_parameter_options]
[PASSWORD_REUSE_TIME password_parameter_options]
[PASSWORD_REUSE_MAX password_parameter_options]
[PASSWORD_LOCK_TIME password_parameter_options]
[PASSWORD_GRACE_TIME password_parameter_options]
[{PASSWORD_COMPLEXITY_CHECKER|PASSWORD_VERIFY_FUNCTION} password_checker_options]

password_parameter_options::=
UNLIMITED|DEFAULT|constant

password_checker_options::=
function|NULL|DEFAULT

function::
TT_VERIFY_FUNCTION|TT_STRONG_VERIFY_FUNCTION|TT_STIG_VERIFY_FUNCTION

Parameters

Parameter	Description
`profile`	Name of the profile.
`LIMIT` `password_parameters`	The `LIMIT` clause sets the limits for the password parameters. The `LIMIT` keyword is required. The password parameters consist of the name of the password parameter and the value (or limit) for the password parameter. This includes the password complexity checker functions. All the parameters (with the exception of `FAILED_LOGIN_ATTEMPTS` and `PASSWORD_REUSE_MAX`) set lengths of time and are interpreted in number of days. You can use a decimal value (for example, you can use `.0833` to denote approximately one hour). The minimum value is `1` second. The maximum value is `106`,`751`,`991` days. The constant value must be expressed in days. For example, to set a value of `5` minutes, specify the constant value of `0.0034722222222222` (`5/1440` days). For `FAILED_LOGIN_ATTEMPTS` and `PASSWORD_REUSE_MAX`, you must specify an integer. If you do not specify a password parameter after the `LIMIT` clause, the limit for that password parameter is based on the limit defined in the `DEFAULT` profile. In addition, if you only specify the `LIMIT` keyword with no additional parameters, the limits for the profile are based on the limits of the `DEFAULT` profile.
`FAILED_LOGIN_ATTEMPTS`	Specifies the number of consecutive failed attempts to connect to the database by a user before that user's account is locked.
`PASSWORD_LIFE_TIME`	Specifies the number of days that a user can use the same password for authentication. If you also set a value for `PASSWORD_GRACE_TIME`, then the password expires if it is not changed within the grace period. In such a situation, future connections to the database are rejected.
`PASSWORD_REUSE_TIME` and `PASSWORD_REUSE_MAX`	These two parameters must be used together. `PASSWORD_REUSE_TIME` specifies the number of days that must pass before a user can reuse a password. For example, if you specify a value of `30`, then after 30 days the user can reuse a previous password. `PASSWORD_REUSE_MAX` specifies the number of password changes that are required before the current password can be reused. You must specify a value for both parameters for them to have any effect. Specifically: If you specify a value for both parameters: A user cannot reuse a password until the password has been changed the number of times specified for `PASSWORD_REUSE_MAX` during the number of days specified for `PASSWORD_REUSE_TIME`. For example, if you specify a value of `30` for `PASSWORD_REUSE_TIME` and a value of `10` for `PASSWORD_REUSE_MAX`, then the user can reuse the password after 30 days if the password has been changed 10 times. If you specify a value for one parameter and specify a value of `UNLIMITED` for the second parameter, then the user can never reuse a password. If you specify a value of `UNLIMITED` for both parameters, then TimesTen ignores both values, indicating that the password can be reused.
`PASSWORD_LOCK_TIME`	Specifies the number of days the user account is locked after the specified number of consecutive failed connection attempts.
`PASSWORD_GRACE_TIME`	Specifies the number of days after the grace period begins during which TimesTen issues a warning, but allows the connection to the database. If the password is not changed during the grace period, the password expires. This parameter is associated with the `PASSWORD_LIFE_TIME` parameter.
`UNLIMITED`	Indicates that there is no limit for the password parameter. If you specify `UNLIMITED`, it must follow the password parameter. For example, `FAILED_LOGIN_ATTEMPTS` `UNLIMITED`.
`DEFAULT`	Indicates that you want to omit a limit for the password parameter in this profile. A user that is assigned this profile is subject to the limit defined in the `DEFAULT` profile for this password parameter. If you specify `DEFAULT`, it must follow the password parameter. For example, `FAILED_LOGIN_ATTEMPTS` `DEFAULT`.
`constant`	Indicates the value of the password parameter if you do not specify `UNLIMITED` or `DEFAULT`. If specified, it must follow the password parameter. For example, `FAILED_LOGIN_ATTEMPTS` `3`.
`{PASSWORD_COMPLEXITY_CHECKER\|` `PASSWORD_VERIFY_FUNCTION}` `{function\|NULL\|DEFAULT}`	Indicates if password verification is done on passwords and, if so, the function used for verification. You can specify either the `PASSWORD_COMPLEXITY_CHECKER` or the `PASSWORD_VERIFY_FUNCTION` password parameter. They are synonymous. `function` refers to one of the three supported password complexity checker functions. Specify one of these functions to direct TimesTen to perform password verification. Valid values: `TT_VERIFY_FUNCTION` `TT_STRONG_VERIFY_FUNCTION` `TT_STIG_VERIFY_FUNCTION` `NULL` indicates that there is not a password verification function assigned for the profile. `DEFAULT` indicates that the user is subject to the limits defined by the `DEFAULT` profile. The `DEFAULT` profile initially has a value of `NULL`. If you do not specify the `PASSWORD_COMPLEXITY_CHECKER` password parameter, the value defaults to the limits defined for the `DEFAULT` profile.

Description: PROFILE Statement

Use the CREATE PROFILE statement to create a profile for the password resources, which is a set of limits for the password parameters. If you assign the profile to a user, the user cannot exceed the limits specified for the profile. If you do not assign a profile to a user, TimesTen assigns the DEFAULT profile. See Password Management in the Oracle TimesTen In-Memory Database Security Guide for more information on password management and profiles.
To specify the password parameter limits for a user, do the following:
- Use the CREATE PROFILE statement to create a profile that defines the password parameter limits.
- Use the CREATE USER or ALTER USER statement to assign the profile to the user.
There is a DEFAULT profile that defines a limit for each of the password parameters. This profile initially defines UNLIMITED for these parameters (which indicates that no limit has been set for the parameter). The exceptions are:
- FAILED_LOGIN_ATTEMPTS: Set to 10.
- PASSWORD_LOCK_TIME: Set to 0.0034722222222222 days (equal to 5 minutes, 5/1440 days)
- PASSWORD_COMPLEXITY_CHECKER: Set to NULL.
You can change these limits by using the ALTER PROFILE statement and specifying "DEFAULT" for the profile name. (Note that DEFAULT must be enclosed in double quotation marks.) See ALTER PROFILE for information.
If a user is not assigned a profile, the user is subject to the limits defined in the DEFAULT profile. If a user is assigned a profile and that profile omits a limit on the password parameter or specifies DEFAULT for the password parameter, then the user is subject to the limits on those password parameters as defined by the DEFAULT profile.
The instance administrator is assigned a system profile. You cannot alter or drop the profile of an instance administrator.

About Password Complexity Checker Verification

Password complexity checker verification ensures the password for a user is complex enough to deter intruders who try to guess passwords. When you specify a password complexity checker function in the CREATE PROFILE statement, and then assign this profile to a user, the user must create a password that meets the requirements defined for the password complexity checker function. These requirements depend on the specific password complexity checker function that you specify.

TimesTen provides the TT_VERIFY_FUNCTION, the TT_STRONG_VERIFY_FUNCTION, and the TT_STIG_VERIFY_FUNCTION password complexity checker functions to manage the complexity of the passwords. These functions are stored in the SYS schema.

The characters of interest for the password complexity checker functions are letters, digits, and special characters:

letter: Uppercase and lowercase letters
digit: 0-9 numbers
special: A character that is neither a letter nor a digit.
```
`~!@#$%^&*()_-+={}[]\/<>,.?':|(space)
```
Note:
- If you use one or more of the special characters, the entire password must be enclosed in double quotation marks ("). The exceptions are the # and the @ special characters. (A password that contains the # or the @ does not need to be enclosed in double quotation marks.)
- The password cannot contain a semicolon (;) or a double quotation mark (").
- The password must begin with a letter unless you enclose the entire password in double quotation marks.

You cannot define your own function for password complexity checker verification. The complexity of the password is checked when you use the IDENTIFIED BY clause in the CREATE USER or ALTER USER statements.

Here are the password complexity checker functions:

TT_VERIFY_FUNCTION

The TT_VERIFY_FUNCTION does the following password complexity checker verification:

A password:
- Must have at least 8 characters.
- Of these 8 characters, must contain at least one letter, at least one digit, and at least one special character. .
A password cannot contain:
- Username or the username reversed
- Database name
- Oracle or TimesTen
Note:
The comparisons are case insensitive.

TT_STRONG_VERIFY_FUNCTION

The TT_STRONG_VERIFY_FUNCTION performs the following password complexity checker verification:

Must have at least 9 characters.
Of these 9 characters, must contain at least two uppercase letters, at least two lowercase letters, at least two digits, and at least two special characters.

TT_STIG_VERIFY_FUNCTION

The TT_STIG_VERIFY_FUNCTION performs the following password complexity checker verification:

Must have at least 15 characters.
Of these 15 characters, must contain at least one uppercase letter, at least one lowercase letter, at least one digit, and at least one special character.

Description: Password Complexity Checker Verification

EXECUTE privilege on TT_VERIFY_FUNCTION, TT_STRONG_VERIFY_FUNCTION, and TT_STIG_VERIFY_FUNCTION is required. TimesTen grants the EXECUTE privilege on these functions to PUBLIC by default.
The SYSTEM and the DEFAULT profiles are assigned a value of a NULL by default. A NULL value indicates that there is no password complexity checker function for these profiles, and as such, there is no password complexity checker verification done.
You cannot modify the SYSTEM profile to specify a password complexity checker function. Passwords for system users do not undergo password complexity checker verification.
You can use the ALTER PROFILE statement to modify the DEFAULT profile to specify a password complexity checker function. You specify such a function in the PASSWORD_COMPLEXITY_CHECKER (or PASSWORD_VERIFY_FUNCTION) clause.
The TT_VERIFY_FUNCTION, TT_STRONG_VERIFY_FUNCTION, and TT_STIG_VERIFY_FUNCTION functions in TimesTen are equivalent to the ORA12C_VERIFY_FUNCTION, ORA12C_STRONG_VERIFY_FUNCTION, and ORA12C_STIG_VERIFY_FUNCTION functions in Oracle Database.
If you use the ttMigrate utility to downgrade to an earlier major release (such as the 18.1 release), the PASSWORD_COMPLEXITY_CHECKER value is set to NULL for each profile in the database.

Restrictions on the Password Complexity Checker Functions

There are restrictions on the password complexity checker functions:

You cannot specify the SYS schema for the password complexity checker function. For example:

Command> CREATE PROFILE my_profile LIMIT 
           PASSWORD_COMPLEXITY_CHECKER SYS.TT_VERIFY_FUNCTION;
15187: Cannot specify schema name for password complexity checker function
The command failed.

You cannot define your own password complexity checker function. Use only the TT_VERIFY_FUNCTION,the TT_STRONG_VERIFY_FUNCTION, or the TT_STIG_VERIFY_FUNCTION password complexity checker functions.
The password complexity checker verification is only done on a newly created password. You specify this new password by using the IDENTIFIED BY clause of the CREATE USER or the ALTER USER statement. TimesTen does not verify differences between an old and a new password.
Multi-byte characters are not supported when specifying passwords. TimesTen does not validate passwords with multi-byte characters.

Examples

These examples illustrate various uses of the CREATE PROFILE statement. The examples also show how to use the supported clauses:

Specify TT_VERIFY_FUNCTION for PASSWORD_COMPLEXITY_CHECKER

This example first creates a profile and specifies the TT_VERIFY_FUNCTION function. The example then queries the dba_profiles system view to verify the TT_VERIFY_FUNCTION has been assigned to this profile. A user who is assigned this profile must specify a password that meets the password verification requirements for this function.

Command> CREATE PROFILE myprofile_pw1 LIMIT 
           PASSWORD_COMPLEXITY_CHECKER TT_VERIFY_FUNCTION;

Profile created.

Command> SELECT * FROM dba_profiles WHERE profile = 'MYPROFILE_PW1';
< MYPROFILE_PW1, FAILED_LOGIN_ATTEMPTS, PASSWORD, DEFAULT >
< MYPROFILE_PW1, PASSWORD_LIFE_TIME, PASSWORD, DEFAULT >
< MYPROFILE_PW1, PASSWORD_REUSE_TIME, PASSWORD, DEFAULT >
< MYPROFILE_PW1, PASSWORD_REUSE_MAX, PASSWORD, DEFAULT >
< MYPROFILE_PW1, PASSWORD_COMPLEXITY_CHECKER, PASSWORD, TT_VERIFY_FUNCTION >
< MYPROFILE_PW1, PASSWORD_LOCK_TIME, PASSWORD, DEFAULT >
< MYPROFILE_PW1, PASSWORD_GRACE_TIME, PASSWORD, DEFAULT >
< MYPROFILE_PW1, TEMP_SPACE_PER_SESSION_MAX, MEMORY, DEFAULT >
8 rows found.

Create the sampleuser_pw1 user and assign the myprofile_pw1 profile to this user. Specify a password that meets the requirements of the TT_VERIFY_FUNCTION. See TT_VERIFY_FUNCTION for information on the TT_VERIFY_FUNCTION function.

Command> CREATE USER sampleuser_pw1 
           IDENTIFIED BY "A1!XXcg3" PROFILE myprofile_pw1;

User created.

Attempt to create the sampleuser_pw2. Assign the myprofile_pw1 profile to the user. Specify a password that contains the username reversed in uppercase. The CREATE USER statement fails. The password cannot contain the username reversed. Note that the comparison is case insensitive.

Command> CREATE USER sampleuser_pw2 
           IDENTIFIED BY "2WP_RESUELPMAS" PROFILE myprofile_pw1;
15186: Password complexity check for the specified password failed
15188: TT-20002: Password contains the username reversed
The command failed.

Specify TT_STRONG_VERIFY_FUNCTION for PASSWORD_COMPLEXITY_CHECKER

This example creates the myprofile_pw2 profile and specifies TT_STRONG_VERIFY_FUNCTION for the PASSWORD_COMPLEXITY_CHECKER password parameter. The example then creates the sampleuser_pw2 user, assigns the myprofile_pw2 profile to the user. The password meets the requirements of the TT_STRONG_VERIFY_FUNCTION function. See TT_STRONG_VERIFY_FUNCTION for more information on the TT_STRONG_VERIFY_FUNCTION function.

Command> CREATE PROFILE myprofile_pw2 LIMIT 
           PASSWORD_COMPLEXITY_CHECKER TT_STRONG_VERIFY_FUNCTION;

Profile created.

Create the sampleuser_pw2, assign the myprofile_pw2 profile to the user. The password meets the requirements of the TT_STRONG_VERIFY_FUNCTION function. The user is successfully created.

Command> CREATE USER sampleuser_pw2 
           IDENTIFIED BY "!ddFF6C2?" PROFILE myprofile_pw2;

User created.

Specify TT_STIG_VERIFY_FUNCTION for PASSWORD_COMPLEXITY_CHECKER

This example creates the myprofile_pw3 profile and specifies TT_STIG_VERIFY_FUNCTION for the PASSWORD_COMPLEXITY_CHECKER password parameter. The example then creates the sampleuser_pw3 user, assigns the myprofile_pw3 profile to the user. The password meets the requirements of the TT_STIG_VERIFY_FUNCTION function. See TT_STIG_VERIFY_FUNCTION for more information on the TT_STIG_VERIFY_FUNCTION function.

Command> CREATE PROFILE myprofile_pw3 LIMIT 
           PASSWORD_COMPLEXITY_CHECKER TT_STIG_VERIFY_FUNCTION;

Profile created.

Create the sampleuser_pw3, assign the myprofile_pw3 profile to the user. The password meets the requirements of the TT_STIG_VERIFY_FUNCTION function. The user is successfully created.

Command> CREATE USER sampleuser_pw3 
           IDENTIFIED BY "!ddBBKKUYT165>m" PROFILE myprofile_pw3;

User created.

Modify PASSWORD_COMPLEXITY_CHECKER Value for SYSTEM and DEFAULT

This example queries the dba_profiles system view to check the value of the PASSWORD_COMPLEXITY_CHECKER password parameter for the SYSTEM and the DEFAULT profiles. The value is NULL by default.

Command> SELECT * FROM dba_profiles WHERE 
           resource_name='PASSWORD_COMPLEXITY_CHECKER' AND 
           profile IN ('DEFAULT','SYSTEM');
< DEFAULT, PASSWORD_COMPLEXITY_CHECKER, PASSWORD, NULL >
< SYSTEM, PASSWORD_COMPLEXITY_CHECKER, PASSWORD, NULL >
2 rows found.

Attempt to modify the PASSWORD_COMPLEXITY_CHECKER password parameter for the SYSTEM profile. An error results as this password parameter cannot be modified.

Command> ALTER PROFILE SYSTEM LIMIT 
           PASSWORD_COMPLEXITY_CHECKER TT_STRONG_VERIFY_FUNCTION;
15176: Profile SYSTEM cannot be altered
The command failed.

Attempt to modify the PASSWORD_COMPLEXITY_CHECKER password parameter for the DEFAULT profile. The modification is successful. A user who is assigned the DEFAULT profile, or is not assigned a profile, must specify a password that meets the password verification requirements for the TT_STRONG_VERIFY_FUNCTION function.

Command> ALTER PROFILE "DEFAULT" LIMIT 
           PASSWORD_COMPLEXITY_CHECKER TT_STRONG_VERIFY_FUNCTION;

Profile altered

Query the dba_profiles view to verify the TT_STRONG_VERIFY_FUNCTION has been assigned to the DEFAULT profile.

Command> SELECT * FROM dba_profiles WHERE 
           resource_name='PASSWORD_COMPLEXITY_CHECKER' AND 
           profile = 'DEFAULT';
< DEFAULT, PASSWORD_COMPLEXITY_CHECKER, PASSWORD, TT_STRONG_VERIFY_FUNCTION >
1 row found.

Create a Profile and Attempt to Specify an Invalid Password Complexity Checker Function

This example specifies an invalid password complexity checker function for the PASSWORD_COMPLEXITY_CHECKER clause. Even though this function resides in the SYS schema, an error results, as you can only specify one of the three supported password complexity checker functions.

Command> CREATE PROFILE myprofile1 LIMIT 
           PASSWORD_COMPLEXITY_CHECKER TT_COMPLEXITY_CHECK;
 8529: Invalid password complexity checker function TT_COMPLEXITY_CHECK
The command failed.

Create a Profile and Set Limits on the Password Parameters

This example creates the profile1 profile and sets various limits on the password parameters. It then queries the dba_profiles system view to verify the limits.

Command> CREATE PROFILE profile1 LIMIT 
           FAILED_LOGIN_ATTEMPTS 5 
           PASSWORD_LIFE_TIME 60 
           PASSWORD_REUSE_TIME 60 
           PASSWORD_REUSE_MAX 5 
           PASSWORD_LOCK_TIME 1 
           PASSWORD_GRACE_TIME 10;
 
Profile created.

Query the dba_profiles system view to verify the limits. Note that since the PASSWORD_COMPLEXITY_CHECKER password parameter was not specified in the CREATE PROFILE statement, the value of PASSWORD_COMPLEXITY_CHECKER is DEFAULT (the value comes from the value that is in the DEFAULT profile).

Command> SELECT * FROM dba_profiles WHERE profile = 'PROFILE1' AND 
           resource_type='PASSWORD';
< PROFILE1, FAILED_LOGIN_ATTEMPTS, PASSWORD, 5 >
< PROFILE1, PASSWORD_LIFE_TIME, PASSWORD, 60 >
< PROFILE1, PASSWORD_REUSE_TIME, PASSWORD, 60 >
< PROFILE1, PASSWORD_REUSE_MAX, PASSWORD, 5 >
< PROFILE1, PASSWORD_COMPLEXITY_CHECKER, PASSWORD, DEFAULT >
< PROFILE1, PASSWORD_LOCK_TIME, PASSWORD, 1 >
< PROFILE1, PASSWORD_GRACE_TIME, PASSWORD, 10 >
7 rows found.

Create a Profile and Specify FAILED_LOGIN_ATTEMPTS

This example creates the profile2 profile and specifies a value of 1 for FAILED_LOGIN_ATTEMPTS. The example then creates the user2 user and assigns user2 the profile2 profile. The user2 user attempts to connect to the database, but specifies an invalid password. The connection fails. After five minutes, the user2 user attempts to reconnect to the database. The connection succeeds due to the 0.0034722222222222 (equal to 5 minutes) value for PASSWORD_LOCK_TIME (specified in the DEFAULT profile).

Command> CREATE PROFILE profile2 LIMIT FAILED_LOGIN_ATTEMPTS 1;
 
Profile created.
 
Command> CREATE USER user2 IDENTIFIED BY user2 PROFILE profile2;
 
User created.

Grant admin privilege to user2.

Command> GRANT ADMIN TO user2;

Attempt to connect to the database. The connection fails due to an invalid password specified in the connection string.

Command> connect adding "UID=user2;PWD=user3" as user2;
 7001: User authentication failed
The command failed.

Attempt to connect again specifying the correct password in the connection string. The connection fails due to:

One previous failed connection attempt
An attempt to connect to the database before the five minute password lock time.

none: Command> use database1
database1: Command> connect adding "UID=user2;PWD=user2" as user2;
15179: the account is locked
The command failed.

After five minutes, attempt to connect to the database again. The connection succeeds.

none: Command> use database1
database1: Command> connect adding "UID=user2;PWD=user2" as user2;
Connection successful: DSN=database1;UID=user2;DataStore=/scratch/database1;
DatabaseCharacterSet=AL32UTF8;ConnectionCharacterSet=AL32UTF8;PermSize=128;
(Default setting AutoCommit=1)

Determine the Password Parameter Values in the DEFAULT Profile

This example queries the dba_profiles system view to determine the password parameter values for the DEFAULT profile.

Command> SELECT * FROM dba_profiles WHERE profile = 'DEFAULT' AND 
           resource_type='PASSWORD';
< DEFAULT, FAILED_LOGIN_ATTEMPTS, PASSWORD, 10 >
< DEFAULT, PASSWORD_LIFE_TIME, PASSWORD, UNLIMITED >
< DEFAULT, PASSWORD_REUSE_TIME, PASSWORD, UNLIMITED >
< DEFAULT, PASSWORD_REUSE_MAX, PASSWORD, UNLIMITED >
< DEFAULT, PASSWORD_COMPLEXITY_CHECKER, PASSWORD, NULL >
< DEFAULT, PASSWORD_LOCK_TIME, PASSWORD, .0034 >
< DEFAULT, PASSWORD_GRACE_TIME, PASSWORD, UNLIMITED >
7 rows found.

Specify PASSWORD_LIFE_TIME and PASSWORD_GRACE_TIME

This example creates the profile4 profile and specifies a value of 0.0034722222222222 (equal to 5 minutes) for the PASSWORD_LIFE_TIME password parameter and a value of 0.01041667 (equal to 15 minutes) for the PASSWORD_GRACE_TIME password parameter. It then creates the user4 user and assigns the profile4 profile to user4. The example continues with attempts to connect to the database as user4.

Command> CREATE PROFILE profile4 LIMIT 
           PASSWORD_LIFE_TIME 0.0034722222222222 
           PASSWORD_GRACE_TIME 0.01041667;
 
Profile created.

Query the dba_profiles system view to verify the values for the password parameters.

Command> SELECT * FROM dba_profiles WHERE profile = 'PROFILE4' AND 
           resource_type='PASSWORD';
< PROFILE2, FAILED_LOGIN_ATTEMPTS, PASSWORD, DEFAULT >
< PROFILE2, PASSWORD_LIFE_TIME, PASSWORD, .0034 >
< PROFILE2, PASSWORD_REUSE_TIME, PASSWORD, DEFAULT >
< PROFILE2, PASSWORD_REUSE_MAX, PASSWORD, DEFAULT >
< PROFILE2, PASSWORD_COMPLEXITY_CHECKER, PASSWORD, DEFAULT >
< PROFILE2, PASSWORD_LOCK_TIME, PASSWORD, DEFAULT >
< PROFILE2, PASSWORD_GRACE_TIME, PASSWORD, .0104 >
7 rows found.

Create the user4 user and assign user4 the profile4 profile. Grant the CONNECT privilege to user4.

Command> CREATE USER user4 IDENTIFIED BY user4 PROFILE profile4;
 
User created.
 
Command> GRANT CONNECT TO user4;

Connect to the database as user4. The connection succeeds.

Command> connect adding "UID=user4;PWD=user4" as user4;
Connection successful: DSN=access1;UID=user4;DataStore=/scratch/database1;
DatabaseCharacterSet=AL32UTF8;ConnectionCharacterSet=AL32UTF8;PermSize=128;
(Default setting AutoCommit=1)

Disconnect from the database. After 5 minutes, reconnect to the database as user4. The connection succeeds but a warning is issued. The password lifetime is 5 minutes and the password grace time is 15 minutes.

user4: Command> disconnect user4;
Disconnecting from user4...
none: Command> use database1
database1: Command> connect adding "UID=user4;PWD=user4" as user4;
 
Warning 15182: Password will expire within 0.010417 days
 
Connection successful: DSN=access1;UID=user4;DataStore=/scratch/database1;
DatabaseCharacterSet=AL32UTF8;ConnectionCharacterSet=AL32UTF8;PermSize=128;
(Default setting AutoCommit=1)

Disconnect from the database. After 15 minutes, reconnect to the database as user4. The connection fails as the password grace time of 15 minutes has ended.

user4: Command> disconnect user4;
Disconnecting from user4...
none: Command> use database1
database1: Command> connect adding "UID=user4;PWD=user4" as user4;
15180: the password has expired
The command failed.

Create a Profile Specifying Only the LIMIT Keyword

This example creates the profile5 profile and specifies just the LIMIT keyword. The example then queries the dba_profiles system view to illustrate the password parameter limits for the profile5 profile are all set to a value of DEFAULT.

Command> CREATE PROFILE profile5 LIMIT;
 
Profile created.
Command> SELECT * FROM dba_profiles WHERE profile = 'PROFILE5' AND 
           resource_type='PASSWORD
< PROFILE5, FAILED_LOGIN_ATTEMPTS, PASSWORD, DEFAULT >
< PROFILE5, PASSWORD_LIFE_TIME, PASSWORD, DEFAULT >
< PROFILE5, PASSWORD_REUSE_TIME, PASSWORD, DEFAULT >
< PROFILE5, PASSWORD_REUSE_MAX, PASSWORD, DEFAULT >
< PROFILE5, PASSWORD_COMPLEXITY_CHECKER, PASSWORD, DEFAULT >
< PROFILE5, PASSWORD_LOCK_TIME, PASSWORD, DEFAULT >
< PROFILE5, PASSWORD_GRACE_TIME, PASSWORD, DEFAULT >
7 rows found.

Specify UNLIMITED for PASSWORD_REUSE_TIME

This example creates the profile6 profile and specifies a PASSWORD_REUSE_TIME of UNLIMITED. The password cannot be reused.

Command> CREATE PROFILE profile6 LIMIT 
           PASSWORD_REUSE_MAX 2 
           PASSWORD_REUSE_TIME UNLIMITED;
 
Profile created.

Create the user6 user and assign user6 the profile6 profile. Change the user6 password two times. Attempt to reuse the user6 password. The attempt fails due to the PASSWORD_REUSE_TIME value of UNLIMITED.

Command> CREATE USER user6 IDENTIFIED BY user6 PROFILE profile6;
 
User created.
 
Command> ALTER USER user6 IDENTIFIED BY user6_test1;
 
User altered.
 
Command> ALTER USER user6 IDENTIFIED BY user6_test2;
 
User altered.
 
Command> ALTER USER user6 IDENTIFIED BY user6;
15183: Password cannot be reused
The command failed.

Specify DEFAULT for PASSWORD_REUSE_TIME

This example creates the profile7 profile, specifying the value of DEFAULT for the PASSWORD_REUSE_TIME password parameter and the value of 3 for the PASSWORD_REUSE_MAX password parameter. TimesTen uses the value in the DEFAULT profile for the PASSWORD_REUSE_TIME password parameter.

Command> CREATE PROFILE profile7 LIMIT 
           PASSWORD_REUSE_TIME DEFAULT 
           PASSWORD_REUSE_MAX 3;
 
Profile created.

Query the dba_profiles system view to verify the password parameter values for the profile7 profile. Note the value of DEFAULT for PASSWORD_REUSE_TIME and a value of 3 for PASSWORD_REUSE_MAX (represented in bold).

Command> SELECT * FROM dba_profiles WHERE profile = 'PROFILE7' AND
           resource_type = 'PASSWORD';
< PROFILE7, FAILED_LOGIN_ATTEMPTS, PASSWORD, DEFAULT >
< PROFILE7, PASSWORD_LIFE_TIME, PASSWORD, DEFAULT >
< PROFILE7, PASSWORD_REUSE_TIME, PASSWORD, DEFAULT >
< PROFILE7, PASSWORD_REUSE_MAX, PASSWORD, 3 >
< PROFILE7, PASSWORD_COMPLEXITY_CHECKER, PASSWORD, DEFAULT >
< PROFILE7, PASSWORD_LOCK_TIME, PASSWORD, DEFAULT >
< PROFILE7, PASSWORD_GRACE_TIME, PASSWORD, DEFAULT >
7 rows found.

Query the dba_profiles system view to verify the password parameter values for the DEFAULT profile. Note the value of UNLIMITED for PASSWORD_REUSE_TIME (represented in bold).

Command> SELECT * FROM dba_profiles WHERE profile = 'DEFAULT' AND
           resource_type = 'PASSWORD';
< DEFAULT, FAILED_LOGIN_ATTEMPTS, PASSWORD, 10 >
< DEFAULT, PASSWORD_LIFE_TIME, PASSWORD, UNLIMITED >
< DEFAULT, PASSWORD_REUSE_TIME, PASSWORD, UNLIMITED >
< DEFAULT, PASSWORD_REUSE_MAX, PASSWORD, UNLIMITED >
< DEFAULT, PASSWORD_COMPLEXITY_CHECKER, PASSWORD, NULL >
< DEFAULT, PASSWORD_LOCK_TIME, PASSWORD, .0034 >
< DEFAULT, PASSWORD_GRACE_TIME, PASSWORD, UNLIMITED >
7 rows found.

Create the user7 user and assign the profile7 profile to user7. Change the user7 password three times. The user7 password cannot be reused due to the value of UNLIMITED for the PASSWORD_REUSE_TIME parameter.

Command> CREATE USER user7 IDENTIFIED BY user7 PROFILE profile7;
 
User created.
 
Command> ALTER USER user7 IDENTIFIED BY user7_test1;
 
User altered.
 
Command> ALTER USER user7 IDENTIFIED BY user7_test2;
 
User altered.
 
Command> ALTER USER user7 IDENTIFIED BY user_test3;
 
User altered.
 
Command> ALTER USER user7 IDENTIFIED BY user7;
15183: Password cannot be reused
The command failed.

Specify PASSWORD_REUSE_TIME and PASSWORD_REUSE_MAX

This example creates the profile8 profile, specifying a value of 0.0020833 (equal to approximately 2 minutes) for the PASSWORD_REUSE_TIME password parameter and a value of 2 for the PASSWORD_REUSE_MAX password parameter. The example then creates the user8 user and assigns user8 the profile8 profile. The user8 password is changed two times within two minutes. Then, still within the two minutes, the original user8 password (user8_pwd) is reused. The ALTER USER operation fails. Even though the password is changed 2 times, the original password can only be reused after 0.00208333 days (equal to approximately two minutes). After two minutes, the original user8 password (user8_pwd) is reused again. The ALTER USER operation succeeds. The user's password was changed two times and more than two minutes had passed.

Command> CREATE PROFILE profile8 LIMIT 
           PASSWORD_REUSE_TIME 0.00208333 
           PASSWORD_REUSE_MAX 2;
 
Profile created.

Create the user8 user and assign user8 the profile8 profile.

Command> CREATE USER user8 IDENTIFIED BY user8_pwd PROFILE profile8;
 
User created.

Immediately alter the user, changing the password two times.

Command> ALTER USER user8 IDENTIFIED BY user8_test1;
 
User altered.
 
Command> ALTER USER user8 IDENTIFIED BY user8_test2;
 
User altered.

Within two minutes, attempt to reuse the original user8_pwd password (represented in bold). The ALTER USER operation fails as the original password can only be reused after two minutes.

Command> ALTER USER user8 IDENTIFIED BY user8_pwd;
15183: Password cannot be reused
The command failed.

After two minutes, attempt to reuse the original user8_pwd password (represented in bold). The ALTER USER operation succeeds. The original password can be reused as the password was changed two times and two minutes had expired.

Command> ALTER USER user8 IDENTIFIED BY user8_pwd;
 
User altered.