Database users and privileges

• 1: Database users
• 1.1: Types of database users
• 1.1.1: Database administration user
• 1.1.2: Object owner
• 1.1.3: PUBLIC user
• 1.2: Creating a database user
• 1.3: User-level configuration parameters
• 1.4: Locking user accounts
• 1.5: Setting and changing user passwords
• 2: Database roles
• 2.1: Predefined database roles
• 2.1.1: DBADMIN
• 2.1.2: PSEUDOSUPERUSER
• 2.1.3: DBDUSER
• 2.1.4: SYSMONITOR
• 2.1.5: UDXDEVELOPER
• 2.1.6: PUBLIC
• 2.2: Role hierarchy
• 2.3: Creating and dropping roles
• 2.4: Granting privileges to roles
• 2.5: Granting database roles
• 2.6: Revoking database roles
• 2.7: Enabling roles
• 2.8: Enabling roles automatically
• 2.9: Viewing user roles
• 3: Database privileges
• 3.1: Ownership and implicit privileges
• 3.2: Inherited privileges
• 3.2.1: Enabling database inheritance
• 3.2.2: Enabling schema inheritance
• 3.2.3: Setting privilege inheritance on tables and views
• 3.2.4: Example usage: implementing inherited privileges
• 3.3: Default user privileges
• 3.4: Effective privileges
• 3.5: Privileges required for common database operations
• 3.6: Database object privileges
• 3.7: Granting and revoking privileges
• 3.7.1: Superuser privileges
• 3.7.2: Schema owner privileges
• 3.7.3: Object owner privileges
• 3.7.4: Granting privileges
• 3.7.5: Revoking privileges
• 3.7.6: Privilege ownership chains
• 3.8: Modifying privileges
• 3.9: Viewing privileges granted on objects
• 4: Access policies
• 4.1: Creating column access policies
• 4.2: Creating row access policies
• 4.3: Access policies and DML operations
• 4.4: Access policies and query optimization
• 4.5: Managing access policies

1 - Database 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.1 - Types of database users

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

• Database administrator (DBADMIN)

• Object owner

• Everyone else (PUBLIC)

1.1.1 - Database administration user

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:

• DBADMIN

• DBDUSER

• PSEUDOSUPERUSER

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.1.2 - Object owner

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.1.3 - PUBLIC user

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

• PUBLIC

1.2 - Creating 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

• Granting and revoking privileges
• Granting database roles

1.3 - User-level configuration parameters

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)
  

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

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

2 - Database roles

A role is a collection of privileges that can be granted to one or more users or other roles. Roles help you grant and manage sets of privileges for various categories of users, rather than grant those privileges to each user individually.

For example, several users might require administrative privileges. You can grant these privileges to them as follows:

1. Create an administrator role with CREATE ROLE:

   CREATE ROLE administrator;
   

2. Grant the role to the appropriate users.

3. Grant the appropriate privileges to this role with one or more GRANT statements. You can later add and remove privileges as needed. Changes in role privileges are automatically propagated to the users who have that role.

After users are assigned roles, they can either enable those roles themselves, or you can automatically enable their roles for them.

2.1 - Predefined database roles

Vertica has the following predefined roles:

• DBADMIN

• PSEUDOSUPERUSER

• DBDUSER

• SYSMONITOR

• UDXDEVELOPER

• PUBLIC

Automatic role grants

On installation, Vertica automatically grants and enables predefined roles as follows:

• The DBADMIN, PSEUDOSUPERUSER, and DBDUSER roles are irrevocably granted to the dbadmin user. These roles are always enabled for dbadmin, and can never be dropped.

• PUBLIC is granted to dbadmin, and to all other users as they are created. This role is always enabled and cannot be dropped or revoked.

Granting predefined roles

After installation, the dbadmin user and users with the PSEUDOSUPERUSER role can grant one or more predefined roles to any user or non-predefined role. For example, the following set of statements creates the userdba role and grants it the predefined role DBADMIN:

=> CREATE ROLE userdba;
CREATE ROLE
=> GRANT DBADMIN TO userdba WITH ADMIN OPTION;
GRANT ROLE

Users and roles that are granted a predefined role can extend that role to other users, if the original GRANT (Role) statement includes WITH ADMIN OPTION. One exception applies: if you grant a user the PSEUDOSUPERUSER role and omit WITH ADMIN OPTION, the grantee can grant any role, including all predefined roles, to other users.

For example, the userdba role was previously granted the DBADMIN role. Because the GRANT statement includes WITH ADMIN OPTION, users who are assigned the userdba role can grant the DBADMIN role to other users:

=> GRANT userdba TO fred;
GRANT ROLE
=> \c - fred
You are now connected as user "fred".
=> SET ROLE userdba;
SET
=> GRANT dbadmin TO alice;
GRANT ROLE

Modifying predefined Roles

Excluding SYSMONITOR, you can grant predefined roles privileges on individual database objects, such as tables or schemas. For example:

=> CREATE SCHEMA s1;
CREATE SCHEMA
=> GRANT ALL ON SCHEMA s1 to PUBLIC;
GRANT PRIVILEGE

You can grant PUBLIC any role, including predefined roles. For example:

=> CREATE ROLE r1;
CREATE ROLE
=> GRANT r1 TO PUBLIC;
GRANT ROLE

You cannot modify any other predefined role by granting another role to it. Attempts to do so return a rollback error:

=> CREATE ROLE r2;
CREATE ROLE
=> GRANT r2 TO PSEUDOSUPERUSER;
ROLLBACK 2347:  Cannot alter predefined role "pseudosuperuser"

2.1.1 - DBADMIN

The DBADMIN role is a predefined role that is assigned to the dbadmin user on database installation. Thereafter, the dbadmin user and users with the PSEUDOSUPERUSER role can grant any role to any user or non-predefined role.

For example, superuser dbadmin creates role fred and grants fred the DBADMIN role:

=> CREATE USER fred;
CREATE USER
=> GRANT DBADMIN TO fred WITH ADMIN OPTION;
GRANT ROLE

After user fred enables its DBADMIN role, he can exercise his DBADMIN privileges by creating user alice. Because the GRANT statement includes WITH ADMIN OPTION, fred can also grant the DBADMIN role to user alice:

=> \c - fred
You are now connected as user "fred".
=> SET ROLE dbadmin;
SET
CREATE USER alice;
CREATE USER
=> GRANT DBADMIN TO alice;
GRANT ROLE

DBADMIN privileges

The following table lists privileges that are supported for the DBADMIN role:

• Create users and roles, and grant them roles and privileges

• Create and drop schemas

• View all system tables

• View and terminate user sessions

• Access all data created by any user

2.1.2 - PSEUDOSUPERUSER

The PSEUDOSUPERUSER role is a predefined role that is automatically assigned to the dbadmin user on database installation. The dbadmin can grant this role to any user or non-predefined role. Thereafter, PSEUDOSUPERUSER users can grant any role, including predefined roles, to other users.

PSEUDOSUPERUSER privileges

Users with the PSEUDOSUPERUSER role are entitled to complete administrative privileges, which cannot be revoked. Role privileges include:

• Bypass all GRANT/REVOKE authorization

• Create schemas and tables

• Create users and roles, and grant privileges to them

• Modify user accounts—for example, set user account's passwords, and lock/unlock accounts.

• Create or drop a UDF library and function, or any external procedure

2.1.3 - DBDUSER

The DBDUSER role is a predefined role that is assigned to the dbadmin user on database installation. The dbadmin and any PSEUDOSUPERUSER can grant this role to any user or non-predefined role. Users who have this role and enable it can call Database Designer functions from the command line.

Associating DBDUSER with resource pools

Be sure to associate a resource pool with the DBDUSER role, to facilitate resource management when you run Database Designer. Multiple users can run Database Designer concurrently without interfering with each other or exhausting all the cluster resources. Whether you run Database Designer programmatically or with Administration Tools, design execution is generally contained by the user's resource pool, but might spill over into system resource pools for less-intensive tasks.

2.1.4 - SYSMONITOR

An organization's database administrator may have many responsibilities outside of maintaining Vertica as a DBADMIN user. In this case, as the DBADMIN you may want to delegate some Vertica administrative tasks to another Vertica user.

The DBADMIN can assign a delegate the SYSMONITOR role to grant access to system tables without granting full DBADMIN access.

The SYSMONITOR role provides the following privileges.

• View all system tables that are marked as monitorable. You can see a list of all the monitorable tables by issuing the statement:

  => SELECT * FROM system_tables WHERE is_monitorable='t';
  

• If WITH ADMIN OPTION was included when granting SYSMONITOR to the user or role, that user or role can then grant SYSMONITOR privileges to other users and roles.

Grant a SYSMONITOR role

To grant a user or role the SYSMONITOR role, you must be one of the following:

• a DBADMIN user

• a user assigned the SYSMONITOR who has the ADMIN OPTION

Use the GRANT (Role) SQL statement to assign a user the SYSMONITOR role. This example shows how to grant the SYSMONITOR role to user1 and includes administration privileges by using the WITH ADMIN OPTION parameter. The ADMIN OPTION grants the SYSMONITOR role administrative privileges:

=> GRANT SYSMONITOR TO user1 WITH ADMIN OPTION;

This example shows how to revoke the ADMIN OPTION from the SYSMONITOR role for user1:

=> REVOKE ADMIN OPTION for SYSMONITOR FROM user1;

Use CASCADE to revoke ADMIN OPTION privileges for all users assigned the SYSMONITOR role:

=> REVOKE ADMIN OPTION for SYSMONITOR FROM PUBLIC CASCADE;

Example

This example shows how to:

• Create a user

• Create a role

• Grant SYSMONITOR privileges to the new role

• Grant the role to the user

=> CREATE USER user1;
=> CREATE ROLE monitor;
=> GRANT SYSMONITOR TO monitor;
=> GRANT monitor TO user1;

