SQL Transactions & Security

SQL Transactions & Security Tutorial: TCL & DCL Commands

📖 Introduction: Keeping Data Safe and Correct

Imagine you're transferring $1,000 from your savings to your checking account. Two things must happen:

1. Deduct $1,000 from savings
2. Add $1,000 to checking

What if step 1 succeeds but step 2 fails? You lose $1,000 into thin air. Transactions prevent this by treating both steps as a single, inseparable unit — either both succeed or both fail.

TCL (Transaction Control Language) handles this atomicity. DCL (Data Control Language) handles who can do what to your data.

🛠️ Setting Up Practice Data

sqlCopy
1CREATE DATABASE bank_db;
2USE bank_db;
3
4CREATE TABLE accounts (
5    account_id INT PRIMARY KEY AUTO_INCREMENT,
6    account_holder VARCHAR(100),
7    account_type VARCHAR(20),
8    balance DECIMAL(12,2) CHECK (balance >= 0)
9);
10
11INSERT INTO accounts (account_holder, account_type, balance) VALUES
12('Alice Johnson', 'Savings', 10000.00),
13('Bob Smith', 'Checking', 5000.00),
14('Carol White', 'Savings', 15000.00);

1️⃣ ACID Properties — The Foundation of Transactions

Every transaction must follow ACID — the gold standard for database reliability.

Key Insight: ACID ensures your database remains accurate even during crashes, power failures, or concurrent user access.

2️⃣ Transaction Control — BEGIN, COMMIT, ROLLBACK

Purpose: Group multiple SQL statements into a single transaction.

Syntax:

sqlCopy
1START TRANSACTION;  -- or BEGIN;
2
3-- SQL statements here
4
5COMMIT;     -- Save all changes permanently
6-- OR
7ROLLBACK;   -- Undo all changes since START TRANSACTION

Example 1: Successful Bank Transfer (COMMIT)

Scenario: Transfer $1,000 from Alice to Bob.

sqlCopy
1START TRANSACTION;
2
3-- Step 1: Deduct from Alice
4UPDATE accounts 
5SET balance = balance - 1000 
6WHERE account_id = 1;
7
8-- Step 2: Add to Bob
9UPDATE accounts 
10SET balance = balance + 1000 
11WHERE account_id = 2;
12
13-- Verify both steps worked
14SELECT * FROM accounts WHERE account_id IN (1, 2);

Output (before COMMIT):

+------------+----------------+--------------+----------+
| account_id | account_holder | account_type | balance  |
+------------+----------------+--------------+----------+
| 1          | Alice Johnson  | Savings      | 9000.00  |
| 2          | Bob Smith      | Checking     | 6000.00  |
+------------+----------------+--------------+----------+

Important: At this point, the changes are visible only to your session. Other users still see the old balances!

sqlCopy
1-- Everything looks good? Make it permanent.
2COMMIT;

Result: Changes are now permanent and visible to everyone.

Example 2: Failed Transfer — ROLLBACK

Scenario: Transfer $20,000 from Bob to Carol. But Bob only has$6,000!

sqlCopy
1START TRANSACTION;
2
3-- Step 1: Deduct from Bob (this would make balance negative!)
4UPDATE accounts 
5SET balance = balance - 20000 
6WHERE account_id = 2;
7-- ERROR: CHECK constraint violation! Balance cannot be negative.
8
9-- Step 2: Would add to Carol, but we never reach here
10-- UPDATE accounts SET balance = balance + 20000 WHERE account_id = 3;
11
12-- Something went wrong. Undo everything.
13ROLLBACK;

Result: Both accounts remain unchanged. Bob still has $6,000. No partial, inconsistent state exists.

Golden Rule: If any step in a transaction fails, always ROLLBACK.

Example 3: Checking Balance Before Transfer

