ADVANCED ⏱ 50 minutes

User Management, Privileges, and Roles

User management and security control who can access your database and what they can do. Creating users with proper privileges ensures data security—giving developers SELECT access but not DELETE, allowing applications to INSERT but not DROP tables, and restricting sensitive data to authorized personnel only. Roles simplify privilege management by grouping permissions: instead of granting five privileges to ten users (50 operations), create a role with those privileges and assign the role to users. Understanding the privilege system, implementing least-privilege access, and using roles effectively are essential skills for database administrators and anyone responsible for database security. This lesson covers user creation, privilege management, role-based access control, and security best practices.

📚 What You'll Learn

How to create and manage database users
How to grant and revoke privileges
Different privilege levels (GLOBAL, DATABASE, TABLE, COLUMN)
How to create and use roles
How to assign roles to users
How to view current privileges with SHOW GRANTS
Common privilege patterns for different user types
Security best practices and least privilege principle
How to manage passwords and authentication
How to audit and monitor user access

1. Creating Database Users

CREATE USER - Basic Syntax

-- CREATE USER 'username'@'host' IDENTIFIED BY 'password';

-- Create user that can connect from anywhere
CREATE USER 'john'@'%' IDENTIFIED BY 'secure_password_123';

-- Create user that can only connect from localhost
CREATE USER 'app_user'@'localhost' IDENTIFIED BY 'app_password';

-- Create user for specific IP address
CREATE USER 'remote_user'@'192.168.1.100' IDENTIFIED BY 'remote_pass';

-- Create user with no password (not recommended!)
CREATE USER 'test_user'@'localhost';

Understanding Host Patterns

Host Patterns:

'%'                  - Any host (most permissive)
'localhost'          - Local connections only
'192.168.1.100'      - Specific IP address
'192.168.1.%'        - Any IP in subnet 192.168.1.*
'%.example.com'      - Any host in example.com domain

Examples:
'john'@'%'           - John can connect from anywhere
'john'@'localhost'   - John can only connect locally
'john'@'10.0.%.%'    - John can connect from 10.0.*.* network

Dropping Users

-- DROP USER removes the user account
DROP USER 'john'@'localhost';

-- Drop multiple users
DROP USER 'user1'@'localhost', 'user2'@'%';

