MySQL & Database Design

MySQL Users & Privileges

13 min Lesson 31 of 40

MySQL Users & Privileges

Managing users and privileges is a critical aspect of database security. In this lesson, you'll learn how to create users, grant appropriate permissions, implement role-based access control, and follow best practices for user management in MySQL.

Understanding MySQL User System

MySQL uses a robust privilege system to control who can access databases and what operations they can perform. Each user is identified by a username and host combination:

User Format:
'username'@'host'

Examples:
'admin'@'localhost'      -- Local access only
'john'@'192.168.1.100'   -- Specific IP address
'mary'@'%.example.com'   -- Any host in domain
'app_user'@'%'           -- Any host (use carefully!)

Security Warning: Avoid using '@'%' (any host) for sensitive accounts. Always restrict access to specific hosts or IP ranges to minimize security risks.

Creating MySQL Users

Creating users is the first step in implementing database security. Always use strong passwords and restrict host access:

-- Create a user with password
CREATE USER 'john'@'localhost' IDENTIFIED BY 'SecureP@ssw0rd';

-- Create user with specific authentication plugin
CREATE USER 'admin'@'localhost'
IDENTIFIED WITH mysql_native_password BY 'StrongPass123!';

-- Create user with password expiration
CREATE USER 'temp_user'@'localhost'
IDENTIFIED BY 'TempPass2024'
PASSWORD EXPIRE INTERVAL 30 DAY;

-- Create user with account locked
CREATE USER 'maintenance'@'localhost'
IDENTIFIED BY 'MaintenancePass'
ACCOUNT LOCK;

Best Practice: Use strong password policies: minimum 12 characters, mix of uppercase, lowercase, numbers, and special characters. Consider using MySQL's password validation plugin.

Privilege Levels

MySQL privileges operate at different levels, allowing fine-grained access control:

1. GLOBAL Privileges:
   Apply to all databases on the server
   Example: GRANT ALL PRIVILEGES ON *.* TO 'admin'@'localhost';

2. DATABASE Privileges:
   Apply to specific database
   Example: GRANT ALL ON shop_db.* TO 'app_user'@'localhost';

3. TABLE Privileges:
   Apply to specific table
   Example: GRANT SELECT ON shop_db.products TO 'analyst'@'localhost';

4. COLUMN Privileges:
   Apply to specific columns
   Example: GRANT SELECT (id, name) ON shop_db.users TO 'viewer'@'localhost';

Granting Privileges

Use the GRANT statement to assign privileges to users. Here are common privilege scenarios:

-- Grant all privileges on all databases (full admin)
GRANT ALL PRIVILEGES ON *.* TO 'admin'@'localhost' WITH GRANT OPTION;

-- Grant database-level privileges
GRANT SELECT, INSERT, UPDATE, DELETE ON shop_db.* TO 'app_user'@'localhost';

-- Grant read-only access to database
GRANT SELECT ON shop_db.* TO 'readonly_user'@'localhost';

-- Grant specific table privileges
GRANT SELECT, INSERT ON shop_db.orders TO 'order_clerk'@'localhost';

-- Grant column-level privileges
GRANT SELECT (id, email, name), UPDATE (name)
ON shop_db.users TO 'profile_editor'@'localhost';

-- Grant privilege to create stored procedures
GRANT CREATE ROUTINE, ALTER ROUTINE, EXECUTE
ON shop_db.* TO 'developer'@'localhost';

-- Apply changes immediately
FLUSH PRIVILEGES;

Note: WITH GRANT OPTION allows the user to grant their privileges to other users. Only give this to trusted administrators.

Common Privilege Types

Understanding available privileges helps you implement least privilege principle:

Data Privileges:
SELECT          -- Read data
INSERT          -- Add new records
UPDATE          -- Modify existing records
DELETE          -- Remove records

Structure Privileges:
CREATE          -- Create databases/tables
ALTER           -- Modify table structure
DROP            -- Delete databases/tables
INDEX           -- Create/drop indexes

