Access Control

🏰 SQL Access Control: The Castle Guard System

Imagine your database is a magical castle full of treasures (your precious data). You wouldn’t let just anyone walk in and take whatever they want, right? That’s exactly what Access Control does—it’s your castle’s guard system!

🎭 The Big Picture

Think of it like this:

• Database = Your Castle 🏰
• Data = Treasures inside 💎
• Users = People who want to enter
• Roles = Job titles (like “Chef” or “Guard”)
• Permissions = Keys to different rooms

Without guards, anyone could steal your gold! Let’s learn how to protect it.

👤 Database Users: Who Knocks at the Gate?

A database user is like a person with a name tag at the castle entrance. Each person who wants to access your castle needs their own identity card.

What Makes a User?

• A unique name (like john_smith)
• A secret password (to prove they’re really them)
• Permissions (what rooms they can enter)

-- Think of this like creating
-- a new ID card for someone
CREATE USER john_smith
IDENTIFIED BY 'secret123';

Real Life Example:

• You have a banking app
• Each customer = a database user
• They can only see THEIR money, not everyone’s!

🔧 Managing Users: The ID Card Office

Managing users is like running the castle’s ID card office. You can:

1. Create New Users (Give out new ID cards)

CREATE USER emma_watson
IDENTIFIED BY 'magic456';

2. Change Passwords (Issue new cards)

ALTER USER emma_watson
IDENTIFIED BY 'newpassword789';

3. Remove Users (Take back ID cards)

DROP USER emma_watson;

4. See All Users (Check who has cards)

-- In MySQL
SELECT User FROM mysql.user;

-- In PostgreSQL
SELECT usename FROM pg_user;

Why This Matters:

• When someone leaves the company → Remove their access!
• When someone forgets password → Reset it safely
• When someone new joins → Create their account

👥 Database Roles: Job Uniforms

Imagine giving permissions to 100 employees one by one—exhausting!

Roles are like job uniforms. Instead of telling each guard individually what they can do, you just say: “If you wear the GUARD uniform, you can patrol all hallways.”

Common Role Examples:

graph TD
    A["🎭 ROLES"] --> B["👑 Admin Role"]
    A --> C["📖 Reader Role"]
    A --> D["✏️ Editor Role"]
    A --> E["👀 Viewer Role"]

    B --> B1["Can do EVERYTHING"]
    C --> C1["Can only READ data"]
    D --> D1["Can READ and CHANGE data"]
    E --> E1["Can only VIEW reports"]

Real Life:

• ADMIN = Castle King (does everything)
• ACCOUNTANT = Treasury room access only
• REPORTER = Can read data, but can’t change anything

🛠️ Managing Roles: The Uniform Shop

1. Create a New Role (Design a new uniform)

CREATE ROLE sales_team;

2. Give Permissions to a Role

GRANT SELECT, INSERT
ON customers
TO sales_team;

3. Assign Role to a User (Give uniform to person)

GRANT sales_team TO john_smith;

4. Remove a Role (Retire a uniform)

DROP ROLE sales_team;

Why Roles Are Amazing:

• Before: Give permissions to 50 people = 50 commands
• After: Create 1 role, assign to 50 people = 2 commands!

🎁 GRANT Statement: Handing Out Keys

GRANT is how you give someone permission. It’s like handing them a key to a specific room.

The Magic Words:

GRANT [permission]
ON [what]
TO [who];

Real Examples:

-- Give Emma permission to READ
-- the products table
GRANT SELECT
ON products
TO emma_watson;

-- Give John permission to READ
-- and ADD to orders table
GRANT SELECT, INSERT
ON orders
TO john_smith;

-- Give the sales_team role
-- permission to UPDATE prices
GRANT UPDATE
ON products
TO sales_team;

Visual Flow:

graph TD
    A["🔑 GRANT"] --> B["What permission?"]
    B --> C["SELECT - Read"]
    B --> D["INSERT - Add new"]
    B --> E["UPDATE - Change"]
    B --> F["DELETE - Remove"]

    A --> G["On what table?"]
    A --> H["To which user/role?"]

🚫 REVOKE Statement: Taking Back Keys

