Part 4 of 5 · 2 min read
DCL and TCL: access and transaction boundaries
Granting permissions, and marking where a transaction starts and ends.
DCL and TCL: access and transaction boundaries
Think of two more roles beyond the drawers and the paper. A building manager decides who is allowed into which room. A supervisor marks the exact moment a shift's paperwork is considered finalized.
In plain words: Data Control Language statements manage who is allowed to do what. GRANT and REVOKE are DCL, covered properly in Level 2's module on SQL authorization. Transaction Control Language statements mark the boundaries of a transaction. BEGIN, COMMIT and ROLLBACK, introduced on the previous page, are TCL.
A worked example. Allowing a reporting account to read orders without letting it change anything is DCL. Wrapping the bank-transfer example from the previous page in BEGIN and COMMIT is TCL.
The key lines.
GRANT SELECT ON orders TO reporting_role;
BEGIN;
UPDATE accounts SET balance = balance - 2000 WHERE account_id = 'bookstore';
UPDATE accounts SET balance = balance + 2000 WHERE account_id = 'supplier';
COMMIT;
DCL, TCL
Data Control Language (DCL) covers statements managing permissions, such as GRANT and REVOKE. Transaction Control Language (TCL) covers statements marking transaction boundaries, such as BEGIN, COMMIT and ROLLBACK.
Example: GRANT SELECT ON orders TO reporting_role is DCL; COMMIT ending a transfer is TCL.
Remember this
Four categories, sorted by what they govern: DDL changes structure, DML changes or reads data, DCL changes permissions, TCL marks transaction boundaries. Nearly every SQL statement seen in this subject sorts cleanly into exactly one of the four.
Quick notes
- DDL (
CREATE,ALTER,DROP) changes database structure. - DML (
SELECT,INSERT,UPDATE,DELETE) reads or changes row data within existing structure. - DCL (
GRANT,REVOKE) manages who is allowed to do what. - TCL (
BEGIN,COMMIT,ROLLBACK) marks transaction boundaries. - Every SQL statement introduced in this level sorts into exactly one of these four categories.