REVOKE

The REVOKE statement removes one or more privileges from a user.

Required privilege

ADMIN to revoke system privileges.

ADMIN or object owner to revoke object privileges.

Usage with TimesTen Scaleout

This statement is supported with TimesTen Scaleout.

SQL syntax

REVOKE {SystemPrivilege [,...] | ALL [PRIVILEGES]} FROM {User |PUBLIC} [,...]

or

REVOKE {{ObjectPrivilege [,...] | ALL [PRIVILEGES]} ON {[Owner.Object}} [,...]
FROM {user | PUBLIC}[,...]

Parameters

The following parameters are for revoking system privileges:

Parameter Description

SystemPrivilege

This is the system privilege to revoke. See "System Privileges" for a list of acceptable values.

ALL [PRIVILEGES]

Revokes all system privileges from the user.

User

Name of the user from whom privileges are being revoked. The user name must first have been introduced to the TimesTen database by a CREATE USER statement.

PUBLIC

Specifies that the privilege is revoked for all users.

The following parameters are for revoking object privileges:

Parameter Description

ObjectPrivilege

This is the object privilege to revoke. See "Object Privileges" for a list of acceptable values.

ALL [PRIVILEGES]

Revokes all object privileges from the user.

User

Name of the user from whom privileges are to be revoked. The user name must first have been introduced to the TimesTen database through a CREATE USER statement.

[Owner.]Object

Object is the name of the object on which privileges are being revoked. Owner is the owner of the object. If Owner is not specified, then the user who is revoking the privilege is known as the owner.

PUBLIC

Specifies that the privilege is revoked for all users.

Description

  • Privileges on objects cannot be revoked from the owner of the objects.

  • Any user who can grant a privilege can revoke the privilege even if they were not the user who originally granted the privilege.

  • Privileges must be revoked at the same level they were granted. You cannot revoke an object privilege from a user who has the associated system privilege. For example, if you grant SELECT ANY TABLE to a user and then try to revoke SELECT ON BOB.TABLE1, the revoke fails unless you have specifically granted SELECT ON BOB.TABLE1 in addition to SELECT ANY TABLE.

  • If a user has been granted all system privileges, you can revoke a specific privilege. For example, you can revoke ALTER ANY TABLE from a user who has been granted all system privileges.

  • If a user has been granted all object privileges, you can revoke a specific privilege on a specific object from the user. For example, you can revoke the DELETE privilege on table CUSTOMERS from user TERRY even if TERRY has previously been granted all object privileges.

  • You can revoke all privileges from a user even if the user has not previously been granted all privileges.

  • You cannot revoke a specific privilege from a user who has not been granted the privilege.

  • You cannot revoke privileges on objects owned by a user.

  • You cannot revoke system privileges and object privileges in the same statement.

  • You can specify only one object in an object privilege statement.

  • Revoking the SELECT privilege on a detail table or a system privilege that includes the SELECT privilege from user2 on a detail table owned by user1 causes associated materialized views owned by user2 to be marked invalid.

  • When replication is configured, this statement is replicated.

Examples

Revoke the ADMIN and DDL privileges from the user terry: 

REVOKE admin, ddl FROM terry;

Assuming the revoker has ADMIN privilege, revoke the UPDATE privilege from terry on the customers table owned by pat: 

REVOKE update ON pat.customers FROM terry;

See also