ALTER PROFILE

The ALTER PROFILE statement adds, modifies, or removes one or more password parameters in a profile.

Required privilege

ADMIN

Usage with TimesTen Scaleout

This statement is supported with TimesTen Scaleout.

SQL syntax

ALTER 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

Use the ALTER PROFILE statement to modify a previously created profile. See "CREATE PROFILE" for information on creating a profile.
Changes made using the ALTER PROFILE statement takes effect the next time any affected user connected to the database. The exception is when you modify the PASSWORD_COMPLEXITY_CHECKER password parameter. Password verification is only done on newly created passwords (on the password provided in the IDENTIFIED BY clause of the CREATE USER or ALTER USER statement). Therefore, a user can connect to the database with an old password. See "ALTER the PASSWORD_COMPLEXITY_CHECKER password parameter" for an example.
You can alter the DEFAULT profile. However, you cannot drop the DEFAULT profile. See "Alter the DEFAULT profile" for an example of altering the DEFAULT profile.
You cannot alter the password parameters of the SYSTEM profile. This profile is assigned to system users, including the instance administrator.
You can alter the profile to change the password verification that is done on the passwords of users that are assigned the profile. See "About Password Complexity Checker Verification" for information on password verification and the password complexity checker verification functions.

Examples

ALTER the PASSWORD_COMPLEXITY_CHECKER password parameter

This example creates the myprofile_alterpw1 profile and specifies TT_VERIFY_FUNCTION for the PASSWORD_COMPLEXITY_CHECKER password parameter. The example then creates the sampleuser_alterpw1 user and assigns the myprofile_alterpw1 profile to the sampleuser_alterpw1 user. The example alters the profile, specifying TT_STIG_VERIFY_FUNCTION for the PASSWORD_COMPLEXITY_CHECKER password parameter. The sampleuser_alterpw1 attempts to connect to the database with the original password. The connection is successful. TimesTen does not perform password verification on old passwords. The example then uses the ALTER USER statement to change the sampleuser_alterpw1 user password to meet the requirements of the TT_STIG_VERIFY_FUNCTION. The ALTER USER statement succeeds and the user's password is changed.

Command> CREATE PROFILE myprofile_alterpw1 LIMIT 
           PASSWORD_COMPLEXITY_CHECKER TT_VERIFY_FUNCTION;

Profile created.

Command> CREATE USER sampleuser_alterpw1 
           IDENTIFIED BY "%aabb2L90" PROFILE myprofile_alterpw1;

User created.

Alter the myprofile_alterpw1 profile, changing the value of PASSWORD_COMPLEXITY_CHECKER to TT_STIG_VERIFY_FUNCTION. Connect to the database as the sampleuser_alterpw1 user. The connection succeeds.

Command> ALTER PROFILE myprofile_alterpw1 LIMIT 
           PASSWORD_COMPLEXITY_CHECKER TT_STIG_VERIFY_FUNCTION;

Profile altered.

Command> GRANT CONNECT TO sampleuser_alterpw1;
Command> connect adding "UID=sampleuser_alterpw1;PWD=%aabb2L90" as sampleuser;
Connection successful: DSN=access1;UID=sampleuser_alterpw1;
DataStore=/scratch/sampleuser/mydatabase1;DatabaseCharacterSet=AL32UTF8;
ConnectionCharacterSet=AL32UTF8;PermSize=128;
(Default setting AutoCommit=1)

Alter the sampleuser_alterpw1 user specifying the same password. The ALTER USER statement fails. The newly created password does not meet the requirements of the TT_STIG_VERIFY_FUNCTION function. Alter the sampleuser_alterpw1 again, specifying a password that meets the requirements of the TT_STIG_VERIFY_FUNCTION function. The ALTER USER statement succeeds. See "TT_STIG_VERIFY_FUNCTION" for information on the TT_STIG_VERIFY_FUNCTION function.

Command> ALTER USER sampleuser_alterpw1 
           IDENTIFIED BY  "%aabb2L90";
15186: Password complexity check for the specified password failed
15188: TT-20001: Password length less than 15
The command failed.

Command> ALTER USER sampleuser_alterpw1 
           IDENTIFIED BY  "%aabb2L##mf5Fn!";

