DCL (Data Control Language)


The DCL commands in SQL are used to grant permissions and rights to the user and controls access to the database by securing it. DCL commands protects the data from unauthorized access.   


A user needs privileges to perform any operations in a database, such as creating tables, views etc. There are two types of privileges which can be accessed by a user:


i. System -  It permits to create table, session and all other system privileges.

ii. Object - It includes  permissions for a command to perform any operation on the database such as alter, update, drop etc..


There are two types of DCL commands:


  • GRANT


GRANT command gives users the privileges to access a database, to perform various operations in the database.


Syntax:

GRANT object_privilege ON table_name TO user1, user2,..., userN;


Explanation:


object_privilege includes update, alter, select etc. whichever the user requires the privilege to access. user1, user2,..., userN are the names of the users.


  • REVOKE


REVOKE command is used to withraw privileges from any user account. 


Syntax:

REVOKE object_privilege ON table_name TO user1, user2, ..., userN;


Now, let's understand the concept of GRANT and REVOKE with the help of below examples. Write queries based on given data using various DCL commands.


Q 1. Grant "update" privilege on table "Food" to a user.

Ans- GRANT UPDATE ON Food TO user_name;


Q 2. Grant permission to a user to create session.

Ans- GRANT CREATE SESSION TO user_name;


Q 3. Grant permission to a user to create tables in a database.

Ans- GRANT CREATE TABLE TO user_name;


Note: Sometimes, a user is not allowed to create tables with names which are reserved for system tables.


Q 4. Provide privileges to a user with space on a tablespace to store the table.

Ans- ALTER USER user_name QUOTA UNLIMITED ON SYSTEM;

The above command is used to alter the user details and further allows it access to unlimited tablespace on system.


Note: Generally, the privilege of unlimited quota on system is provided to Admin users.


Q 5. Grant all privileges to a user.

Ans- GRANT sysdba TO user_name;


Q 6. Grant permission to a user to create any table including those tables with names reserved for system tables.

Ans- GRANT CREATE ANY TABLE TO user_name;


Q 7. Grant permission to "drop" any table from the database.

Ans- GRANT DROP ANY TABLE TO user_name;


Q 8. Take back the privilege to "update" a table "Student" from a user.

Ans- REVOKE UPDATE ON Student FROM user_name;


Q 9. Grant alter permission to a user and it can grant access privileges to other users.

Ans- GRANT ALTER ON table_name TO user_name WITH GRANT OPTION;


Note: A user who has been given the privilege to grant permissions to any other user, can also revoke permission.


TCL (Transaction Control Language)


The TCL commands are used to manage transactions within databases. They maintain the consistency of database. TCL commands can only be used along with the DML commands.

 

The different types of TCL commands are:


  • COMMIT


COMMIT command finishes transaction and all the changes made inside transaction are permanently saved to the database. After manipulating a table using DML commands, COMMIT is used to save changes permanently to the database which cannot be rollback further.


Syntax:

COMMIT;


  • ROLLBACK


ROLLBACK command allows us to undo the transactions which are not yet saved to the database. A transaction rollbacks in case any error occurs and undoes all changes made during the transaction. It restores the database to last commited state.


Syntax:

ROLLBACK;


  • SAVEPOINT


SAVEPOINT command allows us to set a specific point within a transaction upto which we want to rollback without rolling back the entire transaction. The point specified to rollback is called as savepoint. It temporarily saves a transaction so that a user can rollback whenever required.


Syntax:

SAVEPOINT savepoint_name;


  • SET TANSACTION


It specifies characteristics of a transaction as read only or read/write. It affects only the current transaction. SET TRANSACTION must be the first statement in a transaction however, it is not a compulsory in a transaction. Hence, it can be used to initiate a database transaction.


Note: In case where COMMIT and SAVEPOINT commands are used consecutively in a query, then the transaction rollbacks to the SAVEPOINT.