Understanding DCL: Data Control Language

Download - Stock Photography
from © Dreamstime.com

A Data Control Language or DCL is a part Structured Query Language (SQL) used to control the access to data in a database. For any in-house or outsource software development process, Data Control Language plays the critical role of setting authorization mechanism of the database.
Data Control Language primarily includes the following commands:

• GRANT: To grant users the right to access the database or perform certain tasks
  
  


• REVOKE: To cancel any previously granted or denied permission

The GRANT and REVOKE commands can be used with the following privileges for a database user:

• INSERT (to insert records)
  
  


• SELECT (to return a result set from a table)
  
  


• DELETE (to delete specific records)
  
  


• EXECUTE (to execute a procedure)
  
  


• USAGE (to modify the maximum number of records allowed)
  
  


• CONNECT (to set a database connection)
  
  


• UPDATE (to modify a record in a table)

GRANT command

The GRANT command sets permissions (for INSERT, SELECT, UPDATE etc.) on any specific or all tables to different users using the database. The general syntax is:

GRANT {ALL/SPECIFIC PERMISSIONS} ON {TABLENAME} TO {USER ACCOUNT} [WITH GRANT OPTION]

In a real-world database-driven environment (such as in an offshore software development company ) any user getting the permission on a specific object (such as a table) from another user "with GRANT option", then that user has the privilege to set permission on that same table to a different user acting as an owner.

REVOKE Command

The REVOKE command revokes or cancels permissions (for INSERT, SELECT, UPDATE etc.) on any specific or all tables from different users using the database. The general syntax is:

REVOKE {ALL/SPECIFIC PERMISSIONS} ON {TABLENAME} FROM {USER ACCOUNT} [CASCADE]

The CASCADE option revokes the permissions from the user as well as the main user who initiated the permission.

Be it for professionals engaged in software development services in India, the United States or elsewhere, the correct permission settings are of paramount importance through DCL.

Video Source: Youtube