In SQL, the DCL commands are used for assigning the different authorizations to the user, these type of authorizations is known as privilege. Grant and Revoke commands are the DCL commands. The GRANT command is used for conferring the authorization to the users whereas REVOKE command is used for withdrawing the authorization. Select, insert, update and delete are some of the privileges that are included in SQL standards.

Content: Grant Vs Revoke

Comparison Chart

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.

Key Differences Between Grant and Revoke

Conclusion

The GRANT command gives the privileges or access to the users on the database objects. On the other hand, REVOKE command is used for removing the rights or privileges from the users on the database objects.

ncG1vNJzZmislZi1pbXFn5yrnZ6YsrR6wqikaJyZm7OmvsSnmp5lkprBuLHEp2SgqpGjwW6tzZ1kq52mpLimesetpKU%3D