ALTER USER

The ALTER USER statement enables you to change a user's password. It also enables you to change the profile for the user, to lock or unlock the user's account, and to expire the user's password. A user with the ADMIN privilege can perform these operations.

This statement also enables you to change a user from internal to external or from external to internal.

Required Privilege

No privilege is required to change the user's own password.

ADMIN privilege is required for all other operations.

Usage with TimesTen Scaleout

This statement is supported with TimesTen Scaleout.

SQL Syntax

This is the syntax for ALTER USER...IDENTIFIED BY. Ensure to specify at least one of these clauses: IDENTIFIED BY, PROFILE, ACCOUNT, or PASSWORD EXPIRE.

ALTER USER user [IDENTIFIED BY {password | "password"}]
  [PROFILE profile] [ACCOUNT {LOCK|UNLOCK}] [PASSWORD EXPIRE];

This is the syntax for ALTER USER...IDENTIFIED EXTERNALLY. Ensure to specify at least one of these clauses: IDENTIFIED EXTERNALLY, PROFILE, or ACCOUNT.

ALTER USER user [IDENTIFIED EXTERNALLY]
  [PROFILE profile] [ACCOUNT {LOCK|UNLOCK}];

Parameters

Parameter	Description
`user`	Name of the user to alter.
`IDENTIFIED BY password\|"password"`	Specifies an internal user and the password for the internal user. The password you can specify is dependent on the profile assigned to the user. Specifically, the value of the `PASSWORD_COMPLEXITY_CHECKER` password parameter determines the complexity of the password. If the value is `TT_VERIFY_FUNCTION`, `TT_STRONG_VERIFY_FUNCTION`, or `TT_STIG_VERIFY_FUNCTION`, the password must meet specific password verification requirements. For example, if the value is `TT_VERIFY_FUNCTION`, the password cannot contain the name of the database. See About Password Complexity Checker Verification for details.
`IDENTIFIED EXTERNALLY`	Specifies the user is an external user.
`PROFILE` `profile`	Use the `PROFILE` clause to specify the name of the profile (designated by `profile`) that you want to assign to the user. The profile sets the limits for the password parameters for the user. See CREATE PROFILE for information on these password parameters. You can specify a `PROFILE` clause for external users, but the password parameters have no effect for these users.
`ACCOUNT` `[LOCK\|UNLOCK]`	Specify `ACCOUNT` `LOCK` to lock the user's account and disable connections to the database. Specify `ACCOUNT` `UNLOCK` to unlock the user's account and enable connections to the database. The default is `ACCOUNT` `UNLOCK`.
`PASSWORD` `EXPIRE`	Specify `PASSWORD` `EXPIRE` if you want the user's password to expire. This setting forces a user with `ADMIN` privileges to change the password before the user can connect to the database. This clause is not valid for an externally identified user (as denoted by the `IDENTIFIED` `EXTERNALLY` clause).

Description

