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, 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

Database: CREATE
DROP SCHEMA

Schema: owner
ALTER SCHEMA

Database: CREATE

Tables

Operation Required Privileges
CREATE TABLE

Schema: CREATE

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
  • Schema: USAGE
  • View: SELECT

Projections

Operation Required Privileges
CREATE PROJECTION
  • Anchor table: SELECT
  • Schema: USAGE and CREATE, or owner

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 RENAME

Schema: USAGE and CREATE
DROP PROJECTION

Schema: USAGE or owner

External Procedures

Operation Required Privileges
CREATE PROCEDURE

Superuser
DROP PROCEDURE

Superuser
EXECUTE
  • Schema: USAGE
  • Procedure: EXECUTE

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 Functions)CREATE FUNCTION (UDF)
CREATE TRANSFORM FUNCTION
CREATE ANALYTIC FUNCTION (UDAnF)
CREATE AGGREGATE FUNCTION (UDAF)
  • Schema: CREATE
  • Base library: USAGE (if applicable)
DROP FUNCTION DROP TRANSFORM FUNCTION
DROP ANALYTIC FUNCTION DROP AGGREGATE FUNCTION
  • Schema: USAGE privilege
  • Function: owner
ALTER FUNCTION (UDF)…RENAME TO

Schema: USAGE and CREATE
ALTER FUNCTION (UDF)…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 RENAME

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
CONNECTDISCONNECT

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 }:

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 }:

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