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 name [ user‑parameter setting[,…] ]

Parameters

name

Specifies the 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 { LOCK | UNLOCK }

Locks or unlocks a user's access to the database, set to 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 limit

Specifies how long a user query can block on any session socket, where limit is 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
'[password]'

Sets the new user's password, where password must conform to the password complexity policy set by the user's profile.

If you supply an empty string or omit this parameter, the user is assigned no password and is not prompted for one when connecting.

For details, see Password Guidelines and Creating a Database Name and Password.

IDLESESSIONTIMEOUT limit

The length of time the system waits before disconnecting an idle session, where limit is 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 limit

Specifies the maximum number of connections the user can have to the server, where limit is 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 [connection‑mode]: Specifies the maximum number of connections allowed to the user, where connection‑mode is one of the following:

    • ON DATABASE (default): Allows up to integer connections across the database cluster.
    • ON NODE: Allows up to integer connections to each node.

For details, see Managing Client Connections.

MEMORYCAP limit

Specifies how much memory can be allocated to user requests, where limit is specified in this format:

  • NONE (default): No limit
  • 'max‑expression': 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 profile

Assigns a profile that controls password requirements for this user, where profile is 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 pool‑name

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 limit

Specifies how long this user's queries can execute, where limit is 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 in the Administrator's Guide.

SEARCH_PATH path

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

  • DEFAULT (default): Sets the search path as follows:
    "$user", public, v_catalog, v_monitor, v_internal
  • A comma-delimited list of schemas.

For details, see Setting Search Paths in the Administrator's Guide.

TEMPSPACECAP limit

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

  • NONE (default): No limit
  • 'max‑expression': A string value that specifies the storage limit, one of the following:

    • int% — Expresses storage as a percentage of total file space is available, where int is an integer value between 0 and 100.For example:

      TEMPSPACECAP '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;