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, set to one of the following:
To automate account locking, set a maximum number of failed login attempts with |
GRACEPERIOD |
Specifies how long a user query can block on any session socket, set to one of the following:
For details, see Handling Session Socket Blocking. |
IDENTIFIED BY '[password]' | ['password' salt 'hash_salt'] |
Specifies a password with one of the following methods:
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, set to one of the following:
For details, see Managing Client Connections. |
MAXCONNECTIONS |
Specifies the maximum number of connections the user can have to the server, set to one of the following:
For details, see Managing Client Connections. |
MEMORYCAP |
Specifies how much memory can be allocated to user requests, set to one of the following:
|
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, set to one of the following:
|
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 |
RUNTIMECAP |
Specifies how long this user's queries can execute, set to one of the following:
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 |
Specifies the user's default search path that tells Vertica which schemas to search for unqualified references to tables and UDFs, set to one of the following:
For details, see Setting Search Paths in the Administrator's Guide. |
SALT |
Specifies a hex string used to create a password hash. When provided
For details, see Setting Search Paths in the Administrator's Guide. |
TEMPSPACECAP |
Sets how much temporary file storage is available for user requests, one of the following:
|
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;