ALTER USER statement

Use this statement to alter user settings.

Syntax 1
ALTER USER user-name [ IDENTIFIED BY password ]
 [ LOGIN POLICY policy-name ]
 [ FORCE PASSWORD CHANGE { ON | OFF } ]
Syntax 2
ALTER USER user-name
[ RESET LOGIN POLICY ]
Parameters
  • user-name   The name of the user.

  • IDENTIFIED BY clause   The password for the user.

  • policy-name   The name of the login policy to assign the user. No change is made if the LOGIN POLICY clause is not specified.

  • FORCE PASSWORD CHANGE clause   Controls whether the user must specify a new password when they log in. This setting overrides the password_expiry_on_next_login option setting in their policy.

  • RESET LOGIN POLICY clause   Reverts the settings of a user's login policy to the original values. When you reset a login policy, a user can access an account that has been locked for exceeding a login policy option limit such as max_failed_login_attempts or max_days_since_login.

Remarks

User IDs and passwords cannot:

A password can be either a valid identifier, or a string (maximum 255 bytes) placed in single quotes. Passwords are case sensitive. It is recommended that the password be composed of 7-bit ASCII characters, as other characters may not work correctly if the database server cannot convert them from the client's character set to UTF-8.

The verify_password_function option can be used to specify a function to implement password rules (for example, passwords must include at least one digit). If a password verification function is used, you cannot specify more than one user ID and password in the GRANT CONNECT statement. See verify_password_function option [database].

If you set the password_expiry_on_next_login value to ON, the user’s password expires immediately when they next login even if they are assigned to the same policy. You can use the ALTER USER and LOGIN POLICY clauses to force a user to change their password when they next login.

Permissions

Any user can change their own password. All other changes require DBA authority.

Side effects

None.

See also
Standards and compatibility
  • SQL/2003   Vendor extension.

Example

The following alters a user named SQLTester. The password is set to "welcome". The SQLTester user is assigned to the Test1 login policy and the password does not expire on the next login.

ALTER USER SQLTester IDENTIFIED BY welcome
LOGIN POLICY Test1
FORCE PASSWORD CHANGE off;