ALTER USER Statement

Changes user settings.

Syntax

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 ]

Examples

Example 1 – Alter 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
```
The user executing this command requires both USER ADMIN and PERMS ADMIN or DBA authority. PERMS ADMIN authority is required to change the password and USER ADMIN authority to change the login policy.

Usage

user--name—The name of the user.

IDENTIFIED BY clause—Clause providing 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 the user's login to the original values in the login policy. This usually clears all locks that are implicitly set due to the user exceeding the failed logins or exceeding the maximum number of days since the last login. 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.

For more information on managing login policies, see SQL Anywhere Server - Database Administration > Configuring your database > Managing user IDs, authorities, and permissions > Managing login policies.

Note: This reference points to SQL Anywhere documentation.

Enhanced ALTER LOGIN POLICY syntax for multiplex is described in Using Sybase IQ Multiplex.

User IDs and passwords cannot:

Begin with white space, single quotes, or double quotes
End with white space
Contain semicolons

If you set the PASSWORD_EXPIRY_ON_NEXT_LOGIN value to ON, the passwords of all users assigned to this login policy expire immediately when they next log in. You can use the ALTER USER and LOGIN POLICY clauses to force a user to change the password when he next logs in.

Standards

SQL—Vendor extension to ISO/ANSI SQL grammar.
Sybase—Not supported by Adaptive Server Enterprise.

Permissions

Users may change their own passwords. To change other users’ passwords requires either DBA or PERMS ADMIN authorities. A user needs DBA or USER ADMIN authority for ALTER USER LOGIN POLICY, FORCE PASSWORD CHANGE, or RESET LOGIN POLICY.