Database users can be internal or external.
- Internal users are defined for a TimesTen database.
- External users are defined by the operating system. External users cannot be assigned a TimesTen password.
Password requirements:
- Cannot exceed 30 characters.
- Is case-sensitive.
- Must start with a letter. A password cannot start with a digit or a special character unless the password is enclosed in double quotation marks.
- If a special character is used, the password must be contained in double quotation marks. The exceptions are the # and the @ special characters. A password that contains the # or the @ special character does not need to be enclosed in double quotation marks.
- Cannot contain a semi-colon (;) or a double quotation mark (").
Use the PROFILE clause to change the profile for a user. See CREATE PROFILE for details.
Use the ACCOUNT LOCK or ACCOUNT UNLOCK to change the lock settings for the user account.
Use the PASSWORD EXPIRE clause to expire the user's password and force a password change before the user can connect to the database.
You can alter a user over a client/sever connection if the connection is encrypted with TLS. See Transport Layer Security for TimesTen Client/Server in the Oracle TimesTen In-Memory Database Security Guide for details.
When replication is configured, this statement is replicated.

Examples

Illustrate Password Verification When Altering User

This example creates the myprofile_strongpw profile and specifies a value of TT_STRONG_VERIFY_FUNCTION for the PASSWORD_COMPLEXITY_CHECKER password parameter. The example then creates the sampleuser_pwchange user and assigns the myprofile_strongpw profile to this user. The specified password meets the requirements of the TT_STRONG_VERIFY_FUNCTION function and the user is created. See TT_STRONG_VERIFY_FUNCTION for more information on the TT_STRONG_VERIFY_FUNCTION function.

Command> CREATE PROFILE myprofile_strongpw LIMIT 
           PASSWORD_COMPLEXITY_CHECKER TT_STRONG_VERIFY_FUNCTION;

Profile created.

Command> CREATE USER sampleuser_pwchange 
           IDENTIFIED BY "5&AbbN*60" PROFILE myprofile_strongpw;

User created.

Now alter the myprofile_strongpw profile, changing the value of the PASSWORD_COMPLEXITY_CHECKER password parameter to TT_STIG_VERIFY_FUNCTION. Use the ALTER USER statement to expire the password for the sampleuser_pwchange user. Attempt to connect to the database as the sampleuser_pwchange user. The connection fails, as the password is expired.

Command> ALTER PROFILE myprofile_strongpw LIMIT 
           PASSWORD_COMPLEXITY_CHECKER TT_STIG_VERIFY_FUNCTION;

Profile altered.

Command> ALTER USER sampleuser_pwchange PASSWORD EXPIRE;

User altered.

Command> GRANT CONNECT TO sampleuser_pwchange;
Command> connect adding "UID=sampleuser_pwchange;PWD=5&AbbN*60" as sampleuser;
15180: the password has expired
The command failed.

Use the ALTER USER statement to change the password for the sampleuser_pwchange user. The ALTER USER statement succeeds, as the password meets the requirements of the TT_STIG_VERIFY_FUNCTION function. Attempt to connect to the database as the sampleuser_pwchange user. The connection is successful. See TT_STIG_VERIFY_FUNCTION for more information on the TT_STIG_VERIFY_FUNCTION function.

access1: Command> ALTER USER sampleuser_pwchange 
           IDENTIFIED BY "bd@<!BCvvKASn67";

User altered.

Command> connect adding "UID=sampleuser_pwchange;PWD=bd@<!BCvvKASn67" 
           as sampleuser;
Connection successful: DSN=access1;UID=sampleuser_pwchange;
DataStore=/scratch/sampleuser/mydatabase1;DatabaseCharacterSet=AL32UTF8;
ConnectionCharacterSet=AL32UTF8;PermSize=128;
(Default setting AutoCommit=1)

Change the User's Profile

This example creates the user1 user and assigns the user1 user the profile1 profile. The example then uses the ALTER USER statement to change the user1 user's profile to profile2.

Command> CREATE USER user1 IDENTIFIED BY user1 PROFILE profile1;
 
User created.
 
Command> ALTER USER user1 PROFILE profile2;
 
User altered.

Query the dba_users system view to verify the user1 profile has been changed to profile2.

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

Lock and Unlock a User's Account

This example creates the user2 user. It then uses the ALTER USER statement to lock and then unlock the user2 user's account.

Command> CREATE USER user2 IDENTIFIED BY user2 PROFILE profile1;
 
User created.
 
Command> ALTER USER user2 ACCOUNT LOCK;
 
User altered.

Grant the CONNECT privilege to user2;

Command> GRANT CONNECT TO user2;

Attempt to connect to the database as user2. The user2 account is locked so the connection fails.

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

As the instance administrator, reconnect to the database and use the ALTER USER statement to unlock the user2 account.

none: Command> use database1
database1: Command> ALTER USER user2 ACCOUNT UNLOCK;
 
User altered.

Attempt to connect to the database as the user2 user. The connection succeeds.

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

Expire a User's Password

This example uses the ALTER USER statement to change the user2 user's account to expire the password. A user with ADMIN privilege must change the user2 password before user2 can connect to the database.

Command> ALTER USER user2 PASSWORD EXPIRE;
 
User altered.

Attempt to connect to the database as user2. The user2 password must be changed before the user2 user can connect to the database.

Command>  connect adding "UID=user2;PWD=user2" as user2;
15180: the password has expired
The command failed.

As the instance administrator, reconnect to the database and use the ALTER USER statement to change the user2 password.

none: Command> use database1
database1: Command>  ALTER USER user2 IDENTIFIED BY newuser2password;
 
User altered.

Attempt to connect to the database a the user2 user. The connection succeeds.

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

Change a User from External to Internal and Internal to External

This example uses the ALTER USER statement to change the user2 internal user to an external user and then back to an internal user.

Command> ALTER USER user2 IDENTIFIED EXTERNALLY;
 
User altered.

Use the ALTER USER statement to change the user2 external user back to an internal user.

Command> ALTER USER user2 IDENTIFIED BY user2_password_change;
 
User altered.