DCL vs TCL

-- DCL (Data Control Language): manages permissions
GRANT SELECT, INSERT ON products TO analyst_role;
GRANT ALL PRIVILEGES ON DATABASE mydb TO admin_user;
REVOKE DELETE ON products FROM intern_role;
REVOKE ALL PRIVILEGES ON DATABASE mydb FROM old_user;

-- TCL (Transaction Control Language): manages transactions
BEGIN;             -- start transaction
COMMIT;            -- save all changes permanently
ROLLBACK;          -- undo all changes since BEGIN
SAVEPOINT sp1;     -- create a named restore point
ROLLBACK TO sp1;   -- undo to savepoint
RELEASE SAVEPOINT sp1; -- remove the savepoint

-- DCL: Role-based access control
-- Create roles
CREATE ROLE read_only;
CREATE ROLE app_user;

-- Grant appropriate permissions
GRANT SELECT ON ALL TABLES IN SCHEMA public TO read_only;
GRANT SELECT, INSERT, UPDATE, DELETE ON orders, order_items TO app_user;
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA public TO app_user;

-- Assign roles to users
GRANT read_only TO analyst@company.com;
GRANT app_user TO backend_service;

-- TCL: Safe bank transfer using transaction
BEGIN;

SAVEPOINT before_transfer;

UPDATE accounts SET balance = balance - 5000
WHERE user_id = 101 AND balance >= 5000;

-- Check if debit succeeded
-- (application logic checks rows affected)

UPDATE accounts SET balance = balance + 5000
WHERE user_id = 202;

INSERT INTO transaction_log (from_id, to_id, amount)
VALUES (101, 202, 5000);

COMMIT;
-- On error: ROLLBACK TO before_transfer;