Assign SYSMONITOR privileges

This example uses the user and role created in the Grant SYSMONITOR Role example and shows how to:

• Create a table called personal_data

• Log in as user1

• Grant user1 the monitor role. (You already granted the monitor SYSMONITOR privileges in the Grant a SYSMONITOR Role example.)

• Run a SELECT statement as user1

The results of the operations are based on the privilege already granted to user1.

=> CREATE TABLE personal_data (SSN varchar (256));
=> \c -user1;
=> SET ROLE monitor;
=> SELECT COUNT(*) FROM TABLES;
 COUNT
-------
 1
(1 row)

Because you assigned the SYSMONITOR role, user1 can see the number of rows in the Tables system table. In this simple example, there is only one table (personal_data) in the database so the SELECT COUNT returns one row. In actual conditions, the SYSMONITOR role would see all the tables in the database.

Check if a table is accessible by SYSMONITOR

To check if a system table can be accessed by a user assigned the SYSMONITOR role:

=> SELECT table_name, is_monitorable FROM system_tables WHERE table_name='table_name'

For example, the following statement shows that the CURRENT_SESSION system table is accessible by the SYSMONITOR:

=> SELECT table_name, is_monitorable FROM system_tables WHERE table_name='current_session';
   table_name    | is_monitorable
-----------------+----------------
 current_session | t
(1 row)

2.1.5 - UDXDEVELOPER

The UDXDEVELOPER role is a predefined role that enables users to create and replace user-defined libraries. The dbadmin can grant this role to any user or non-predefined role.

UDXDEVELOPER privileges

Users with the UDXDEVELOPER role can perform the following actions:

• CREATE LIBRARY

• If library owner or with the DROP privilege:

  • CREATE OR REPLACE LIBRARY

  • DROP LIBRARY
  • ALTER LIBRARY

To use the privileges of this role, you must explicitly enable it using SET ROLE.

Security considerations

A user with the UDXDEVELOPER role can create libraries and, therefore, can install any UDx function in the database. UDx functions run as the Linux user that owns the database, and therefore have access to resources that Vertica has access to.

A poorly-written function can degrade database performance. Give this role only to users you trust to use UDxs responsibly. You can limit the memory that a UDx can consume by running UDxs in fenced mode and by setting the FencedUDxMemoryLimitMB configuration parameter.

2.1.6 - PUBLIC

The PUBLIC role is a predefined role that is automatically assigned to all new users. It is always enabled and cannot be dropped or revoked. Use this role to grant all database users the same minimum set of privileges.

Like any role, the PUBLIC role can be granted privileges to individual objects and other roles. The following example grants the PUBLIC role INSERT and SELECT privileges on table publicdata. This enables all users to read data in that table and insert new data:

=> CREATE TABLE publicdata (a INT, b VARCHAR);
CREATE TABLE
=> GRANT INSERT, SELECT ON publicdata TO PUBLIC;
GRANT PRIVILEGE
=> CREATE PROJECTION publicdataproj AS (SELECT * FROM publicdata);
CREATE PROJECTION
=> \c - bob
You are now connected as user "bob".
=> INSERT INTO publicdata VALUES (10, 'Hello World');
OUTPUT
--------
      1
(1 row)

The following example grants PUBLIC the employee role, so all database users have employee privileges:

=> GRANT employee TO public;
GRANT ROLE

2.2 - Role hierarchy

By granting roles to other roles, you can build a hierarchy of roles, where roles lower in the hierarchy have a narrow range of privileges, while roles higher in the hierarchy are granted combinations of roles and their privileges. When you organize roles hierarchically, any privileges that you add to lower-level roles are automatically propagated to the roles above them.

Creating hierarchical roles

The following example creates two roles, assigns them privileges, then assigns both roles to another role.

1. Create table applog:

   => CREATE TABLE applog (id int, sourceID VARCHAR(32), data TIMESTAMP, event VARCHAR(256));
   

2. Create the logreader role and grant it read-only privileges on table applog:

   => CREATE ROLE logreader;
   CREATE ROLE
   => GRANT SELECT ON applog TO logreader;
   GRANT PRIVILEGE
   

3. Create the logwriter role and grant it write privileges on table applog:

   => CREATE ROLE logwriter;
   CREATE ROLE
   => GRANT INSERT, UPDATE ON applog to logwriter;
   GRANT PRIVILEGE
   

4. Create the logadmin role and grant it DELETE privilege on table applog:

   => CREATE ROLE logadmin;
   CREATE ROLE
   => GRANT DELETE ON applog to logadmin;
   GRANT PRIVILEGE
   

5. Grant the logreader and logwriter roles to role logadmin:

   => GRANT logreader, logwriter TO logadmin;
   

6. Create user bob and grant him the logadmin role:

   => CREATE USER bob;
   CREATE USER
   => GRANT logadmin TO bob;
   GRANT PRIVILEGE
   

7. Modify user bob's account so his logadmin role is automatically enabled on login:

   
   => ALTER USER bob DEFAULT ROLE logadmin;
   ALTER USER
   => \c - bob
   You are now connected as user "bob".
   => SHOW ENABLED_ROLES;
        name      | setting
   ---------------+----------
    enabled roles | logadmin
   (1 row)
   

Enabling hierarchical roles

Only roles that are explicitly granted to a user can be enabled for that user. In the previous example, roles logreader or logwriter cannot be enabled for bob. They can only be enabled indirectly, by enabling logadmin.

Hierarchical role grants and WITH ADMIN OPTION

If one or more roles are granted to another role using WITH ADMIN OPTION, then users who are granted the 'higher' role inherit administrative access to the subordinate roles.

For example, you might modify the earlier grants of roles logreader and logwriter to logadmin as follows:

=> GRANT logreader, logwriter TO logadmin WITH ADMIN OPTION;
NOTICE 4617:  Role "logreader" was already granted to role "logadmin"
NOTICE 4617:  Role "logwriter" was already granted to role "logadmin"
GRANT ROLE

User bob , through his logadmin role, is now authorized to grant its two subordinate roles to other users—in this case, role logreader to user Alice:

=> \c - bob;
You are now connected as user "bob".
=> GRANT logreader TO Alice;
GRANT ROLE
=> \c - alice;
You are now connected as user "alice".
=> show available_roles;
      name       |  setting
-----------------+-----------
 available roles | logreader
(1 row)

=> \c - bob;
You are now connected as user "bob".
=> GRANT logadmin TO alice;
ROLLBACK 4925:  The role "logadmin" cannot be granted to "alice"

2.3 - Creating and dropping roles

As a superuser with the DBADMIN or PSEUDOSUPERUSER role, you can create and drop roles with CREATE ROLE and DROP ROLE, respectively.

=> CREATE ROLE administrator;
CREATE ROLE

A new role has no privileges or roles granted to it. Only superusers can grant privileges and access to the role.

Dropping database roles with dependencies

If you try to drop a role that is granted to users or other roles Vertica returns a rollback message:

=> DROP ROLE administrator;
NOTICE:  User Bob depends on Role administrator
ROLLBACK:  DROP ROLE failed due to dependencies
DETAIL:  Cannot drop Role administrator because other objects depend on it
HINT:  Use DROP ROLE ... CASCADE to remove granted roles from the dependent users/roles

To force the drop operation, qualify the DROP ROLE statement with CASCADE:

=> DROP ROLE administrator CASCADE;
DROP ROLE

2.4 - Granting privileges to roles

You can use GRANT statements to assign privileges to a role, just as you assign privileges to users. See Database privileges for information about which privileges can be granted.

Granting a privilege to a role immediately affects active user sessions. When you grant a privilege to a role, it becomes immediately available to all users with that role enabled.

The following example creates two roles and assigns them different privileges on the same table.

1. Create table applog:

   => CREATE TABLE applog (id int, sourceID VARCHAR(32), data TIMESTAMP, event VARCHAR(256));
   

2. Create roles logreader and logwriter:

   => CREATE ROLE logreader;
   CREATE ROLE
   => CREATE ROLE logwriter;
   CREATE ROLE
   

3. Grant read-only privileges on applog to logreader, and write privileges to logwriter:

   => GRANT SELECT ON applog TO logreader;
   GRANT PRIVILEGE
   => GRANT INSERT ON applog TO logwriter;
   GRANT PRIVILEGE
   

Revoking privileges from roles

Use REVOKE statements to revoke a privilege from a role. Revoking a privilege from a role immediately affects active user sessions. When you revoke a privilege from a role, it is no longer available to users who have the privilege through that role.

For example:

=> REVOKE INSERT ON applog FROM logwriter;
REVOKE PRIVILEGE

2.5 - Granting database roles

You can assign one or more roles to a user or another role with GRANT (Role):

GRANT role[,...] TO grantee[,...] [ WITH ADMIN OPTION ]

For example, you might create three roles—appdata, applogs, and appadmin—and grant appadmin to user bob:

=> CREATE ROLE appdata;
CREATE ROLE
=> CREATE ROLE applogs;
CREATE ROLE
=> CREATE ROLE appadmin;
CREATE ROLE
=> GRANT appadmin TO bob;
GRANT ROLE

Granting roles to another role

GRANT can assign one or more roles to another role. For example, the following GRANT statement grants roles appdata and applogs to role appadmin:

=> GRANT appdata, applogs TO appadmin;
 -- grant to other roles
GRANT ROLE

Because user bob was previously assigned the role appadmin, he now has all privileges that are granted to roles appdata and applogs.

When you grant one role to another role, Vertica checks for circular references. In the previous example, role appdata is assigned to the appadmin role. Thus, subsequent attempts to assign appadmin to appdata fail, returning with the following warning:

=> GRANT appadmin TO appdata;
WARNING:  Circular assignation of roles is not allowed
HINT:  Cannot grant appadmin to appdata
GRANT ROLE

