REVOKE (schema)

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.

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

Important

Inherited privileges must be explicitly revoked.

[database.]schema

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

grantee

Specifies whose privileges are revoked, one of the following:

• User name

• Role

• PUBLIC: The default role of all users

CASCADE

If the target grantees have a grant option to extend the specified privileges to other users, CASCADE specifies to search for these users and revoke the privileges from them also.

Privileges

Non-superuser, one of the following:

• Ownership

• GRANT OPTION on the object

Examples

Revoke user Joe's USAGE privilege on schema online_sales.

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

See also

• GRANT (schema)
• Granting and revoking privileges