Administrative Privileges:
CREATE USER     -- Create new users
GRANT OPTION    -- Grant privileges to others
RELOAD          -- Reload privilege tables
SHUTDOWN        -- Shutdown server
SUPER           -- Administrative operations

Stored Program Privileges:
CREATE ROUTINE  -- Create stored procedures/functions
ALTER ROUTINE   -- Modify stored programs
EXECUTE         -- Run stored procedures/functions

Revoking Privileges

When users no longer need access or change roles, revoke their privileges:

-- Revoke specific privileges
REVOKE INSERT, UPDATE, DELETE ON shop_db.* FROM 'readonly_user'@'localhost';

-- Revoke all privileges on database
REVOKE ALL PRIVILEGES ON shop_db.* FROM 'old_employee'@'localhost';

-- Revoke grant option
REVOKE GRANT OPTION ON *.* FROM 'former_admin'@'localhost';

-- Apply changes
FLUSH PRIVILEGES;

Role-Based Access Control (MySQL 8.0+)

Roles simplify privilege management by grouping permissions. Create roles once and assign them to multiple users:

-- Create roles
CREATE ROLE 'app_developer', 'app_read', 'app_write';

-- Grant privileges to roles
GRANT SELECT ON shop_db.* TO 'app_read';
GRANT INSERT, UPDATE, DELETE ON shop_db.* TO 'app_write';
GRANT ALL PRIVILEGES ON shop_db.* TO 'app_developer';

-- Assign roles to users
CREATE USER 'alice'@'localhost' IDENTIFIED BY 'AlicePass123';
GRANT 'app_developer' TO 'alice'@'localhost';

CREATE USER 'bob'@'localhost' IDENTIFIED BY 'BobPass456';
GRANT 'app_read', 'app_write' TO 'bob'@'localhost';

-- Set default role for user
SET DEFAULT ROLE 'app_developer' TO 'alice'@'localhost';

-- Activate role in session
SET ROLE 'app_developer';

Advantage: Roles make it easy to manage permissions for groups of users. Change the role's privileges once, and all users with that role are updated automatically.

Viewing User Privileges

Regularly audit user privileges to maintain security:

-- Show current user's privileges
SHOW GRANTS;

-- Show specific user's privileges
SHOW GRANTS FOR 'john'@'localhost';

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

-- View detailed user information
SELECT User, Host, account_locked, password_expired
FROM mysql.user;

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

-- View password expiration settings
SELECT User, Host, password_lifetime
FROM mysql.user;

Password Policies

Implement strong password policies to enhance security:

-- Require password change on first login
CREATE USER 'new_employee'@'localhost'
IDENTIFIED BY 'TempPass2024'
PASSWORD EXPIRE;

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

-- Never expire password (not recommended)
ALTER USER 'service_account'@'localhost'
PASSWORD EXPIRE NEVER;

-- Use default password expiration
ALTER USER 'mary'@'localhost'
PASSWORD EXPIRE DEFAULT;

-- Require password history (MySQL 8.0+)
ALTER USER 'john'@'localhost'
PASSWORD HISTORY 5
PASSWORD REUSE INTERVAL 365 DAY;

Managing User Accounts

Additional user management operations:

-- Change user password
ALTER USER 'john'@'localhost' IDENTIFIED BY 'NewSecurePass123';

-- Change own password
SET PASSWORD = 'MyNewPassword456';

-- Lock user account
ALTER USER 'suspended_user'@'localhost' ACCOUNT LOCK;

-- Unlock user account
ALTER USER 'suspended_user'@'localhost' ACCOUNT UNLOCK;

-- Rename user
RENAME USER 'old_name'@'localhost' TO 'new_name'@'localhost';

-- Delete user
DROP USER 'former_employee'@'localhost';

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

Important: Always document user changes. Keep a log of who has access, when accounts were created, and when privileges were modified.

Practical Example: Setting Up Application Users

Let's create a complete user setup for a web application:

-- 1. Create database
CREATE DATABASE ecommerce_db;