Enabling roles

After granting a role to a user, the role must be enabled. You can enable a role for the current session:

=> SET ROLE appdata;
SET ROLE

You can also enable a role as part of the user's login, by modifying the user's profile with ALTER USER...DEFAULT ROLE:

=> ALTER USER bob DEFAULT ROLE appdata;
ALTER USER

For details, see Enabling roles and Enabling roles automatically.

Granting administrative privileges

You can delegate to non-superusers users administrative access to a role by qualifying the GRANT (Role) statement with the option WITH ADMIN OPTION. Users with administrative access can manage access to the role for other users, including granting them administrative access. In the following example, a superuser grants the appadmin role with administrative privileges to users bob and alice.

=> GRANT appadmin TO bob, alice WITH ADMIN OPTION;
GRANT ROLE

Now, both users can exercise their administrative privileges to grant the appadmin role to other users, or revoke it. For example, user bob can now revoke the appadmin role from user alice:

=> \connect - bob
You are now connected as user "bob".
=> REVOKE appadmin FROM alice;
REVOKE ROLE

Example

The following example creates a role called commenter and grants that role to user bob:

1. Create the comments table:

   => CREATE TABLE comments (id INT, comment VARCHAR);
   

2. Create the commenter role:

   => CREATE ROLE commenter;
   

3. Grant to commenter INSERT and SELECT privileges on the comments table:

   => GRANT INSERT, SELECT ON comments TO commenter;
   

4. Grant the commenter role to user bob.

   => GRANT commenter TO bob;
   

5. In order to access the role and its associated privileges, bob enables the newly-granted role for himself:

   => \c - bob
   => SET ROLE commenter;
   

6. Because bob has INSERT and SELECT privileges on the comments table, he can perform the following actions:

   => INSERT INTO comments VALUES (1, 'Hello World');
    OUTPUT
   --------
         1
   (1 row)
   => SELECT * FROM comments;
    id |   comment
   ----+-------------
     1 | Hello World
   (1 row)
   => COMMIT;
   COMMIT
   

7. Because bob's role lacks DELETE privileges, the following statement returns an error:

   
   => DELETE FROM comments WHERE id=1;
   ERROR 4367:  Permission denied for relation comments
   

See also

2.6 - Revoking database roles

REVOKE (Role) can revoke roles from one or more grantees—that is, from users or roles:

REVOKE [ ADMIN OPTION FOR ] role[,...] FROM grantee[,...] [ CASCADE ]

For example, the following statement revokes the commenter role from user bob:

=> \c
You are now connected as user "dbadmin".
=> REVOKE commenter FROM bob;
REVOKE ROLE

Revoking administrative access from a role

You can qualify REVOKE (Role) with the clause ADMIN OPTION FOR. This clause revokes from the grantees the authority (granted by an earlier GRANT (Role)...WITH ADMIN OPTION statement) to grant the specified roles to other users or roles. Current roles for the grantees are unaffected.

The following example revokes user Alice's authority to grant and revoke the commenter role:

=> \c
You are now connected as user "dbadmin".
=> REVOKE ADMIN OPTION FOR commenter FROM alice;
REVOKE ROLE

2.7 - Enabling roles

When you enable a role in a session, you obtain all privileges assigned to that role. You can enable multiple roles simultaneously, thereby gaining all privileges of those roles, plus any privileges that are already granted to you directly.

By default, only predefined roles are enabled automatically for users. Otherwise, on starting a session, you must explicitly enable assigned roles with the Vertica function SET ROLE.

For example, the dbadmin creates the logreader role and assigns it to user alice:

=> \c
You are now connected as user "dbadmin".
=> CREATE ROLE logreader;
CREATE ROLE
=> GRANT SELECT ON TABLE applog to logreader;
GRANT PRIVILEGE
=> GRANT logreader TO alice;
GRANT ROLE

User alice must enable the new role before she can view the applog table:

=> \c - alice
You are now connected as user "alice".
=> SELECT * FROM applog;
ERROR:  permission denied for relation applog
=> SET ROLE logreader;
SET
=> SELECT * FROM applog;
 id | sourceID |            data            |                    event
----+----------+----------------------------+----------------------------------------------
  1 | Loader   | 2011-03-31 11:00:38.494226 | Error: Failed to open source file
  2 | Reporter | 2011-03-31 11:00:38.494226 | Warning: Low disk space on volume /scratch-a
(2 rows)

Enabling all user roles

You can enable all roles available to your user account with SET ROLE ALL:

=> SET ROLE ALL;
SET
=> SHOW ENABLED_ROLES;
     name      |           setting
---------------+------------------------------
 enabled roles | logreader, logwriter
(1 row)

Disabling roles

A user can disable all roles with SET ROLE NONE. This statement disables all roles for the current session, excluding predefined roles:

=> SET ROLE NONE;
=> SHOW ENABLED_ROLES;
     name      | setting
---------------+---------
 enabled roles |
(1 row)

2.8 - Enabling roles automatically

By default, new users are assigned the PUBLIC, which is automatically enabled when a new session starts. Typically, other roles are created and users are assigned to them, but these are not automatically enabled. Instead, users must explicitly enable their assigned roles with each new session, with SET ROLE.

You can automatically enable roles for users in two ways:

• Enable roles for individual users on login

• Enable all roles for all users on login

Enable roles for individual users

After assigning roles to users, you can set one or more default roles for each user by modifying their profiles, with ALTER USER...DEFAULT ROLE. User default roles are automatically enabled at the start of the user session. You should consider setting default roles for users if they typically rely on the privileges of those roles to carry out routine tasks.

The following example shows how to set regional_manager as the default role for user LilyCP:

=> \c
You are now connected as user "dbadmin".
=> GRANT regional_manager TO LilyCP;
GRANT ROLE
=> ALTER USER LilyCP DEFAULT ROLE regional_manager;
ALTER USER
=> \c - LilyCP
You are now connected as user "LilyCP".
=> SHOW ENABLED_ROLES;
     name      |     setting
---------------+------------------
 enabled roles | regional_manager
(1 row)

Enable all roles for all users

Configuration parameter EnableAllRolesOnLogin specifies whether to enable all roles for all database users on login. By default, this parameter is set to 0. If set to 1, Vertica enables the roles of all users when they log in to the database.

Clearing default roles

You can clear all default role assignments for a user with ALTER USER...DEFAULT ROLE NONE. For example:

=> ALTER USER fred DEFAULT ROLE NONE;
ALTER USER
=> SELECT user_name, default_roles, all_roles FROM users WHERE user_name = 'fred';
 user_name | default_roles | all_roles
-----------+---------------+-----------
 fred      |               | logreader
(1 row)

2.9 - Viewing user roles

You can obtain information about roles in three ways:

• Verify specific role assignments with the function HAS_ROLE.

• View all available (granted) and enabled roles.

• Obtain comprehensive information about roles, the users assigned to them, and the privileges granted to those users and roles by querying system tables ROLES, USERS, AND GRANTS, respectively.

  Note

  System tables do not show whether a role is available to a user indirectly through other roles. Call HAS_ROLE to obtain that information.

Verifying role assignments

The function HAS_ROLE checks whether a Vertica role is granted to the specified user or role. Non-superusers can use this function to check their own role membership. Superusers can use it to determine role assignments for other users and roles. You can also use Management Console to check role assignments.

In the following example, a dbadmin user checks whether user MikeL is assigned the admnistrator role:

=> \c
You are now connected as user "dbadmin".
=> SELECT HAS_ROLE('MikeL', 'administrator');
 HAS_ROLE
----------
 t
(1 row)

User MikeL checks whether he has the regional_manager role:

=> \c - MikeL
You are now connected as user "MikeL".
=> SELECT HAS_ROLE('regional_manager');
 HAS_ROLE
----------
 f
(1 row)

The dbadmin grants the regional_manager role to the administrator role. On checking again, MikeL verifies that he now has the regional_manager role:

dbadmin=> \c
You are now connected as user "dbadmin".
dbadmin=> GRANT regional_manager to administrator;
GRANT ROLE
dbadmin=> \c - MikeL
You are now connected as user "MikeL".
dbadmin=> SELECT HAS_ROLE('regional_manager');
 HAS_ROLE
----------
 t
(1 row)

Viewing available and enabled roles

SHOW AVAILABLE ROLES lists all roles granted to you:

=> SHOW AVAILABLE ROLES;
      name       |           setting
-----------------+-----------------------------
 available roles | logreader, logwriter
(1 row)

SHOW ENABLED ROLES lists the roles enabled in your session:

=> SHOW ENABLED ROLES;
     name      | setting
---------------+----------
 enabled roles | logreader
(1 row)

Querying system tables

You can query tables ROLES, USERS, AND GRANTS, either separately or joined, to obtain detailed information about user roles, users assigned to those roles, and the privileges granted explicitly to users and implicitly through roles.

The following query on ROLES returns the names of all roles users can access, and the roles granted (assigned) to those roles. An asterisk (*) appended to a role indicates that the user can grant the role to other users:

=> SELECT * FROM roles;
      name       | assigned_roles
-----------------+----------------
 public          |
 dbduser         |
 dbadmin         | dbduser*
 pseudosuperuser | dbadmin*
 logreader       |
 logwriter       |
 logadmin        | logreader, logwriter
(7 rows)

The following query on system table USERS returns all users with the DBADMIN role. An asterisk (*) appended to a role indicates that the user can grant the role to other users:

=> SELECT user_name, is_super_user, default_roles, all_roles FROM v_catalog.users WHERE all_roles ILIKE '%dbadmin%';
 user_name | is_super_user |            default_roles             |              all_roles
-----------+---------------+--------------------------------------+--------------------------------------
 dbadmin   | t             | dbduser*, dbadmin*, pseudosuperuser* | dbduser*, dbadmin*, pseudosuperuser*
 u1        | f             |                                      | dbadmin*
 u2        | f             |                                      | dbadmin
