This is the multi-page printable view of this section. Click here to print.

Return to the regular view of this page.

REVOKE statements

REVOKE statements let you revoke privileges on database objects from users and roles.

REVOKE statements let you revoke privileges on database objects from users and roles.

1 - REVOKE (authentication)

Revokes privileges on an authentication method from users and roles.

Revokes privileges on an authentication method from users and roles.

Syntax

REVOKE AUTHENTICATION auth-method-name FROM grantee[,...]

Parameters

auth-method-name
Name of the target authentication method.
grantee

Whose privileges are revoked, one of the following:

Privileges

Superuser

Examples

  • Revoke v_ldap authentication from user jsmith:

    => REVOKE AUTHENTICATION v_ldap FROM jsmith;

  • Revoke v_gss authentication from the role DBprogrammer:

    => REVOKE AUTHENTICATION v_gss FROM DBprogrammer;

  • Revoke localpwd as the default client authentication method:

    => REVOKE AUTHENTICATION localpwd FROM PUBLIC;

See also

2 - REVOKE (data loader)

Revokes data-loader privileges from users and roles.

Revokes privileges on automatic data loaders from users and roles.

Syntax

REVOKE [ GRANT OPTION FOR ] { privilege[,...] | ALL [ PRIVILEGES ] }
   ON DATA LOADER [schema.]name
   FROM grantee[,...]
   [ CASCADE ]

Arguments

privilege
One of the following privileges:
  • EXECUTE
  • ALTER
  • DROP
ALL [PRIVILEGES]
Revokes all privileges.
schema
Schema containing the data loader. The default schema is public.
name
Name of the data loader.
grantee

Whose privileges are revoked, one of the following:

CASCADE

Revoke privileges from users who received them from the grantee through WITH GRANT OPTION.

Privileges

Non-superuser, one of the following:

See also

3 - REVOKE (database)

Revokes database privileges from users and roles.

Revokes database privileges from users and roles.

Syntax

REVOKE [ GRANT OPTION FOR ] { privilege[,...] | ALL [ PRIVILEGES ] }
   ON DATABASE db-spec
   FROM grantee[,...]
   [ CASCADE ]

Parameters

GRANT OPTION FOR

Revokes the grant option for the specified privileges. Current privileges for grantees remain unaffected. If you omit this clause, Vertica revokes both the grant option and current privileges.

privilege
The database privilege to revoke, one of the following:

  • CREATE: Create schemas.

  • TEMP: Create temporary tables.

ALL [PRIVILEGES]
Revokes all database privileges that also belong to the revoker. Users cannot revoke privileges that they themselves lack.

The optional keyword PRIVILEGES is supported to comply with the SQL standard.

ON DATABASE db-spec

Specifies the current database, set to the database name or DEFAULT.

*`grantee`*

Whose privileges are revoked, one of the following:

CASCADE

Revoke privileges from users who received them from the grantee through WITH GRANT OPTION.

Privileges

Non-superuser, one of the following:

Examples

Revoke user Fred's privilege to create schemas in the current database:

=> REVOKE CREATE ON DATABASE DEFAULT FROM Fred;

Revoke user Fred's privilege to create temporary tables in the current database:

=> REVOKE TEMP ON DATABASE DEFAULT FROM Fred;

See also

4 - REVOKE (key)

Revokes privileges on a cryptographic key from a user or role.

Revokes privileges on a cryptographic key from a user or role.

To grant privileges on a key, see GRANT (key).

Syntax

REVOKE [ GRANT OPTION FOR ] { privilege[,...] | ALL [ PRIVILEGES ] } ON KEY
    key_name[,...]
    FROM user[,...]

Parameters

`GRANT OPTION FOR`

Revokes the grant option for the specified privileges. Current privileges for grantees remain unaffected. If you omit this clause, Vertica revokes both the grant option and current privileges.

privilege

