ALL Keyword

You can use the ALL keyword to revoke both Alter and Select privileges from another user or from a role.

User List

The authorization identifiers (or the PUBLIC keyword) that follow the FROM keyword of REVOKE specify who loses the revoked privileges or revoked roles. If you use the PUBLIC keyword as the user list, the REVOKE statement revokes the specified privileges or roles from PUBLIC, thereby revoking them from all users to whom the privileges or roles have not been explicitly granted, or who do not hold some other role through which they have received the role or privilege.

The user list can consist of the authorization identifier of a single user or of multiple users, separated by commas. If you use the PUBLIC keyword as the user list, the REVOKE statement revokes the specified privileges from all users.

User List:
 
|--+-PUBLIC---------+-------------------------------------------|
   | .-,----------. |
   | V            | |
   '---+-user---+-+-'
       '-'user'-'

Element	Description	Restrictions	Syntax
user	Login name of a user whose privilege or role you are revoking	Must be a valid authorization identifier	Owner Name, p. Owner Name

Spell the user names in the list exactly as they were spelled in the GRANT statement. You can optionally use quotes around each user name in the list to preserve the lettercase. In an ANSI-compliant database, if you do not use quotes to delimit user, the name of the user is stored in uppercase letters unless the ANSIOWNER environment variable was set to 1 before the database server was initialized.

When you specify login names, you can use the REVOKE statement and the GRANT statement to secure various types of database objects selectively. For examples, see When to Use REVOKE Before GRANT.

Role Name

Only the DBA or a user who was granted a role WITH GRANT OPTION can revoke a role or its privileges. Users cannot revoke roles from themselves.

Role Name:
 
|--+-'role'-+---------------------------------------------------|
   '-role---'

Element	Description	Restrictions	Syntax
role	A role with one of these attributes: Loses an existing privilege or role Is lost by a user or by another role	Must exist. If enclosed between quotation marks, role is case sensitive.	Owner Name, p. Owner Name

Immediately after the REVOKE keyword, the name of a role specifies a role to be revoked from the user list. After the FROM keyword, however, the name of a role specifies a role from which access privilege (or another role) is to be revoked. The same FROM clause can include both user and role names if no other REVOKE options conflict with the user or role specifications. Syntax to revoke privileges on a role or from a role are extensions to the ANSI/ISO standard for SQL.

When you include a role after the FROM keyword of the REVOKE statement, the specified privilege (or another role) is revoked from that role, but users who have that role retain any privileges or roles that were granted to them individually.

If you enclose role between quotation marks, the name is case sensitive and is stored exactly as you typed it. In an ANSI-compliant database, if you do not use quotation marks as delimiters, the role is stored in uppercase letters.

When you revoke a role that was granted to a user with the WITH GRANT OPTION keywords, you revoke both the role and the option to grant it.

The following examples show the effects of REVOKE role:

Remove users or remove another role from inclusion in the specified role:
```
REVOKE accounting FROM mary
REVOKE payroll FROM accounting
```
Remove one or more access privileges from a role:
```
REVOKE UPDATE ON employee FROM accounting
```

When you revoke table-level privileges from a role, you cannot include the RESTRICT or CASCADE keywords.