(3 rows)

The following query on system table GRANTS returns the privileges granted to user Jane or role R1. An asterisk (*) appended to a privilege indicates that the user can grant the privilege to other users:

=> SELECT grantor,privileges_description,object_name,object_type,grantee FROM grants WHERE grantee='Jane' OR grantee='R1';
grantor | privileges_description | object_name | object_type  |  grantee
--------+------------------------+-------------+--------------+-----------
dbadmin | USAGE                  | general     | RESOURCEPOOL | Jane
dbadmin |                        | R1          | ROLE         | Jane
dbadmin | USAGE*                 | s1          | SCHEMA       | Jane
dbadmin | USAGE, CREATE*         | s1          | SCHEMA       | R1
(4 rows)

3 - Database privileges

When a database object is created, such as a schema, table, or view, ownership of that object is assigned to the user who created it. By default, only the object's owner, and users with superuser privileges such as database administrators, have privileges on a new object. Only these users (and other users whom they explicitly authorize) can grant object privileges to other users

Privileges are granted and revoked by GRANT and REVOKE statements, respectively. The privileges that can be granted on a given object are specific to its type. For example, table privileges include SELECT, INSERT, and UPDATE, while library and resource pool privileges have USAGE privileges only. For a summary of object privileges, see Database object privileges.

Because privileges on database objects can come from several different sources like explicit grants, roles, and inheritance, privileges can be difficult to monitor. Use the GET_PRIVILEGES_DESCRIPTION meta-function to check the current user's effective privileges across all sources on a specified database object.

3.1 - Ownership and implicit privileges

All users have implicit privileges on the objects that they own. On creating an object, its owner automatically is granted all privileges associated with the object's type (see Database object privileges). Regardless of object type, the following privileges are inseparable from ownership and cannot be revoked, not even by the owner:

• Authority to grant all object privileges to other users, and revoke them

• ALTER (where applicable) and DROP

• Extension of privilege granting authority on their objects to other users, and revoking that authority

Object owners can revoke all non-implicit, or ordinary, privileges from themselves. For example, on creating a table, its owner is automatically granted all implicit and ordinary privileges:

If user Joan creates table t1, she can revoke ordinary privileges UPDATE and INSERT from herself, which effectively makes this table read-only:

=> \c - Joan
You are now connected as user "Joan".
=> CREATE TABLE t1 (a int);
CREATE TABLE
=> INSERT INTO t1 VALUES (1);
 OUTPUT
--------
      1
(1 row)

=> COMMIT;
COMMIT
=> REVOKE UPDATE, INSERT ON TABLE t1 FROM Joan;
REVOKE PRIVILEGE
=> INSERT INTO t1 VALUES (3);
ERROR 4367:  Permission denied for relation t1
=> SELECT * FROM t1;
 a
---
 1
(1 row)

Joan can subsequently restore UPDATE and INSERT privileges to herself:

=> GRANT UPDATE, INSERT on TABLE t1 TO Joan;
GRANT PRIVILEGE
dbadmin=> INSERT INTO t1 VALUES (3);
 OUTPUT
--------
      1
(1 row)

=> COMMIT;
COMMIT
dbadmin=> SELECT * FROM t1;
 a
---
 1
 3
(2 rows)

3.2 - Inherited privileges

You can manage inheritance of privileges at three levels:

• Database

• Schema

• Tables and views

By default, inherited privileges are enabled at the database level and disabled at the schema level. If privilege inheritance is enabled at both levels, new tables and views automatically inherit those privileges when they are created. You can also exclude inheritance from specific tables and views.

3.2.1 - Enabling database inheritance

By default, inherited privileges are enabled at the database level, through configuration parameter disableinheritedprivileges. To enable inherited privileges:

=> ALTER DATABASE [database name] SET disableinheritedprivileges = 0;

To disable inherited privileges:

=> ALTER DATABASE [database name] SET disableinheritedprivileges = 1;

3.2.2 - Enabling schema inheritance

By default, inherited privileges are disabled at the schema level. If inherited privileges are enabled for the database, you can enable inheritance of schema privileges by its tables and views, with CREATE SCHEMA and ALTER SCHEMA. Unless explicitly excluded, privileges granted on the schema are automatically inherited by all new tables and views in it.

For information about which tables and views inherit privileges from which schemas, see INHERITING_OBJECTS.

For information about which privileges each table or view inherits, see the INHERITED_PRIVILEGES.

Inherited privileges are globally disabled; schema parameter is set but has no effect.

Enabling inheritance of schema privileges has no effect on existing tables and views. You must explicitly set schema inheritance on them with ALTER TABLE and ALTER VIEW. You can also explicitly exclude tables and views from inheriting schema privileges with CREATE TABLE/ALTER TABLE, and CREATE VIEW/ALTER VIEW, respectively.

You can enable schema privilege inheritance during schema creation with the following statement:

=> CREATE SCHEMA s1 DEFAULT INCLUDE PRIVILEGES;

If the schema already exists, you can use ALTER SCHEMA to have all newly created tables and views inherit the privileges of the schema. Tables and views created on the schema before this statement are not affected:

=> ALTER SCHEMA s1 DEFAULT INCLUDE PRIVILEGES;

After enabling inherited privileges on a schema, you can grant privileges on it to users and roles with GRANT (schema):

=> GRANT USAGE, CREATE, SELECT, INSERT ON SCHEMA S1 TO PUBLIC;
GRANT PRIVILEGE

See also

• Setting privilege inheritance on tables and views
• Granting and revoking privileges

3.2.3 - Setting privilege inheritance on tables and views

If inherited privileges are enabled for the database and a schema, privileges granted to the schema are automatically granted to all new tables and views in it. You can also explicitly exclude tables and views from inheriting schema privileges.

For information about which tables and views inherit privileges from which schemas, see INHERITING_OBJECTS.

For information about which privileges each table or view inherits, see the INHERITED_PRIVILEGES.

Set privileges inheritance on tables and views

CREATE TABLE/ALTER TABLE and CREATE VIEW/ALTER VIEW can allow tables and views to inherit privileges from their parent schemas. For example, the following statements enable inheritance on schema s1, so new table s1.t1 and view s1.myview automatically inherit the privileges set on that schema as applicable:

=> CREATE SCHEMA s1 DEFAULT INCLUDE PRIVILEGES;
CREATE SCHEMA
=> GRANT USAGE, CREATE, SELECT, INSERT ON SCHEMA S1 TO PUBLIC;
GRANT PRIVILEGE
=> CREATE TABLE s1.t1 ( ID int, f_name varchar(16), l_name(24));
WARNING 6978:  Table "t1" will include privileges from schema "s1"
CREATE TABLE
=> CREATE VIEW s1.myview AS SELECT ID, l_name FROM s1.t1
WARNING 6978:  View "myview" will include privileges from schema "s1"
CREATE VIEW

If the schema already exists, you can use ALTER SCHEMA to have all newly created tables and views inherit the privileges of the schema. Tables and views created on the schema before this statement, however, are not affected:

=> CREATE SCHEMA s2;
CREATE SCHEMA
=> CREATE TABLE s2.t22 ( a int );
CREATE TABLE
...
=> ALTER SCHEMA S2 DEFAULT INCLUDE PRIVILEGES;
ALTER SCHEMA

In this case, inherited privileges were enabled on schema s2 after it already contained table s2.t22. To set inheritance on this table and other existing tables and views, you must explicitly set schema inheritance on them with ALTER TABLE and ALTER VIEW:

=> ALTER TABLE s2.t22 INCLUDE SCHEMA PRIVILEGES;

Exclude privileges inheritance from tables and views

You can use CREATE TABLE/ALTER TABLE and CREATE VIEW/ALTER VIEW to prevent table and views from inheriting schema privileges.

The following example shows how to create a table that does not inherit schema privileges:

=> CREATE TABLE s1.t1 ( x int) EXCLUDE SCHEMA PRIVILEGES;

You can modify an existing table so it does not inherit schema privileges:

=> ALTER TABLE s1.t1 EXCLUDE SCHEMA PRIVILEGES;

3.2.4 - Example usage: implementing inherited privileges

The following steps show how user Joe enables inheritance of privileges on a given schema so other users can access tables in that schema.

1. Joe creates schema schema1, and creates table table1 in it:

   
   =>\c - Joe
   You are now connected as user Joe
   => CREATE SCHEMA schema1;
   CRDEATE SCHEMA
   => CREATE TABLE schema1.table1 (id int);
   CREATE TABLE
   

2. Joe grants USAGE and CREATE privileges on schema1 to Myra:

   
   => GRANT USAGE, CREATE ON SCHEMA schema1 to Myra;
   GRANT PRIVILEGE
   

3. Myra queries schema1.table1, but the query fails:

   
   =>\c - Myra
   You are now connected as user Myra
   => SELECT * FROM schema1.table1;
   ERROR 4367: Permission denied for relation table1
   

4. Joe grants Myra SELECT ON SCHEMA privileges on schema1:

   
   =>\c - Joe
   You are now connected as user Joe
   => GRANT SELECT ON SCHEMA schema1 to Myra;
   GRANT PRIVILEGE
   

5. Joe uses ALTER TABLE to include SCHEMA privileges for table1:

   
   => ALTER TABLE schema1.table1 INCLUDE SCHEMA PRIVILEGES;
   ALTER TABLE
   

6. Myra's query now succeeds:

   
   =>\c - Myra
   You are now connected as user Myra
   => SELECT * FROM schema1.table1;
   id
   ---
   (0 rows)
   

7. Joe modifies schema1 to include privileges so all tables created in schema1 inherit schema privileges:

   
   =>\c - Joe
   You are now connected as user Joe
   => ALTER SCHEMA schema1 DEFAULT INCLUDE PRIVILEGES;
   ALTER SCHEMA
   => CREATE TABLE schema1.table2 (id int);
   CREATE TABLE
   

