REVOKE
NAME
REVOKE: take privileges, or roles, away
SYNOPSIS
<revoke statement> ::= <revoke privilege statement> | <revoke role statement>
<revoke privilege statement> ::=
REVOKE [ GRANT OPTION FOR ] <privileges> ON <object name>
FROM <grantee> [ { , <grantee> }... ]
[ GRANTED BY <grantor> ] <drop behavior>
<revoke role statement> ::=
REVOKE [ ADMIN OPTION FOR ] <role name> [ { , <role name> }... ]
FROM <grantee> [ { , <grantee> }... ]
[ GRANTED BY <grantor> ] <drop behavior>
<drop behavior> ::= CASCADE | RESTRICT
The <privileges>, <object name>, <grantee> and <grantor> are
those of GRANT.
DESCRIPTION
REVOKE takes from each grantee the privileges its user granted it on the object; GRANT OPTION FOR takes only the grant option, leaving the privileges. A privilege another user granted the grantee is not taken.
What a privilege taken leaves without the privileges it needs is abandoned: a privilege the grantee granted on, by the grant option it loses; a view, a constraint, an assertion, a domain constraint or a trigger of the grantee's that needs the privilege: TRIGGER on its table, SELECT on it if the trigger names a transition variable or table, or one its action's statements need. A function or procedure of a schema whose owner loses a privilege its body needs, or EXECUTE on a routine its body invokes, is abandoned too, with what invokes it. A routine of a module goes with its module, and what invokes a module's routine needs EXECUTE on the module (CREATE MODULE). RESTRICT refuses the revoke (SQLSTATE 2B000) if anything would be abandoned; CASCADE destroys what is abandoned, and what that abandons in turn.
A generated column whose generation expression names a domain or a collation is lost when its table's schema's owner loses USAGE on it. RESTRICT refuses the revoke, and CASCADE drops the column first, as ALTER TABLE DROP COLUMN with CASCADE drops it. A revoke that would leave a table no column is refused before it drops any. If the revoke fails after one is dropped, the transaction can only roll back.
USAGE taken on a collation leaves some schemas' owners without it. A column or domain of such a schema that is of the collation is impacted: RESTRICT refuses the revoke (SQLSTATE 2B000), and CASCADE makes it of the default collation, each table's rows copied and its indexes built again, as ALTER TABLE does. That can take as long, and it fails if a constraint no longer holds of the rows. A table whose foreign key references one reverted is checked again. The schema's views and constraints that name the collation in COLLATE are abandoned.
USAGE taken on a distinct type, or EXECUTE on one of its functions, abandons what needs it. A column of a schema whose owner is left without USAGE on the type is lost if it is of the type, an array of it, or a type based on it, and so is a generated column that names the type or invokes one of its functions. A view, constraint or assertion that does is abandoned. RESTRICT refuses the revoke (SQLSTATE 2B000), and CASCADE drops each lost column first, as ALTER TABLE DROP COLUMN with CASCADE drops it, then what is abandoned. A revoke that would drop every column of a table is refused before it drops any.
USAGE taken on a registered XML Schema abandons the grants the grantee made by the grant option it loses, and what names it of a schema whose owner it leaves without USAGE: each registered XML Schema that includes, redefines or imports it, and each view, constraint, assertion, trigger and routine that validates by it or is of a type of XMLSCHEMA of it. RESTRICT refuses the revoke (SQLSTATE 2B000), and CASCADE drops them as DROP XML SCHEMA with CASCADE does. A column, domain or user-defined type of an XML type of XMLSCHEMA of it is dropped by nothing, so it refuses the revoke even with CASCADE (SQLSTATE 2B000).
USAGE taken on a registered XQuery module abandons, as on a registered XML Schema, the grants the grantee made by the grant option it loses, and, of a schema whose owner it leaves without USAGE, each registered XQuery module that imported it and each view, constraint, assertion, trigger and routine whose query imported it. RESTRICT refuses the revoke (SQLSTATE 2B000), and CASCADE drops them as DROP XQUERY MODULE with CASCADE does.
A privilege named that its user had not granted the grantee is not taken, and the statement completes with the warning SQLSTATE 01006.
The revoker is the grantor GRANT would be: the session's user, or, with GRANTED BY CURRENT_ROLE, its role.
REVOKE of roles takes each role from each grantee, as the revoker granted it; ADMIN OPTION FOR takes only the admin option, leaving the role. The revoker holds each role WITH ADMIN OPTION. A grant of the role the grantee made by the admin option it loses is abandoned, unless the grantee holds the admin option another way. So is a privilege a role granted that it held only through a role it no longer has, and what needs that privilege, as for privileges taken. RESTRICT refuses the revoke (SQLSTATE 2B000) if a privilege or an object would be abandoned, and leaves an abandoned grant of a role as it is; CASCADE destroys what is abandoned, grants of roles among them.
REVOKE runs within the session's transaction: ROLLBACK undoes it.
RULES
- The object exists, as for GRANT; each role named exists.
- RESTRICT: nothing is abandoned, and no column or domain is impacted.
- Of GRANTED BY CURRENT_USER, the session has a current user; of GRANTED BY CURRENT_ROLE, it has set a role.
- The transaction is not READ ONLY.
PRIVILEGES
None of privileges: a user revokes only what it granted. Of roles, the revoker holds each WITH ADMIN OPTION.
ERRORS
| SQLSTATE | SQLCODE | When |
|---|---|---|
| 2A000 | -102 | No <drop behavior> is written, or the statement is not written as the synopsis shows |
| 2A000 | -103 | A rule above is broken |
| 2A000 | -104 | The revoker holds a role named not WITH ADMIN OPTION |
| 2B000 | -103 | RESTRICT, and a privilege, view, constraint, assertion, trigger or routine would be abandoned, a column or domain of a schema left without USAGE is of the collation, a grant of USAGE on the sequence generator, the registered XML Schema or the registered XQuery module, or a registered XML Schema or XQuery module using it, would be abandoned, or a column, view, constraint, generated column or grant would be lost or abandoned with a privilege on a distinct type or its function |
| 23000 | -201 | A constraint no longer holds of the rows of a table reverted |
| 25000 | -917 | The transaction is READ ONLY |
| 01006 | 0 | Not an error: a privilege named was not granted, and is not taken |
EXAMPLES
Of the privileges of GRANT: BOB holds SELECT on STAFF with grant option, granted SELECT to CAROL, and has a view STAFF_NAMES of STAFF. Run by ANN:
REVOKE SELECT ON STAFF FROM BOB RESTRICT;
REVOKE GRANT OPTION FOR SELECT ON STAFF FROM BOB CASCADE;
SELECT GRANTOR, GRANTEE, PRIVILEGE_TYPE, IS_GRANTABLE FROM INFORMATION_SCHEMA.TABLE_PRIVILEGES
WHERE TABLE_NAME = 'STAFF' AND PRIVILEGE_TYPE = 'SELECT' AND GRANTEE <> 'ANN' ORDER BY 2;
REVOKE SELECT ON STAFF FROM BOB CASCADE;
SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'STAFF_NAMES';
REVOKE DELETE ON STAFF FROM BOB RESTRICT;
turborel: SQLCODE -103 (SQLSTATE 2A000): REVOKE ... RESTRICT would abandon a privilege BOB granted CAROL on ANN.STAFF [ISO/IEC 9075:1992 §11.37 SR18]
OK
GRANTOR | GRANTEE | PRIVILEGE_TYPE | IS_GRANTABLE
--------------------------------------------------------------+--------------------------------------------------------------+--------------------------------------------------------------+--------------------------------------------------------------
ANN | BOB | SELECT | NO
(1 row)
OK
----------------------
0
(1 row)
OK, with a warning: SQLSTATE 01006
Taking BOB's grant option took CAROL's SELECT, which it had granted; taking BOB's SELECT took his view. ANN had granted BOB no DELETE.
Of a collation: ANN made FOLD, FOR SQL_TEXT FROM CASE_INSENSITIVE NO PAD, and granted BOB USAGE on it, and BOB's table FRIENDS has a column
N of it, unique, holding Bob. Run by ANN:
REVOKE USAGE ON COLLATION ANN.FOLD FROM BOB RESTRICT;
REVOKE USAGE ON COLLATION ANN.FOLD FROM BOB CASCADE;
COMMIT;
turborel: SQLCODE -103 (SQLSTATE 2B000): revoking USAGE on ANN.FOLD impacts BOB.FRIENDS, which is of it: revoke it with CASCADE [ISO/IEC 9075-2:2023 §12.7 GR12q]
OK
OK
Then by BOB:
SELECT COLLATION FOR (N) FROM FRIENDS;
SELECT COUNT(*) FROM FRIENDS WHERE N = 'BOB';
INSERT INTO FRIENDS VALUES ('bob');
--------------------------------------------------------------
SQL_TEXT
(1 row)
----------------------
0
(1 row)
OK
N compares by the default collation now, so 'bob' is not Bob.
Of roles: ANN granted SENIOR to BOB WITH ADMIN OPTION, and BOB granted it to CAROL by that option (see GRANT). Run by ANN:
REVOKE SENIOR FROM BOB CASCADE;
COMMIT;
OK
OK
CAROL's grant, made by the admin option BOB lost, was abandoned and destroyed with it:
SET ROLE 'SENIOR';
turborel: SQLCODE -104 (SQLSTATE 0P000): no role SENIOR is granted to CAROL or to PUBLIC [ISO/IEC 9075-2:2023 §19.3 GR3, GR4]
STANDARD
ISO/IEC 9075:1992 §11.37. Of a collation, ISO/IEC 9075-2:2023 §12.7, as SQL-92 has no rule for a column of one; of a sequence generator, which SQL-92 lacks, the same; of a registered XML Schema, whose USAGE ISO/IEC 9075-14 leaves to the implementation [§4.7.1], as of a sequence generator; of a registered XQuery module, turborel's, as of a registered XML Schema. SQL-86 has no REVOKE. ISO/IEC 9075-2:2023 §12.7 GR12h loses a generated column only when its generation expression loses EXECUTE on a routine. Losing it over USAGE on a domain or collation is turborel's reading, as the expression would no longer bind. REVOKE of roles is ISO/IEC 9075-2:2023 §12.7 (Feature T331), and GRANTED BY §12.8 (T332).