-- Drop if exists (no error if doesn't exist)
DROP USER IF EXISTS 'test_user'@'localhost';

2. Granting Privileges

GRANT - Basic Syntax

-- GRANT privilege ON database.table TO 'user'@'host';

-- Grant SELECT on all tables in a database
GRANT SELECT ON company_db.* TO 'readonly_user'@'localhost';

-- Grant multiple privileges
GRANT SELECT, INSERT, UPDATE ON company_db.employees 
TO 'hr_user'@'localhost';

-- Grant all privileges on a database
GRANT ALL PRIVILEGES ON company_db.* TO 'admin_user'@'localhost';

-- Grant on specific table
GRANT SELECT, INSERT ON company_db.orders TO 'sales_user'@'%';

Common Privilege Types

Privilege	Description	Common Use
SELECT	Read data	Reporting, read-only users
INSERT	Add new rows	Application users
UPDATE	Modify existing rows	Application users
DELETE	Remove rows	Admins, cleanup jobs
CREATE	Create tables/databases	Developers, DBAs
DROP	Delete tables/databases	DBAs only
ALTER	Modify table structure	Developers, DBAs
INDEX	Create/drop indexes	DBAs
EXECUTE	Run stored procedures	Application users
GRANT OPTION	Grant privileges to others	Admins only

Privilege Levels

-- GLOBAL level (all databases)
GRANT SELECT ON *.* TO 'global_reader'@'localhost';

-- DATABASE level (all tables in database)
GRANT SELECT, INSERT ON company_db.* TO 'app_user'@'localhost';

-- TABLE level (specific table)
GRANT SELECT, UPDATE ON company_db.employees TO 'hr_user'@'localhost';

-- COLUMN level (specific columns)
GRANT SELECT (employee_id, name, email) ON company_db.employees 
TO 'limited_user'@'localhost';

-- Column-level UPDATE
GRANT UPDATE (salary) ON company_db.employees TO 'payroll_user'@'localhost';

3. Revoking Privileges

REVOKE - Removing Privileges

-- REVOKE privilege ON database.table FROM 'user'@'host';

-- Revoke SELECT privilege
REVOKE SELECT ON company_db.* FROM 'readonly_user'@'localhost';

-- Revoke multiple privileges
REVOKE INSERT, UPDATE, DELETE ON company_db.* FROM 'app_user'@'localhost';

-- Revoke all privileges
REVOKE ALL PRIVILEGES ON company_db.* FROM 'temp_user'@'localhost';

-- Revoke column-level privilege
REVOKE UPDATE (salary) ON company_db.employees FROM 'user'@'localhost';

4. Creating and Using Roles

Roles are named collections of privileges. Instead of granting privileges to each user individually, create a role with the necessary privileges and assign the role to users. This simplifies management and ensures consistency.

CREATE ROLE - Basic Syntax

-- Create a role
CREATE ROLE 'app_readonly';
CREATE ROLE 'app_developer';
CREATE ROLE 'app_admin';

-- Create multiple roles
CREATE ROLE 'role1', 'role2', 'role3';

Granting Privileges to Roles

-- Grant privileges to a role (just like granting to a user)

-- Read-only role
CREATE ROLE 'readonly';
GRANT SELECT ON company_db.* TO 'readonly';

-- Developer role
CREATE ROLE 'developer';
GRANT SELECT, INSERT, UPDATE, DELETE ON company_db.* TO 'developer';
GRANT CREATE, ALTER, DROP ON company_db.* TO 'developer';

-- Admin role
CREATE ROLE 'admin';
GRANT ALL PRIVILEGES ON company_db.* TO 'admin';
GRANT GRANT OPTION ON company_db.* TO 'admin';

Assigning Roles to Users

-- GRANT role TO user

-- Assign readonly role to user
GRANT 'readonly' TO 'john'@'localhost';

-- Assign developer role to user
GRANT 'developer' TO 'jane'@'localhost';

-- Assign multiple roles to user
GRANT 'readonly', 'developer' TO 'bob'@'localhost';

-- Set default role (automatically active on login)
SET DEFAULT ROLE 'readonly' TO 'john'@'localhost';

-- Set all roles as default
SET DEFAULT ROLE ALL TO 'jane'@'localhost';

Activating Roles

-- Users must activate their roles (unless set as default)

-- Activate specific role
SET ROLE 'developer';

-- Activate all assigned roles
SET ROLE ALL;

-- Deactivate all roles
SET ROLE NONE;

-- Check current active roles
SELECT CURRENT_ROLE();

Revoking Roles

-- Revoke role from user
REVOKE 'developer' FROM 'jane'@'localhost';

-- Drop role (removes it completely)
DROP ROLE 'old_role';
DROP ROLE IF EXISTS 'temp_role';

5. Role Hierarchies

Granting Roles to Roles

-- Create role hierarchy

-- Base role with read access
CREATE ROLE 'base_reader';
GRANT SELECT ON company_db.* TO 'base_reader';

-- Writer role includes reader + write privileges
CREATE ROLE 'base_writer';
GRANT 'base_reader' TO 'base_writer';  -- Inherit reader privileges
GRANT INSERT, UPDATE ON company_db.* TO 'base_writer';

-- Admin role includes writer + admin privileges
CREATE ROLE 'base_admin';
GRANT 'base_writer' TO 'base_admin';  -- Inherit writer (and reader) privileges
GRANT DELETE, CREATE, ALTER, DROP ON company_db.* TO 'base_admin';

-- Now assigning 'base_admin' to a user gives them all privileges
GRANT 'base_admin' TO 'super_user'@'localhost';

Practical Role Hierarchy Example

-- Department-based role hierarchy

-- Create department base roles
CREATE ROLE 'hr_base';
GRANT SELECT ON company_db.employees TO 'hr_base';
GRANT SELECT ON company_db.departments TO 'hr_base';

CREATE ROLE 'hr_manager';
GRANT 'hr_base' TO 'hr_manager';  -- Inherit base privileges
GRANT INSERT, UPDATE ON company_db.employees TO 'hr_manager';
GRANT UPDATE (salary) ON company_db.employees TO 'hr_manager';

CREATE ROLE 'hr_director';
GRANT 'hr_manager' TO 'hr_director';  -- Inherit manager privileges
GRANT DELETE ON company_db.employees TO 'hr_director';

-- Assign to users
GRANT 'hr_base' TO 'hr_clerk'@'localhost';
GRANT 'hr_manager' TO 'hr_manager_user'@'localhost';
GRANT 'hr_director' TO 'hr_director_user'@'localhost';

6. Viewing Grants and Privileges

SHOW GRANTS

-- Show grants for current user
SHOW GRANTS;

-- Show grants for specific user
SHOW GRANTS FOR 'john'@'localhost';

-- Show grants for role
SHOW GRANTS FOR 'readonly';

-- Show grants using role
SHOW GRANTS FOR 'john'@'localhost' USING 'developer';

Querying mysql.user Table

-- List all users
SELECT user, host FROM mysql.user;

-- Show users with their privileges
SELECT 
    user,
    host,
    Select_priv,
    Insert_priv,
    Update_priv,
    Delete_priv
FROM mysql.user;

-- Find users with specific privilege
SELECT user, host 
FROM mysql.user 
WHERE Delete_priv = 'Y';

7. Common User and Role Patterns

Read-Only User

-- Create read-only role and user
CREATE ROLE 'reporting_readonly';
GRANT SELECT ON company_db.* TO 'reporting_readonly';

CREATE USER 'reporter'@'%' IDENTIFIED BY 'secure_pass';
GRANT 'reporting_readonly' TO 'reporter'@'%';
SET DEFAULT ROLE 'reporting_readonly' TO 'reporter'@'%';

Application User

-- Create application role with CRUD operations
CREATE ROLE 'app_role';
GRANT SELECT, INSERT, UPDATE, DELETE ON company_db.* TO 'app_role';
GRANT EXECUTE ON company_db.* TO 'app_role';  -- For stored procedures

CREATE USER 'app_user'@'192.168.1.%' IDENTIFIED BY 'app_secure_pass';
GRANT 'app_role' TO 'app_user'@'192.168.1.%';
SET DEFAULT ROLE 'app_role' TO 'app_user'@'192.168.1.%';

Developer User

-- Developer with structure modification but limited DELETE
CREATE ROLE 'dev_role';
GRANT SELECT, INSERT, UPDATE ON company_db.* TO 'dev_role';
GRANT CREATE, ALTER, INDEX ON company_db.* TO 'dev_role';
-- Notice: NO DELETE or DROP privileges for safety

CREATE USER 'developer'@'localhost' IDENTIFIED BY 'dev_pass';
GRANT 'dev_role' TO 'developer'@'localhost';
SET DEFAULT ROLE 'dev_role' TO 'developer'@'localhost';

DBA/Admin User

-- Full admin with grant ability
CREATE ROLE 'dba_role';
GRANT ALL PRIVILEGES ON company_db.* TO 'dba_role' WITH GRANT OPTION;

CREATE USER 'dba'@'localhost' IDENTIFIED BY 'dba_secure_pass';
GRANT 'dba_role' TO 'dba'@'localhost';
SET DEFAULT ROLE 'dba_role' TO 'dba'@'localhost';

8. Password and Authentication Management

Changing Passwords

-- Change your own password
ALTER USER USER() IDENTIFIED BY 'new_password';

-- Change another user's password (requires privilege)
ALTER USER 'john'@'localhost' IDENTIFIED BY 'new_password';

-- Set password expiration
ALTER USER 'john'@'localhost' PASSWORD EXPIRE;

-- Require password change on next login
ALTER USER 'john'@'localhost' PASSWORD EXPIRE;

Password Policies

-- Set password expiration interval
ALTER USER 'john'@'localhost' PASSWORD EXPIRE INTERVAL 90 DAY;

-- Password never expires
ALTER USER 'app_user'@'localhost' PASSWORD EXPIRE NEVER;

-- Require current password to change
ALTER USER 'john'@'localhost' PASSWORD REQUIRE CURRENT;

9. Security Best Practices

✅ Security Best Practices

✅ Least Privilege: Grant only necessary privileges
✅ Use Roles: Manage privileges through roles, not individual users
✅ Specific Hosts: Use specific hosts, not '%' when possible
✅ Strong Passwords: Enforce password policies
✅ Regular Audits: Review user privileges periodically
✅ Remove Unused: Drop old users and revoke unnecessary privileges
✅ No root Remote: Never allow root to connect remotely
✅ Application Users: Separate users for each application
✅ Read-Only Default: Start with SELECT, add privileges as needed
✅ Monitor Access: Log and audit database access

Least Privilege Example

-- ❌ BAD: Giving too much access
GRANT ALL PRIVILEGES ON *.* TO 'app_user'@'%';

-- ✅ GOOD: Only what's needed
CREATE ROLE 'web_app_role';
GRANT SELECT, INSERT, UPDATE ON company_db.orders TO 'web_app_role';
GRANT SELECT, INSERT, UPDATE ON company_db.order_items TO 'web_app_role';
GRANT SELECT ON company_db.products TO 'web_app_role';
GRANT SELECT ON company_db.customers TO 'web_app_role';

CREATE USER 'web_app'@'10.0.1.%' IDENTIFIED BY 'secure_pass';
GRANT 'web_app_role' TO 'web_app'@'10.0.1.%';
SET DEFAULT ROLE 'web_app_role' TO 'web_app'@'10.0.1.%';

10. 🎯 Practice Exercises

Exercise 1: Basic User and Role Setup

Set up a basic multi-user system with roles:

Database: ecommerce_db
Tables: products, orders, customers, reviews

Tasks:

Create a read-only role and assign it to an analyst user
Create a customer service role with read on all tables, update on orders
Create an application role with full CRUD on orders and customers
Verify grants for each user

Show Solution

-- Task 1: Read-only analyst
CREATE ROLE 'analyst_readonly';
GRANT SELECT ON ecommerce_db.* TO 'analyst_readonly';

CREATE USER 'analyst'@'localhost' IDENTIFIED BY 'analyst_pass';
GRANT 'analyst_readonly' TO 'analyst'@'localhost';
SET DEFAULT ROLE 'analyst_readonly' TO 'analyst'@'localhost';


-- Task 2: Customer service role
CREATE ROLE 'customer_service';
GRANT SELECT ON ecommerce_db.* TO 'customer_service';
GRANT UPDATE ON ecommerce_db.orders TO 'customer_service';

CREATE USER 'cs_agent'@'localhost' IDENTIFIED BY 'cs_pass';
GRANT 'customer_service' TO 'cs_agent'@'localhost';
SET DEFAULT ROLE 'customer_service' TO 'cs_agent'@'localhost';


-- Task 3: Application role
CREATE ROLE 'ecommerce_app';
GRANT SELECT, INSERT, UPDATE, DELETE ON ecommerce_db.orders TO 'ecommerce_app';
GRANT SELECT, INSERT, UPDATE, DELETE ON ecommerce_db.customers TO 'ecommerce_app';
GRANT SELECT ON ecommerce_db.products TO 'ecommerce_app';
GRANT SELECT, INSERT ON ecommerce_db.reviews TO 'ecommerce_app';

CREATE USER 'app_backend'@'192.168.1.%' IDENTIFIED BY 'app_secure_pass';
GRANT 'ecommerce_app' TO 'app_backend'@'192.168.1.%';
SET DEFAULT ROLE 'ecommerce_app' TO 'app_backend'@'192.168.1.%';


-- Task 4: Verify grants
SHOW GRANTS FOR 'analyst'@'localhost';
SHOW GRANTS FOR 'cs_agent'@'localhost';
SHOW GRANTS FOR 'app_backend'@'192.168.1.%';

Exercise 2: Department-Based Access Control

Create a role hierarchy for different departments:

Database: company_db
Tables: employees, salaries, departments, projects

Tasks:

Create HR base role (read employees, departments)
Create HR manager role (inherits base + can update employees)
Create Finance role (read salaries, update salaries)
Create Project Manager role (full access to projects table only)

Show Solution

-- Task 1: HR base role
CREATE ROLE 'hr_base';
GRANT SELECT ON company_db.employees TO 'hr_base';
GRANT SELECT ON company_db.departments TO 'hr_base';

CREATE USER 'hr_assistant'@'localhost' IDENTIFIED BY 'hr_pass';
GRANT 'hr_base' TO 'hr_assistant'@'localhost';
SET DEFAULT ROLE 'hr_base' TO 'hr_assistant'@'localhost';


-- Task 2: HR manager (inherits base + update)
CREATE ROLE 'hr_manager';
GRANT 'hr_base' TO 'hr_manager';  -- Inherit base privileges
GRANT INSERT, UPDATE ON company_db.employees TO 'hr_manager';
GRANT INSERT, UPDATE ON company_db.departments TO 'hr_manager';

CREATE USER 'hr_mgr'@'localhost' IDENTIFIED BY 'hr_mgr_pass';
GRANT 'hr_manager' TO 'hr_mgr'@'localhost';
SET DEFAULT ROLE 'hr_manager' TO 'hr_mgr'@'localhost';


-- Task 3: Finance role
CREATE ROLE 'finance';
GRANT SELECT ON company_db.employees TO 'finance';  -- Need to see employee info
GRANT SELECT, UPDATE ON company_db.salaries TO 'finance';

CREATE USER 'payroll_admin'@'localhost' IDENTIFIED BY 'finance_pass';
GRANT 'finance' TO 'payroll_admin'@'localhost';
SET DEFAULT ROLE 'finance' TO 'payroll_admin'@'localhost';


-- Task 4: Project Manager
CREATE ROLE 'project_manager';
GRANT SELECT, INSERT, UPDATE, DELETE ON company_db.projects TO 'project_manager';
GRANT SELECT ON company_db.employees TO 'project_manager';  -- View team members

CREATE USER 'pm_user'@'localhost' IDENTIFIED BY 'pm_pass';
GRANT 'project_manager' TO 'pm_user'@'localhost';
SET DEFAULT ROLE 'project_manager' TO 'pm_user'@'localhost';


-- Verify the hierarchy
SHOW GRANTS FOR 'hr_manager';  -- Should show hr_base privileges too

🔥 Challenge: Enterprise Security Setup

Design a complete enterprise security model:

Multi-database system:
- production_db (live data)
- staging_db (testing)
- reporting_db (analytics)

Challenge: Create a comprehensive role hierarchy with different access levels for developers, QA testers, analysts, and production support. Include column-level security for sensitive data.

Show Solution

-- ========================================
-- ENTERPRISE SECURITY MODEL
-- ========================================

-- 1. BASE ROLES (Building blocks)
-- ========================================

-- Read-only across all databases
CREATE ROLE 'base_viewer';
GRANT SELECT ON production_db.* TO 'base_viewer';
GRANT SELECT ON staging_db.* TO 'base_viewer';
GRANT SELECT ON reporting_db.* TO 'base_viewer';

-- Staging CRUD (for testing)
CREATE ROLE 'staging_crud';
GRANT SELECT, INSERT, UPDATE, DELETE ON staging_db.* TO 'staging_crud';


-- 2. ANALYST ROLES
-- ========================================

CREATE ROLE 'analyst';
GRANT 'base_viewer' TO 'analyst';  -- Inherit read access
GRANT SELECT ON reporting_db.* TO 'analyst';

-- Senior analyst with reporting DB write
CREATE ROLE 'senior_analyst';
GRANT 'analyst' TO 'senior_analyst';
GRANT INSERT, UPDATE ON reporting_db.* TO 'senior_analyst';


-- 3. DEVELOPER ROLES
-- ========================================

-- Junior developer: staging only
CREATE ROLE 'junior_developer';
GRANT 'staging_crud' TO 'junior_developer';
GRANT SELECT ON production_db.* TO 'junior_developer';  -- Read prod for debugging

-- Senior developer: staging full + prod read
CREATE ROLE 'senior_developer';
GRANT 'junior_developer' TO 'senior_developer';
GRANT CREATE, ALTER, DROP, INDEX ON staging_db.* TO 'senior_developer';


-- 4. QA ROLES
-- ========================================

CREATE ROLE 'qa_tester';
GRANT SELECT, INSERT, UPDATE ON staging_db.* TO 'qa_tester';
-- Note: No DELETE or schema changes

CREATE ROLE 'qa_lead';
GRANT 'qa_tester' TO 'qa_lead';
GRANT DELETE ON staging_db.* TO 'qa_lead';


-- 5. PRODUCTION SUPPORT
-- ========================================

-- Read-only production access
CREATE ROLE 'prod_support';
GRANT SELECT ON production_db.* TO 'prod_support';

-- Column-level: hide sensitive data
-- Can read customers but not credit card info
GRANT SELECT (customer_id, name, email, phone) 
ON production_db.customers TO 'prod_support';


-- 6. OPERATIONS/DBA
-- ========================================

-- Limited production write (emergencies)
CREATE ROLE 'prod_operator';
GRANT 'prod_support' TO 'prod_operator';
GRANT INSERT, UPDATE ON production_db.orders TO 'prod_operator';
GRANT UPDATE ON production_db.customers TO 'prod_operator';

-- Full DBA access
CREATE ROLE 'dba';
GRANT ALL PRIVILEGES ON production_db.* TO 'dba' WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON staging_db.* TO 'dba' WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON reporting_db.* TO 'dba' WITH GRANT OPTION;


-- ========================================
-- CREATE USERS AND ASSIGN ROLES
-- ========================================

-- Analysts
CREATE USER 'analyst_john'@'%' IDENTIFIED BY 'secure_pass1';
GRANT 'analyst' TO 'analyst_john'@'%';
SET DEFAULT ROLE 'analyst' TO 'analyst_john'@'%';

-- Developers
CREATE USER 'dev_jane'@'10.0.%.%' IDENTIFIED BY 'secure_pass2';
GRANT 'junior_developer' TO 'dev_jane'@'10.0.%.%';
SET DEFAULT ROLE 'junior_developer' TO 'dev_jane'@'10.0.%.%';

CREATE USER 'dev_senior_bob'@'10.0.%.%' IDENTIFIED BY 'secure_pass3';
GRANT 'senior_developer' TO 'dev_senior_bob'@'10.0.%.%';
SET DEFAULT ROLE 'senior_developer' TO 'dev_senior_bob'@'10.0.%.%';

-- QA
CREATE USER 'qa_alice'@'10.0.%.%' IDENTIFIED BY 'secure_pass4';
GRANT 'qa_tester' TO 'qa_alice'@'10.0.%.%';
SET DEFAULT ROLE 'qa_tester' TO 'qa_alice'@'10.0.%.%';

-- Production Support
CREATE USER 'support_user'@'localhost' IDENTIFIED BY 'secure_pass5';
GRANT 'prod_support' TO 'support_user'@'localhost';
SET DEFAULT ROLE 'prod_support' TO 'support_user'@'localhost';

-- DBA
CREATE USER 'dba_admin'@'localhost' IDENTIFIED BY 'very_secure_pass';
GRANT 'dba' TO 'dba_admin'@'localhost';
SET DEFAULT ROLE 'dba' TO 'dba_admin'@'localhost';


-- ========================================
-- AUDIT AND VERIFY
-- ========================================

-- Show all roles
SELECT * FROM mysql.role_edges;

-- Verify specific user privileges
SHOW GRANTS FOR 'dev_jane'@'10.0.%.%';
SHOW GRANTS FOR 'dev_jane'@'10.0.%.%' USING 'junior_developer';

-- List all users with their default roles
SELECT 
    user,
    host,
    default_role
FROM mysql.default_roles;

Enterprise Security Features:

✅ Role hierarchy with inheritance
✅ Environment-based separation (prod/staging/reporting)
✅ Progressive privilege levels (junior → senior)
✅ Column-level security for sensitive data
✅ Host-based restrictions (localhost, specific subnets)
✅ Least privilege principle throughout
✅ Clear separation of duties
✅ Audit trail via SHOW GRANTS
💡 Production-ready enterprise security model!

📝 Key Takeaways

CREATE USER creates database user accounts
GRANT gives privileges; REVOKE removes them
Privileges can be at GLOBAL, DATABASE, TABLE, or COLUMN level
Roles are named collections of privileges
CREATE ROLE defines reusable privilege sets
Roles can be granted to users and to other roles (hierarchy)
SET DEFAULT ROLE activates roles automatically on login
SHOW GRANTS displays current privileges
Always follow least privilege principle
Use specific hosts, not '%' when possible
Roles simplify management of user privileges
Role hierarchies enable progressive access levels
Regularly audit user access and remove unused accounts
Strong passwords and password policies are essential
Proper user management is critical for database security