8. With inherited privileges enabled, Myra can query table2 without Joe having to explicitly grant privileges on the table:

   
   =>\c - Myra
   You are now connected as user Myra
   => SELECT * FROM schema1.table2;
   id
   ---
   (0 rows)
   

3.3 - Default user privileges

To set the minimum level of privilege for all users, Vertica has the special PUBLIC, which it grants to each user automatically. This role is automatically enabled, but the database administrator or a superuser can also grant higher privileges to users separately using GRANT statements.

Default privileges for MC users

Privileges on Management Console (MC) are managed through roles, which determine a user's access to MC and to MC-managed Vertica databases through the MC interface. MC privileges do not alter or override Vertica privileges or roles. See Users, roles, and privileges for details.

3.4 - Effective privileges

A user's effective privileges on an object encompass privileges of all types, including:

• Implicit privileges through object ownership

• Explicit privileges through GRANT statements on objects

• Inherited privileges through privileges on objects with inheritance enabled

You can view your effective privileges on an object with the GET_PRIVILEGES_DESCRIPTION meta-function.

3.5 - Privileges required for common database operations

This topic lists the required privileges for database objects in Vertica.

Unless otherwise noted, superusers can perform all operations shown in the following tables. Object owners always can perform operations on their own objects.

Schemas

The PUBLIC schema is present in any newly-created Vertica database. Newly-created users must be granted access to this schema:

=> GRANT USAGE ON SCHEMA public TO user;

A database superuser must also explicitly grant new users CREATE privileges, as well as grant them individual object privileges so the new users can create or look up objects in the PUBLIC schema.

Tables

Views

Projections

External procedures

Stored procedures

Libraries

User-defined functions

Sequences

Resource pools

Users/profiles/roles

Object visibility

You can use one or a combination of vsql \d meta commands and SQL system tables to view objects on which you have privileges to view.

• Use \dn to view schema names and owners

• Use \dt to view all tables in the database, as well as the system table V_CATALOG.TABLES

• Use \dj to view projections showing the schema, projection name, owner, and node, as well as the system table V_CATALOG.PROJECTIONS

I/O operations

Comments

Transactions

Sessions

Tuning operations

TLS configuration

3.6 - Database object privileges

Privileges can be granted explicitly on most user-visible objects in a Vertica database, such as tables and models. For some objects such as projections, privileges are implicitly derived from other objects.

Explicitly granted privileges

The following table provides an overview of privileges that can be explicitly granted on Vertica database objects:

Implicitly granted privileges

Metadata privileges

Superusers have unrestricted access to all database metadata. For non-superusers, access to the metadata of specific objects depends on their privileges on those objects:

Projection privileges

Projections, which store table data, do not have an owner or privileges directly associated with them. Instead, the privileges to create, access, or alter a projection are derived from the privileges that are set on its anchor tables and respective schemas.

3.7 - Granting and revoking privileges

Vertica supports GRANT and REVOKE statements to control user access to database objects—for example, GRANT (schema) and REVOKE (schema), GRANT (table) and REVOKE (table), and so on. Typically, a superuser creates users and roles shortly after creating the database, and then uses GRANT statements to assign them privileges.

Where applicable, GRANT statements require USAGE privileges on the object schema. The following users can grant and revoke privileges:

• Superusers: all privileges on all database objects, including the database itself

• Non-superusers: all privileges on objects that they own

• Grantees of privileges that include WITH GRANT OPTION: the same privileges on that object

In the following example, a dbadmin (with superuser privileges) creates user Carol. Subsequent GRANT statements grant Carol schema and table privileges:

• CREATE and USAGE privileges on schema PUBLIC

• SELECT, INSERT, and UPDATE privileges on table public.applog. This GRANT statement also includes WITH GRANT OPTION. This enables Carol to grant the same privileges on this table to other users —in this case, SELECT privileges to user Tom:

=> CREATE USER Carol;
CREATE USER
=> GRANT CREATE, USAGE ON SCHEMA PUBLIC to Carol;
GRANT PRIVILEGE
=> GRANT SELECT, INSERT, UPDATE ON TABLE public.applog TO Carol WITH GRANT OPTION;
GRANT PRIVILEGE
=> GRANT SELECT ON TABLE public.applog TO Tom;
GRANT PRIVILEGE

3.7.1 - Superuser privileges

A Vertica superuser is a database user—by default, named dbadmin—that is automatically created on installation. Vertica superusers have complete and irrevocable authority over database users, privileges, and roles.

Superusers can change the privileges of any user and role, as well as override any privileges that are granted by users with the PSEUDOSUPERUSER role. They can also grant and revoke privileges on any user-owned object, and reassign object ownership.

See also

3.7.2 - Schema owner privileges

The schema owner is typically the user who creates the schema. By default, the schema owner has privileges to create objects within a schema. The owner can also alter the schema: reassign ownership, rename it, and enable or disable inheritance of schema privileges.

Schema ownership does not necessarily grant the owner access to objects in that schema. Access to objects depends on the privileges that are granted on them.

All other users and roles must be explicitly granted access to a schema by its owner or a superuser.

3.7.3 - Object owner privileges

The database, along with every object in it, has an owner. The object owner is usually the person who created the object, although a superuser can alter ownership of objects, such as table and sequence.

Object owners must have appropriate schema privilege to access, alter, rename, move or drop any object it owns without any additional privileges.

An object owner can also:

• Grant privileges on their own object to other users

  The WITH GRANT OPTION clause specifies that a user can grant the permission to other users. For example, if user Bob creates a table, Bob can grant privileges on that table to users Ted, Alice, and so on.

• Grant privileges to roles

  Users who are granted the role gain the privilege.

3.7.4 - Granting privileges

As described in Granting and revoking privileges, specific users grant privileges using the GRANT statement with or without the optional WITH GRANT OPTION, which allows the user to grant the same privileges to other users.

• A superuser can grant privileges on all object types to other users.

• A superuser or object owner can grant privileges to roles. Users who have been granted the role then gain the privilege.

• An object owner can grant privileges on the object to other users using the optional WITH GRANT OPTION clause.

• The user needs to have USAGE privilege on schema and appropriate privileges on the object.

When a user grants an explicit list of privileges, such as GRANT INSERT, DELETE, REFERENCES ON applog TO Bob:

• The GRANT statement succeeds only if all the roles are granted successfully. If any grant operation fails, the entire statement rolls back.

• Vertica will return ERROR if the user does not have grant options for the privileges listed.

When a user grants ALL privileges, such as GRANT ALL ON applog TO Bob, the statement always succeeds. Vertica grants all the privileges on which the grantor has the WITH GRANT OPTION and skips those privileges without the optional WITH GRANT OPTION.

For example, if the user Bob has delete privileges with the optional grant option on the applog table, only DELETE privileges are granted to Bob, and the statement succeeds:

=> GRANT DELETE ON applog TO Bob WITH GRANT OPTION;GRANT PRIVILEGE

For details, see the GRANT statements.

3.7.5 - Revoking privileges

The following non-superusers can revoke privileges on an object:

• Object owner

• Grantor of the object privileges

The user also must have USAGE privilege on the object's schema.

For example, the following query on system table V_CATALOG.GRANTS shows that users u1, u2, and u3 have the following privileges on schema s1 and table s1.t1:

 => SELECT object_type, object_name, grantee, grantor, privileges_description FROM v_catalog.grants
     WHERE object_name IN ('s1', 't1') AND grantee IN ('u1', 'u2', 'u3');
object_type | object_name | grantee | grantor |  privileges_description
-------------+-------------+---------+---------+---------------------------
 SCHEMA      | s1          | u1      | dbadmin | USAGE, CREATE
 SCHEMA      | s1          | u2      | dbadmin | USAGE, CREATE
 SCHEMA      | s1          | u3      | dbadmin | USAGE
 TABLE       | t1          | u1      | dbadmin | INSERT*, SELECT*, UPDATE*
 TABLE       | t1          | u2      | u1      | INSERT*, SELECT*, UPDATE*
 TABLE       | t1          | u3      | u2      | SELECT*
(6 rows)

In the following statements, u2 revokes the SELECT privileges that it granted on s1.t1 to u3 . Subsequent attempts by u3 to query this table return an error:

=> \c - u2
You are now connected as user "u2".
=> REVOKE SELECT ON s1.t1 FROM u3;
REVOKE PRIVILEGE
=> \c - u3
You are now connected as user "u2".
=> SELECT * FROM s1.t1;
ERROR 4367:  Permission denied for relation t1

Revoking grant option

If you revoke privileges on an object from a user, that user can no longer act as grantor of those same privileges to other users. If that user previously granted the revoked privileges to other users, the REVOKE statement must include the CASCADE option to revoke the privilege from those users too; otherwise, it returns with an error.

For example, user u2 can grant SELECT, INSERT, and UPDATE privileges, and grants those privileges to user u4:

=> \c - u2
You are now connected as user "u2".
=> GRANT SELECT, INSERT, UPDATE on TABLE s1.t1 to u4;
GRANT PRIVILEGE

If you query V_CATALOG.GRANTS for privileges on table s1.t1, it returns the following result set:

=> \ c
You are now connected as user "dbadmin".
=> SELECT object_type, object_name, grantee, grantor, privileges_description FROM v_catalog.grants
     WHERE object_name IN ('t1') ORDER BY grantee;
 object_type | object_name | grantee | grantor |                   privileges_description
-------------+-------------+---------+---------+------------------------------------------------------------
 TABLE       | t1          | dbadmin | dbadmin | INSERT*, SELECT*, UPDATE*, DELETE*, REFERENCES*, TRUNCATE*
 TABLE       | t1          | u1      | dbadmin | INSERT*, SELECT*, UPDATE*
 TABLE       | t1          | u2      | u1      | INSERT*, SELECT*, UPDATE*
 TABLE       | t1          | u4      | u2      | INSERT, SELECT, UPDATE