A privilege, one of the following:

  • USAGE: Allows a user to perform the following actions:

    USAGE on the key also gives implicit USAGE privileges on a certificate that uses it as its private key. Users can also get these privileges from ownership of the key or certificate. USAGE privileges on a certificate allow a user to perform the following actions:

    • View the contents of the certificate.

    • Add (with CREATE or ALTER) the certificate to a TLS Configuration.

    • Reuse the CA certificate when importing certificates signed by it. For example, if a user imports a chain of certificates A > B > C and have USAGE on B, the database reuses B (as opposed to creating a duplicate of B).

    • Specify that the CA certificate signed an imported certificate. For example, if certificate B signed certificate C, USAGE on B allows a user to import C and specify that it was SIGNED BY B.

  • DROP

  • ALTER: Allows a user to see the key and its associated certificates in their respective system tables, but not their contents.

key_name
The target key.
user

Who is granted privileges, one of the following:

Privileges

Non-superuser:

  • Owner

  • Privileges grantee given the option (WITH GRANT OPTION) of granting privileges to other users or roles.

Examples

The following example revokes DROP privileges on a key (and, by extension, its associated certificate) from a user:

=> REVOKE USAGE ON KEY new_key FROM u1;
REVOKE PRIVILEGE

5 - REVOKE (library)

Revokes library privileges from users and roles.

Revokes library privileges from users and roles.

Syntax

REVOKE [ GRANT OPTION FOR ] { USAGE | ALL [ PRIVILEGES ] }
   ON LIBRARY [[database.]schema.]library[,...]
   FROM grantee[,...]
   [ CASCADE ]

Parameters

GRANT OPTION FOR

Revokes the grant option for the specified privileges. Current privileges for grantees remain unaffected. If you omit this clause, Vertica revokes both the grant option and current privileges.

USAGE
Revokes access to the specified libraries.
ALL [PRIVILEGES]
Revokes all library privileges that also belong to the revoker. Users cannot revoke privileges that they themselves lack. The optional keyword PRIVILEGES conforms with the SQL standard.
[database.]schema

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

library
The target library.
grantee

Whose privileges are revoked, one of the following:

CASCADE

Revoke privileges from users who received them from the grantee through WITH GRANT OPTION.

Examples

These commands show how to create a new library, and then grant and revoke user Fred's USAGE privilege on that library.

=> CREATE LIBRARY MyFunctions AS 'home/dbadmin/my_functions.so';
=> GRANT USAGE ON LIBRARY MyFunctions TO Fred;
=> REVOKE USAGE ON LIBRARY MyFunctions FROM Fred;

See also

6 - REVOKE (model)

Revokes model privileges from users and roles.

Revokes model privileges from users and roles.

Syntax

REVOKE [ GRANT OPTION FOR ] { privilege[,...] | ALL [ PRIVILEGES ] }
   ON MODEL [[database.]schema.]model-name [,...]
   FROM grantee[,...]
   [ CASCADE ]

Parameters

GRANT OPTION FOR

Revokes the grant option for the specified privileges. Current privileges for grantees remain unaffected. If you omit this clause, Vertica revokes both the grant option and current privileges.

USAGE
One of the following privileges:

  • USAGE: Usage of the specified models

  • ALTER

  • DROP

ALL [PRIVILEGES]
Revokes all model privileges that also belong to the revoker. Users cannot revoke privileges that they themselves lack.

The optional keyword PRIVILEGES conforms with the SQL standard.

[database.]schema

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

model-name
Name of the target model.
grantee

Whose privileges are revoked, one of the following:

CASCADE

Revoke privileges from users who received them from the grantee through WITH GRANT OPTION.

Privileges

Non-superuser, one of the following:

Examples

Revoke user Fred's USAGE privilege on model mySvmClassModel:

=> REVOKE USAGE ON mySvmClassModel FROM Fred;

See also

7 - REVOKE (procedure)

Revokes procedure privileges from users and roles.

Revokes procedure privileges from users and roles.

Syntax

REVOKE [ GRANT OPTION FOR ] { EXECUTE | ALL PRIVILEGES }
   ON PROCEDURE [[database.]schema.]procedure( [argument-list] )[,...]
   FROM grantee[,...]
   [ CASCADE ]

