ALTER USER

Changes user account parameters and user-level configuration parameters.

Changes user account parameters and user-level configuration parameters.

Syntax

ALTER USER user-name {
   account-parameter value[,...]
   | SET [PARAMETER] cfg-parameter=value[,...]
   | CLEAR [PARAMETER] cfg-parameter[,...]
}

Parameters

user-name
Name of the user. Names that contain special characters must be double-quoted. To enforce case-sensitivity, use double-quotes.

For details on name requirements, see Creating a database name and password.

account-parameter value
Specifies user account settings (see below).
SET [PARAMETER]
Sets the specified configuration parameters. The new setting applies only to the current session, and to all later sessions launched by this user. Concurrent user sessions are unaffected by new settings unless they call meta-function RESET_SESSION.
CLEAR [PARAMETER]
Resets the specified configuration parameters to their default values.

User account parameters

Specify one or more user-account parameters and their settings as a comma-delimited list:

account-parameter value[,...]

Parameter Setting
ACCOUNT

Locks or unlocks user access to the database, one of the following:

  • UNLOCK (default)

  • LOCK prevents a new user from logging in. This can be useful when creating an account for a user who does not need immediate access.

DEFAULT ROLE

Specifies what roles are the default roles for this user, set to one of the following:

  • NONE (default): Removes all default roles.

  • role[,...]: Comma-delimited list of roles.

  • ALL: Sets as default all user roles.

  • ALL EXCEPT role[,...]: Comma-delimited list of roles to exclude as default roles.

Default roles are automatically activated when a user logs in. The roles specified by this parameter supersede any roles assigned earlier.

GRACEPERIOD

Specifies how long a user query can block on any session socket, one of the following:

  • NONE (default): Removes any grace period previously set on session queries.

  • 'interval': Specifies as an interval the maximum grace period for current session queries, up to 20 days.

For details, see Handling session socket blocking.

IDENTIFIED BY

Changes the user's password:

IDENTIFIED BY '[new-password]'
   | ['hashed-password' SALT 'hash-salt'] 
   [REPLACE 'current-password']

  • new-password: ASCII password that Vertica then hashes for internal storage. An empty string enables this user to access the database with no password.

  • hashed-password: A pre-hashed password and its associated hex string hash-salt. Setting a password this way bypasses all password complexity requirements.

  • REPLACE: Required for non-superusers, who must supply their current password. Non-superusers can only change their own passwords.

For details, see Password guidelines and Creating a database name and password.

IDLESESSIONTIMEOUT

The length of time the system waits before disconnecting an idle session, one of the following:

  • NONE (default): No limit set for this user. If you omit this parameter, no limit is set for this user.

  • 'interval': An interval value, up to one year.

For details, see Managing client connections.

MAXCONNECTIONS

Sets the maximum number of connections the user can have to the server, one of the following:

  • NONE (default): No limit set. If you omit this parameter, the user can have an unlimited number of connections across the database cluster.

  • integer ON DATABASE: Sets to integer the maximum number of connections across the database cluster.

  • integer ON NODE: Sets to integer the maximum number of connections to each node.

For details, see Managing client connections.

MEMORYCAP

Sets how much memory can be allocated to user requests, one of the following:

  • NONE (default): No limit

  • A string value that specifies the memory limit, one of the following:

    • 'int%' expresses the maximum as a percentage of total memory available to the Resource Manager, where int is an integer value between 0 and 100.For example:

      MEMORYCAP '40%'

    • 'int{K|M|G|T}' expresses memory allocation in kilobytes, megabytes, gigabytes, or terabytes. For example:

      MEMORYCAP '10G'

PASSWORD EXPIRE

Forces immediate expiration of the user's password. The user must change the password on the next login.

PROFILE

Assigns a profile that controls password requirements for this user, one of the following:

  • DEFAULT (default): Assigns the default database profile to this user.

  • profile-name: A profile that is defined by CREATE PROFILE.

RENAME TO

Assigns the user a new user name. All privileges assigned to the user remain unchanged.

RESOURCE POOL pool-name [FOR SUBCLUSTER sc-name]

Assigns a resource pool to this user. The user must also be granted privileges to this pool, unless privileges to the pool are set to PUBLIC.

The FOR SUBCLUSTER clause assigns a subcluster-specific resource pool to the user. You can assign only one subcluster-specific resource pool to each user.

RUNTIMECAP

Sets how long this user's queries can execute, one of the following:

  • NONE (default): No limit set for this user. If you omit this parameter, no limit is set for this user.

  • 'interval': An interval value, up to one year.

