EXPT8 DCL LMD

EXPERIMENT NO: 8
Date of Performance:
Date of Submission:
AIM:
Study DCL and TCL commands
THEORY:
● DCL stands for Data Control Language.
● DCL is used to control user access in a database.
● This command is related to the security issues.
● Using DCL command, it allows or restricts the user from accessing data in database schema.
● It is used to grant or revoke access permissions from any database user.
DCL commands
1. GRANT COMMAND
● GRANT command gives user's access privileges to the database.
● This command allows specified users to perform specific tasks.
Syntax:
GRANT <privilege list>
ON <relation name or view name>
TO <user/role list>;
Example : GRANT Command

GRANT ALL ON employee
TO ABC;
[WITH GRANT OPTION]
In the above example, user 'ABC' has been given permission to view and modify the records in
the 'employee' table.
2. REVOKE COMMAND
● REVOKE command is used to cancel previously granted or denied permissions.
● This command withdraws access privileges given with the GRANT command.
● It takes back permissions from user.

Syntax:
REVOKE <privilege list>
ON <relation name or view name>
FROM <user name>;
Example: REVOKE Command

REVOKE UPDATE
ON employee
FROM ABC;
TCL Commands
● In SQL, TCL stands for Transaction control language.
● A single unit of work in a database is formed after the consecutive execution of commands is
known as a transaction.
● There are certain commands present in SQL known as TCL commands that help the user
manage the transactions that take place in a database.
● COMMIT. ROLLBACK and SAVEPOINT are the most commonly used TCL commands
in SQL.
1. COMMIT
COMMIT command in SQL is used to save all the transaction-related changes permanently to
the disk. Whenever DDL commands such as INSERT, UPDATE and DELETE are used, the
changes made by these commands are permanent only after closing the current session. So
before closing the session, one can easily roll back the changes made by the DDL commands.
Hence, if the changes to be saved permanently to the disk without closing the session, use the
commit command.
Syntax:
COMMIT;
1. Select an existing database, i.e., school.

mysql> USE school;
2. create a table named t_school
mysql> CREATE TABLE t_school(ID INT, School_Name VARCHAR(40),
Number_Of_StudentsINT, Number_Of_Teachers INT, Number_Of_Classrooms INT,
EmailID VARCHAR(40));
3. BEGIN / START TRANSACTION command is used to start the transaction.
mysql> START TRANSACTION;
4. Execute query to insert multiple records at the same time in the t_school table.
mysql> INSERT INTO t_school(ID, School_Name, Number_Of_Students, Number_Of_Te
achers, Number_Of_Classrooms, EmailID) VALUES(1, "Boys Town Public School", 1000,
80, 12, "btps15@gmail.com"), (2, "Guru Govind Singh Public School", 800, 35, 15, "ggps25
@gmail.com"), (3, "Delhi Public School", 1200, 30, 10, "dps101@gmail.com"), (4, "Ashoka
Universal School", 1110, 40, 40, "aus17@gmail.com"), (5, "Calibers English Medium Schoo
l", 9000, 31, 50, "cems@gmail.com");
5. Execute the SELECT query to verify the execution of the INSERT INTO query.
mysql> SELECT * FROM t_school;
6. Execute the COMMIT command to save results of operations carried on the t_school table.
mysql> COMMIT;
7. Autocommit is by default enabled in MySQL. To turn it off, set value of autocommit as 0.
mysql> SET autocommit = 0;
2. SAVEPOINT
We can divide the database operations into parts. For example, we can consider all the insert
related queries that we will execute consecutively as one part of the transaction and the delete
command as the other part of the transaction. Using the SAVEPOINT command in SQL, we can
save these different parts of the same transaction using different names. For example, save all
the insert related queries with the savepoint named INS. To save all the insert related queries in
one savepoint, we have to execute the SAVEPOINT query followed by the savepoint name after
finishing the insert command execution.
Syntax:
SAVEPOINT savepoint_name;
3. ROLLBACK
While carrying a transaction, we must create savepoints to save different parts of the transaction.
According to the user's changing requirements, he/she can roll back the transaction to different
savepoints. Consider a scenario: We have initiated a transaction followed by the table creation
and record insertion into the table. After inserting records, we have created a savepoint INS.
Then we executed a delete query, but later developer thought that mistakenly he had removed the
useful record. Therefore in such situations, an option of rolling back transaction is available. In
this case, developer has to roll back his transaction using the ROLLBACK command to the
savepoint INS, which we have created before executing the DELETE query.
Syntax:
ROLLBACK TO savepoint_name;
1. Select an existing database, i.e., school.

mysql> USE school;
2. create a table named t_school
mysql> CREATE TABLE t_school(ID INT, School_Name VARCHAR(40),
Number_Of_StudentsINT, Number_Of_Teachers INT, Number_Of_Classrooms INT,
EmailID VARCHAR(40));
3. Execute query to insert multiple records at the same time in the t_school table.
mysql> INSERT INTO t_school(ID, School_Name, Number_Of_Students, Number_Of_Te
achers, Number_Of_Classrooms, EmailID) VALUES(1, "Boys Town Public School", 1000,
80, 12, "btps15@gmail.com"), (2, "Guru Govind Singh Public School", 800, 35, 15, "ggps25
@gmail.com"), (3, "Delhi Public School", 1200, 30, 10, "dps101@gmail.com"), (4, "Ashoka
Universal School", 1110, 40, 40, "aus17@gmail.com"), (5, "Calibers English Medium Schoo
l", 9000, 31, 50, "cems@gmail.com");
4. Execute the SELECT query to verify the execution of the INSERT INTO query.
5. BEGIN / START TRANSACTION command is used to start the transaction.
mysql> START TRANSACTION;
6. Save this part of transaction (insertion) using a savepoint named Insertion.
mysql> SAVEPOINT Insertion;
7. Execute the update command on the t_school table to set the Number_Of_Students as 9050
for the record with ID 5.
mysql> UPDATE t_school SET Number_Of_Students = 9050 WHERE ID = 5;
8. Verify that the record with ID 5 now has the Number_Of_Students as 9050 using Select
query.
9. Save this part of transaction (updation) using a savepoint named Insertion.
mysql> SAVEPOINT Updation;
10. Due to new client requirements, developer needs to roll back transaction to the savepoint,
which was created prior to the execution of the UPDATE command.
mysql> ROLLBACK TO Insertion;
11. Verify using Select query.
CONCLUSION:
DBA can control the transaction in DBMS using DCL and TCL statement. DCL has two parts
first is the grant and the second is revoke. Likewise, TCL performs in parts such as commit,
rollback, set a save point and transactions.
MARKS & SIGNATURE:
R1 R2 R3 R4 Total Signature
(3 Marks) (5 Marks) (4 Marks) (3 Marks) (15 Marks)

EXPT8 DCL LMD

Uploaded by

Copyright:

Available Formats

EXPT8 DCL LMD

Uploaded by

Document Information

Original Title

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

EXPT8 DCL LMD

Uploaded by

Copyright:

Available Formats

EXPERIMENT NO: 8

Study DCL and TCL commands

● DCL stands for Data Control Language.

● DCL is used to control user access in a database.

● This command is related to the security issues.

● It is used to grant or revoke access permissions from any database user.

● This command allows specified users to perform specific tasks.

Example : GRANT Command

● REVOKE command is used to cancel previously granted or denied permissions.

● It takes back permissions from user.

Example: REVOKE Command

1. Select an existing database, i.e., school.

1. Select an existing database, i.e., school.

MARKS & SIGNATURE:

You might also like