Parameters

GRANT OPTION FOR

Revokes the grant option for the specified privileges. Current privileges for grantees remain unaffected. If you omit this clause, Vertica revokes both the grant option and current privileges.

EXECUTE
Revokes grantees ability to run the specified procedures.
ALL [PRIVILEGES]
Revokes all procedure privileges that also belong to the revoker. Users cannot revoke privileges that they themselves lack.

The optional keyword PRIVILEGES conforms with the SQL standard.

[database.]schema

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

procedure
The target procedure.
argument-list
A comma-delimited list of procedure arguments, where each argument is specified as follows: 
[argname] argtype

If the procedure is defined with no arguments, supply an empty argument list.

grantee

Whose privileges are revoked, one of the following:

CASCADE

Revoke privileges from users who received them from the grantee through WITH GRANT OPTION.

Privileges

Non-superuser, one of the following:

Examples

This example revokes user Bob's execute privilege on the tokenize procedure.

=> REVOKE EXECUTE ON PROCEDURE tokenize(varchar) FROM Bob;

See also

8 - REVOKE (Resource pool)

Revokes resource pool access privileges from users and roles.

Revokes resource pool access privileges from users and roles.

Vertica checks resource pool privileges at runtime. Revoking a user's privileges for a resource pool can have an immediate effect on the user's current session. For example, a user query might require USAGE privileges on a resource pool. If you revoke those privileges from that user, subsequent attempts by the user to execute that query fail and return with an error message.

Syntax

REVOKE  [ GRANT OPTION FOR ] { USAGE | ALL PRIVILEGES }
   ON RESOURCE POOL resource-pool[,...]
   [FOR SUBCLUSTER subcluster | FOR CURRENT SUBCLUSTER]
   FROM grantee[,...]
   [ CASCADE ]

Parameters

GRANT OPTION FOR

Revokes the grant option for the specified privileges. Current privileges for grantees remain unaffected. If you omit this clause, Vertica revokes both the grant option and current privileges.

USAGE
Revokes grantee's access to the specified resource pool.
ALL PRIVILEGES
Revokes all resource pool privileges that also belong to the revoker. Users cannot revoke privileges that they themselves lack.

The optional keyword PRIVILEGES conforms with the SQL standard.

resource-pool
The target resource pool.
subcluster
The subcluster for the resource pool.
grantee

Whose privileges are revoked, one of the following:

CASCADE

Revoke privileges from users who received them from the grantee through WITH GRANT OPTION.

Privileges

Non-superuser, one of the following:

Examples

Revoke user Joe's USAGE privileges on resource pool Joe_pool.

=> REVOKE USAGE ON RESOURCE POOL Joe_pool FROM Joe;
REVOKE PRIVILEGE

Revoke user Joe's USAGE privileges on resource pool Joe_pool for subcluster sub1.

=> REVOKE USAGE ON RESOURCE POOL Joe_pool FOR SUBCLUSTER sub1 FROM Joe;
REVOKE PRIVILEGE

See also

9 - REVOKE (Role)

Revokes a role from users and roles.

Revokes a role from users and roles.

Syntax

REVOKE [ ADMIN OPTION FOR ] role[,...]
   FROM grantee[,...]
   [ CASCADE ]

Parameters

ADMIN OPTION FOR
Revokes from the grantees the authority to assign the specified roles to other users or roles. Current roles for grantees remain unaffected. If you omit this clause, Vertica revokes role assignment privileges and the current roles .
role
Role to revoke.
grantee

Whose privileges are revoked, one of the following:

CASCADE

Revoke privileges from users who received them from the grantee through WITH GRANT OPTION.

Privileges

One of the following:

  • Superuser

  • Privileges grantee who was given the option (WITH ADMIN OPTION) of extending these privileges to other users

Examples

This example shows the revocation of the pseudosuperuser role from the dbadmin user:

=> REVOKE pseudosuperuser from dbadmin;

This example shows the revocation of administration access from the dbadmin user for the pseudosuperuser role. The ADMIN OPTION command does not remove the pseudosuperuser role.