sqlCopy
1START TRANSACTION;
2
3-- Check if Alice has enough money
4SELECT balance FROM accounts WHERE account_id = 1;
5-- Result: 9000.00 (after previous transfer)
6
7-- Only proceed if balance >= 1000
8UPDATE accounts 
9SET balance = balance - 1000 
10WHERE account_id = 1 AND balance >= 1000;
11
12UPDATE accounts 
13SET balance = balance + 1000 
14WHERE account_id = 2;
15
16COMMIT;

3️⃣ SAVEPOINT — Partial Rollback

Purpose: Create a checkpoint within a transaction. You can roll back to this point without undoing the entire transaction.

Syntax:

sqlCopy
1SAVEPOINT savepoint_name;
2ROLLBACK TO SAVEPOINT savepoint_name;

Example 4: Using SAVEPOINT

Scenario: A complex batch job with multiple stages.

sqlCopy
1START TRANSACTION;
2
3-- Stage 1: Deduct fee from Alice
4UPDATE accounts SET balance = balance - 50 WHERE account_id = 1;
5SAVEPOINT after_fee_deduction;
6
7-- Stage 2: Transfer principal amount
8UPDATE accounts SET balance = balance - 1000 WHERE account_id = 1;
9UPDATE accounts SET balance = balance + 1000 WHERE account_id = 2;
10SAVEPOINT after_principal_transfer;
11
12-- Stage 3: Add bonus interest (this fails!)
13UPDATE accounts SET balance = balance + 999999 WHERE account_id = 2;
14-- ERROR: Unreasonable amount detected!
15
16-- Rollback only the failed stage
17ROLLBACK TO SAVEPOINT after_principal_transfer;
18
19-- Commit the valid stages (fee + principal)
20COMMIT;

Result: Alice paid the fee and transferred $1,000. The bogus interest was rejected, but the valid work was saved.

Without SAVEPOINT: You'd have to ROLLBACK everything and redo the fee and principal transfer.

4️⃣ User Management — Creating Accounts

Purpose: Control who can access the database and from where.

Example 5: Create New Users

sqlCopy
1-- Create an application user (can connect only from localhost)
2CREATE USER 'app_user'@'localhost' IDENTIFIED BY 'SecurePass123!';
3
4-- Create an admin user (can connect from any IP)
5CREATE USER 'admin'@'%' IDENTIFIED BY 'AdminPass456!';

What the @ part means:

Example 6: Change Password

sqlCopy
1ALTER USER 'app_user'@'localhost' IDENTIFIED BY 'NewSecurePass!';

5️⃣ GRANT — Giving Permissions

Purpose: Assign specific privileges to users.

Syntax:

sqlCopy
1GRANT privilege_list ON database.table TO 'user'@'host';

Example 7: Grant Read-Only Access

sqlCopy
1-- App user can only read from the bank_db database
2GRANT SELECT ON bank_db.* TO 'app_user'@'localhost';

bank_db.* means all tables in the bank_db database.

Example 8: Grant Read and Write Access

sqlCopy
1-- App user can read and modify data, but not change structure
2GRANT SELECT, INSERT, UPDATE, DELETE ON bank_db.* TO 'app_user'@'localhost';

Example 9: Grant Admin Access

sqlCopy
1-- Admin can do everything everywhere
2GRANT ALL PRIVILEGES ON *.* TO 'admin'@'%';

*.* means all databases and all tables.

Example 10: Grant Specific Table Access

sqlCopy
1-- Analyst can only read the accounts table
2GRANT SELECT ON bank_db.accounts TO 'analyst'@'localhost';

6️⃣ REVOKE — Removing Permissions

Purpose: Take away previously granted privileges.

Example 11: Remove Specific Privilege

sqlCopy
1-- Stop app_user from deleting data
2REVOKE DELETE ON bank_db.* FROM 'app_user'@'localhost';

Example 12: Remove All Privileges

sqlCopy
1-- Strip all access from app_user
2REVOKE ALL PRIVILEGES ON bank_db.* FROM 'app_user'@'localhost';

7️⃣ SHOW GRANTS — Check Permissions

Example 13: View User Privileges

sqlCopy
1-- See what app_user can do
2SHOW GRANTS FOR 'app_user'@'localhost';

