Privileges Revoked Through Roles

Examples that follow show privileges that can be revoked because a role was specified in the AS grantor clause of the GRANT statement through which the privileges were acquired.

Example 1:  general Usage

DATABASE ruv;
CREATE ROLE schaden;
GRANT schaden TO user11;

CREATE TABLE 'admin'.test ( aa INTEGER );
REVOKE ALL ON test FROM PUBLIC;
GRANT SELECT ON test TO schaden WITH GRANT OPTION;
REVOKE ALL ON test FROM Schaden;

Example 2:  REVOKE AS

{User admin} 

DATABASE ruv;
CREATE ROLE schaden;

CREATE TABLE 'admin'.test ( aa INTEGER );
REVOKE ALL ON test FROM PUBLIC;
GRANT SELECT ON test TO schaden WITH GRANT OPTION;
GRANT SELECT ON test TO user1 AS schaden;

REVOKE SELECT ON test FROM user1 AS schaden;

Here the role schaden holds the Select privilege on tabletest. The owner of that table is able to grant that privilege in the name of schaden to another user. The code is able to revoke specific privileges granted on behalf of a role, using the AS clause of the REVOKE statement. The difference between this example and example 4 below is that here user admin revokes privileges on behalf of the role through the AS clause.

Similar to example 2 for the GRANT statement, with this example the DBA is able to get around the restrictions described in the section above.

Example 3:  Revoke chains of privileges

DATABASE ruv;
CREATE ROLE schaden;
CREATE ROLE leben;

CREATE TABLE 'admin'.test ( aa INTEGER );
REVOKE ALL ON test FROM PUBLIC;
GRANT SELECT ON test TO schaden WITH GRANT OPTION;
GRANT SELECT ON test TO leben as schaden;

REVOKE ALL ON test FROM Schaden;

The behavior is similar to a revoke from a simple user; both leben and schaden no longer have Select privilege on the table test.

The next example is of a REVOKE that fails:

Example 4: Revoker was not the grantor of the privilege:

{User admin} 

DATABASE ruv;
CREATE ROLE schaden;

CREATE TABLE 'admin'.test ( aa INTEGER );
REVOKE ALL ON test FROM PUBLIC;
GRANT SELECT ON test TO schaden WITH GRANT OPTION;
GRANT SELECT ON test TO user1 AS schaden;


{User user1}

DATABASE ruv;
SET ROLE schaden;

REVOKE SELECT ON test FROM ebach;

– Error -580/111

In the example, a user named user1 is granted the role schaden. When user1 set the role to schaden, and attempt to revoke privileges, this leads to error 580/111, because only the privileges granted on behalf of user1 are considered; no role privileges are examined. For a REVOKE statement to succeed in this case, it must be issued by a user who holds the DBA privilege, so that the AS clause specifying the role can be included, as in the following statement:

REVOKE SELECT ON test FROM ebach AS schaden

If the AS clause of the GRANT statement specified a role as the grantor of a privilege, then to successfully revoke the privilege, the REVOKE statement must also specify that role in its AS clause. Example 2 in the REVOKE examples section illustrates the same point.