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
| Command | What it does | Example |
|---|---|---|
| CREATE USER | Creates a login | CREATE USER 'app'@'%' IDENTIFIED BY 'Str0ng#Pass'; |
| ALTER USER | Changes password / options | ALTER USER 'app'@'%' IDENTIFIED BY 'N3w#Pass'; |
| DROP USER | Removes a login | DROP USER 'app'@'%'; |
| GRANT privileges ON obj TO user | Gives permissions | GRANT SELECT, INSERT ON campus.students TO 'app'@'%'; |
| GRANT ALL PRIVILEGES | Gives every permission on a scope | GRANT ALL PRIVILEGES ON campus.* TO 'admin'@'localhost'; |
| WITH GRANT OPTION | Lets the user grant to others | GRANT SELECT ON campus.* TO 'lead'@'%' WITH GRANT OPTION; |
| REVOKE privileges ON obj FROM user | Removes permissions | REVOKE INSERT ON campus.students FROM 'app'@'%'; |
| CREATE ROLE / GRANT role | Groups permissions (MySQL 8) | CREATE ROLE reader; GRANT SELECT ON campus.* TO reader; GRANT reader TO 'app'@'%'; |
| SET DEFAULT ROLE | Activates roles at login | SET DEFAULT ROLE reader TO 'app'@'%'; |
| SHOW GRANTS | Lists a user's permissions | SHOW GRANTS FOR 'app'@'%'; |
| START TRANSACTION / BEGIN | Starts a transaction | START TRANSACTION; |
| COMMIT | Saves all changes permanently | COMMIT; |
| ROLLBACK | Undoes changes since start | ROLLBACK; |
| SAVEPOINT name | Marks a point inside a transaction | SAVEPOINT before_fees; |
| ROLLBACK TO SAVEPOINT | Undoes back to a savepoint | ROLLBACK TO SAVEPOINT before_fees; |
| RELEASE SAVEPOINT | Removes a savepoint | RELEASE SAVEPOINT before_fees; |
| SET autocommit = 0|1 | Turns auto-commit off/on | SET autocommit = 0; |
| SET TRANSACTION ISOLATION LEVEL | READ UNCOMMITTED / READ COMMITTED / REPEATABLE READ / SERIALIZABLE | SET TRANSACTION ISOLATION LEVEL READ COMMITTED; |
| LOCK TABLES / UNLOCK TABLES | Explicit table locks | LOCK 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.
Lesson test
Take an 8-question test on “Commands Reference: DCL & TCL (GRANT, REVOKE, COMMIT, ROLLBACK)” — concepts, output prediction, bug spotting and interview questions.