← SQL roadmap

Topic 33 of 52 · Intermediate

Commands Reference: DCL & TCL (GRANT, REVOKE, COMMIT, ROLLBACK)

Users, roles and permissions, plus transaction control with savepoints and isolation levels.

Concept

DCL (Data Control Language) manages who can do what: CREATE USER, GRANT, REVOKE and roles.

TCL (Transaction Control Language) groups statements into an all-or-nothing unit: START TRANSACTION, COMMIT, ROLLBACK, SAVEPOINT and SET TRANSACTION ISOLATION LEVEL.

Follow the principle of least privilege: give an application user only SELECT/INSERT/UPDATE on the tables it needs, never ALL on *.*.

Cheat sheet

CommandWhat it doesExample
CREATE USERCreates a loginCREATE USER 'app'@'%' IDENTIFIED BY 'Str0ng#Pass';
ALTER USERChanges password / optionsALTER USER 'app'@'%' IDENTIFIED BY 'N3w#Pass';
DROP USERRemoves a loginDROP USER 'app'@'%';
GRANT privileges ON obj TO userGives permissionsGRANT SELECT, INSERT ON campus.students TO 'app'@'%';
GRANT ALL PRIVILEGESGives every permission on a scopeGRANT ALL PRIVILEGES ON campus.* TO 'admin'@'localhost';
WITH GRANT OPTIONLets the user grant to othersGRANT SELECT ON campus.* TO 'lead'@'%' WITH GRANT OPTION;
REVOKE privileges ON obj FROM userRemoves permissionsREVOKE INSERT ON campus.students FROM 'app'@'%';
CREATE ROLE / GRANT roleGroups permissions (MySQL 8)CREATE ROLE reader; GRANT SELECT ON campus.* TO reader; GRANT reader TO 'app'@'%';
SET DEFAULT ROLEActivates roles at loginSET DEFAULT ROLE reader TO 'app'@'%';
SHOW GRANTSLists a user's permissionsSHOW GRANTS FOR 'app'@'%';
START TRANSACTION / BEGINStarts a transactionSTART TRANSACTION;
COMMITSaves all changes permanentlyCOMMIT;
ROLLBACKUndoes changes since startROLLBACK;
SAVEPOINT nameMarks a point inside a transactionSAVEPOINT before_fees;
ROLLBACK TO SAVEPOINTUndoes back to a savepointROLLBACK TO SAVEPOINT before_fees;
RELEASE SAVEPOINTRemoves a savepointRELEASE SAVEPOINT before_fees;
SET autocommit = 0|1Turns auto-commit off/onSET autocommit = 0;
SET TRANSACTION ISOLATION LEVELREAD UNCOMMITTED / READ COMMITTED / REPEATABLE READ / SERIALIZABLESET TRANSACTION ISOLATION LEVEL READ COMMITTED;
LOCK TABLES / UNLOCK TABLESExplicit table locksLOCK TABLES stock WRITE; ... UNLOCK TABLES;

Code (SQL · MySQL 8)

-- DCL: a read-only reporting user via a role
CREATE ROLE reporting;
GRANT SELECT ON campus.* TO reporting;
CREATE USER 'analyst'@'%' IDENTIFIED BY 'An@lyst2026';
GRANT reporting TO 'analyst'@'%';
SET DEFAULT ROLE reporting TO 'analyst'@'%';
SHOW GRANTS FOR 'analyst'@'%';

-- TCL: transfer money safely
START TRANSACTION;
UPDATE accounts SET balance = balance - 500 WHERE id = 1;
SAVEPOINT debited;
UPDATE accounts SET balance = balance + 500 WHERE id = 2;
-- something went wrong with the credit? ROLLBACK TO SAVEPOINT debited;
COMMIT;

Remember

  • ACID: Atomicity, Consistency, Isolation, Durability.
  • MySQL InnoDB default isolation level is REPEATABLE READ.
  • DDL inside a transaction causes an implicit COMMIT.
  • Prefer roles over granting the same privileges to many users.

Interview questions

COMMIT vs ROLLBACK vs SAVEPOINT?

COMMIT makes changes permanent, ROLLBACK undoes all uncommitted changes, SAVEPOINT marks a point you can partially roll back to.

What problems do isolation levels prevent?

Dirty reads (READ COMMITTED+), non-repeatable reads (REPEATABLE READ+) and phantom reads (SERIALIZABLE; InnoDB also limits phantoms with gap locks).

What does WITH GRANT OPTION do?

It allows the grantee to pass the same privileges on to other users — use sparingly.

Practise SQL with the AI mentor

Lesson test

Take an 8-question test on “Commands Reference: DCL & TCL (GRANT, REVOKE, COMMIT, ROLLBACK)” — concepts, output prediction, bug spotting and interview questions.

Can't find a SQL topic you need for exams or placements? Request it and we'll add it.