Users, Roles, and Privileges

Learning Focus

Use this lesson to understand MySQL user management — creating users, granting privileges, organizing access with roles, and following the principle of least privilege.

Concept Overview

Why User Management Matters

Every MySQL connection authenticates as a user. That user's privileges determine what they can do — which databases, tables, and operations they have access to.

Poor user management leads to:

Data breaches — overprivileged users can read/modify data they shouldn't
Accidental damage — developers with DROP privileges on production
Audit failures — shared accounts make it impossible to trace who did what

The Principle of Least Privilege

Grant each user only the privileges they need to perform their specific role — nothing more.

User Type	Needs	Should NOT Have
Application backend	SELECT, INSERT, UPDATE on app tables	DROP, CREATE, GRANT, access to other databases
Read-only dashboard	SELECT on specific views	INSERT, UPDATE, DELETE
DBA admin	Full privileges	Should still use separate accounts for different tasks
Backup process	SELECT, LOCK TABLES, SHOW VIEW	INSERT, UPDATE, DELETE

MySQL User Identity

A MySQL user is identified by username + host:

'app_user'@'10.0.1.%'      -- app_user from 10.0.1.x network
'admin'@'localhost'          -- admin from local machine only
'backup'@'192.168.1.50'     -- backup from specific IP
'readonly'@'%'               -- readonly from anywhere (careful!)

The host part is critical — 'root'@'localhost' and 'root'@'%' are different users.

Basic Syntax & Rules

Creating Users

CREATE USER 'app_user'@'10.0.1.%'
    IDENTIFIED BY 'StrongP@ssw0rd!'
    PASSWORD EXPIRE INTERVAL 90 DAY
    FAILED_LOGIN_ATTEMPTS 5
    PASSWORD_LOCK_TIME 1;

Option	Purpose
`IDENTIFIED BY`	Sets the password
`PASSWORD EXPIRE INTERVAL 90 DAY`	Force password change every 90 days
`FAILED_LOGIN_ATTEMPTS 5`	Lock after 5 failed attempts
`PASSWORD_LOCK_TIME 1`	Lock for 1 day after failed attempts

Granting Privileges

-- Grant specific privileges on specific table
GRANT SELECT, INSERT, UPDATE ON app_db.orders TO 'app_user'@'10.0.1.%';

-- Grant all on a database
GRANT ALL PRIVILEGES ON app_db.* TO 'admin'@'localhost';

-- Grant select on a view (not the base table)
GRANT SELECT ON app_db.v_dashboard_data TO 'dashboard'@'%';

-- Apply changes
FLUSH PRIVILEGES;

Common Privilege Levels

Level	Syntax	Scope
Global	`ON .`	All databases, all tables
Database	`ON app_db.*`	All tables in one database
Table	`ON app_db.orders`	One specific table
Column	`GRANT SELECT (name, email) ON app_db.users`	Specific columns only

Revoking Privileges

REVOKE INSERT, UPDATE ON app_db.orders FROM 'app_user'@'10.0.1.%';
FLUSH PRIVILEGES;

Viewing Privileges

-- Current user's privileges
SHOW GRANTS;

-- Specific user's privileges
SHOW GRANTS FOR 'app_user'@'10.0.1.%';

Step-by-Step Examples

Example 1: Application Backend User

Step 1: Create the user

CREATE USER 'app_backend'@'10.0.1.%'
    IDENTIFIED BY 'B@ckend_Secur3!2026'
    PASSWORD EXPIRE INTERVAL 180 DAY;

Step 2: Grant only needed privileges

GRANT SELECT, INSERT, UPDATE, DELETE
ON app_db.customers TO 'app_backend'@'10.0.1.%';

GRANT SELECT, INSERT, UPDATE, DELETE
ON app_db.orders TO 'app_backend'@'10.0.1.%';

GRANT SELECT, INSERT
ON app_db.order_items TO 'app_backend'@'10.0.1.%';