=> REVOKE ADMIN OPTION FOR pseudosuperuser FROM dbadmin;

Notes

If the role you are trying to revoke was not already granted to the user, Vertica returns a NOTICE:

=> REVOKE commentor FROM Sue;
NOTICE 2022:  Role "commentor" was not already granted to user "Sue"
REVOKE ROLE

See also

10 - REVOKE (schema)

Revokes schema privileges from users and roles.

Revokes schema privileges from users and roles.

Syntax

REVOKE [ GRANT OPTION FOR ] { privilege[,...] | ALL [ PRIVILEGES ] }
   ON SCHEMA [database.]schema[,...]
   FROM grantee[,...]
   [ CASCADE ]

Parameters

GRANT OPTION FOR

Revokes the grant option for the specified privileges. Current privileges for grantees remain unaffected. If you omit this clause, Vertica revokes both the grant option and current privileges.

privilege
The schema privilege to revoke, one of the following:

  • USAGE: Access objects in the specified schemas.

  • CREATE: Create objects in the specified schemas.

You can also revoke privileges from tables and views that they inherited on creation from this schema. When you revoke inherited privileges at the schema level, Vertica automatically applies the revocation to all tables and views that inherited these privileges.

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

  • INSERT: Insert rows, or and load data into tables with COPY.

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

  • ALTER: Modify the DDL of tables and views with ALTER TABLE and ALTER VIEW, respectively.

  • DROP: Drop tables and views.

ALL [PRIVILEGES]
Revokes USAGE AND CREATE privileges. Users cannot revoke privileges that they themselves lack.

The optional keyword PRIVILEGES conforms with the SQL standard.

[database.]schema
The schema on which to revoke privileges. If you specify a database, it must be the current database.
grantee

Whose privileges are revoked, one of the following:

CASCADE

Revoke privileges from users who received them from the grantee through WITH GRANT OPTION.

Privileges

Non-superuser, one of the following:

Examples

Revoke user Joe's USAGE privilege on schema online_sales.

=> REVOKE USAGE ON SCHEMA online_sales FROM Joe;
REVOKE PRIVILEGE

See also

11 - REVOKE (sequence)

Revokes sequence privileges from users and roles.

Revokes sequence privileges from users and roles.

Syntax

REVOKE [ GRANT OPTION FOR ] { privilege[,...] | ALL [ PRIVILEGES ] }
   ON {
     SEQUENCE [[database.]schema.]sequence[,...]
     | ALL SEQUENCES IN SCHEMA [database.]schema[,...] }
   FROM grantee[,...]
   [ CASCADE ]

Parameters

GRANT OPTION FOR

Revokes the grant option for the specified privileges. Current privileges for grantees remain unaffected. If you omit this clause, Vertica revokes both the grant option and current privileges.

privilege
One of the following privileges:
ALL [PRIVILEGES]
Revokes all sequence privileges that also belong to the revoker. Users cannot revoke privileges that they themselves lack.

The optional keyword PRIVILEGES is supported to comply with the SQL standard.

[database.]schema

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

SEQUENCE sequence
Specifies the sequence on which to revoke privileges.
ALL SEQUENCES IN SCHEMA schema
Revokes the specified privileges on all sequences in schema schema.
grantee

Whose privileges are revoked, one of the following:

CASCADE

Revoke privileges from users who received them from the grantee through WITH GRANT OPTION.

Privileges

Non-superuser, one of the following:

Examples

Revoke user Joe's privileges on sequence my_seq.

=> REVOKE ALL PRIVILEGES ON SEQUENCE my_seq FROM Joe;
REVOKE PRIVILEGE

See also

12 - REVOKE (storage location)

Revokes privileges on a USER-defined storage location from users and roles.

Revokes privileges on a USER-defined storage location from users and roles. For more information, see Creating storage locations.

Syntax

REVOKE [ GRANT OPTION FOR ] { privilege[,...] | ALL [ PRIVILEGES ] }
   ON LOCATION 'path' [ ON node ]
   FROM grantee[,...]
   [ CASCADE ]

