How to Revoke Privileges
In general, ONLY the user who originally granted a privilege can revoke it using a REVOKE statement. That user must have superuser privilege or have the optional WITH GRANT OPTION on the privilege. The user also must have USAGE privilege on the schema and appropriate privileges on the object for the REVOKE statement to succeed.
In order to revoke a privilege, this privilege must have been granted to the specified grantee by this grantor before. If Vertica finds that to be the case, the above REVOKE statement removes the privilege (and WITH GRANT OPTION privilege, if supplied) from the grantee. Otherwise, Vertica prints a NOTICE that the operation failed, as in the following example.
=> REVOKE SELECT ON applog FROM Bob; NOTICE 0: Cannot revoke "SELECT" privilege(s) for relation "applog" that you did not grant to "Bob" REVOKE PRIVILEGE
The above REVOKE statement removes the privilege (and WITH GRANT OPTION privilege, if applicable) from the grantee or it prints a notice that the operation failed.
In order to revoke grant option for a privilege, the grantor must have previously granted the grant option for the privilege to the specified grantee. Otherwise, Vertica prints a NOTICE.
The following REVOKE statement removes the GRANT option only but leaves the privilege intact:
=> GRANT INSERT on applog TO Bob WITH GRANT OPTION; GRANT PRIVILEGE => REVOKE GRANT OPTION FOR INSERT ON applog FROM Bob; REVOKE PRIVILEGE
When a user revokes an explicit list of privileges, such as GRANT INSERT, DELETE, REFERENCES ON applog TO Bob
:
- The REVOKE statement succeeds only if all the roles are revoked successfully. If any revoke operation fails, the entire statement rolls back.
- Vertica returns ERROR if the user does not have grant options for the privileges listed.
- Vertica returns NOTICE when revoking privileges that this user had not been previously granted.
When a user revokes ALL privileges, such as REVOKE ALL ON applog TO Bob
, the statement always succeeds. Vertica revokes all the privileges on which the grantor has the optional WITH GRANT OPTION and skips those privileges without the WITH GRANT OPTION.
For example, if the user Bob has delete privileges with the optional grant option on the applog table, only grant option is revoked from Bob, and the statement succeeds without NOTICE:
=> REVOKE GRANT OPTION FOR DELETE ON applog FROM Bob;
For details, see the REVOKE Statements in the SQL Reference Manual.