-- No DROP, CREATE, ALTER, or GRANT privileges!
FLUSH PRIVILEGES;

Step 3: Verify

SHOW GRANTS FOR 'app_backend'@'10.0.1.%';

Example 2: Read-Only Dashboard User

CREATE USER 'dashboard'@'10.0.2.%'
    IDENTIFIED BY 'D@shboard_2026!';

-- Only SELECT on views — not base tables
GRANT SELECT ON app_db.rpt_monthly_revenue TO 'dashboard'@'10.0.2.%';
GRANT SELECT ON app_db.v_customer_summary TO 'dashboard'@'10.0.2.%';
FLUSH PRIVILEGES;

Example 3: Using Roles (MySQL 8.0+)

Roles group privileges together so you can assign multiple privileges at once.

Step 1: Create roles

CREATE ROLE 'app_read', 'app_write', 'app_admin';

Step 2: Assign privileges to roles

-- Read role: SELECT only
GRANT SELECT ON app_db.* TO 'app_read';

-- Write role: includes read + modifications
GRANT SELECT, INSERT, UPDATE, DELETE ON app_db.* TO 'app_write';

-- Admin role: everything including DDL
GRANT ALL PRIVILEGES ON app_db.* TO 'app_admin';

Step 3: Assign roles to users

CREATE USER 'alice'@'%' IDENTIFIED BY 'Alice_Pass!';
GRANT 'app_write' TO 'alice'@'%';

CREATE USER 'bob'@'%' IDENTIFIED BY 'Bob_Pass!';
GRANT 'app_read' TO 'bob'@'%';

Step 4: Activate roles

-- Users need to activate their roles (or set defaults)
SET DEFAULT ROLE ALL TO 'alice'@'%';
SET DEFAULT ROLE ALL TO 'bob'@'%';

Example 4: Managing Users

-- Change password
ALTER USER 'app_user'@'10.0.1.%' IDENTIFIED BY 'NewP@ss!2026';

-- Lock an account
ALTER USER 'app_user'@'10.0.1.%' ACCOUNT LOCK;

-- Unlock an account
ALTER USER 'app_user'@'10.0.1.%' ACCOUNT UNLOCK;

-- Drop a user
DROP USER IF EXISTS 'old_user'@'%';

-- List all users
SELECT User, Host, account_locked, password_expired
FROM mysql.user;

Practical Use Cases

1. Multi-Service Architecture

Each microservice gets its own user with access only to the tables it needs.

2. Development vs. Production

Dev users get broad access to dev databases; production accounts are tightly scoped.

3. Third-Party Integrations

External services get read-only access to specific views — never base tables.

4. Compliance Auditing

Named accounts (not shared) ensure every action can be traced to a person.

5. Role-Based Access Control (RBAC)

Use MySQL roles to mirror your organization's access model: admin, editor, viewer.

Common Mistakes & Troubleshooting

Mistake	What Happens	How to Fix
Using `'root'@'%'` for applications	Full access from anywhere — major security risk	Create dedicated users with minimal privileges
Sharing accounts	Can't audit who did what	One user per person/service
Granting `ALL PRIVILEGES ON .`	User can modify any database	Grant on specific databases/tables only
Forgetting `FLUSH PRIVILEGES`	Privilege changes may not take effect	Always run after GRANT/REVOKE
Using `'%'` as host without need	Allows connections from any IP	Restrict to specific IPs or subnets
Not expiring passwords	Compromised passwords persist indefinitely	Set `PASSWORD EXPIRE INTERVAL`

Best Practices

Principle of least privilege — start with nothing, add only what's needed
Named accounts — never share MySQL users between people or services
Restrict host access — use specific IPs or subnets, not '%'
Use roles (MySQL 8.0+) — organize privileges and simplify management
Rotate passwords — set expiry policies and enforce rotation
Audit regularly — review SHOW GRANTS for all users quarterly
Use views for external access — grant SELECT on views, not base tables
Separate admin accounts — don't use the same account for DBA and routine app operations

