ALTER USER statement (UNIX, Linux)

Use the ALTER USER statement to change one or more of the properties, including the password, user ID, surrogate group, administrative authorization, and home directory, and to enable or disable the account of an internally authenticated user, or of the default internally authenticated user.

This statement is an extension to the ANSI/ISO standard for the SQL language.

Syntax

Notes:

¹ Use this path no more than 16 times


Element	Description	Restrictions	Syntax
`directory`	Path name of directory where user files are stored.	Must be 255 bytes or fewer, and must conform to the rules of your operating system. The `directory` must also: Belong to the mapped `user_ID` and `surrog_group_ID`. Have read, write, and execute permissions for the owner.	Quoted String
`password`	Password for internal authentication of the user.	Must be between 6 and 32 bytes.	Quoted String
`surrog_group`	Name of an existing operating system group (surrogate group) that has the permissions to which you want to map `user`. The list of `surrog_group` values must be enclosed in parentheses.	Must be 32 bytes or fewer.	Owner name
`surrog_group_ID`	Group identifier number (surrogate group) to which you want to map the `user`. The list of `surrog_group_id` value or values that you specify must be enclosed in parentheses.	The `surrog_group_ID` cannot be: A group ID with server administrative privileges (DBSA, DBSSO, AAO, and BARGROUP) Group 0 (root, sometimes referred to as wheel or system) A group ID associated with group bin or group sys	Literal Number
`surrog_user`	Name of an existing OS user account (surrogate user) on the HCL OneDB™ host computer having the permissions to which you want to map `user`.	Must conform to the rules of your operating system	Owner name
`user`	Authorization identifier of the specific user that you are mapping to user properties.	Must be an authenticated authorization identifier	Owner name
`user_ID`	User identifier number to which you want to map `user`.	`user_ID` cannot be the one that belongs to user root or user informix.	Literal Number

Usage

Only a DBSA can run the ALTER USER statement. With a non-root installation, the user who installs the server is the equivalent of the DBSA, unless the user delegates DBSA privileges to a different user.

The USERMAPPING configuration parameter must be set to a value (ADMIN or BASIC) that enables support for mapped users before users created with the CREATE USER statement can connect to the database server.

The USERMAPPING configuration parameter must be set to ADMIN to enable the AUTHORIZATION clause. For more information about this deprecated syntax, see the CREATE USER statement (UNIX, Linux) description of the AUTHORIZATION clause.

You must also enter values in the SYSUSERMAP table of the sysusers database to map users with the appropriate user properties so that the mapped user statements of SQL to work correctly.

Mapped users can connect to HCL OneDB with the surrogate user properties if they authenticate with pluggable authentication module (PAM) or single sign-on (SSO).

Mapped users can connect to HCL OneDB with the surrogate user properties if they authenticate with pluggable authentication module.

The best practice is to map user to a specific surrog_user that is reserved as a surrogate user identity only. You can add groups associated with the surrogate user identity with the GROUP keyword, and change the home directory with the HOME keyword.

The ALTER USER statement does not affect any active operations with the same surrogate user or user ID. Only subsequent operations that require authentication are affected.

An ALTER USER statement can add a password for a user with the ADD keyword only if that user does not have a password. To change an existing password, use the MODIFY option in the ALTER USER statement.

The total number of groups after the ALTER USER operation cannot exceed 16, which is the maximum number of allowed groups.

An ALTER USER statement can only add a home directory with the ADD keyword if no home directory exists. To modify an existing home directory, use the MODIFY keyword.

In a single ALTER USER statement, a specific property can only be specified once. For example, you cannot drop a GROUP property and add a GROUP property in the same statement.

After the ALTER USER statement, the user must have either one USER property or one UID property.

Execution of the ALTER USER statement can be audited with the ALUR audit code.

Examples

Example 1: Replace a USER property with a UID property

The following statement replaces the USER property with a UID property for the user bill:

ALTER USER bill DROP USER, ADD UID 1360;

Example 2: Change and add properties

The following statement changes a UID property, adds the DBSA group, and adds a home directory for the user bill:

ALTER USER bill MODIFY UID 1361, ADD GROUP (dbsa), ADD HOME "/u/user1";

Example 3: Unlock an account and drop an authorization property

The following statement unlocks the account and drops the DBSSO authorization for the user bill:

ALTER USER bill ACCOUNT UNLOCK DROP AUTHORIZATION (dbsso);

Example 4: Drop a home directory

The following statement drops the home directory for the user bill:

ALTER USER bill DROP HOME;