What are the User Privileges in Vertica database

A new create user account in Vertica must be explicitly granted the privileges that will allow him to act on the database. This privileges must be carefully chosen by the dbadmin(superuser) when given to a user. We will see what are the required privileges for a given action that a user must have in order to conclude that action. We will be discuss privileges needs based on the action classes bellow:

• Actions on Schema
• Actions on Tables
• Actions on Views
• Actions on Projections
• Actions on External Procedures
• Actions on Libraries
• Actions on User defined functions
• Actions on Sequences
• Actions on Resource Pools
• Actions on User, Profiles and Roles

 We will see what Privileges are required for Schema actions

• so the test_user  user was created using the syntax bellow:

dbadmin= create user test_user identified by 'secret';
CREATE USER
dbadmin= grant usage on schema public to test_user;

• the user is only granted USAGE to schema PUBLIC.

dbadmin= c - test_user
Password:
You are now connected as user "test_user".
dbadmin= create schema bla;
ROLLBACK 4368:  Permission denied for database [d]

dbadmin= grant create on database d to test_user;
GRANT PRIVILEGE

dbadmin= c - test_user
Password:
You are now connected as user "test_user".
dbadmin= create schema bla;
CREATE SCHEMA
-- list the schemas available on the database
dbadmin= dn
         List of schemas
    Name    |   Owner   | Comment
------------+-----------+---------
 v_internal | dbadmin   |
 v_catalog  | dbadmin   |
 v_monitor  | dbadmin   |
 public     | dbadmin   |
 bla        | test_user |
(5 rows)

  Drop Schema privileges

• to drop a schema the user must be the owner of the schema or be a superuser.

dbadmin= select username();
 username
-----------

 test_user
(1 row)

dbadmin= drop schema public ;
ROLLBACK 3989:  Must be owner of schema public

dbadmin= drop schema bla ;
DROP SCHEMA

Rename Schema privileges

• to rename a schema the user must have the same privileges as create schema

dbadmin= alter schema bla rename to bla_new;
ALTER SCHEMA