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.

Note

Certain actions, such as setting another user's default resource pool or selecting a view, depend on the effective privileges of other users. If that other user acquires these prerequisite privileges through a role, it must be a default role for the action to succeed.

For more information on changing a user's default roles, see Enabling roles automatically.

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.

Operation	Required Privileges
CREATE SCHEMA	Database: CREATE
DROP SCHEMA	Schema: owner
ALTER SCHEMA	Database: CREATE

Tables

Operation	Required Privileges
CREATE TABLE	Schema: CREATE Note Referencing sequences in the CREATE TABLE statement requires the following privileges: Sequence schema: USAGE Sequence: SELECT
DROP TABLE	Schema: USAGE or schema owner
TRUNCATE TABLE	Schema: USAGE or schema owner
ALTER TABLE ADD/DROP/ RENAME/ALTER-TYPE COLUMN	Schema: USAGE
ALTER TABLE ADD/DROP CONSTRAINT	Schema: USAGE
ALTER TABLE PARTITION (REORGANIZE)	Schema: USAGE
ALTER TABLE RENAME	USAGE and CREATE privilege on the schema that contains the table
ALTER TABLE...SET SCHEMA	New schema: CREATE Old Schema: USAGE
SELECT	Schema: USAGE SELECT privilege on table
INSERT	Table: INSERT Schema: USAGE
DELETE	Schema: USAGE Table: DELETE, SELECT when executing DELETE that references table column values in a WHERE or SET clause
UPDATE	Schema: USAGE Table: UPDATE, SELECT when executing UPDATE that references table column values in a WHERE or SET clause
REFERENCES	Schema: USAGE on schema that contains constrained table and source of foreign key Table: REFERENCES to create foreign key constraints that reference this table
ANALYZE_STATISTICS ANALYZE_STATISTICS_PARTITION	Schema: USAGE Table: One of INSERT, DELETE, or UPDATE
DROP_STATISTICS	Schema: USAGE Table: One of INSERT, DELETE, or UPDATE
DROP_PARTITIONS	Schema: USAGE

Views

Operation	Required Privileges
CREATE VIEW	Schema: CREATE on view schema, USAGE on schema with base objects Base objects: SELECT
DROP VIEW	Schema: USAGE or owner View: Owner
SELECT	Base table: View owner must have SELECT...WITH GRANT OPTION Schema: USAGE View: SELECT

Projections

Operation	Required Privileges
CREATE PROJECTION	Anchor table: SELECT Schema: USAGE and CREATE, or owner 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: Schema: USAGE Anchor table: SELECT
ALTER PROJECTION	Schema: USAGE and CREATE
DROP PROJECTION	Schema: USAGE or owner

External procedures

Operation	Required Privileges
CREATE PROCEDURE (external)	Superuser
DROP PROCEDURE (external)	Superuser
EXECUTE	Schema: USAGE Procedure: EXECUTE

Stored procedures

Operation	Required Privileges
CREATE PROCEDURE (stored)	Schema: CREATE

Libraries

Operation	Required Privileges
CREATE LIBRARY	Superuser
DROP LIBRARY	Superuser

User-defined functions

Note

The following table uses these abbreviations:

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

Operation	Required Privileges
CREATE FUNCTION (SQL)CREATE FUNCTION (scalar) CREATE TRANSFORM FUNCTION CREATE ANALYTIC FUNCTION (UDAnF) CREATE AGGREGATE FUNCTION (UDAF)	Schema: CREATE Base library: USAGE (if applicable)
DROP FUNCTION DROP TRANSFORM FUNCTION DROP AGGREGATE FUNCTION DROP ANALYTIC FUNCTION	Schema: USAGE privilege Function: owner
ALTER FUNCTION (scalar)...RENAME TO	Schema: USAGE and CREATE
ALTER FUNCTION (scalar)...SET SCHEMA	Old schema: USAGE New Schema: CREATE
EXECUTE (SQL/UDF/UDT/ ADAF/UDAnF) function	Schema: USAGE Function: EXECUTE

Sequences

Operation	Required Privileges
CREATE SEQUENCE	Schema: CREATE
DROP SEQUENCE	Schema: USAGE or owner
ALTER SEQUENCE	Schema: USAGE and CREATE
ALTER SEQUENCE...SET SCHEMA	Old schema: USAGE New schema: CREATE
CURRVAL NEXTVAL	Sequence schema: USAGE Sequence: SELECT

Resource pools

Operation	Required Privileges
CREATE RESOURCE POOL	Superuser
ALTER RESOURCE POOL	Superuser to alter: MAXMEMORYSIZE PRIORITY QUEUETIMEOUT Non-superuser, UPDATE to alter: PLANNEDCONCURRENCY SINGLEINITIATOR MAXCONCURRENCY
SET SESSION RESOURCE_POOL	Resource pool: USAGE 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	Superuser
DROP USER DROP PROFILE DROP ROLE	Superuser

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

Operation	Required Privileges
Look up schema	Schema: At least one privilege
Look up object in schema or in system tables	Schema: USAGE At least one privilege on any of the following objects: TABLE VIEW FUNCTION PROCEDURE SEQUENCE
Look up projection	All anchor tables: At least one privilege Schema (all anchor tables): USAGE
Look up resource pool	Resource pool: SELECT
Existence of object	Schema: USAGE

I/O operations

Operation	Required Privileges
CONNECT TO VERTICA DISCONNECT	None
EXPORT TO VERTICA	Source table: SELECT Source schema: USAGE Destination table: INSERT Destination schema: USAGE
COPY FROM VERTICA	Source/destination schema: USAGE Source table: SELECT Destination table: INSERT
COPY FROM `file`	Superuser
COPY FROM STDIN	Schema: USAGE Table: INSERT
COPY LOCAL	Schema: USAGE Table: INSERT

Comments

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

Operation

Required Privileges

COMMENT ON { is one of }:

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

TLS configuration

Operation	Required Privileges
ALTER	ALTER privileges on the TLS CONFIGURATION.