Parameters

GRANT OPTION FOR

Revokes the grant option for the specified privileges. Current privileges for grantees remain unaffected. If you omit this clause, Vertica revokes both the grant option and current privileges.

privilege
One of the following privileges:

  • READ: Copy data from files in the storage location into a table.

  • WRITE: Export data from the database to the storage location. With WRITE privileges, grantees can also save COPY statement rejected data and exceptions files to the storage location.

ALL [PRIVILEGES]
Revokes all storage location privileges that also belong to the revoker. Users cannot revoke privileges that they themselves lack.

The optional keyword PRIVILEGES is supported to comply with the SQL standard.

ON LOCATION 'path' [ ON node ]
Specifies the path name mount point of the storage location. If qualified by ON NODE, Vertica revokes access to the storage location residing on node.

If no node is specified, the revoke operation applies to all nodes on the specified path. All nodes must be on the specified path; otherwise, the entire revoke operation rolls back.

grantee

Whose privileges are revoked, one of the following:

CASCADE

Revoke privileges from users who received them from the grantee through WITH GRANT OPTION.

Privileges

Non-superuser, one of the following:

Examples

See GRANT (storage location).

See also

Granting and revoking privileges

13 - REVOKE (table)

Revokes table privileges from users and roles.

Revokes table privileges from users and roles.

Syntax

REVOKE [ GRANT OPTION FOR ] { privilege[,...] | ALL [ PRIVILEGES ] }
   ON {
     [ TABLE ] [[database.]schema.]table[,...]
     | ALL TABLES IN SCHEMA [database.]schema[,...] }
   FROM grantee[,...]
   [ CASCADE ]

Parameters

GRANT OPTION FOR

Revokes the grant option for the specified privileges. Current privileges for grantees remain unaffected. If you omit this clause, Vertica revokes both the grant option and current privileges.

privilege
One of the following privileges:
ALL [PRIVILEGES]
Revokes all table privileges that also belong to the revoker. Users cannot revoke privileges that they themselves lack.

The optional keyword PRIVILEGES is supported to comply with the SQL standard.

[database.]schema
Specifies a schema, by default public. If schema is any schema other than public, you must supply the schema name. For example: 
myschema.thisDbObject

One exception applies: you can specify system tables without their schema name.

If you specify a database, it must be the current database.

TABLE table
Specifies the table on which to revoke privileges.
ON ALL TABLES IN SCHEMA schema
Revokes the specified privileges on all tables and views in schema schema.
grantee

Whose privileges are revoked, one of the following:

CASCADE

Revoke privileges from users who received them from the grantee through WITH GRANT OPTION.

Privileges

Non-superuser, one of the following:

Examples

Revoke user Joe's privileges on table customer_dimension.

=> REVOKE ALL PRIVILEGES ON TABLE customer_dimension FROM Joe;
REVOKE PRIVILEGE

See also

14 - REVOKE (TLS configuration)

Revokes privileges granted on one or more TLS Configurations from users and roles.

Revokes privileges granted on one or more TLS Configurations from users and roles.

Syntax

REVOKE [ GRANT OPTION FOR ]
    { ALL | [ privilege[,...] ]}
    ON TLS CONFIGURATION tls_configuration[,...]
    FROM grantee [,...]

Parameters

GRANT OPTION FOR

Revokes the grant option for the specified privileges. Current privileges for grantees remain unaffected. If you omit this clause, Vertica revokes both the grant option and current privileges.

privilege

A privilege, one of the following:

tls_configuration
The TLS Configuration on which to revoke privileges.
grantee

Who is granted privileges, one of the following:

Privileges

Non-superusers require USAGE on the schema and one of the following:

  • Owner

  • Privileges grantee given the option (WITH GRANT OPTION) of granting privileges to other users or roles.

Examples

To revoke ALTER privileges on the TLS Configuration server from the role client_server_tls_manager:

=> REVOKE ALTER ON TLS CONFIGURATION server FROM client_server_tls_manager;

15 - REVOKE (user defined extension)

Revokes privileges on one or more user-defined extensions from users and roles.