REVOKE is the opposite of GRANT. When someone shouldn’t have access anymore, you take their key back.

The Magic Words:

REVOKE [permission]
ON [what]
FROM [who];

Real Examples:

-- Take away Emma's permission
-- to read products
REVOKE SELECT
ON products
FROM emma_watson;

-- Remove ALL permissions from
-- a terminated employee
REVOKE ALL PRIVILEGES
ON orders
FROM fired_employee;

When to Use REVOKE:

• Employee leaves the company
• Someone changed departments
• Security breach—quick lockdown!
• Temporary access period ended

📝 DML Privileges: The 4 Magic Keys

DML stands for Data Manipulation Language—the ways to interact with data.

Think of your data like a notebook:

Examples of Each:

-- SELECT: Look at customer names
GRANT SELECT
ON customers
TO viewer_role;

-- INSERT: Add new products
GRANT INSERT
ON products
TO inventory_manager;

-- UPDATE: Change prices
GRANT UPDATE
ON products
TO pricing_team;

-- DELETE: Remove old records
GRANT DELETE
ON old_logs
TO cleanup_bot;

The Power Combo:

-- Give someone multiple powers
GRANT SELECT, INSERT, UPDATE
ON orders
TO sales_team;

🏛️ Permission Levels: Floors of the Castle

Permissions can work at different levels—like having keys to different floors!

graph TD
    A["🏛️ Permission Levels"] --> B["🌐 Global Level"]
    A --> C["📁 Database Level"]
    A --> D["📊 Table Level"]
    A --> E["📎 Column Level"]

    B --> B1["Access to EVERYTHING"]
    C --> C1["Access to one database"]
    D --> D1["Access to one table"]
    E --> E1["Access to specific columns"]

Level 1: Global (Whole Castle)

-- Super admin - everywhere!
GRANT ALL PRIVILEGES
ON *.*
TO super_admin;

Level 2: Database (One Building)

-- Access to sales database only
GRANT ALL PRIVILEGES
ON sales_db.*
TO sales_manager;

Level 3: Table (One Room)

-- Access to customers table only
GRANT SELECT, INSERT
ON sales_db.customers
TO customer_service;

Level 4: Column (One Drawer)

-- Can only see name and email,
-- NOT salary or SSN!
GRANT SELECT (name, email)
ON employees
TO reception_staff;

Why Levels Matter:

• Least Privilege Principle: Give only what’s needed!
• Receptionist doesn’t need salary info
• Intern doesn’t need delete powers
• Keep your castle safe! 🏰

🎯 Putting It All Together

Let’s build a complete access system for a small company:

-- Step 1: Create roles (uniforms)
CREATE ROLE managers;
CREATE ROLE employees;
CREATE ROLE interns;

-- Step 2: Give powers to roles
GRANT SELECT, INSERT, UPDATE, DELETE
ON company_db.*
TO managers;

GRANT SELECT, INSERT
ON company_db.*
TO employees;

GRANT SELECT
ON company_db.public_info
TO interns;

-- Step 3: Create users (people)
CREATE USER alice IDENTIFIED BY 'pass1';
CREATE USER bob IDENTIFIED BY 'pass2';
CREATE USER charlie IDENTIFIED BY 'pass3';

-- Step 4: Give roles to users
GRANT managers TO alice;
GRANT employees TO bob;
GRANT interns TO charlie;

The Result:

• Alice (Manager): Can do everything
• Bob (Employee): Can read and add data
• Charlie (Intern): Can only read public info

🌟 Remember This!

“Access Control is like being a good castle guard: Know who’s coming in, what keys they have, and always be ready to take keys back!”

Quick Checklist:

• ✅ Every person needs a USER account
• ✅ Group similar users into ROLES
• ✅ Use GRANT to give permissions
• ✅ Use REVOKE to take them away
• ✅ DML = SELECT, INSERT, UPDATE, DELETE
• ✅ Apply permissions at the right LEVEL

🚀 You Did It!

You now understand how to protect your database castle like a pro! Your data treasures are safe, only the right people get in, and you know how to kick anyone out if needed.

Next time someone asks: “Who can see this data?”

You’ll confidently say: “Let me check their permissions!” 💪