Revoking Permissions - Firebird

A REVOKE statement is required for removing permissions assigned by GRANT statements. According to the standard, the REVOKE should cascade down through all grantees that acquired the same privilege as a result of a WITH GRANT OPTION grant from this user. However, you should not rely on this in Firebird, since conflicting rules in the standard could cause implementation logic to prevail over the standard under some conditions.

REVOKE statements can remove any privilege that GRANT can assign. Only SYSDBA or the user that granted a privilege can revoke it—the same or other privileges that were granted by other users are not affected.

Permissions acquired “in bulk” can’t be revoked individually. That means

  • A privilege that a user acquired by being granted ALL or a role can be removed only by the original grantor revoking ALL or the role, respectively.
  • Revoking a privilege for a user that got the privilege by way of a grant to PUBLIC or to a UNIX group can be achieved only by the original grantor revoking the privilege on PUBLIC or the group, respectively.
  • Privileges granted to PUBLIC can only be revoked FROM PUBLIC.

Using REVOKE

The simplified syntax pattern for REVOKE is the other face of the GRANT syntax. The TO <grantee> clause is replaced by FROM <grantee>:

REVOKE <privileges> ON <object> FROM <grantee> ;

The following statement removes the SELECT privilege for the user KILROY on the DEPARTMENT table if it was granted with GRANT SELECT:

REVOKE SELECT ON DEPARTMENT FROM KILROY;

The following statement removes the UPDATE privilege for the procedure COUNT_BEANS on the CUSTOMER table:

REVOKE UPDATE ON CUSTOMER FROM PROCEDURE COUNT_BEANS;

The next statement removes the EXECUTE privilege that was granted to the procedure COUNT_BEANS on the ABANDON_OLD procedure:

REVOKE EXECUTE ON PROCEDURE ABANDON_OLD FROM PROCEDURE COUNT_BEANS;

Revoking Multiple Privileges

To remove some, but not all, of the access privileges assigned to a user or procedure, list the privileges to remove, separating them with commas. For example, the following statement removes the INSERT and UPDATE privileges on DEPARTMENT from a user, SERENA:

REVOKE INSERT, UPDATE ON DEPARTMENT FROM SERENA;

The next statement removes two privileges on the CUSTOMER table from a stored procedure, COUNT_BEANS:

REVOKE INSERT, DELETE ON CUSTOMER FROM PROCEDURE COUNT_BEANS;

Any combination of previously assigned SELECT, INSERT, UPDATE, DELETE, or REFERENCES privileges can be revoked from a grantee, whether they were granted individually, in a list, or using ALL.

As with GRANT, the REVOKE ALL privilege combines the SELECT, INSERT, UPDATE, DELETE, and REFERENCES privileges into a single expression. It will revoke any of these permissions from a grantee that had the privilege assigned to its own name.

For example, the following statement revokes all access privileges on DEPARTMENT from a user named MAGPIE:

REVOKE ALL ON DEPARTMENTS FROM MAGPIE;

If the grantee does not have all of the privileges that ALL encompasses, it will not cause an exception. That can make REVOKE ALL quite useful if you do not know what privileges a grantee has. It will not necessarily solve the problem of eliminating all of the permissions available to the user, because REVOKE ALL has limits on what it is capable of revoking.

What REVOKE ALL Does Not Revoke

REVOKE ALL does not revoke

  • The grantee’s role membership or privileges the grantee inherits with role membership
  • Any privileges the grantee has by way of PUBLIC
  • The grantee’s EXECUTE privileges

Revoking the EXECUTE Privilege

The syntax for revoking a grantee’s EXECUTE privilege on a stored procedure has this
syntax pattern:

REVOKE EXECUTE
ON PROCEDURE <procedure-name>
FROM <grantee> [, <grantee> [, ...]]]
| [TRIGGER <trigger-name> [, <trigger-name> [,...]]]
[PROCEDURE <procedure-name> [, <procedure-name [, ...]]]
[VIEW <view-name> [, VIEW <view-name> [, ...]]];

The following statement removes the EXECUTE privilege from user HPOTTER on the procedure COUNT_CHICKENS:

REVOKE EXECUTE ON PROCEDURE COUNT_CHICKENS FROM HPOTTER;

Revoking from Grantees

Now, we take a look at how grantees—the objective of the FROM clause in the REVOKE statement —can be bundled for bulk denial of privileges.

From a List of Grantees

Use a comma-separated list of grantees to bulk-remove privileges from a number of users in a single statement. The following statement revokes INSERT and UPDATE permissions on DEPARTMENT from three grantees in one hit:

REVOKE INSERT, UPDATE
ON DEPARTMENTS
FROM MAGPIE, BRUNHILDE, KILROY;

From a Role

Revoking privileges granted to a role denies those privileges to any grantees having that role as a privilege:

REVOKE UPDATE
ON DEPARTMENT
FROM CARTEBLANCHE;

Now, users who were granted the CARTEBLANCHE role no longer have the UPDATE privilege on DEPARTMENT, but they retain other privileges (SELECT, INSERT, DELETE, REFERENCES, EXECUTE) that they might have inherited from their membership of CARTEBLANCHE.

You can use a single statement to revoke the same privileges from one or more roles:

REVOKE DELETE, INSERT
ON DEPARTMENT
FROM CARTEBLANCHE, MAITRE_D;

From a Role User

Revoking membership from a role that has been assigned to a grantee denies the grantee all of the permissions that it acquired through the role. Use REVOKE to remove a role that you assigned to users. The following statement revokes the CARTEBLANCHE role from KILROY:

REVOKE CARTEBLANCHE FROM KILROY;

KILROY no longer has any of the access privileges acquired as a result of membership in the role. However, other grantees who acquired the same privileges through membership of the role are unaffected. From Objects

To revoke privileges from one or more procedures, triggers, or views, include the appropriate keyword (PROCEDURE, TRIGGER, VIEW ) before the name of the grantee object.

You can revoke the same privilege from different types of grantee objects by making a separate comma-separated list for each object type. In that case, just start each list with the object type keyword.

The following statement revokes INSERT and UPDATE privileges on the CUSTOMER table from two procedures and a trigger:

REVOKE INSERT, UPDATE
ON CUSTOMER
FROM PROCEDURE COUNT_CHICKENS, ABANDON_OLD
TRIGGER AI_SALES ;

From User PUBLIC

To revoke privileges that all users are granted as user PUBLIC, just treat PUBLIC as any other grantee. For example, the following statement revokes INSERT and DELETE permissions on DEPARTMENT from all users:

REVOKE SELECT, INSERT, UPDATE
ON DEPARTMENT
FROM PUBLIC;

Executing this statement leaves the table’s owner and SYSDBA, as well as any stored procedures, views, or triggers that already had them, retaining INSERT and DELETE privileges on DEPARTMENT. Also, revoking privileges from PUBLIC does not strip privileges from users that had them in their own right.


All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

Firebird Topics