Question: Describe GRANT and REVOKE commands.
Definition of Grant:
The database administrator defines the GRANT command in SQL for giving the access or privileges to the users of the database. Three major components which are involved in the authorization are the users, privilege/s (operations) and a database object. The user is the one who triggers the execution of the application program. Operations are the component which is embedded in an application program. The operations are performed on database objects such as relation or view name.
SYNTAX of GRANT Command:
grant <privilege record>
on <relation title or view title>
to <user/role record>;
Here the privilege list could involve select, insert, update and delete operations or combination of them. These three aspects of the command are checked by authorization control before proceeding.
When an owner account A1 of the relation (table) R grants privilege to another account A2 on R then the account A2 can access the relation R and is authorised to give the privileges to another account on R. If the A1 revokes the privileges from A2 on R1 then, all the privileges that A2 propagated will be revoked automatically by the system. So, this is how the privileges on tables can propagate. Thus, a DBMS permitting propagation should follow the privileges that are granted so that the privileges can be revoked easily.
Let’s take an example to illustrate the Granting of privileges. We have two schemas for the tables Faculty and Department and accounts A1 and A2.
GRANT SELECT, INSERT, UPDATE ON FACULTY, DEPARTMENT TO A1, A2;
In the above given example, the account A1 and A2 are allowed to perform the select, insert and update operations on the employee and department table.
Definition of Revoke:
The REVOKE command in SQL is defined to take away the granted privileges (authorizations) from the user of the database. The one who has the authority to withdraw the privileges is the database administrator.
SYNTAX of REVOKE Command:
revoke <privilege list>
on <relation name or view name>
from <user/role list>;
The command is similar to grant command except for the revoke keyword and ‘from’. In given command, the operations included in the privilege are cancelled for the particular user or role list. Revoking becomes complex when privileges are propagated from one user to other.
Let’s take the similar example to illustrate the Revoking of privileges.
REVOKE INSERT, UPDATE ON FACULTY, DEPARTMENT FROM A1, A2;
In the above given example, the A1 and A2 accounts are withdrawn from their rights and are not permitted to perform insert and update operations on the employee and department table.
