HAS_TABLE_PRIVILEGE

Returns true or false to verify whether a user has the specified privilege on a table.

This is a meta-function. You must call meta-functions in a top-level SELECT statement.

Behavior type

Behavior type

Syntax

HAS_TABLE_PRIVILEGE ( [ user, ] '[[database.]schema.]table', 'privilege' )

Parameters

user

Name or OID of a database user. If omitted, Vertica checks privileges for the current user.

[database.]schema

Database and schema. The default schema is public. If you specify a database, it must be the current database.

table

Name or OID of the table to check.

privilege

A table privilege, one of the following:

• SELECT: Query tables. SELECT privileges are granted by default to the PUBLIC role.

• INSERT: Insert table rows with INSERT, and load data with COPY.

  Note

  COPY FROM STDIN is allowed for users with INSERT privileges, while COPY FROM file requires admin privileges.

• UPDATE: Update table rows.

• DELETE: Delete table rows.

• REFERENCES: Create foreign key constraints on this table. This privilege must be set on both referencing and referenced tables.

• TRUNCATE: Truncate table contents. Non-owners of tables can also execute the following partition operations on them:

  • DROP_PARTITIONS
  • SWAP_PARTITIONS_BETWEEN_TABLES
  • MOVE_PARTITIONS_TO_TABLE

• ALTER: Modify a table's DDL with ALTER TABLE.

• DROP: Drop a table.

Privileges

Non-superuser, one of the following:

• Table owner

• USAGE privilege on the table schema and one or more privileges on the table

Examples

=> SELECT HAS_TABLE_PRIVILEGE('store.store_dimension', 'SELECT');
 HAS_TABLE_PRIVILEGE
---------------------
 t
(1 row)

=> SELECT HAS_TABLE_PRIVILEGE('release', 'store.store_dimension', 'INSERT');
 HAS_TABLE_PRIVILEGE
---------------------
 t
(1 row)

=> SELECT HAS_TABLE_PRIVILEGE(45035996273711159, 45035996273711160, 'select');
 HAS_TABLE_PRIVILEGE
---------------------
t
(1 row)