MySQL: MySQL User Management and Permission Control

Last updated: 2026-08-26

Access control is the foundation of database security—different users can only access the data they are authorized to access.

This lesson covers user and permission management.

graph TB
    A[MySQL Permission Levels] --> B[Global Permissions<br/>*.*]
    A --> C[Database Permissions<br/>mydb.*]
    A --> D[Table Permissions<br/>mydb.users]
    A --> E[Column Permissions<br/>mydb.users col]
    B --> B1[ALL/CREATE/RELOAD...]
    C --> C1[SELECT/INSERT/UPDATE/DELETE]
    D --> D1[SELECT/ALTER/INDEX...]
    E --> E1[SELECT col1, col2]

graph TB
    A[MySQL Permission Levels] --> B[Global Permissions<br/>*.*]
    A --> C[Database Permissions<br/>mydb.*]
    A --> D[Table Permissions<br/>mydb.users]
    A --> E[Column Permissions<br/>mydb.users col]
    B --> B1[ALL/CREATE/RELOAD...]
    C --> C1[SELECT/INSERT/UPDATE/DELETE]
    D --> D1[SELECT/ALTER/INDEX...]
    E --> E1[SELECT col1, col2]

1. What You'll Learn

• CREATE USER: Create a user
• GRANT Authorization
• REVOKE Revoke permissions
• Permission Level (Global/Database/Table/Column)
• Role management (8.0+)

2. A True Story

(1) Pain Point: A Mistake with the Root Account Leads to a Major Disaster

The development team had always been connecting to the production database using the root account—it was convenient, but then one day, a developer accidentally executed DROP TABLE users, and the data for 3 million users vanished in an instant. It took four hours to restore the backup, during which time the service was completely down, resulting in direct losses of over one million.

(2) Solutions for the Permissions System

Apply the principle of least privilege: Grant each role only the necessary permissions. Developers are granted read-only access plus read/write access to development repositories; operations staff have management permissions for specific repositories; and root access is restricted to DBAs using the bastion host.

3. User Management

▶ Example: Creating a User

-- Create a Local User
CREATE USER 'dev_user'@'localhost' IDENTIFIED BY 'DevPass123!';

-- Create a user who is allowed to connect remotely
CREATE USER 'remote_user'@'%' IDENTIFIED BY 'RemotePass123!';

-- Create a user allowed to connect from a specific IP
CREATE USER 'app_user'@'192.168.1.%' IDENTIFIED BY 'AppPass123!';

Output:

Query OK, 0 rows affected

Query OK, 0 rows affected

--------+----------
user    | host     
--------+----------
value_1 | localhost
value_2 | localhost
value_3 | localhost
--------+----------
3 rows in set

▶ Example: Modifying and Deleting Users

-- Change Password
ALTER USER 'dev_user'@'localhost' IDENTIFIED BY 'NewPass123!';

-- Delete User
DROP USER 'dev_user'@'localhost';

-- View All Users
SELECT user, host FROM mysql.user;

Output:

Output displayed

4. GRANT Authorization

▶ Example: Permissions Management

Output:

Query OK, 0 rows affected

Query OK, 0 rows affected

Query OK, 0 rows affected

Query OK, 0 rows affected

Query OK, 0 rows affected

-- Grant All Privileges (Administrator)
GRANT ALL PRIVILEGES ON *.* TO 'admin'@'localhost';

-- Grant Permissions to a Specific Database
GRANT SELECT, INSERT, UPDATE, DELETE ON mydb.* TO 'dev_user'@'localhost';

-- Grant Permissions on a Specific Table
GRANT SELECT ON mydb.users TO 'readonly'@'localhost';

-- Grant Permissions on Specific Columns
GRANT SELECT (username, email) ON mydb.users TO 'limited'@'localhost';

-- Refresh Permissions
FLUSH PRIVILEGES;

Output:

Output displayed

5. Permission Levels

6. REVOKE: Revoke Permissions

-- Revoke All Permissions
REVOKE ALL PRIVILEGES ON *.* FROM 'dev_user'@'localhost';

-- Revoke Specific Permissions
REVOKE INSERT, DELETE ON mydb.* FROM 'dev_user'@'localhost';

FLUSH PRIVILEGES;

7. View Permissions

-- View Current User Permissions
SHOW GRANTS;

-- View a Specific User's Permissions
SHOW GRANTS FOR 'dev_user'@'localhost';

8. User Management (MySQL 8.0+)

-- Create a role
CREATE ROLE 'app_read', 'app_write';

-- Grant privileges to roles
GRANT SELECT ON mydb.* TO 'app_read';
GRANT INSERT, UPDATE, DELETE ON mydb.* TO 'app_write';

-- Assign Roles to Users
GRANT 'app_read', 'app_write' TO 'dev_user'@'localhost';

-- Activate role
SET DEFAULT ROLE ALL TO 'dev_user'@'localhost';

❓ FAQ

📖 Summary

• CREATE USER Creates a user, specifying the host and password
• GRANT grants permission; REVOKE revokes permission
• Permission Level: Global → Database → Table → Column
• Roles (8.0+) Batch Management Permissions
• FLUSH PRIVILEGES refreshes permissions

📝 Exercises

1. Basic Question (Difficulty: ⭐): Create a read-only user who can only query the mydb database.

2. Advanced Exercise (Difficulty: ⭐⭐): Create a role and assign it to a user.

3. Challenge Question (Difficulty: ⭐⭐⭐): Design a permissions scheme where developers have read and write access, operations staff have read-only access, and administrators have full access.

Web-Tutorial Tech Team

A team of developers maintaining programming tutorials. Each tutorial is written and reviewed by developers with expertise in that field. We work to keep our content accurate and reliable — if you spot an issue, please let us know.

About Us