A query's runtime limit can be set at three levels: the user's runtime limit, the user's resource pool, and the session setting. For more information, see Setting a runtime limit for queries.

SEARCH_PATH

Specifies the user's default search path, that tells Vertica which schemas to search for unqualified references to tables and UDFs, one of the following:

  • DEFAULT (default): Sets the search path as follows:

    "$user", public, v_catalog, v_monitor, v_internal

  • Comma-delimited list of schemas.

For details, see Setting Search Paths.

SECURITY_ALGORITHM 'algorithm'

Sets the user-level security algorithm for hash authentication, where algorithm is one of the following:

  • NONE (default): Uses the system-level parameter, SecurityAlgorithm

  • SHA512

  • MD5

The user's password expires when you change the SECURITY_ALGORITHM value and must be reset.

TEMPSPACECAP

Sets how much temporary file storage is available for user requests, one of the following:

  • NONE (default): No limit

  • String value that specifies the storage limit, one of the following:

    • int% expresses the maximum as a percentage of total temporary storage available to the Resource Manager, where int is an integer value between 0 and 100. For example:

      TEMPSPACECAP '40%'

    • int{K|M|G|T} expresses storage allocation in kilobytes, megabytes, gigabytes, or terabytes. For example:

      TEMPSPACECAP '10G'

Privileges

Non-superusers can change the following options on their own user accounts:

  • IDENTIFIED BY

  • RESOURCE POOL

  • SEARCH_PATH

  • SECURITY_ALGORITHM

When changing a another user's resource pool to one outside of the PUBLIC schema, the user must have USAGE privileges on the resource pool from at least one of the following:

Setting user-level configuration parameters

SET | CLEAR PARAMETER can specify only user-level configuration parameters, otherwise Vertica returns an error. Only superusers can set and clear user-level parameters, unless they are also supported at the session level.

To get the names of user-level parameters, query system table CONFIGURATION_PARAMETERS. For example:

=> SELECT parameter_name, allowed_levels FROM configuration_parameters
      WHERE allowed_levels ilike '%USER%' AND parameter_name ilike '%depot%' ORDER BY parameter_name;
       parameter_name        |     allowed_levels
-----------------------------+-------------------------
 BackgroundDepotWarming      | SESSION, USER, DATABASE
 DepotOperationsForQuery     | SESSION, USER, DATABASE
 EnableDepotWarmingFromPeers | SESSION, USER, DATABASE
 UseDepotForReads            | SESSION, USER, DATABASE
 UseDepotForWrites           | SESSION, USER, DATABASE
(5 rows)

The following example sets the user-level configuration parameter UseDepotForWrites for two users, Yvonne and Ahmed:

=> SHOW USER Yvonne PARAMETER ALL;
  user  |        parameter        | setting
--------+-------------------------+---------
 Yvonne | DepotOperationsForQuery | Fetches
(1 row)

=> ALTER USER Yvonne SET PARAMETER UseDepotForWrites = 0;
ALTER USER
=> SHOW USER Yvonne PARAMETER ALL;
  user  |        parameter        | setting
--------+-------------------------+---------
 Yvonne | DepotOperationsForQuery | Fetches
 Yvonne | UseDepotForWrites       | 0
(2 rows)

=> ALTER USER Ahmed SET PARAMETER DepotOperationsForQuery = 'Fetches';
ALTER USER
=> SHOW USER ALL PARAMETER ALL;
  user  |        parameter        | setting
--------+-------------------------+---------
 Ahmed  | DepotOperationsForQuery | Fetches
 Yvonne | DepotOperationsForQuery | Fetches
 Yvonne | UseDepotForWrites       | 0
(3 rows)

Examples

Set a user's password

=> CREATE USER user1;
=> ALTER USER user1 IDENTIFIED BY 'newpassword';

Set user's security algorithm and password

This example sets a user's security algorithm and password to SHA-512 and newpassword, respectively. When you execute the ALTER USER statement, Vertica hashes the password with the SHA-512 algorithm and saves the hash:

=> CREATE USER user1;
        => ALTER USER user1 SECURITY_ALGORITHM 'SHA512' IDENTIFIED BY 'newpassword'

Assign default roles to a user

This example make a user's assigned roles their default roles. Default roles are automatically set (enabled) when a user logs in:

=> CREATE USER user1;
CREATE USER
=> GRANT role1, role2, role3 to user1;
=> ALTER USER user1 DEFAULT ROLE ALL;

You can pair ALL with EXCEPT to exclude certain roles:

=> CREATE USER user2;
CREATE USER
=> GRANT role1, role2, role3 to user2;
=> ALTER USER user2 DEFAULT ROLE ALL EXCEPT role1;

See also