This is the multi-page printable view of this section. Click here to print.

Return to the regular view of this page.

Database users

Every Vertica database has one or more users.

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.

1 - Types of database users

In a Vertica database, there are three types of users:.

In a Vertica database, there are three types of users:

  • Database administrator (DBADMIN)

  • Object owner

  • Everyone else (PUBLIC)

1.1 - Database administration user

On installation, a new Vertica database automatically contains a user with superuser privileges.

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.

Creating additional database administrators

As the dbadmin user, you can create other users with the same privileges:

  1. Create a user:

    => CREATE USER DataBaseAdmin2;
    CREATE USER
    
  2. 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.

  3. 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
    
  4. Confirm the roles are enabled:

    => SHOW ENABLED ROLES;
    name          | setting
    -------------------------------------------------
    enabled roles | dbduser, dbadmin, pseudosuperuser
    

1.2 - Object owner

An object owner is the user who creates a particular database object and can perform any operation on that object.

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.

1.3 - PUBLIC user

All non-DBA (superuser) or object owners are PUBLIC users.

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.

See also

2 - Creating a database user

To create a database user:.

To create a database user:

  1. From vsql, connect to the database as a superuser.

  2. Issue the CREATE USER statement with optional parameters.

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

New user privileges

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

Modifying users

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.

Example

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.

See also

3 - User-level configuration parameters

ALTER USER lets you set user-level configuration parameters on individual users.

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

Identifying user-level parameters

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)

Viewing user parameter settings

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)
    

4 - Locking user accounts

As a superuser, you can manually lock and unlock a database user account with ALTER USER...ACCOUNT LOCK and ALTER USER...ACCOUNT UNLOCK, respectively.

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

Locking new accounts

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

Locking accounts for failed login attempts

A user's profile can specify to lock an account after a certain number of failed login attempts.

5 - Setting and changing user passwords

As a superuser, you can set any user's password when you create that user with CREATE USER, or later with ALTER USER.

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.

Setting user passwords in VSQL

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

Setting user passwords in Management Console

On Management Console, users with ADMIN or IT privileges can reset a user's non-LDAP password:

  1. Sign in to Management Console and navigate to MC Settings > User management.

  2. Click to select the user to modify and click Edit.

  3. Click Edit password and enter the new password twice.

  4. Click OK and then Save.