DCL & TCL - Security & Integrity

Permissions, Transactions & Data Safety

In a global retail powerhouse, data isn't just rows in a table; it's money, trust, and privacy. How do we ensure a cashier in London can't see the CEO's salary? How do we guarantee that if a customer's payment fails, the stock isn't accidentally reduced?

This chapter covers Data Control Language (DCL) for security and Transaction Control Language (TCL) for data integrity. We will learn how to manage access across teams and how to use transactions to ensure our database remains a "Single Source of Truth."

DCL: Managing the Keys to the Kingdom

Data security is non-negotiable. In a database with millions of sensitive records, we use DCL to enforce the "Principle of Least Privilege."

  • GRANT: Gives a user specific permissions (e.g., READ, WRITE) on specific tables.
  • REVOKE: Removes previously granted permissions.

By controlling access at the database level, we protect the business from both internal errors and external threats.

SQL
GRANT UPDATE (price) ON products TO marketing_dept;

Transactions: All or Nothing

A "Transaction" is a single unit of work that consists of multiple steps. Imagine a transfer of stock between two warehouses. You must SUBTRACT from one and ADD to the other. If the system crashes halfway, you've "lost" stock from the digital universe.

TCL ensures this never happens. A transaction is Atomic: it either finishes completely (COMMIT) or is completely undone (ROLLBACK).

SQL
BEGIN;UPDATE warehouse_a SET qty = qty - 10;UPDATE warehouse_b SET qty = qty + 10;COMMIT;

Savepoints: The Safety Checkpoints

Sometimes, a transaction is very long and complex. Instead of rolling back the entire thing, we can use SAVEPOINT. This acts like a "checkpoint" in a video game, allowing us to roll back only to that specific point if a non-critical step fails.

SQL
BEGIN;INSERT INTO logs VALUES ('Step 1 Done');SAVEPOINT sp1;INSERT INTO risky_table VALUES ('Step 2');-- If Step 2 fails:ROLLBACK TO sp1;COMMIT;
SQLRuns entirely in your browser — nothing is sent to a server.

Practice Questions

Question 1

Which command finalize a transaction and makes changes permanent?

  • SAVE
  • COMMIT
  • FINISH
  • LOCK

Question 2

If a query inside a transaction causes an error, which command restores the database to its state before the transaction started?

  • UNDO
  • REVERT
  • ROLLBACK
  • REVOKE

Question 3

Which DCL command is used to give a new joiner 'READ' access to the sales reports?

  • ALLOW
  • GIVE
  • GRANT
  • PERMIT

Question 4

What is the purpose of a SAVEPOINT?

  • To speed up the query
  • To allow a partial rollback within a transaction
  • To backup the entire database
  • To lock the table for other users

Question 5

Which category does 'REVOKE' belong to?

  • DML
  • DDL
  • DCL
  • TCL