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
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
Revoking the EXECUTE Privilege
The syntax for revoking a grantee’s EXECUTE privilege on a stored procedure has this
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
From a Role
Revoking privileges granted to a role denies those privileges to any grantees having that role as a privilege:REVOKE UPDATE
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
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
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
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.
Firebird Related Interview Questions
|RDBMS Interview Questions||MySQL Interview Questions|
|Linux Interview Questions||Mac OS X Deployment Interview Questions|
|Windows Administration Interview Questions||Windows Server 2003 Interview Questions|
|SQL Interview Questions||NoSQL Interview Questions|
|Advanced C++ Interview Questions|
All rights reserved © 2020 Wisdom IT Services India Pvt. Ltd
Wisdomjobs.com is one of the best job search sites in India.