User altered.

Alter the DEFAULT profile

This example verifies the values of the password parameters in the DEFAULT profile. It then alters the profile with different values. Users that are assigned the DEFAULT profile will inherit the modified values at the user's next connection to the database.

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 user1 user and do not specify a profile. User1 is assigned the DEFAULT profile. Use the ALTER PROFILE statement to change the value of the FAILED_LOGIN_ATTEMPTS password parameter to 5 and the value of the PASSWORD_LOCK_TIME password parameter to 1 for the DEFAULT profile. Enclose DEFAULT in double quotation marks as DEFAULT is a reserved word. Connect to the database five times as user1 supplying an incorrect password each time. On the sixth attempt, the user1 account is locked.

Command> CREATE USER user1 IDENTIFIED BY user1;
 
User created.
Command> GRANT CONNECT TO user1;

Query the dba_users system view to verify that user1 is assigned the DEFAULT profile.

Command> SELECT profile FROM dba_users WHERE username='USER1';
< DEFAULT >
1 row found.

Use the ALTER PROFILE statement to modify the DEFAULT profile.

Command> ALTER PROFILE "DEFAULT" LIMIT 
           FAILED_LOGIN_ATTEMPTS 5 
           PASSWORD_LOCK_TIME 1;
 
Profile altered.

Query the dba_profiles system view to verify the values are changed (represented in bold).

Command> SELECT * FROM dba_profiles WHERE profile='DEFAULT' AND 
   resource_type='PASSWORD';
< DEFAULT, FAILED_LOGIN_ATTEMPTS, PASSWORD, 5 >
< 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, 1 >
< DEFAULT, PASSWORD_GRACE_TIME, PASSWORD, UNLIMITED >
7 rows found.

Attempt to connect to the database as user1. Supply an incorrect password. On the sixth attempt, the user1 account is locked.

Command> connect adding "uid=user1;pwd=user1_test1" as user1;
 7001: User authentication failed
The command failed.
none: Command> connect adding "uid=user1;pwd=user1_test2" as user1;
 7001: User authentication failed
The command failed.
none: Command> connect adding "uid=user1;pwd=user1_test3" as user1;
 7001: User authentication failed
The command failed.
none: Command> connect adding "uid=user1;pwd=user1_test4" as user1;
 7001: User authentication failed
The command failed.
none: Command> connect adding "uid=user1;pwd=user1_test5" as user1;
 7001: User authentication failed
The command failed.
none: Command> connect adding "uid=user1;pwd=user1_test6" as user1;
15179: the account is locked
The command failed.

Create a profile then alter the profile

This example creates the profile1 profile and specifies values for the FAILED_LOGIN_ATTEMPTS, the PASSWORD_LIFE_TIME, the PASSWORD_LOCK_TIME, and the PASSWORD_GRACE_TIME password parameters. It then alters the profile1 profile to modify the PASSWORD_REUSE_TIME and the PASSWORD_REUSE_MAX password parameters.

Command> CREATE PROFILE profile1 LIMIT 
           FAILED_LOGIN_ATTEMPTS 3 
           PASSWORD_LIFE_TIME 90 
           PASSWORD_LOCK_TIME 30 
           PASSWORD_GRACE_TIME 10;
 
Profile created.

Query the dba_profiles system view to verify the values for the password parameters. Note that the PASSWORD_REUSE_TIME and the PASSWORD_REUSE_MAX password parameters each have a value of DEFAULT (represented in bold). These password parameters were not specified in the CREATE PROFILE definition, so TimesTen assigns a value of DEFAULT to each parameter. The values for these parameters are derived from the values in the DEFAULT profile.

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

Alter the profile1 profile, specifying a value of 20 for the PASSWORD_REUSE_TIME password and a value of 15 for the PASSWORD_REUSE_MAX password parameter (represented in bold). A user assigned this profile can reuse the same password after 20 days if the password has been changed 15 times.

Command> ALTER PROFILE profile1 LIMIT 
           PASSWORD_REUSE_TIME 20 
           PASSWORD_REUSE_MAX 15;
 
Profile altered.

Query the dba_profiles system view to verify the values for the password parameters are changed (represented in bold).

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