This is the multi-page printable view of this section. Click here to print.
Every Vertica database has one or more users. When users connect to a database, they must log on with valid credentials (username and password) that a superuser defined in the database.
Database users own the objects they create in a database, such as tables, procedures, and storage locations.
In a Vertica database, there are three types of users:
Database administrator (DBADMIN)
Object owner
Everyone else (PUBLIC)
On installation, a new Vertica database automatically contains a user with superuser privileges. Unless explicitly named during installation, this user is identified as dbadmin. This user cannot be dropped and has the following irrevocable roles:
With these roles, the dbadmin user can perform all database operations. This user can also create other users with administrative privileges.
Do not confuse the dbadmin user with the DBADMIN role. The DBADMIN role is a set of privileges that can be assigned to one or more users.
The Vertica documentation often references the dbadmin user as a superuser. This reference is unrelated to Linux superusers.
As the dbadmin user, you can create other users with the same privileges:
Create a user:
=> CREATE USER DataBaseAdmin2;
CREATE USER
Grant the appropriate roles to new user DataBaseAdmin2:
=> GRANT dbduser, dbadmin, pseudosuperuser to DataBaseAdmin2;
GRANT ROLE
User DataBaseAdmin2 now has the same privileges granted to the original dbadmin user.
As DataBaseAdmin2, enable your assigned roles with SET ROLE:
=> \c - DataBaseAdmin2;
You are now connected to database "VMart" as user "DataBaseAdmin2".
=> SET ROLE dbadmin, dbduser, pseudosuperuser;
SET ROLE
Confirm the roles are enabled:
=> SHOW ENABLED ROLES;
name | setting
-------------------------------------------------
enabled roles | dbduser, dbadmin, pseudosuperuser
An object owner is the user who creates a particular database object and can perform any operation on that object. By default, only an owner (or a superuser) can act on a database object. In order to allow other users to use an object, the owner or superuser must grant privileges to those users using one of the GRANT statements.
See Database privileges for more information.
All non-DBA (superuser) or object owners are PUBLIC users.
Newly-created users do not have access to schema PUBLIC by default. Make sure to GRANT USAGE ON SCHEMA PUBLIC to all users you create.
To create a database user:
From vsql, connect to the database as a superuser.
Issue the
CREATE USER statement with optional parameters.
Run a series of GRANT statements to grant the new user privileges.
To create a user on MC, see Creating an MC user in Management Console
By default, new database users have the right to create temporary tables in the database.
Newly-created users do not have access to schema PUBLIC by default. Make sure to GRANT USAGE ON SCHEMA PUBLIC to all users you create
You can change information about a user, such as his or her password, by using the
ALTER USER statement. If you want to configure a user to not have any password authentication, you can set the empty password ββ in CREATE USER or ALTER USER statements, or omit the IDENTIFIED BY parameter in CREATE USER.
The following series of commands add user Fred to a database with password 'password. The second command grants USAGE privileges to Fred on the public schema:
=> CREATE USER Fred IDENTIFIED BY 'password';
=> GRANT USAGE ON SCHEMA PUBLIC to Fred;
User names created with double-quotes are case sensitive. For example:
=> CREATE USER "FrEd1";
In the above example, the logon name must be an exact match. If the user name was created without double-quotes (for example, FRED1), then the user can log on as FRED1, FrEd1, fred1, and so on.
ALTER USER lets you set user-level configuration parameters on individual users. These settings override database- or session-level settings on the same parameters. For example, the following ALTER USER statement sets DepotOperationsForQuery for users Yvonne and Ahmed to FETCHES, thus overriding the default setting of ALL:
=> SELECT user_name, parameter_name, current_value, default_value FROM user_configuration_parameters
WHERE user_name IN('Ahmed', 'Yvonne') AND parameter_name = 'DepotOperationsForQuery';
user_name | parameter_name | current_value | default_value
-----------+-------------------------+---------------+---------------
Ahmed | DepotOperationsForQuery | ALL | ALL
Yvonne | DepotOperationsForQuery | ALL | ALL
(2 rows)
=> ALTER USER Ahmed SET DepotOperationsForQuery='FETCHES';
ALTER USER
=> ALTER USER Yvonne SET DepotOperationsForQuery='FETCHES';
ALTER USER
To identify user-level configuration parameters, query the allowed_levels column of system table CONFIGURATION_PARAMETERS. For example, the following query identifies user-level parameters that affect depot usage:
n=> SELECT parameter_name, allowed_levels, default_value, current_level, current_value
FROM configuration_parameters WHERE allowed_levels ilike '%USER%' AND parameter_name ilike '%depot%';
parameter_name | allowed_levels | default_value | current_level | current_value
-------------------------+-------------------------+---------------+---------------+---------------
UseDepotForReads | SESSION, USER, DATABASE | 1 | DEFAULT | 1
DepotOperationsForQuery | SESSION, USER, DATABASE | ALL | DEFAULT | ALL
UseDepotForWrites | SESSION, USER, DATABASE | 1 | DEFAULT | 1
(3 rows)
You can obtain user settings in two ways:
Query system table USER_CONFIGURATION_PARAMETERS:
=> SELECT * FROM user_configuration_parameters;
user_name | parameter_name | current_value | default_value
-----------+---------------------------+---------------+---------------
Ahmed | DepotOperationsForQuery | FETCHES | ALL
Yvonne | DepotOperationsForQuery | FETCHES | ALL
Yvonne | LoadSourceStatisticsLimit | 512 | 256
(3 rows)
Use SHOW USER:
=> SHOW USER Yvonne PARAMETER ALL;
user | parameter | setting
--------+---------------------------+---------
Yvonne | DepotOperationsForQuery | FETCHES
Yvonne | LoadSourceStatisticsLimit | 512
(2 rows)
=> SHOW USER ALL PARAMETER ALL;
user | parameter | setting
--------+---------------------------+---------
Yvonne | DepotOperationsForQuery | FETCHES
Yvonne | LoadSourceStatisticsLimit | 512
Ahmed | DepotOperationsForQuery | FETCHES
(3 rows)
As a superuser, you can manually lock and unlock a database user account with ALTER USER...ACCOUNT LOCK and ALTER USER...ACCOUNT UNLOCK, respectively. For example, the following command prevents user Fred from logging in to the database:
=> ALTER USER Fred ACCOUNT LOCK;
=> \c - Fred
FATAL 4974: The user account "Fred" is locked
HINT: Please contact the database administrator
The following example unlocks access to Fred's user account:
=> ALTER USER Fred ACCOUNT UNLOCK;|
=> \c - Fred
You are now connected as user "Fred".
CREATE USER can specify to lock a new account. Like any locked account, it can be unlocked with ALTER USER...ACCOUNT UNLOCK.
=> CREATE USER Bob ACCOUNT LOCK;
CREATE USER
A user's profile can specify to lock an account after a certain number of failed login attempts.
As a superuser, you can set any user's password when you create that user with CREATE USER, or later with ALTER USER. Non-superusers can also change their own passwords with ALTER USER. One exception applies: users who are added to the Vertica database with the LDAPLink service cannot change their passwords with ALTER USER.
You can also give a user a pre-hashed password if you provide its associated salt. The salt must be a hex string. This method bypasses password complexity requirements.
To view password hashes and salts of existing users, see the PASSWORDS system table.
Changing a user's password has no effect on their current session.
In this example, the user 'Bob' is created with the password 'mypassword.'
=> CREATE USER Bob IDENTIFIED BY 'mypassword';
CREATE USER
The password is then changed to 'Orca.'
=> ALTER USER Bob IDENTIFIED BY 'Orca' REPLACE 'mypassword';
ALTER USER
In this example, the user 'Alice' is created with a pre-hashed password and salt.
=> CREATE USER Alice IDENTIFIED BY
'sha512e0299de83ecfaa0b6c9cbb1feabfbe0b3c82a1495875cd9ec1c4b09016f09b42c1'
SALT '465a4aec38a85d6ecea5a0ac8f2d36d8';
On Management Console, users with ADMIN or IT privileges can reset a user's non-LDAP password:
Sign in to Management Console and navigate to MC Settings > User management.
Click to select the user to modify and click Edit.
Click Edit password and enter the new password twice.
Click OK and then Save.