(3 rows)

Now, if user u1 wants to revoke UPDATE privileges from user u2, the revoke operation must cascade to user u4, who also has UPDATE privileges that were granted by u2; otherwise, the REVOKE statement returns with an error:

=> \c - u1
=> REVOKE update ON TABLE s1.t1 FROM u2;
ROLLBACK 3052:  Dependent privileges exist
HINT:  Use CASCADE to revoke them too
=> REVOKE update ON TABLE s1.t1 FROM u2 CASCADE;
REVOKE PRIVILEGE
=> \c
You are now connected as user "dbadmin".
=>  SELECT object_type, object_name, grantee, grantor, privileges_description FROM v_catalog.grants
     WHERE object_name IN ('t1') ORDER BY grantee;
 object_type | object_name | grantee | grantor |                   privileges_description
-------------+-------------+---------+---------+------------------------------------------------------------
 TABLE       | t1          | dbadmin | dbadmin | INSERT*, SELECT*, UPDATE*, DELETE*, REFERENCES*, TRUNCATE*
 TABLE       | t1          | u1      | dbadmin | INSERT*, SELECT*, UPDATE*
 TABLE       | t1          | u2      | u1      | INSERT*, SELECT*
 TABLE       | t1          | u4      | u2      | INSERT, SELECT
(4 rows)

You can also revoke grantor privileges from a user without revoking those privileges. For example, user u1 can prevent user u2 from granting INSERT privileges to other users, but allow user u2 to retain that privilege:

=> \c - u1
You are now connected as user "u1".
=> REVOKE GRANT OPTION FOR INSERT ON TABLE s1.t1 FROM U2 CASCADE;
REVOKE PRIVILEGE

You can confirm results of the revoke operation by querying V_CATALOG.GRANTS for privileges on table s1.t1:

=> \c
You are now connected as user "dbadmin".
=> SELECT object_type, object_name, grantee, grantor, privileges_description FROM v_catalog.grants
      WHERE object_name IN ('t1') ORDER BY grantee;
 object_type | object_name | grantee | grantor |                   privileges_description
-------------+-------------+---------+---------+------------------------------------------------------------
 TABLE       | t1          | dbadmin | dbadmin | INSERT*, SELECT*, UPDATE*, DELETE*, REFERENCES*, TRUNCATE*
 TABLE       | t1          | u1      | dbadmin | INSERT*, SELECT*, UPDATE*
 TABLE       | t1          | u2      | u1      | INSERT, SELECT*
 TABLE       | t1          | u4      | u2      | SELECT
(4 rows)

The query results show:

• User u2 retains INSERT privileges on the table but can no longer grant INSERT privileges to other users (as indicated by absence of an asterisk).

• The revoke operation cascaded down to grantee u4, who now lacks INSERT privileges.

See also

3.7.6 - Privilege ownership chains

The ability to revoke privileges on objects can cascade throughout an organization. If the grant option was revoked from a user, the privilege that this user granted to other users will also be revoked.

If a privilege was granted to a user or role by multiple grantors, to completely revoke this privilege from the grantee the privilege has to be revoked by each original grantor. The only exception is a superuser may revoke privileges granted by an object owner, with the reverse being true, as well.

In the following example, the SELECT privilege on table t1 is granted through a chain of users, from a superuser through User3.

• A superuser grants User1 CREATE privileges on the schema s1:

  => \c - dbadmin
  You are now connected as user "dbadmin".
  => CREATE USER User1;
  CREATE USER
  => CREATE USER User2;
  CREATE USER
  => CREATE USER User3;
  CREATE USER
  => CREATE SCHEMA s1;
  CREATE SCHEMA
  => GRANT USAGE on SCHEMA s1 TO User1, User2, User3;
  GRANT PRIVILEGE
  => CREATE ROLE reviewer;
  CREATE ROLE
  => GRANT CREATE ON SCHEMA s1 TO User1;
  GRANT PRIVILEGE
  

• User1 creates new table t1 within schema s1 and then grants SELECT WITH GRANT OPTION privilege on s1.t1 to User2:

  => \c - User1
  You are now connected as user "User1".
  => CREATE TABLE s1.t1(id int, sourceID VARCHAR(8));
  CREATE TABLE
  => GRANT SELECT on s1.t1 to User2 WITH GRANT OPTION;
  GRANT PRIVILEGE
  

• User2 grants SELECT WITH GRANT OPTION privilege on s1.t1 to User3:

  => \c - User2
  You are now connected as user "User2".
  => GRANT SELECT on s1.t1 to User3 WITH GRANT OPTION;
  GRANT PRIVILEGE
  

• User3 grants SELECT privilege on s1.t1 to the reviewer role:

  => \c - User3
  You are now connected as user "User3".
  => GRANT SELECT on s1.t1 to reviewer;
  GRANT PRIVILEGE
  

Users cannot revoke privileges upstream in the chain. For example, User2 did not grant privileges on User1, so when User1 runs the following REVOKE command, Vertica rolls back the command:

=> \c - User2
You are now connected as user "User2".
=> REVOKE CREATE ON SCHEMA s1 FROM User1;
ROLLBACK 0:  "CREATE" privilege(s) for schema "s1" could not be revoked from "User1"

Users can revoke privileges indirectly from users who received privileges through a cascading chain, like the one shown in the example above. Here, users can use the CASCADE option to revoke privileges from all users "downstream" in the chain. A superuser or User1 can use the CASCADE option to revoke the SELECT privilege on table s1.t1 from all users. For example, a superuser or User1 can execute the following statement to revoke the SELECT privilege from all users and roles within the chain:

=> \c - User1
You are now connected as user "User1".
=> REVOKE SELECT ON s1.t1 FROM User2 CASCADE;
REVOKE PRIVILEGE

When a superuser or User1 executes the above statement, the SELECT privilege on table s1.t1 is revoked from User2, User3, and the reviewer role. The GRANT privilege is also revoked from User2 and User3, which a superuser can verify by querying the V_CATALOG.GRANTS system table.

=> SELECT * FROM grants WHERE object_name = 's1' AND grantee ILIKE 'User%';
 grantor | privileges_description | object_schema | object_name | grantee
---------+------------------------+---------------+-------------+---------
 dbadmin | USAGE                  |               | s1          | User1
 dbadmin | USAGE                  |               | s1          | User2
 dbadmin | USAGE                  |               | s1          | User3
(3 rows)

3.8 - Modifying privileges

A superuser or object owner can use one of the ALTER statements to modify a privilege, such as changing a sequence owner or table owner. Reassignment to the new owner does not transfer grants from the original owner to the new owner; grants made by the original owner are dropped.

3.9 - Viewing privileges granted on objects

You can view information about privileges, grantors, grantees, and objects by querying these system tables:

• GRANTS
• INHERITED_PRIVILEGES
• INHERITING_OBJECTS

An asterisk (*) appended to a privilege indicates that the user can grant the privilege to other users.

You can also view the effective privileges on a specified database object by using the GET_PRIVILEGES_DESCRIPTION meta-function.

Viewing explicitly granted privileges

To view explicitly granted privileges on objects, query the GRANTS table.

The following query returns the explicit privileges for the schema, myschema.

=> SELECT grantee, privileges_description FROM grants WHERE object_name='myschema';
 grantee | privileges_description
---------+------------------------
 Bob     | USAGE, CREATE
 Alice   | CREATE
 (2 rows)

Viewing inherited privileges

To view which tables and views inherit privileges from which schemas, query the INHERITING_OBJECTS table.

The following query returns the tables and views that inherit their privileges from their parent schema, customers.

=> SELECT * FROM inheriting_objects WHERE object_schema='customers';
     object_id     |     schema_id     | object_schema |  object_name  | object_type
-------------------+-------------------+---------------+---------------+-------------
 45035996273980908 | 45035996273980902 | customers     | cust_info     | table
 45035996273980984 | 45035996273980902 | customers     | shipping_info | table
 45035996273980980 | 45035996273980902 | customers     | cust_set      | view
 (3 rows)

To view the specific privileges inherited by tables and views and information on their associated grant statements, query the INHERITED_PRIVILEGES table.

The following query returns the privileges that the tables and views inherit from their parent schema, customers.

=> SELECT object_schema,object_name,object_type,privileges_description,principal,grantor FROM inherited_privileges WHERE object_schema='customers';
 object_schema |  object_name  | object_type |                          privileges_description                           | principal | grantor
---------------+---------------+-------------+---------------------------------------------------------------------------+-----------+---------
 customers     | cust_info     | Table       | INSERT*, SELECT*, UPDATE*, DELETE*, ALTER*, REFERENCES*, DROP*, TRUNCATE* | dbadmin   | dbadmin
 customers     | shipping_info | Table       | INSERT*, SELECT*, UPDATE*, DELETE*, ALTER*, REFERENCES*, DROP*, TRUNCATE* | dbadmin   | dbadmin
 customers     | cust_set      | View        | SELECT*, ALTER*, DROP*                                                    | dbadmin   | dbadmin
 customers     | cust_info     | Table       | SELECT                                                                    | Val       | dbadmin
 customers     | shipping_info | Table       | SELECT                                                                    | Val       | dbadmin
 customers     | cust_set      | View        | SELECT                                                                    | Val       | dbadmin
 customers     | cust_info     | Table       | INSERT                                                                    | Pooja     | dbadmin
 customers     | shipping_info | Table       | INSERT                                                                    | Pooja     | dbadmin
 (8 rows)

Viewing effective privileges on an object

To view the current user's effective privileges on a specified database object, user the GET_PRIVILEGES_DESCRIPTION meta-function.

