CREATE USER

Adds a name to the list of authorized database users.

Note: 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[,...] ]

 
ACCOUNT { LOCK | UNLOCK }
GRACEPERIOD limit
IDENTIFIED BY 'password'
IDLESESSIONTIMEOUT limit
MAXCONNECTIONS limit 
MEMORYCAP limit 
PASSWORD EXPIRE 
PROFILE profile
RESOURCE POOL pool‑name
RUNTIMECAP expression
SEARCH_PATH path
TEMPSPACECAP limit

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:

  • 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.

Tip: 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 clause, 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

Indicates 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.
  • 'integer' ON NODE: Sets the maximum number of connections to each node to integer.
  • 'integer' ON DATABASE: Sets the maximum number of connections across the database cluster to integer.

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.

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

PROFILE profile

Assigns the user to a profile, where profile is one of the following:

  • DEFAULT (default): Assigns the default profile to this user. If you omit this parameter, the user is assigned to the default profile.
  • profile-name: The profile assigned to this user, which includes the user's password policy.
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

Superusers

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;
=> GRANT USAGE ON SCHEMA PUBLIC to Fred;

See Also