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.

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

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

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

Superuser

DROP PROCEDURE

Superuser

EXECUTE
  • Schema: USAGE
  • Procedure: EXECUTE

Libraries

Operation Required Privileges
CREATE LIBRARY

Superuser

DROP LIBRARY

Superuser

User-Defined Functions

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 VERTICADISCONNECT

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