REVOKE (schema)

Revokes schema privileges from users and roles.

Syntax

REVOKE [ GRANT OPTION FOR ] { privilege[,...] | ALL [ PRIVILEGES ] }
   ON SCHEMA [{namespace. | 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, OpenText™ Analytics Database 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, OpenText™ Analytics Database 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 | namespace }

Name of the database or namespace that contains schema:

• Database name: If specified, it must be the current database.

• Namespace name (Eon Mode only): You must specify the namespace of objects in non-default namespaces. If no namespace is provided, it is assumed the object is in the default namespace.

schema

Name of the schema, by default public.

If you specify a schema name that contains a period, the part before the period cannot be the same as the name of an existing namespace.

grantee

Whose privileges are revoked, one of the following:

• User name

• Role

• PUBLIC: The default role of all users

CASCADE

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

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