Structured Query Language: Data control language (DCL)

The SQL specifies that a transaction begins automatically when an SQL statement is executed. The following four statements show the different ways to end the transaction and the next two statements  show  different  privilages on  database  to  users.

1.  Commit Statement

A transaction is completed successfully after commit. Commit statement is used to make data changes permanent to database.

Syntax : COMMIT;

2. Rollback

Rollback statement is used to terminate current transaction and discarding all data changes pending due to that transaction.

Syntax : ROLLBACK;

3. Savepoint

It is used to partially commit the current transaction and put a savepoint at that position.

Syntax : SAVEPOINT <name>;

If second savepoint will be created within same transaction then earlier savepoint is automatically discarded  by  database.

4. Rollback to Savepoint

It is used to partially rollback any transaction and pending data changes upto specified savepoint are  discarded.

Syntax : ROLLBACK To <SAVEPOINT name>;

5. Grant Statement

Grant statement is used to give different permissions on different portions of database to different users. In a multi-user database management system, it is required to grant different permissions for security purposes.

Syntax :  GRANT <privilege-list>| ALL

ON    <object>

TO     <user-list>| PUBLIC



Privilege list–specifies the permissions to be granted to users like ALTER, DELETE, CREATE, UPDATE  etc.

ALL–specifies all  the permissions.

Object–specifies name of  tables or  their columns.

User-list–specifies name of users to which permissions are granted.

PUBLIC–specifies all  users.

WITH GRANT OPTION–specifies that the user in user-list can give permissions to any other user  that  were  granted  to him  [If  specified].

Ex. :           Grant all the permissions on table Emp to all users


ON            Emp

TO             PUBLIC;

Ex. :           Grant UPDATE and DELETE permissions on table Dept to users Nick and Rohn


ON            Dept

TO             Nick, Rohn;

Ex. :           Grant ALTER authority on table Emp to user Nick with the capability to grant those authorities to other users


ON            Emp

TO             Nick


Ex. :           Grant INSERT authority on  EID column of  table Emp to  user Nick.


ON           Emp EID

TO             Nick;

Note: In table emp, eID is primary key so you cannot insert NULL value. Before granting insert authority on a particular column you must sure that column has a default value otherwise you cannot insert any new value.

6. REVOKE Statement

REVOKE statement is used to take away any authority from a user that was granted earlier.

Syntax :    REVOKE          <privilege-list>| ALL

ON                   <table name> [(column-comma-list)]

FROM             <user-list>| PUBLIC

Ex. :        REVOKE the UPDATE permission on table Dept from Rohan.
REVOKE             UPDATE
ON                       Dept
FROM                  Rohn;

Ex. :       REVOKE INSERT and UPDATE permission on Name and EID columns of
table Emp from all users.
REVOKE             INSERT,          UPDATE          (Name, EID)
ON                       Emp
FROM                  PUBLIC;

Source: Gupta Satinder Bal, Mittal Aditya (2017), Introduction to Basic Database Management System, 2nd Edition-University Science Press (2017)

Leave a Reply

Your email address will not be published. Required fields are marked *