Output:

+----------------------------------------------------------+
| Grants for app_user@localhost                            |
+----------------------------------------------------------+
| GRANT USAGE ON *.* TO 'app_user'@'localhost'             |
| GRANT SELECT, INSERT, UPDATE ON `bank_db`.* TO ...      |
+----------------------------------------------------------+

8️⃣ DROP USER — Remove Users

Example 14: Delete a User

sqlCopy
1-- Remove the user completely
2DROP USER 'app_user'@'localhost';

📝 Complete TCL & DCL Reference

🚀 Hands-On Project: Secure Banking System

Project Goal

Implement a secure banking transaction system with proper user roles and transaction safety.

Step 1: Create the Database Schema

sqlCopy
1CREATE DATABASE secure_bank;
2USE secure_bank;
3
4CREATE TABLE accounts (
5    account_id INT PRIMARY KEY AUTO_INCREMENT,
6    account_holder VARCHAR(100) NOT NULL,
7    account_type ENUM('Savings', 'Checking') DEFAULT 'Savings',
8    balance DECIMAL(12,2) NOT NULL DEFAULT 0.00 CHECK (balance >= 0),
9    status ENUM('Active', 'Frozen', 'Closed') DEFAULT 'Active',
10    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
11);
12
13CREATE TABLE transactions (
14    transaction_id INT PRIMARY KEY AUTO_INCREMENT,
15    from_account INT,
16    to_account INT,
17    amount DECIMAL(12,2) NOT NULL CHECK (amount > 0),
18    transaction_type ENUM('Transfer', 'Deposit', 'Withdrawal') NOT NULL,
19    status ENUM('Pending', 'Completed', 'Failed', 'RolledBack') DEFAULT 'Pending',
20    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
21    FOREIGN KEY (from_account) REFERENCES accounts(account_id),
22    FOREIGN KEY (to_account) REFERENCES accounts(account_id)
23);
24
25CREATE TABLE audit_log (
26    log_id INT PRIMARY KEY AUTO_INCREMENT,
27    action VARCHAR(50),
28    account_id INT,
29    amount DECIMAL(12,2),
30    performed_by VARCHAR(100),
31    performed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
32);
33
34-- Insert sample accounts
35INSERT INTO accounts (account_holder, account_type, balance) VALUES
36('Alice Johnson', 'Savings', 10000.00),
37('Bob Smith', 'Checking', 5000.00),
38('Carol White', 'Savings', 15000.00);

Step 2: Create User Roles

sqlCopy
1-- Teller: Can read accounts and process transfers
2CREATE USER 'teller'@'localhost' IDENTIFIED BY 'TellerPass2024!';
3
4-- Manager: Can read everything and modify accounts
5CREATE USER 'manager'@'localhost' IDENTIFIED BY 'ManagerPass2024!';
6
7-- Auditor: Read-only access to everything
8CREATE USER 'auditor'@'%' IDENTIFIED BY 'AuditPass2024!';

Step 3: Grant Privileges by Role

sqlCopy
1-- Teller permissions: Read accounts, insert transactions and audit logs
2GRANT SELECT ON secure_bank.accounts TO 'teller'@'localhost';
3GRANT SELECT, INSERT ON secure_bank.transactions TO 'teller'@'localhost';
4GRANT INSERT ON secure_bank.audit_log TO 'teller'@'localhost';
5
6-- Manager permissions: Full access to accounts and transactions
7GRANT SELECT, INSERT, UPDATE, DELETE ON secure_bank.accounts TO 'manager'@'localhost';
8GRANT SELECT, INSERT, UPDATE ON secure_bank.transactions TO 'manager'@'localhost';
9GRANT SELECT ON secure_bank.audit_log TO 'manager'@'localhost';
10
11-- Auditor permissions: Read-only across all tables
12GRANT SELECT ON secure_bank.* TO 'auditor'@'%';
13
14-- Apply changes
15FLUSH PRIVILEGES;

