DCL and TCL Commands

DCL commands control access and permissions to database objects. They manage security by defining who can do what.

DCL — Data Control Language

DCL commands control access and permissions to database objects. They manage security by defining who can do what.

GRANT

Gives specific privileges to a user or role.

-- Syntax GRANT privilege_list ON object TO user [WITH GRANT OPTION]; -- Grant SELECT on one table GRANT SELECT ON Employee TO 'hr_user'@'localhost'; -- Grant multiple privileges GRANT SELECT, INSERT, UPDATE ON Employee TO 'app_user'@'localhost'; -- Grant all privileges on a database GRANT ALL PRIVILEGES ON university.* TO 'admin'@'localhost'; -- Grant with ability to pass privileges to others GRANT SELECT ON Employee TO 'senior_user'@'localhost' WITH GRANT OPTION; -- Grant on a specific column GRANT SELECT (emp_id, name, dept_id) ON Employee TO 'intern'@'localhost'; -- Create user and grant in one flow CREATE USER 'analyst'@'localhost' IDENTIFIED BY 'SecurePass123'; GRANT SELECT ON university.* TO 'analyst'@'localhost';

REVOKE

Removes previously granted privileges from a user.

sql exampleWoHoTech

-- Syntax
REVOKE privilege_list ON object FROM user;

-- Revoke a specific privilege
REVOKE INSERT ON Employee FROM 'app_user'@'localhost';

-- Revoke all privileges
REVOKE ALL PRIVILEGES ON university.* FROM 'admin'@'localhost';

-- Revoke grant option only
REVOKE GRANT OPTION FOR SELECT ON Employee FROM 'senior_user'@'localhost';

Privilege Types

Privilege	Description
SELECT	Read data from a table/view
INSERT	Add new rows
UPDATE	Modify existing rows
DELETE	Remove rows
CREATE	Create tables, indexes, views
DROP	Drop tables, databases
ALTER	Modify table structure
INDEX	Create/drop indexes
EXECUTE	Run stored procedures/functions
ALL PRIVILEGES	All of the above

View Current Privileges

sql exampleWoHoTech

-- Show privileges for a user
SHOW GRANTS FOR 'hr_user'@'localhost';

-- Show current user's grants
SHOW GRANTS;

TCL — Transaction Control Language

TCL commands manage transactions — groups of SQL statements that should execute as a single unit.

COMMIT

Permanently saves all changes made during the current transaction.

sql exampleWoHoTech

START TRANSACTION;

UPDATE Accounts SET balance = balance - 5000 WHERE account_id = 'A001';
UPDATE Accounts SET balance = balance + 5000 WHERE account_id = 'A002';

COMMIT;  -- Changes are now permanent on disk

ROLLBACK

Undoes all changes made since the transaction began (or since the last SAVEPOINT).

sql exampleWoHoTech

START TRANSACTION;

DELETE FROM Employee WHERE dept_id = 2;

-- Realized it was a mistake!
ROLLBACK;  -- All deletions undone

-- Nothing was changed

SAVEPOINT

Creates a named point within a transaction to which you can partially roll back.

sql exampleWoHoTech

START TRANSACTION;

INSERT INTO Orders VALUES (1001, 'Alice', 500.00);
SAVEPOINT after_alice;

INSERT INTO Orders VALUES (1002, 'Bob', 300.00);
SAVEPOINT after_bob;

INSERT INTO Orders VALUES (1003, 'Carol', 750.00);

-- Undo Carol's order but keep Alice's and Bob's
ROLLBACK TO SAVEPOINT after_bob;

COMMIT;  -- Only Alice's and Bob's orders are saved

SET TRANSACTION

Configures the isolation level and mode of the current transaction.

sql exampleWoHoTech

-- Set isolation level
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

-- Set read-only transaction
SET TRANSACTION READ ONLY;

Transaction Isolation Levels

Isolation Level	Dirty Read	Non-Repeatable Read	Phantom Read
READ UNCOMMITTED	Possible	Possible	Possible
READ COMMITTED	Prevented	Possible	Possible
REPEATABLE READ	Prevented	Prevented	Possible
SERIALIZABLE	Prevented	Prevented	Prevented

AUTOCOMMIT

By default, most SQL databases run in autocommit mode — each statement is its own transaction and is immediately committed.

sql exampleWoHoTech

-- Disable autocommit (MySQL)
SET AUTOCOMMIT = 0;

-- Now all statements are in a manual transaction
UPDATE Employee SET salary = 80000 WHERE emp_id = 101;
-- Not committed yet!

COMMIT;  -- Now it's committed

-- Re-enable autocommit
SET AUTOCOMMIT = 1;

TCL Flow Diagram

START TRANSACTION | ▼ Execute DML (INSERT/UPDATE/DELETE) | ┌────┴────┐ │ │ COMMIT ROLLBACK │ │ Changes Changes Permanent Undone

DCL vs. TCL Summary

