Privileges Required for Common Database Operations

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

Unless otherwise noted, superusers can perform all of the operations shown in the following tables without any additional privilege requirements. Object owners have the necessary rights to perform operations on their own objects, by default.

Schemas

The PUBLIC schema is present in any newly-created Vertica database, and newly-created users have only USAGE privilege on PUBLIC. A database superuser must 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.

Operation	Required Privileges
CREATE SCHEMA	CREATE privilege on database
DROP SCHEMA	Schema owner
ALTER SCHEMA RENAME	CREATE privilege on database

Tables

Operation	Required Privileges
CREATE TABLE	CREATE privilege on schema Note: Referencing sequences in the CREATE TABLE statement requires the following privileges: SELECT privilege on sequence object USAGE privilege on sequence schema
DROP TABLE	USAGE privilege on the schema that contains the table or schema owner
TRUNCATE TABLE	USAGE privilege on the schema that contains the table or schema owner
ALTER TABLE ADD/DROP/ RENAME/ALTER-TYPE COLUMN	USAGE privilege on the schema that contains the table
ALTER TABLE ADD/DROP CONSTRAINT	USAGE privilege on the schema that contains the table
ALTER TABLE PARTITION (REORGANIZE)	USAGE privilege on the schema that contains the table
ALTER TABLE RENAME	USAGE and CREATE privilege on the schema that contains the table
ALTER TABLE SET SCHEMA	CREATE privilege on new schema USAGE privilege on the old schema
SELECT	SELECT privilege on table USAGE privilege on schema that contains the table
INSERT	INSERT privilege on table USAGE privilege on schema that contains the table
DELETE	DELETE privilege on table USAGE privilege on schema that contains the table SELECT privilege on the referenced table when executing a DELETE statement that references table column values in a WHERE or SET clause
UPDATE	UPDATE privilege on table USAGE privilege on schema that contains the table SELECT privilege on the table when executing an UPDATE statement that references table column values in a WHERE or SET clause
REFERENCES	REFERENCES privilege on table to create foreign key constraints that reference this table USAGE privileges on schema that contains the constrained table and the source of the foreign k
`ANALYZE_STATISTICS`	INSERT/UPDATE/DELETE privilege on table USAGE privilege on schema that contains the table
DROP_STATISTICS	INSERT/UPDATE/DELETE privilege on table USAGE privilege on schema that contains the table
DROP_PARTITION	USAGE privilege on schema that contains the table

Views

Operation	Required Privileges
CREATE VIEW	CREATE privilege on the schema to contain a view SELECT privileges on base objects (tables/views) USAGE privileges on schema that contains the base objects
DROP VIEW	USAGE privilege on schema that contains the view or schema owner
SELECT ... FROM VIEW	SELECT privilege on view USAGE privilege on the schema that contains the view. View owner must have SELECT ... WITH GRANT OPTION privileges on the view's anchor tables or views if non-owner runs a SELECT query on the view. View owner must have SELECT privilege on a view's base objects (table or view) if owner runs a SELECT query on the view.

Operation

Required Privileges

CREATE VIEW

CREATE privilege on the schema to contain a view
SELECT privileges on base objects (tables/views)
USAGE privileges on schema that contains the base objects

DROP VIEW

USAGE privilege on schema that contains the view or schema owner

SELECT ... FROM VIEW

SELECT privilege on view
USAGE privilege on the schema that contains the view.

View owner must have SELECT ... WITH GRANT OPTION privileges on the view's anchor tables or views if non-owner runs a SELECT query on the view.
View owner must have SELECT privilege on a view's base objects (table or view) if owner runs a SELECT query on the view.

Projections

Operation	Required Privileges
CREATE PROJECTION	SELECT privilege on anchor tables USAGE privilege on schema that contains anchor tables or schema owner CREATE privilege on schema to contain the projection Note: If a projection is implicitly created with the table, no additional privilege is needed other than privileges for table creation.
AUTO/DELAYED PROJECTION	On projections created during INSERT..SELECT or COPY operations: SELECT privilege on anchor tables USAGE privilege on schema that contains anchor tables
ALTER PROJECTION RENAME	USAGE and CREATE privilege on schema that contains the projection
DROP PROJECTION	USAGE privilege on schema that contains the projection or schema owner

External Procedures

Operation	Required Privileges
CREATE PROCEDURE	Superuser
DROP PROCEDURE	Superuser
EXECUTE	EXECUTE privilege on procedure USAGE privilege on schema that contains the procedure

Libraries

Operation	Required Privileges
CREATE LIBRARY	Superuser
DROP LIBRARY	Superuser

User-Defined Functions

The following abbreviations are used in the UDF table:

UDF = Scalar
UDT = Transform
UDAnF= Analytic
UDAF = Aggregate