-- 2. Create admin user (full control)
CREATE USER 'ecom_admin'@'localhost'
IDENTIFIED BY 'AdminSecure2024!';
GRANT ALL PRIVILEGES ON ecommerce_db.* TO 'ecom_admin'@'localhost';

-- 3. Create application user (read/write)
CREATE USER 'ecom_app'@'192.168.1.%'
IDENTIFIED BY 'AppPass2024!'
PASSWORD EXPIRE INTERVAL 90 DAY;
GRANT SELECT, INSERT, UPDATE, DELETE ON ecommerce_db.*
TO 'ecom_app'@'192.168.1.%';

-- 4. Create reporting user (read-only)
CREATE USER 'ecom_reports'@'localhost'
IDENTIFIED BY 'ReportsPass2024!';
GRANT SELECT ON ecommerce_db.* TO 'ecom_reports'@'localhost';

-- 5. Create backup user
CREATE USER 'ecom_backup'@'localhost'
IDENTIFIED BY 'BackupPass2024!';
GRANT SELECT, LOCK TABLES, SHOW VIEW, EVENT, TRIGGER
ON ecommerce_db.* TO 'ecom_backup'@'localhost';

-- 6. Apply all changes
FLUSH PRIVILEGES;

-- 7. Verify grants
SHOW GRANTS FOR 'ecom_app'@'192.168.1.%';

User Management Best Practices

✓ Principle of Least Privilege:
  Grant only necessary permissions for the task

✓ Use Specific Hosts:
  Avoid '@'%' - restrict to specific IPs or networks

✓ Strong Passwords:
  Minimum 12 characters, complex requirements

✓ Regular Audits:
  Review user privileges quarterly

✓ Password Expiration:
  Enforce password changes every 90 days

✓ Remove Unused Accounts:
  Delete accounts of departed employees immediately

✓ Use Roles (MySQL 8.0+):
  Group permissions for easier management

✓ Document Everything:
  Maintain records of all user changes

✓ Separate Accounts:
  Different accounts for different purposes

✓ Monitor Access:
  Enable audit logging for sensitive databases

Practical Exercise:

Scenario: Set up users for a blog application with these requirements:

Admin user: Full database control
Web application user: Read/write to posts, comments, users tables
Analytics user: Read-only access to all tables
Backup user: Appropriate backup privileges

Solution:

-- Create database
CREATE DATABASE blog_db;

-- Admin user
CREATE USER 'blog_admin'@'localhost' IDENTIFIED BY 'AdminPass2024!';
GRANT ALL PRIVILEGES ON blog_db.* TO 'blog_admin'@'localhost';

-- Application user
CREATE USER 'blog_app'@'localhost' IDENTIFIED BY 'AppPass2024!';
GRANT SELECT, INSERT, UPDATE, DELETE
ON blog_db.posts TO 'blog_app'@'localhost';
GRANT SELECT, INSERT, UPDATE, DELETE
ON blog_db.comments TO 'blog_app'@'localhost';
GRANT SELECT, INSERT, UPDATE
ON blog_db.users TO 'blog_app'@'localhost';

-- Analytics user
CREATE USER 'blog_analytics'@'localhost' IDENTIFIED BY 'AnalyticsPass!';
GRANT SELECT ON blog_db.* TO 'blog_analytics'@'localhost';

-- Backup user
CREATE USER 'blog_backup'@'localhost' IDENTIFIED BY 'BackupPass2024!';
GRANT SELECT, LOCK TABLES, SHOW VIEW
ON blog_db.* TO 'blog_backup'@'localhost';

FLUSH PRIVILEGES;

Summary

In this lesson, you learned:

MySQL user system with username@host format
Creating users with strong password policies
Four privilege levels: GLOBAL, DATABASE, TABLE, COLUMN
Granting and revoking privileges with GRANT/REVOKE
Role-based access control in MySQL 8.0+
Viewing and auditing user privileges
Managing accounts: locking, unlocking, renaming, deleting
Best practices for user management and security

Next Up: In the next lesson, we'll explore database security best practices including SQL injection prevention, server hardening, SSL connections, and audit logging.