Step 4: Create a Stored Procedure for Safe Transfers

sqlCopy
1DELIMITER //
2
3CREATE PROCEDURE sp_SafeTransfer(
4    IN p_from_account INT,
5    IN p_to_account INT,
6    IN p_amount DECIMAL(12,2),
7    OUT p_status VARCHAR(50)
8)
9BEGIN
10    DECLARE from_balance DECIMAL(12,2);
11    
12    START TRANSACTION;
13    
14    -- Get sender's current balance (with lock)
15    SELECT balance INTO from_balance 
16    FROM accounts 
17    WHERE account_id = p_from_account FOR UPDATE;
18    
19    -- Check sufficient funds
20    IF from_balance IS NULL THEN
21        SET p_status = 'FAILED: Sender account not found';
22        ROLLBACK;
23    ELSEIF from_balance < p_amount THEN
24        SET p_status = 'FAILED: Insufficient funds';
25        ROLLBACK;
26    ELSEIF p_from_account = p_to_account THEN
27        SET p_status = 'FAILED: Cannot transfer to same account';
28        ROLLBACK;
29    ELSE
30        -- Deduct from sender
31        UPDATE accounts 
32        SET balance = balance - p_amount 
33        WHERE account_id = p_from_account;
34        
35        -- Add to receiver
36        UPDATE accounts 
37        SET balance = balance + p_amount 
38        WHERE account_id = p_to_account;
39        
40        -- Log the transaction
41        INSERT INTO transactions (from_account, to_account, amount, transaction_type, status)
42        VALUES (p_from_account, p_to_account, p_amount, 'Transfer', 'Completed');
43        
44        -- Audit log
45        INSERT INTO audit_log (action, account_id, amount, performed_by)
46        VALUES ('TRANSFER_OUT', p_from_account, p_amount, CURRENT_USER());
47        
48        SET p_status = 'SUCCESS';
49        COMMIT;
50    END IF;
51END //
52
53DELIMITER ;

Step 5: Test the Secure Transfer System

sqlCopy
1-- Test 1: Valid transfer
2CALL sp_SafeTransfer(1, 2, 1000.00, @result);
3SELECT @result;
4-- Expected: SUCCESS
5
6SELECT * FROM accounts WHERE account_id IN (1, 2);
7-- Alice: 9000.00, Bob: 6000.00
8
9-- Test 2: Insufficient funds
10CALL sp_SafeTransfer(2, 1, 50000.00, @result2);
11SELECT @result2;
12-- Expected: FAILED: Insufficient funds
13
14-- Test 3: Verify no partial changes occurred
15SELECT * FROM accounts WHERE account_id = 2;
16-- Bob still has 6000.00 (transaction rolled back)
17
18-- Test 4: Check audit trail
19SELECT * FROM audit_log;
20SELECT * FROM transactions;

Step 6: Verify Security with SHOW GRANTS

sqlCopy
1-- As admin, verify each role's permissions
2SHOW GRANTS FOR 'teller'@'localhost';
3SHOW GRANTS FOR 'manager'@'localhost';
4SHOW GRANTS FOR 'auditor'@'%';

✅ Module 11 Summary

🎯 Practice Exercises

1. Start a transaction, update an account balance, and ROLLBACK. Verify the balance didn't change.
2. Create a SAVEPOINT, update a row, then rollback to the savepoint and COMMIT.
3. Create a user 'intern'@'localhost' who can only SELECT from the accounts table.
4. GRANT INSERT on transactions to 'teller'@'localhost', then REVOKE it.
5. Write a transaction that transfers money between three accounts (A→B→C). If any step fails, roll back all.
6. Explain why COMMIT is necessary after a successful transaction.
7. Why should you avoid using root for application connections?

🎓 What's Next?

In Module 12, you'll master Advanced SQL & Query Optimization — Window Functions (ROW_NUMBER, RANK, LEAD, LAG), CASE expressions, PIVOT, execution plans, and techniques to make your queries run lightning fast on millions of rows! 🚀