Skip to content
≡

Administrator

Developer

Tools

External Tools

ALTER USER

ALTER USER#

Syntax#

alter_user ::=#

alter_user_image89

password_parameters ::=

Prerequisites#

Only the SYS user and users having the ALTER USER system privilege can execute the ALTER USER statement. However, individual users can change their own passwords without this privilege.

Description#

This statement is used to change a user's password and the tablespaces associated with the user.

IDENTIFIED clause#

This is used to specify a new password for the user. The new password is specified after the BY element.

Because the other commands are the same as the corresponding commands executed using the CREATE USER statement, please refer to the description of the CREATE USER statement.

LIMIT clause#

This modifies password management policies for accounts. This clause can only be executed by the SYS user. When a password management policy is changed, an existing policy that is not specified is initialized.

ACCOUNT LOCK/UNLOCK#

This explicitly locks or unlocks accounts.

ENABLE/ DISABLE#

The user can restrict TCP connections. Only the SYS user can execute this clause.

Precautions#

  • When changing the password for the SYS user, who can log on in SYSDBA mode, after the password is changed using the ALTER USER statement, it must be changed one more time by running the altipasswd utility in a console window of the operating system (Unix shell or DOS terminal window). For more information on the altipasswd utility, please refer to the Utilities Manual.
  • After the password is changed using the ALTER USER statement, it must also be changed in the shell scripts files containing the password such as: server, is, and il to ensure that the script files will run without error.

Examples#

<Query> Change the password of the user Tom to ab1rose.

iSQL> ALTER USER tom
    IDENTIFIED BY ab1rose;
Alter success.

<Query> Change a user's default tablespace.

iSQL> ALTER USER tom
  DEFAULT TABLESPACE uare_data;
Alter success.

<Query> This statement modifies password management policies for user rose2.

iSQL> ALTER USER rose2 LIMIT (FAILED_LOGIN_ATTEMPTS 7, PASSWORD_LOCK_TIME 7);

<Query> Modify the managing policy of user rose2's password. Do not put a limit on the number of times for logging in, and maintain the account locked for 10 days.

iSQL> ALTER USER rose2 LIMIT (FAILED_LOGIN_ATTEMPTS UNLIMITED,
PASSWORD_LOCK_TIME 10);

<Query> Login fails for user rose2 after locking the account.

ALTER USER rose2 ACCOUNT LOCK;
iSQL> CONNECT rose2/rose2;
[ERR-31370 : The account is locked.]