CREATE USER

Adds a name to the list of authorized database users.

New users lack default access to schema PUBLIC. Be sure to assign new users USAGE privileges to the PUBLIC schema (GRANT USAGE ON SCHEMA PUBLIC)

Syntax

CREATE USER user‑name [ account‑parameter value[,...] ]

Parameters

user‑name

Name of the new 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 One or more user account parameter settings (see below).

User Account Parameters

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

account‑parameter setting[,...]
Parameter Settings
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.

To automate account locking, set a maximum number of failed login attempts with CREATE PROFILE.

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

Sets the user's password as follows:

IDENTIFIED BY { '[password]' | 'hashed-password' SALT 'hash-salt' }
  • 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.

If you omit this parameter, this user can access the database with no password.

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.

PASSWORD EXPIRE has no effect when using external password authentication methods such as LDAP or Kerberos.

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.

If you omit this parameter, the user is assigned the default profile.

RESOURCE POOL

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

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.

TEMPSPACECAP

Sets how much temporary file storage is available for 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:

      TEMPSPACECAP '10G'

Privileges

Superuser

User Name Best Practices

Vertica database user names are logically separate from user names of the operating system in which the server runs. If all the users of a particular server also have accounts on the server's machine, it makes sense to assign database user names that match their operating system user names. However, a server that accepts remote connections might many database users with no local operating system account. In this case, there is no need to connect database and system user names.

Examples

=> CREATE USER Fred IDENTIFIED BY 'Mxyzptlk';
=> GRANT USAGE ON SCHEMA PUBLIC to Fred;