Revokes privileges on one or more user-defined extensions from users and roles.

Syntax

REVOKE [ GRANT OPTION FOR ] { EXECUTE | ALL PRIVILEGES }
   ON {
       UDx-type [[database.]schema.]function-name( [argument-list] )[,...]
       | ALL FUNCTIONS IN SCHEMA schema[,...] }
   FROM grantee[,...]

Parameters

GRANT OPTION FOR

Revokes the grant option for the specified privileges. Current privileges for grantees remain unaffected. If you omit this clause, Vertica revokes both the grant option and current privileges.

EXECUTE
Revokes grantees ability to run the specified functions.
ALL [PRIVILEGES]
Revokes all function privileges that also belong to the revoker. Users cannot revoke privileges that they themselves lack.

The optional keyword PRIVILEGES conforms with the SQL standard.

ON UDx-type
Specifies the function's user-defined extension (UDx) type, where UDx-type is one of the following:

  • FUNCTION

  • AGGREGATE FUNCTION

  • ANALYTIC FUNCTION

  • TRANSFORM FUNCTION

  • FILTER

  • PARSER

  • SOURCE

[database.]schema

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

function-name
The name of the user-defined function on which to revoke privileges.
ON ALL FUNCTIONS IN SCHEMA schema
Revokes privileges on all functions in the specified schema.
argument-list
Required for all polymorphic functions, a comma-delimited list of function arguments, where each argument is specified as follows: 
[argname] argtype

If the procedure is defined with no arguments, supply an empty argument list.

grantee

Whose privileges are revoked, one of the following:

CASCADE

Revoke privileges from users who received them from the grantee through WITH GRANT OPTION.

Privileges

Non-superuser, one of the following:

  • Owner of the target function

  • Privileges grantee who was given the option (WITH GRANT OPTION) of extending these privileges to other users

Examples

Revoke EXECUTE privileges from user Bob on function myzeroifnull:

=> REVOKE EXECUTE ON FUNCTION myzeroifnull (x INT) FROM Bob;

Revoke all privileges from user Doug on function Pagerank:

=> REVOKE ALL ON TRANSFORM FUNCTION Pagerank (t float) FROM Doug;

Revoke EXECUTE privileges on all functions in the zero-schema schema from user Bob:

=> REVOKE EXECUTE ON ALL FUNCTIONS IN SCHEMA zero-schema FROM Bob;

Revoke EXECUTE privileges from user Bob on the tokenize function:

=> REVOKE EXECUTE ON TRANSFORM FUNCTION tokenize(VARCHAR) FROM Bob;

Revoke all privileges on the ExampleSource() source from user Alice:

=> REVOKE ALL ON SOURCE ExampleSource() FROM Alice;

See also

16 - REVOKE (view)

Revokes privileges on a view from users and roles.

Revokes privileges on a view from users and roles.

Syntax

REVOKE [ GRANT OPTION FOR ] { privilege[,...] | ALL [ PRIVILEGES ] }
   ON [[database.]schema.]view[,...]
   FROM grantee[,...]
   [ CASCADE ]

Parameters

GRANT OPTION FOR

Revokes the grant option for the specified privileges. Current privileges for grantees remain unaffected. If you omit this clause, Vertica revokes both the grant option and current privileges.

privilege
One of the following:

  • SELECT: Query the specified views.

  • ALTER: Modify a view's DDL with ALTER VIEW

  • DROP: Drop this view with DROP VIEW.

ALL PRIVILEGES
Revokes all privileges that pertain to views that also belong to the revoker. Users cannot revoke privileges that they themselves lack.

The optional keyword PRIVILEGES conforms with the SQL standard.

[database.]schema

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

view
The view on which to revoke privileges.
grantee

Whose privileges are revoked, one of the following:

CASCADE

Revoke privileges from users who received them from the grantee through WITH GRANT OPTION.

Examples

Revoke SELECT privileges from user Joe on view test_view.

=> REVOKE SELECT ON test_view FROM Joe;
REVOKE PRIVILEGE

See also