GRANT	Give permissions to users
REVOKE	Take permissions away
COMMIT	Save transaction permanently
ROLLBACK	Undo entire transaction
SAVEPOINT	Mark a point inside transaction

DCL — Data Control Language

DCL commands control access and permissions to database objects. They manage security by defining who can do what.

GRANT

Gives specific privileges to a user or role.

REVOKE

Removes previously granted privileges from a user.

sql exampleWoHoTech

-- Syntax
REVOKE privilege_list ON object FROM user;

-- Revoke a specific privilege
REVOKE INSERT ON Employee FROM 'app_user'@'localhost';

-- Revoke all privileges
REVOKE ALL PRIVILEGES ON university.* FROM 'admin'@'localhost';

-- Revoke grant option only
REVOKE GRANT OPTION FOR SELECT ON Employee FROM 'senior_user'@'localhost';

Privilege Types

Privilege	Description
SELECT	Read data from a table/view
INSERT	Add new rows
UPDATE	Modify existing rows
DELETE	Remove rows
CREATE	Create tables, indexes, views
DROP	Drop tables, databases
ALTER	Modify table structure
INDEX	Create/drop indexes
EXECUTE	Run stored procedures/functions
ALL PRIVILEGES	All of the above

View Current Privileges

sql exampleWoHoTech

-- Show privileges for a user
SHOW GRANTS FOR 'hr_user'@'localhost';

-- Show current user's grants
SHOW GRANTS;

TCL — Transaction Control Language

TCL commands manage transactions — groups of SQL statements that should execute as a single unit.

COMMIT

Permanently saves all changes made during the current transaction.

sql exampleWoHoTech

START TRANSACTION;

UPDATE Accounts SET balance = balance - 5000 WHERE account_id = 'A001';
UPDATE Accounts SET balance = balance + 5000 WHERE account_id = 'A002';

COMMIT;  -- Changes are now permanent on disk

ROLLBACK

Undoes all changes made since the transaction began (or since the last SAVEPOINT).

sql exampleWoHoTech

START TRANSACTION;

DELETE FROM Employee WHERE dept_id = 2;

-- Realized it was a mistake!
ROLLBACK;  -- All deletions undone

-- Nothing was changed

SAVEPOINT

Creates a named point within a transaction to which you can partially roll back.

sql exampleWoHoTech

START TRANSACTION;

INSERT INTO Orders VALUES (1001, 'Alice', 500.00);
SAVEPOINT after_alice;

INSERT INTO Orders VALUES (1002, 'Bob', 300.00);
SAVEPOINT after_bob;

INSERT INTO Orders VALUES (1003, 'Carol', 750.00);

-- Undo Carol's order but keep Alice's and Bob's
ROLLBACK TO SAVEPOINT after_bob;

COMMIT;  -- Only Alice's and Bob's orders are saved

SET TRANSACTION

Configures the isolation level and mode of the current transaction.

sql exampleWoHoTech

-- Set isolation level
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

-- Set read-only transaction
SET TRANSACTION READ ONLY;

Transaction Isolation Levels

Isolation Level	Dirty Read	Non-Repeatable Read	Phantom Read
READ UNCOMMITTED	Possible	Possible	Possible
READ COMMITTED	Prevented	Possible	Possible
REPEATABLE READ	Prevented	Prevented	Possible
SERIALIZABLE	Prevented	Prevented	Prevented

AUTOCOMMIT

By default, most SQL databases run in autocommit mode — each statement is its own transaction and is immediately committed.

sql exampleWoHoTech

-- Disable autocommit (MySQL)
SET AUTOCOMMIT = 0;

-- Now all statements are in a manual transaction
UPDATE Employee SET salary = 80000 WHERE emp_id = 101;
-- Not committed yet!

COMMIT;  -- Now it's committed

-- Re-enable autocommit
SET AUTOCOMMIT = 1;

TCL Flow Diagram

START TRANSACTION | ▼ Execute DML (INSERT/UPDATE/DELETE) | ┌────┴────┐ │ │ COMMIT ROLLBACK │ │ Changes Changes Permanent Undone

DCL vs. TCL Summary

GRANT	Give permissions to users
REVOKE	Take permissions away
COMMIT	Save transaction permanently
ROLLBACK	Undo entire transaction
SAVEPOINT	Mark a point inside transaction

DCL and TCL Commands

DCL — Data Control Language

GRANT

REVOKE

Privilege Types

View Current Privileges

TCL — Transaction Control Language

COMMIT

ROLLBACK

SAVEPOINT

SET TRANSACTION

Transaction Isolation Levels

AUTOCOMMIT

TCL Flow Diagram

DCL vs. TCL Summary

Continue learning this concept

DCL and TCL Commands

DCL — Data Control Language

GRANT

REVOKE

Privilege Types

View Current Privileges

TCL — Transaction Control Language

COMMIT

ROLLBACK

SAVEPOINT

SET TRANSACTION

Transaction Isolation Levels

AUTOCOMMIT

TCL Flow Diagram

DCL vs. TCL Summary

Continue learning this concept