In the following example, user Glenn has set the REPORTER role and wants to check his effective privileges on schema s1 and table s1.articles.

• Table s1.articles inherits privileges from its schema (s1).

• The REPORTER role has the following privileges:

  • SELECT on schema s1

  • INSERT WITH GRANT OPTION on table s1.articles

• User Glenn has the following privileges:

  • UPDATE and USAGE on schema s1.

  • DELETE on table s1.articles.

GET_PRIVILEGES_DESCRIPTION returns the following effective privileges for Glenn on schema s1:

=> SELECT GET_PRIVILEGES_DESCRIPTION('schema', 's1');
   GET_PRIVILEGES_DESCRIPTION
--------------------------------
 SELECT, UPDATE, USAGE
(1 row)

GET_PRIVILEGES_DESCRIPTION returns the following effective privileges for Glenn on table s1.articles:

=> SELECT GET_PRIVILEGES_DESCRIPTION('table', 's1.articles');
   GET_PRIVILEGES_DESCRIPTION
--------------------------------
 INSERT*, SELECT, UPDATE, DELETE
(1 row)

See also

• Inherited privileges
• Database users and privileges

4 - Access policies

CREATE ACCESS POLICY lets you create access policies on tables that specify how much data certain users and roles can query from those tables. Access policies typically prevent these users from viewing the data of specific columns and rows of a table. You can apply access policies to table columns and rows. If a table has access policies on both, Vertica filters row access policies first, then filters the column access policies.

You can create most access policies for any table type—columnar, external, or flex. (You cannot create column access policies on flex tables.) You can also create access policies on any column type, including joins.

4.1 - Creating column access policies

CREATE ACCESS POLICY can create access policies on individual table columns, one policy per column. Each column access policy lets you specify, for different users and roles, various levels of access to the data of that column. The column access expression can also specify how to render column data for users and roles.

The following example creates an access policy on the customer_address column in the client_dimension table. This access policy gives non-superusers with the administrator role full access to all data in that column, but masks customer address data from all other users:

=> CREATE ACCESS POLICY ON public.customer_dimension FOR COLUMN customer_address
-> CASE
-> WHEN ENABLED_ROLE('administrator') THEN customer_address
-> ELSE '**************'
-> END ENABLE;
CREATE ACCESS POLICY

Vertica uses this policy to determine the access it gives to users MaxineT and MikeL, who are assigned employee and administrator roles, respectively. When these users query the customer_dimension table, Vertica applies the column access policy expression as follows:

=> \c - MaxineT;
You are now connected as user "MaxineT".
=> SET ROLE employee;
SET
=> SELECT customer_type, customer_name, customer_gender, customer_address, customer_city FROM customer_dimension;
 customer_type |      customer_name      | customer_gender | customer_address |  customer_city
---------------+-------------------------+-----------------+------------------+------------------
 Individual    | Craig S. Robinson       | Male            | **************   | Fayetteville
 Individual    | Mark M. Kramer          | Male            | **************   | Joliet
 Individual    | Barbara S. Farmer       | Female          | **************   | Alexandria
 Individual    | Julie S. McNulty        | Female          | **************   | Grand Prairie
 ...

=> \c - MikeL
You are now connected as user "MikeL".
=> SET ROLE administrator;
SET
=> SELECT customer_type, customer_name, customer_gender, customer_address, customer_city FROM customer_dimension;
 customer_type |      customer_name      | customer_gender | customer_address |  customer_city
---------------+-------------------------+-----------------+------------------+------------------
 Individual    | Craig S. Robinson       | Male            | 138 Alden Ave    | Fayetteville
 Individual    | Mark M. Kramer          | Male            | 311 Green St     | Joliet
 Individual    | Barbara S. Farmer       | Female          | 256 Cherry St    | Alexandria
 Individual    | Julie S. McNulty        | Female          | 459 Essex St     | Grand Prairie
 ...

Restrictions

The following limitations apply to access policies:

• A column can have only one access policy.

• Column access policies cannot be set on columns of complex types other than native arrays.

• Column access policies cannot be set for materialized columns on flex tables. While it is possible to set an access policy for the __raw__ column, doing so restricts access to the whole table.

• Row access policies are invalid on temporary tables and tables with aggregate projections.

• Access policy expressions cannot contain:

  • Subqueries

  • Aggregate functions

  • Analytic functions

  • User-defined transform functions (UDTF)

• If the query optimizer cannot replace a deterministic expression that involves only constants with their computed values, it blocks all DML operations such as INSERT.

4.2 - Creating row access policies

CREATE ACCESS POLICY can create a single row access policy for a given table. This policy lets you specify for different users and roles various levels of access to table row data. When a user launches a query, Vertica evaluates the access policy's WHERE expression against all table rows. The query returns with only those rows where the expression evaluates to true for the current user or role.

For example, you might want to specify different levels of access to table store.store_store_sales for four roles:

• employee: Users with this role should only access sales records that identify them as the employee, in column employee_key. The following query shows how many sales records (in store.store_sales_fact) are associated with each user (in public.emp_dimension):

  => SELECT COUNT(sf.employee_key) AS 'Total Sales', sf.employee_key, ed.user_name FROM store.store_sales_fact sf
       JOIN emp_dimension ed ON sf.employee_key=ed.employee_key
       WHERE ed.job_title='Sales Associate' GROUP BY sf.employee_key, ed.user_name ORDER BY sf.employee_key
  
   Total Sales | employee_key |  user_name
  -------------+--------------+-------------
           533 |          111 | LucasLC
           442 |          124 | JohnSN
           487 |          127 | SamNS
           477 |          132 | MeghanMD
           545 |          140 | HaroldON
           ...
           563 |         1991 | MidoriMG
           367 |         1993 | ThomZM
  (318 rows)
  

• regional_manager: Users with this role (public.emp_dimension) should only access sales records for the sales region that they manage (store.store_dimension):

  => SELECT distinct sd.store_region, ed.user_name, ed.employee_key, ed.job_title FROM store.store_dimension sd
       JOIN emp_dimension ed ON sd.store_region=ed.employee_region WHERE ed.job_title = 'Regional Manager';
   store_region | user_name | employee_key |    job_title
  --------------+-----------+--------------+------------------
   West         | JamesGD   |         1070 | Regional Manager
   South        | SharonDM  |         1710 | Regional Manager
   East         | BenOV     |          593 | Regional Manager
   MidWest      | LilyCP    |          611 | Regional Manager
   NorthWest    | CarlaTG   |         1058 | Regional Manager
   SouthWest    | MarcusNK  |          150 | Regional Manager
  (6 rows)
  

• dbadmin and administrator: Users with these roles have unlimited access to all table data.

Given these users and the data associated with them, you can create a row access policy on store.store_store_sales that looks like this:

CREATE ACCESS POLICY ON store.store_sales_fact FOR ROWS WHERE
   (ENABLED_ROLE('employee')) AND (store.store_sales_fact.employee_key IN
     (SELECT employee_key FROM public.emp_dimension WHERE user_name=CURRENT_USER()))
   OR
   (ENABLED_ROLE('regional_manager')) AND (store.store_sales_fact.store_key IN
     (SELECT sd.store_key FROM store.store_dimension sd
      JOIN emp_dimension ed ON sd.store_region=ed.employee_region WHERE ed.user_name = CURRENT_USER()))
   OR ENABLED_ROLE('dbadmin')
   OR ENABLED_ROLE ('administrator')
ENABLE;

The following examples indicate the different levels of access that are available to users with the specified roles:

• dbadmin has access to all rows in store.store_sales_fact:

  => \c
  You are now connected as user "dbadmin".
  => SELECT count(*) FROM store.store_sales_fact;
    count
  ---------
   5000000
  (1 row)
  

• User LilyCP has the role of regional_manager, so she can access all sales data of the Midwest region that she manages:

  
  => \c - LilyCP;
  You are now connected as user "LilyCP".
  => SET ROLE regional_manager;
  SET
  => SELECT count(*) FROM store.store_sales_fact;
   count
  --------
   782272
  (1 row)
  

• User SamRJ has the role of employee, so he can access only the sales data that he is associated with:

  
  => \c - SamRJ;
  You are now connected as user "SamRJ".
  => SET ROLE employee;
  SET
  => SELECT count(*) FROM store.store_sales_fact;
   count
  -------
     417
  (1 row)
  

Restrictions

The following limitations apply to row access policies:

• A table can have only one row access policy.

• Row access policies are invalid on the following tables:

  • Tables with aggregate projections

  • Temporary tables

  • System tables

  • Views

• You cannot create directed queries on a table with a row access policy.

4.3 - Access policies and DML operations

By default, Vertica abides by a rule that a user can only edit what they can see. That is, you must be able to view all rows and columns in the table in their original values (as stored in the table) and in their originally defined data types to perform actions that modify data on a table. For example, if a column is defined as VARCHAR(9) and an access policy on that column specifies the same column as VARCHAR(10), users using the access policy will be unable to perform the following operations:

• INSERT

• UPDATE

• DELETE

• MERGE

• COPY

You can override this behavior by specifying GRANT TRUSTED in a new or existing access policy. This option forces the access policy to defer entirely to explicit GRANT statements when assessing whether a user can perform the above operations.

You can view existing access policies with the ACCESS_POLICY system table.

Row access

On tables where a row access policy is enabled, you can only perform DML operations when the condition in the row access policy evaluates to TRUE. For example:

t1 appears as follows:

 A | B
---+---
 1 | 1
 2 | 2
 3 | 3

Create the following row access policy on t1:

=> CREATE ACCESS POLICY ON t1 for ROWS
WHERE enabled_role('manager')
OR
A<2
ENABLE;

With this policy enabled, the following behavior exists for users who want to perform DML operations:

• A user with the manager role can perform DML on all rows in the table, because the WHERE clause in the policy evaluates to TRUE.

