Neoview SQL Reference Manual (R2.4 SP2)

grantee is authid
authid specifies an authorization ID to whom you revoke privileges. Authorization IDs
identify roles during the processing of SQL statements. The authorization ID must be a valid
role name enclosed in double quotes. authid is not case-sensitive.
REVOKE allows two special authorization IDs called PUBLIC and SCHEMA:
• PUBLIC grants the privilege to all present and future authorization IDs.
• SYSTEM specifies the implicit grantor of privileges to the creator of objects. You cannot
specify SYSTEM as authid in a REVOKE statement.
drop-behavior
If you specify RESTRICT, the REVOKE operation fails if there are privilege descriptors or
objects that would no longer be valid after the specified privileges are removed. If you specify
CASCADE, any such dependent privilege descriptors and objects are removed as part of the
REVOKE operation.
The default is RESTRICT.
Considerations for REVOKE
Authorization and Availability Requirements
You cannot revoke a privilege from a specific role if that privilege has been granted to PUBLIC.
You can revoke the PUBLIC privilege but you cannot revoke an individual privilege such as
SELECT.
Examples of REVOKE
• This example revokes one role’s SELECT privileges on a table. The REVOKE operation fails
if there are privilege descriptors or objects that would no longer be valid after the SELECT
privilege is removed.
REVOKE SELECT ON TABLE persnl.employee
FROM "role.dev" RESTRICT;
• This example revokes the privileges of granting SELECT and DELETE privileges on a table
from two roles. The REVOKE operation fails if there are privilege descriptors or objects that
would no longer be valid after the SELECT and DELETE privileges are removed (RESTRICT
is implied).
REVOKE GRANT OPTION FOR SELECT, DELETE
ON TABLE sales.odetail FROM "role.payroll", "role.finance";
• This example revokes UPDATE privileges on two columns of a table. The REVOKE operation
fails if there are privilege descriptors or objects that would no longer be valid after the
UPDATE privileges are removed.
REVOKE UPDATE (start_date, ship_timestamp)
ON TABLE persnl.project FROM PUBLIC RESTRICT;
156 SQL Statements