[go: up one dir, main page]
Scribd
Upload
22 views9 pages

Lecture 11

This document provides an overview of Transaction Control Language (TCL) and Data Control Language (DCL) in database systems. TCL includes commands like COMMIT, SAVEPOINT, and ROLLBACK to manage transactions, while DCL involves granting and revoking privileges on database objects. The document also includes syntax examples for these commands and references a textbook for further reading.

Uploaded by

anas
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
22 views9 pages

Lecture 11

This document provides an overview of Transaction Control Language (TCL) and Data Control Language (DCL) in database systems. TCL includes commands like COMMIT, SAVEPOINT, and ROLLBACK to manage transactions, while DCL involves granting and revoking privileges on database objects. The document also includes syntax examples for these commands and references a textbook for further reading.

Uploaded by

anas
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
You are on page 1/ 9

By: Engr.

Sapna Kumari
DATABASE SYSTEMS- LECTURE 11 Lecturer, CS-SZABIST
Sapna.kumari@hyd.szabist.edu.pk
OUTLINE
• TCL – Transaction Control Language
DCL – Data Control Language
TCL – TRANSACTION CONTROL LANGUAGE
A transaction consists of a sequence of query and update statements. Transaction
Control Language gives you flexibility to undo transactions or write transactions to the
disk. Transactions provide consistency in case of a system failure.
1. Commit
2. Rollback
3. Savepoint
TCL – TRANSACTION CONTROL LANGUAGE
Commit:
Statement is used to end the current transaction and makes permanent any changes
made during transaction. The general syntax is:
COMMIT;
TCL – TRANSACTION CONTROL LANGUAGE
SAVEPOINT:
SAVEPOINT allows the user to create logical marking in the whole transaction, so that
the system will discard all the changes up to a point. The syntax for creating a
SAVEPOINT is:
SAVEPOINT savepointname;
The syntax for roll backing to a particular savepoint is:
ROLLBACK TO SAVEPOINT savepointname;
DCL-DATA CONTROL LANGUAGE
An object privilege specifies what a user can do with a database object, such as a
table or a view. The different privileges for table are: ALTER, INSERT, UPDATE,
DELETE, and SELECT.
• Grant
• Revoke
DCL-DATA CONTROL LANGUAGE
Granting Privileges:
• A user can grant privileges on objects from own schema to other users or roles by
using GRANT command.
• The syntax of providing a privilege is: GRANT privileges/ ALL ON objectname TO
username/ PUBLIC [WITH GRANT OPTION];
• WITH GRANT OPTION clause allows the grantee to grant privileges to other users
and roles.
Example:
GRANT SELECT, INSERT ON Employee TO Aliya;
GRANT SELECT, INSERT ON Employee TO Aliya WITH GRANT OPTION;
DCL-DATA CONTROL LANGUAGE
Revoking Privileges:
If a user granted privileges by a WITH GRANT OPTION to another user and that
second user passed on those privileges, the REVOKE statement takes privileges not
only from the grantee but also from the users granted privileges by the grantee.
The general syntax is: REVOKE privileges/ALL ON objectname FROM username/
PUBLIC;
Example:
REVOKE INSERT ON Employee FROM Aliya;
REVOKE ALL ON Employee FROM Aliya;
BOOK
• Fundamentals of Database Systems , 7th Edition
By Ramez Elmarsi & Shamkant Navathi

You might also like