Operation	Required Privileges
CREATE FUNCTION (SQL)CREATE FUNCTION (UDF) CREATE TRANSFORM FUNCTION (UDF) CREATE ANALYTIC FUNCTION (UDAnF CREATE AGGREGATE FUNCTION (UDAF)	CREATE privilege on schema to contain the function USAGE privilege on base library (if applicable)
DROP FUNCTION DROP TRANSFORM FUNCTION DROP ANALYTIC FUNCTION DROP AGGREGATE FUNCTION	Superuser or function owner USAGE privilege on schema that contains the function
ALTER FUNCTION (UDF or UDT) RENAME TO	USAGE and CREATE privilege on schema that contains the function
ALTER FUNCTION (UDF or UDT) SET SCHEMA	USAGE privilege on schema that currently contains the function (old schema) CREATE privilege on the schema to which the function will be moved (new schema)
EXECUTE (SQL/UDF/UDT/ ADAF/UDAnF) function	EXECUTE privilege on function USAGE privilege on schema that contains the function

Sequences

Operation	Required Privileges
CREATE SEQUENCE	CREATE privilege on schema to contain the sequence Note: Referencing sequence in the CREATE TABLE statement requires SELECT privilege on sequence object and USAGE privilege on sequence schema.
CREATE TABLE with SEQUENCE	SELECT privilege on sequence USAGE privilege on sequence schema
DROP SEQUENCE	USAGE privilege on schema containing the sequence or schema owner
ALTER SEQUENCE RENAME TO	USAGE and CREATE privileges on schema
ALTER SEQUENCE SET SCHEMA	USAGE privilege on the schema that currently contains the sequence (old schema) CREATE privilege on new schema to contain the sequence
CURRVAL()NEXTVAL()	SELECT privilege on sequence USAGE privilege on sequence schema

Resource Pools

Operation	Required Privileges
CREATE RESOURCE POOL	Superuser
ALTER RESOURCE POOL	Superuser on the resource pool to alter: MAXMEMORYSIZE PRIORITY QUEUETIMEOUT UPDATE privilege on the resource pool to alter: PLANNEDCONCURRENCY SINGLEINITIATOR MAXCONCURRENCY
SET SESSION RESOURCE_POOL	USAGE privilege on the resource pool Users can only change their own resource pool setting using ALTER USER syntax
DROP RESOURCE POOL	Superuser

Users/Profiles/Roles

Operation	Required Privileges
CREATE USER CREATE PROFILE CREATE ROLE	Superuser
ALTER USER ALTER PROFILE ALTER ROLE RENAME	Superuser
DROP USER DROP PROFILE DROP ROLE	Superuser

Operation

Required Privileges

CREATE USER

CREATE PROFILE

CREATE ROLE

Superuser

ALTER USER

ALTER PROFILE

ALTER ROLE RENAME

Superuser

DROP USER

DROP PROFILE

DROP ROLE

Superuser

Object Visibility

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

Use \dn [pattern] to view schema names and owners
Use \dt [pattern] to view all tables in the database, as well as the system table V_CATALOG.TABLES
Use \dj [pattern] to view projections showing the schema, projection name, owner, and node, as well as the system table V_CATALOG.PROJECTIONS

Operation	Required Privileges
Look up schema	At least one privilege on schema that contains the object
Look up Object in Schema or in System Tables	USAGE privilege on schema At least one privilege on any of the following objects: TABLE VIEW FUNCTION PROCEDURE SEQUENCE
Look up Projection	At least one privilege on all anchor tables USAGE privilege on schema of all anchor table
Look up resource pool	SELECT privilege on the resource pool
Existence of object	USAGE privilege on the schema that contains the object

I/O Operations

Operation	Required Privileges
CONNECT DISCONNECT	None
EXPORT TO Vertica	SELECT privileges on the source table USAGE privilege on source table schema INSERT privileges for the destination table in target database USAGE privilege on destination table schema
COPY FROM Vertica	SELECT privileges on the source table USAGE privilege on source table schema INSERT privileges for the destination table in target database USAGE privilege on destination table schema
COPY FROM file	Superuser
COPY FROM STDIN	INSERT privilege on table USAGE privilege on schema
COPY LOCAL	INSERT privilege on table USAGE privilege on schema

Comments

Operation	Required Privileges
COMMENT ON { is one of }: AGGREGATE FUNCTION ANALYTIC FUNCTION COLUMN CONSTRAINT FUNCTION LIBRARY NODE PROJECTION SCHEMA SEQUENCE TABLE TRANSFORM FUNCTION VIEW	Object owner or superuser

Operation

Required Privileges

COMMENT ON { is one of }:

AGGREGATE FUNCTION
ANALYTIC FUNCTION
COLUMN
CONSTRAINT
FUNCTION
LIBRARY
NODE
PROJECTION
SCHEMA
SEQUENCE
TABLE
TRANSFORM FUNCTION
VIEW

Object owner or superuser

Transactions

Operation	Required Privileges
COMMIT	None
ROLLBACK	None
RELEASE SAVEPOINT	None
SAVEPOINT	None

Sessions

Operation	Required Privileges
SET { is one of }: DATESTYLE ESCAPE_STRING_WARNING INTERVALSTYLE LOCALE ROLE SEARCH_PATH SESSION AUTOCOMMIT SESSION CHARACTERISTICS SESSION MEMORYCAP SESSION RESOURCE POOL SESSION RUNTIMECAP SESSION TEMPSPACE STANDARD_CONFORMING_STRINGS TIMEZONE	None
SHOW { name \| ALL }	None

Operation

Required Privileges

SET { is one of }:

None

SHOW { name | ALL }

None

Tuning Operations

Operation	Required Privileges
PROFILE	Same privileges required to run the query being profiled
EXPLAIN	Same privileges required to run the query for which you use the EXPLAIN keyword