• Users with non-manager roles can only perform a SELECT to return data in column A that has a value of less than two. If the access policy has to read the data in the table to confirm a condition, it does not allow DML operations.

Column access

On tables where a column access policy is enabled, you can perform DML operations if you can view the entire column in its originally defined type.

Suppose table t1 is created with the following data types and values:

=> CREATE TABLE t1 (A int, B int);
=> INSERT INTO t1 VALUES (1,2);
=> SELECT * FROM t1;
 A | B
---+---
 1 | 2
(1 row)

Suppose the following access policy is created, which coerces the data type of column A from INT to VARCHAR(20) at execution time.

=> CREATE ACCESS POLICY on t1 FOR column A A::VARCHAR(20) ENABLE;
Column "A" is of type int but expression in Access Policy is of type varchar(20). It will be coerced at execution time

In this case, u1 can view column A in its entirety, but because the active access policy doesn't specify column A's original data type, u1 cannot perform DML operations on column A.

=> \c - u1
You are now connected as user "u1".
=> SELECT A FROM t1;
 A
---
 1
(1 row)

=> INSERT INTO t1 VALUES (3);
ERROR 6538:  Unable to INSERT: "Access denied due to active access policy on table "t1" for column "A""

Overriding default behavior with GRANT TRUSTED

Specifying GRANT TRUSTED in an access policy overrides the default behavior ("users can only edit what they can see") and instructs the access policy to defer entirely to explicit GRANT statements when assessing whether a user can perform a DML operation.

GRANT TRUSTED is useful in cases where the form the data is stored in doesn't match its semantically "true" form.

For example, when integrating with Voltage SecureData, a common use case is storing encrypted data with VoltageSecureProtect, where decryption is left to a case expression in an access policy that calls VoltageSecureAccess. In this case, while the decrypted form is intuitively understood to be the data's "true" form, it's still stored in the table in its encrypted form; users who can view the decrypted data wouldn't see the data as it was stored and therefore wouldn't be able to perform DML operations. You can use GRANT TRUSTED to override this behavior and allow users to perform these operations if they have the grants.

In this example, the customer_info table contains columns for the customer first and last name and SSN. SSNs are sensitive and access to it should be controlled, so it is encrypted with VoltageSecureProtect as it is inserted into the table:

=> CREATE TABLE customer_info(first_name VARCHAR, last_name VARCHAR, ssn VARCHAR);
=> INSERT INTO customer_info SELECT 'Alice', 'Smith', VoltageSecureProtect('998-42-4910' USING PARAMETERS format='ssn');
=> INSERT INTO customer_info SELECT 'Robert', 'Eve', VoltageSecureProtect('899-28-1303' USING PARAMETERS format='ssn');
=> SELECT * FROM customer_info;
 first_name | last_name |     ssn
------------+-----------+-------------
 Alice      | Smith     | 967-63-8030
 Robert     | Eve       | 486-41-3371
(2 rows)

In this system, the role "trusted_ssn" identifies privileged users for which Vertica will decrypt the values of the "ssn" column with VoltageSecureAccess. To allow these privileged users to perform DML operations for which they have grants, you might use the following access policy:

=> CREATE ACCESS POLICY ON customer_info FOR COLUMN ssn
  CASE WHEN enabled_role('trusted_ssn') THEN VoltageSecureAccess(ssn USING PARAMETERS format='ssn')
  ELSE ssn END
  GRANT TRUSTED
  ENABLE;

Again, note that GRANT TRUSTED allows all users with GRANTs on the table to perform the specified operations, including users without the "trusted_ssn" role.

4.4 - Access policies and query optimization

Access policies affect the projection designs that the Vertica Database Designer produces, and the plans that the optimizer creates for query execution.

Projection designs

When Database Designer creates projections for a given table, it takes into account access policies that apply to the current user. The set of projections that Database Designer produces for the table are optimized for that user's access privileges, and other users with similar access privileges. However, these projections might be less than optimal for users with different access privileges. These differences might have some effect on how efficiently Vertica processes queries for the second group of users. When you evaluate projection designs for a table, choose a design that optimizes access for all authorized users.

Query rewrite

The Vertica optimizer enforces access policies by rewriting user queries in its query plan, which can affect query performance. For example, the clients table has row and column access policies, both enabled. When a user queries this table, the query optimizer produces a plan that rewrites the query so it includes both policies:

=> SELECT * FROM clients;

The query optimizer produces a query plan that rewrites the query as follows:

SELECT * FROM (
SELECT custID, password, CASE WHEN enabled_role('manager') THEN SSN ELSE substr(SSN, 8, 4) END AS SSN FROM clients
WHERE enabled_role('broker') AND
  clients.clientID IN (SELECT brokers.clientID FROM brokers WHERE broker_name = CURRENT_USER())
) clients;

4.5 - Managing access policies

By default, you can only manage access policies on tables that you own. You can optionally restrict access policy management to superusers with the AccessPolicyManagementSuperuserOnly parameter (false by default):

=> ALTER DATABASE DEFAULT SET PARAMETER AccessPolicyManagementSuperuserOnly = 1;
ALTER DATABASE

You can view and manage access policies for tables in several ways:

• View access policies

• Modify the expression of an access policy

• Enable or disable access policies

• Copy access policies to another table

Viewing access policies

You can view access policies in two ways:

• Query system table ACCESS_POLICY. For example, the following query returns all access policies on table public.customer_dimension:

  => \x
  => SELECT policy_type, is_policy_enabled, table_name, column_name, expression FROM access_policy WHERE table_name = 'public.customer_dimension';
  -[ RECORD 1 ]-----+----------------------------------------------------------------------------------------
  policy_type       | Column Policy
  is_policy_enabled | Enabled
  table_name        | public.customer_dimension
  column_name       | customer_address
  expression        | CASE WHEN enabled_role('administrator') THEN customer_address ELSE '**************' END
  

• Export table DDL from the database catalog with EXPORT_TABLES, EXPORT_OBJECTS, or EXPORT_CATALOG. For example:

  => SELECT export_tables('','customer_dimension');
                                  export_tables
  -----------------------------------------------------------------------------
  CREATE TABLE public.customer_dimension
  (
      customer_key int NOT NULL,
      customer_type varchar(16),
      customer_name varchar(256),
      customer_gender varchar(8),
      ...
      CONSTRAINT C_PRIMARY PRIMARY KEY (customer_key) DISABLED
  );
  
  CREATE ACCESS POLICY ON public.customer_dimension FOR COLUMN customer_address CASE WHEN enabled_role('administrator') THEN customer_address ELSE '**************' END ENABLE;
  

Modifying access policy expression

ALTER ACCESS POLICY can modify the expression of an existing access policy. For example, you can modify the access policy in the earlier example by extending access to the dbadmin role:

=> ALTER ACCESS POLICY ON public.customer_dimension FOR COLUMN customer_address
    CASE WHEN enabled_role('dbadmin') THEN customer_address
         WHEN enabled_role('administrator') THEN customer_address
         ELSE '**************' END ENABLE;
ALTER ACCESS POLICY

Querying system table ACCESS_POLICY confirms this change:

=> SELECT policy_type, is_policy_enabled, table_name, column_name, expression FROM access_policy
  WHERE table_name = 'public.customer_dimension' AND column_name='customer_address';
-[ RECORD 1 ]-----+-------------------------------------------------------------------------------------------------------------------------------------------
policy_type       | Column Policy
is_policy_enabled | Enabled
table_name        | public.customer_dimension
column_name       | customer_address
expression        | CASE WHEN enabled_role('dbadmin') THEN customer_address WHEN enabled_role('administrator') THEN customer_address ELSE '**************' END

Enabling and disabling access policies

Owners of a table can enable and disable its row and column access policies.

Row access policies

You enable and disable row access policies on a table:

ALTER ACCESS POLICY ON [schema.]table FOR ROWS { ENABLE | DISABLE }

The following examples disable and then re-enable the row access policy on table customer_dimension:

=> ALTER ACCESS POLICY ON customer_dimension FOR ROWS DISABLE;
ALTER ACCESS POLICY
=> ALTER ACCESS POLICY ON customer_dimension FOR ROWS ENABLE;
ALTER ACCESS POLICY

Column access policies

You enable and disable access policies on a table column as follows:

ALTER ACCESS POLICY ON [schema.]table FOR COLUMN column { ENABLE | DISABLE }

The following examples disable and then re-enable the same column access policy on customer_dimension.customer_address:

=> ALTER ACCESS POLICY ON public.customer_dimension FOR COLUMN customer_address DISABLE;
ALTER ACCESS POLICY
=> ALTER ACCESS POLICY ON public.customer_dimension FOR COLUMN customer_address ENABLE;
ALTER ACCESS POLICY

Copying access polices

You copy access policies from one table to another as follows. Non-superusers must have ownership of both the source and destination tables:

ALTER ACCESS POLICY ON [schema.]table { FOR COLUMN column | FOR ROWS } COPY TO TABLE table

When you create a copy of a table or move its contents with the following functions (but not CREATE TABLE AS SELECT or CREATE TABLE LIKE), the access policies of the original table are copied to the new/destination table:

• COPY_TABLE
• COPY_PARTITIONS_TO_TABLE
• MOVE_PARTITIONS_TO_TABLE
• SWAP_PARTITIONS_BETWEEN_TABLES

To copy access policies to another table, use ALTER ACCESS POLICY.

For example, you can copy a row access policy as follows:

=> ALTER ACCESS POLICY ON public.emp_dimension FOR ROWS COPY TO TABLE public.regional_managers_dimension;

The following statement copies the access policy on column employee_key from table public.emp_dimension to store.store_sales_fact:

=> ALTER ACCESS POLICY ON public.emp_dimension FOR COLUMN employee_key COPY TO TABLE store.store_sales_fact;