3.3.1 Specification format and rules for the ALTER USER statement : Hitachi Advanced Database SQL Reference

(1) Specification format

Changing the password of a HADB user who uses database authentication, or changing the user authentication method of a HADB user to database authentication
```
ALTER-USER-statement ::= ALTER USER authorization-identifier IDENTIFIED BY post-change-password
```

Changing the user authentication method of a HADB user to PAM authentication

ALTER-USER-statement ::= ALTER USER authorization-identifier IDENTIFIED WITH PAM

Changing the user authentication method of a HADB user to Keycloak authentication
```
ALTER-USER-statement ::= ALTER USER authorization-identifier IDENTIFIED WITH KEYCLOAK
```

To Page Top

(2) Explanation of specification format

authorization-identifier

Specify the authorization identifier of the HADB user whose user information is to be changed.

For rules about specifying authorization identifiers, see 6.1.4 Specifying names.

● IDENTIFIED BY post-change-password

Specify the new password.

If the HADB user whose user information is to be changed uses PAM authentication or Keycloak authentication as the user authentication method, the user authentication method for that HADB user will be changed to database authentication. For details about the user authentication method, see User authentication in the HADB Setup and Operation Guide.

The rules for specifying the password are as follows:

The password can include single-byte uppercase and lowercase letters, numbers, backslashes (\), as well as the following characters:

@ ` ! " # $ % & ' ( ) * : + ; [ ] { } , = < > | - . ^ ~ / ? _
Specify the password in the form of a character string literal. Therefore, you must enclose the password in single quotation marks. The following are examples:

Example 1: Set the new password to Password01

IDENTIFIED BY 'Password01'

Example 2: Set the new password to Pass'01

IDENTIFIED BY 'Pass''01'

If the password itself includes a single quotation mark ('), specify two single quotation marks ('') to represent the single quotation mark ('), as shown in the example above.

For rules on specifying a character string literal, see Table 6‒12: Description formats and assumed data types of literals.
The password cannot be empty. That is, the following is not permitted:

IDENTIFIED BY ''
The password cannot exceed 255 characters (255 bytes).

Note

If you are using the JDBC driver, we recommend that you not use the following character in the password:

&
If you are using the ODBC driver, we recommend that you not use the following characters in the password:

[ ] { } ( ) , ; ? * = ! @

●IDENTIFIED WITH PAM

Specify if you want to change the user authentication method for HADB users to PAM authentication.

Note the following rules:

When this option is specified, the authorization identifier must be the same as the external user name (e.g., OS user name).
This option cannot be specified for HADB users who are already using PAM authentication.
HADB users who has the audit privilege cannot use PAM authentication. Therefore, this option cannot be specified for HADB users who has the audit privilege.
HADB users who has the CRYPTO ADMIN privilege cannot use PAM authentication. Therefore, this option cannot be specified for HADB users who has the CRYPTO ADMIN privilege.

For details about PAM authentication, see PAM authentication in the HADB Setup and Operation Guide.

●IDENTIFIED WITH KEYCLOAK

Specify if you want to change the user authentication method for HADB users to Keycloak authentication.

Note the following rules:

When this option is specified, the authorization identifier must be the same as the external user name (e.g., OS user name registered on the Keycloak server).
This option cannot be specified for HADB users who are already using Keycloak authentication.
HADB users who has the audit privilege cannot use Keycloak authentication. Therefore, this option cannot be specified for HADB users who has the audit privilege.
HADB users who has the CRYPTO ADMIN privilege cannot use Keycloak authentication. Therefore, this option cannot be specified for HADB users who has the CRYPTO ADMIN privilege.

For details about Keycloak authentication, see Keycloak authentication in the HADB Setup and Operation Guide.

To Page Top

(3) Privileges required at execution

To execute the ALTER USER statement, the CONNECT privilege is required.

To Page Top

(4) Rules

An HADB user with the DBA privilege can change the user information of every HADB user. However, the user information of HADB users with the audit privilege cannot be changed. The user information of an HADB user with the audit privilege can be changed by that HADB user only.
An HADB user without the DBA privilege can change the user information of only the current user (the HADB user whose authorization identifier is currently connected to the HADB server).
At least one HADB user with DBA and CONNECT privileges is required, using database authentication as the user authentication method. Therefore, when there is only one such HADB user, the user authentication method for that HADB user cannot be changed from database authentication to PAM authentication or Keycloak authentication.

To Page Top

(5) Examples

Example 1

Change the password of HADB user ADBUSER01, which uses database authentication, to #HelloHADB_02.

ALTER USER "ADBUSER01" IDENTIFIED BY '#HelloHADB_02'

Example 2

Change the user authentication method for HADB user OSUSER01 to PAM authentication.

ALTER USER "OSUSER01" IDENTIFIED WITH PAM

OSUSER01 is an external user name (e.g., OS user name).

Example 3

Change the user authentication method for HADB user KEYUSER01 to Keycloak authentication.

ALTER USER "KEYUSER01" IDENTIFIED WITH KEYCLOAK

KEYUSER01 is an external user name (e.g., OS user name registered on the Keycloak server).

Example 4

Change the user authentication method for HADB user ADBUSER03 to database authentication.

ALTER USER "ADBUSER03" IDENTIFIED BY '#HelloHADB_03'

To Page Top