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:
|
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 |
|
SELECT |
|
INSERT |
|
DELETE |
|
UPDATE |
|
REFERENCES |
|
|
|
DROP_STATISTICS |
|
DROP_PARTITIONS |
Schema: USAGE |
Views
Operation | Required Privileges |
---|---|
CREATE VIEW |
|
DROP VIEW |
|
SELECT |
|
Projections
Operation | Required Privileges |
---|---|
CREATE PROJECTION |
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:
|
ALTER PROJECTION |
Schema: USAGE and CREATE |
DROP PROJECTION |
Schema: USAGE or owner |
External Procedures
Operation | Required Privileges |
---|---|
CREATE PROCEDURE |
Superuser |
DROP PROCEDURE |
Superuser |
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) |
|
DROP FUNCTION
|
|
ALTER FUNCTION (Scalar)…RENAME TO |
Schema: USAGE and CREATE |
ALTER FUNCTION (Scalar)…SET SCHEMA |
|
EXECUTE (SQL/UDF/UDT/ ADAF/UDAnF) function |
|
Sequences
Operation | Required Privileges |
---|---|
CREATE SEQUENCE |
Schema: CREATE |
DROP SEQUENCE |
Schema: USAGE or owner |
ALTER SEQUENCE |
Schema: USAGE and CREATE |
ALTER SEQUENCE…SET SCHEMA |
|
CURRVAL
NEXTVAL |
|
Resource Pools
Operation | Required Privileges |
---|---|
CREATE RESOURCE POOL |
Superuser |
ALTER RESOURCE POOL |
Superuser to alter:
Non-superuser, UPDATE to alter:
|
SET SESSION RESOURCE_POOL |
|
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 |
|
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 |
|
COPY FROM VERTICA |
|
COPY FROM file |
Superuser |
COPY FROM STDIN |
|
COPY LOCAL |
|
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 |