Hands-On Practice

Exercise 1: Create Application Users (Easy)

Create two users:

'api_service'@'10.0.1.%' with SELECT, INSERT, UPDATE on app_db.orders
'report_viewer'@'10.0.2.%' with SELECT only on app_db.rpt_monthly_revenue

Verify with SHOW GRANTS.

Exercise 2: Role-Based Setup (Medium)

Create three roles: reader, writer, admin. Assign escalating privileges. Create three users and assign one role each. Verify that each user can only perform their role's operations.

Exercise 3: Security Audit (Advanced)

Write a query that identifies:

All users with GRANT OPTION
All users with '%' host access
All users with no password expiry
All users who haven't logged in for 90 days

Connection to Other Concepts

Related Concept	How It Connects
Security Hardening	Next lesson — TLS, password policies, network restrictions
Views	Views provide column-level access without granting table access
Stored Procedures	Grant EXECUTE on procedures instead of direct table access
Audit Triggers	`CURRENT_USER()` in triggers tracks who made changes
Backup and Recovery	Backup users need specific privileges (SELECT, LOCK TABLES)

What to Learn Next

Security Hardening — TLS, password policies, and network-level security

Visual Learning Diagram

flowchart TD
    A["MySQL User\n'app_user'@'10.0.1.%'"] --> B["Assigned Role:\n'app_write'"]
    B --> C["Granted Privileges:\nSELECT, INSERT, UPDATE, DELETE"]
    C --> D["On Database:\napp_db.*"]

    E["Authentication"] --> F{"Valid credentials?"}
    F -->|Yes| G{"Has required\nprivilege?"}
    G -->|Yes| H["✅ Query executes"]
    G -->|No| I["❌ Access denied"]
    F -->|No| J["❌ Connection refused"]

    classDef success fill:#28a745,stroke:#fff,color:#fff
    classDef error fill:#dc3545,stroke:#fff,color:#fff
    class H success
    class I,J error

Quick Reference

-- Create user
CREATE USER 'name'@'host' IDENTIFIED BY 'password';

-- Grant privileges
GRANT SELECT, INSERT ON db.table TO 'name'@'host';
FLUSH PRIVILEGES;

-- Roles (MySQL 8.0+)
CREATE ROLE 'role_name';
GRANT SELECT ON db.* TO 'role_name';
GRANT 'role_name' TO 'user'@'host';
SET DEFAULT ROLE ALL TO 'user'@'host';

-- Manage users
ALTER USER 'name'@'host' IDENTIFIED BY 'new_password';
ALTER USER 'name'@'host' ACCOUNT LOCK;
REVOKE privilege ON db.table FROM 'name'@'host';
DROP USER IF EXISTS 'name'@'host';
SHOW GRANTS FOR 'name'@'host';

What's Next

Security Hardening

Concept Overview​

Why User Management Matters​

The Principle of Least Privilege​

MySQL User Identity​

Basic Syntax & Rules​

Creating Users​

Granting Privileges​

Common Privilege Levels​

Revoking Privileges​

Viewing Privileges​

Step-by-Step Examples​

Example 1: Application Backend User​

Example 2: Read-Only Dashboard User​

Example 3: Using Roles (MySQL 8.0+)​

Example 4: Managing Users​

Practical Use Cases​

1. Multi-Service Architecture​

2. Development vs. Production​

3. Third-Party Integrations​

4. Compliance Auditing​

5. Role-Based Access Control (RBAC)​

Common Mistakes & Troubleshooting​

Best Practices​

Hands-On Practice​

Exercise 1: Create Application Users (Easy)​

Exercise 2: Role-Based Setup (Medium)​

Exercise 3: Security Audit (Advanced)​

Connection to Other Concepts​

What to Learn Next​

Visual Learning Diagram​

Quick Reference​

What's Next​