Access Control & Privileges

Complete guide to MySQL privileges, grants, and access control management.

Access Control & Privileges

MySQL's privilege system is fundamental to database security. This comprehensive guide covers all aspects of granting, revoking, and managing user privileges.

Understanding MySQL Privileges

Privilege Hierarchy

MySQL privileges work at different levels:

  1. Global: Apply to all databases on the server
  2. Database: Apply to a specific database
  3. Table: Apply to specific tables
  4. Column: Apply to specific columns
  5. Routine: Apply to stored procedures and functions

Privilege Types

Administrative Privileges

  • ALL PRIVILEGES: All available privileges
  • SUPER: Administrative operations
  • RELOAD: Reload privileges and flush caches
  • SHUTDOWN: Shutdown the MySQL server
  • PROCESS: View all processes
  • FILE: Read/write files on server
  • REFERENCES: Create foreign keys
  • INDEX: Create and drop indexes
  • ALTER: Modify table structures
  • CREATE USER: Create, drop, rename users
  • GRANT OPTION: Grant privileges to other users

Database/Table Privileges

  • CREATE: Create databases and tables
  • DROP: Drop databases and tables
  • SELECT: Read data
  • INSERT: Add new data
  • UPDATE: Modify existing data
  • DELETE: Remove data
  • LOCK TABLES: Lock tables for reading
  • CREATE TEMPORARY TABLES: Create temporary tables
  • CREATE VIEW: Create views
  • SHOW VIEW: View existing views
  • ALTER ROUTINE: Modify stored procedures/functions
  • CREATE ROUTINE: Create stored procedures/functions
  • EXECUTE: Execute stored procedures/functions

Granting Privileges

Global Privileges

-- Grant all privileges on all databases
GRANT ALL PRIVILEGES ON *.* TO 'username'@'localhost';

-- Grant specific global privileges
GRANT SELECT, INSERT, UPDATE, DELETE ON *.* TO 'username'@'localhost';

-- Grant administrative privileges
GRANT RELOAD, PROCESS, SUPER ON *.* TO 'admin'@'localhost';

-- Grant with grant option (user can grant to others)
GRANT SELECT ON *.* TO 'username'@'localhost' WITH GRANT OPTION;

Database-Level Privileges

-- Grant all privileges on specific database
GRANT ALL PRIVILEGES ON database_name.* TO 'username'@'localhost';

-- Grant specific privileges on database
GRANT SELECT, INSERT, UPDATE, DELETE ON database_name.* TO 'username'@'localhost';

-- Grant create and drop privileges on database
GRANT CREATE, DROP ON database_name.* TO 'username'@'localhost';

-- Common application user privileges
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER ON myapp.* TO 'app_user'@'%';

Table-Level Privileges

-- Grant privileges on specific table
GRANT SELECT, INSERT, UPDATE, DELETE ON database_name.table_name TO 'username'@'localhost';

-- Grant only read access to specific table
GRANT SELECT ON database_name.users TO 'readonly'@'%';

-- Grant specific operations on table
GRANT INSERT, UPDATE ON database_name.orders TO 'app_user'@'%';

Column-Level Privileges

-- Grant access to specific columns
GRANT SELECT (id, name, email) ON database_name.users TO 'limited_user'@'localhost';

-- Grant update on specific columns
GRANT UPDATE (status, updated_at) ON database_name.orders TO 'status_updater'@'%';

-- Combine column and table privileges
GRANT SELECT ON database_name.users TO 'user1'@'localhost';
GRANT UPDATE (password) ON database_name.users TO 'user1'@'localhost';

Stored Procedure Privileges

-- Grant execute privilege on specific procedure
GRANT EXECUTE ON PROCEDURE database_name.procedure_name TO 'username'@'localhost';

-- Grant execute on all procedures in database
GRANT EXECUTE ON database_name.* TO 'username'@'localhost';

-- Grant create routine privilege
GRANT CREATE ROUTINE ON database_name.* TO 'developer'@'%';

Common Privilege Combinations

Application User

-- Standard web application user
CREATE USER 'webapp'@'%' IDENTIFIED BY 'SecurePassword123!';
GRANT SELECT, INSERT, UPDATE, DELETE ON myapp.* TO 'webapp'@'%';
GRANT CREATE TEMPORARY TABLES ON myapp.* TO 'webapp'@'%';

Read-Only User

-- Reporting/analytics user
CREATE USER 'reporting'@'%' IDENTIFIED BY 'ReportPassword456!';
GRANT SELECT ON *.* TO 'reporting'@'%';
GRANT SHOW VIEW ON *.* TO 'reporting'@'%';

Backup User

-- Backup user with necessary privileges
CREATE USER 'backup_user'@'localhost' IDENTIFIED BY 'BackupPassword789!';
GRANT SELECT, RELOAD, SHOW DATABASES, LOCK TABLES ON *.* TO 'backup_user'@'localhost';
GRANT REPLICATION CLIENT ON *.* TO 'backup_user'@'localhost';
GRANT EVENT ON *.* TO 'backup_user'@'localhost';

Developer User

-- Development user with broader access
CREATE USER 'developer'@'192.168.1.%' IDENTIFIED BY 'DevPassword321!';
GRANT ALL PRIVILEGES ON dev_*.* TO 'developer'@'192.168.1.%';
GRANT CREATE, DROP ON test_*.* TO 'developer'@'192.168.1.%';

DBA User

-- Database administrator with full access
CREATE USER 'dba'@'localhost' IDENTIFIED BY 'DBAPassword654!';
GRANT ALL PRIVILEGES ON *.* TO 'dba'@'localhost' WITH GRANT OPTION;

Monitoring User

-- Monitoring tools user
CREATE USER 'monitor'@'localhost' IDENTIFIED BY 'MonitorPassword987!';
GRANT PROCESS, REPLICATION CLIENT ON *.* TO 'monitor'@'localhost';
GRANT SELECT ON performance_schema.* TO 'monitor'@'localhost';
GRANT SELECT ON information_schema.* TO 'monitor'@'localhost';

Replication User

-- Replication user for master-slave setup
CREATE USER 'replication'@'%' IDENTIFIED BY 'ReplPassword147!';
GRANT REPLICATION SLAVE ON *.* TO 'replication'@'%';

Revoking Privileges

Revoking Specific Privileges

-- Revoke specific privileges from user
REVOKE INSERT, UPDATE ON database_name.* FROM 'username'@'localhost';

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

-- Revoke global privileges
REVOKE SELECT ON *.* FROM 'username'@'localhost';

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

Complete Privilege Removal

-- Revoke all privileges from user
REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'username'@'localhost';

Viewing Privileges

User Privileges

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

-- Show grants for current user
SHOW GRANTS;
SHOW GRANTS FOR CURRENT_USER();

-- Show grants in tabular format (MySQL 8.0+)
SHOW GRANTS FOR 'username'@'localhost' USING 'username'@'localhost';

System Privilege Tables

-- Global privileges
SELECT * FROM mysql.user WHERE user = 'username';

-- Database privileges
SELECT * FROM mysql.db WHERE user = 'username';

-- Table privileges
SELECT * FROM mysql.tables_priv WHERE user = 'username';

-- Column privileges
SELECT * FROM mysql.columns_priv WHERE user = 'username';

-- Procedure privileges
SELECT * FROM mysql.procs_priv WHERE user = 'username';

Formatted Privilege Display

-- Show detailed user privileges
SELECT 
    user,
    host,
    Select_priv,
    Insert_priv,
    Update_priv,
    Delete_priv,
    Create_priv,
    Drop_priv,
    Super_priv
FROM mysql.user 
WHERE user = 'username';

-- Show database-level privileges
SELECT 
    user,
    host,
    db,
    Select_priv,
    Insert_priv,
    Update_priv,
    Delete_priv,
    Create_priv,
    Drop_priv
FROM mysql.db 
WHERE user = 'username';

Role-Based Access Control (MySQL 8.0+)

Creating Roles

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

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

Assigning Roles to Users

-- Grant roles to users
GRANT 'app_read' TO 'user1'@'localhost';
GRANT 'app_read', 'app_write' TO 'user2'@'localhost';
GRANT 'app_admin' TO 'admin1'@'localhost';

-- Set default roles
ALTER USER 'user1'@'localhost' DEFAULT ROLE 'app_read';
ALTER USER 'user2'@'localhost' DEFAULT ROLE 'app_read', 'app_write';

Managing Roles

-- Show roles
SHOW GRANTS FOR 'app_read';

-- Show user roles
SELECT * FROM mysql.role_edges WHERE TO_USER = 'user1';

-- Revoke role from user
REVOKE 'app_write' FROM 'user2'@'localhost';

-- Drop role
DROP ROLE 'app_read';

Advanced Access Control

Resource Limits Integration

-- Grant privileges with resource limits
CREATE USER 'limited_user'@'%' IDENTIFIED BY 'Password123!';
GRANT SELECT ON database_name.* TO 'limited_user'@'%';
ALTER USER 'limited_user'@'%' 
    WITH MAX_QUERIES_PER_HOUR 1000 
    MAX_CONNECTIONS_PER_HOUR 10;

Conditional Privileges

-- Grant different privileges based on time or conditions
-- (This requires application-level logic, but you can create time-based users)

-- Morning shift user (8 AM - 4 PM access)
CREATE USER 'morning_shift'@'%' IDENTIFIED BY 'MorningPass123!';
GRANT SELECT, UPDATE ON production.* TO 'morning_shift'@'%';

-- Night shift user (8 PM - 4 AM access)
CREATE USER 'night_shift'@'%' IDENTIFIED BY 'NightPass123!';
GRANT SELECT ON production.* TO 'night_shift'@'%';

Proxy Users (MySQL 8.0+)

-- Create backend users
CREATE USER 'backend_user1'@'localhost';
CREATE USER 'backend_user2'@'localhost';

-- Create proxy user
CREATE USER 'proxy_user'@'%' IDENTIFIED BY 'ProxyPass123!';

-- Grant proxy privilege
GRANT PROXY ON 'backend_user1'@'localhost' TO 'proxy_user'@'%';
GRANT PROXY ON 'backend_user2'@'localhost' TO 'proxy_user'@'%';

Security Best Practices

Principle of Least Privilege

-- BAD: Overly broad privileges
GRANT ALL PRIVILEGES ON *.* TO 'app_user'@'%';

-- GOOD: Specific privileges only
GRANT SELECT, INSERT, UPDATE, DELETE ON myapp.* TO 'app_user'@'%';

Host-Based Restrictions

-- BAD: Allow from anywhere
CREATE USER 'sensitive_user'@'%' IDENTIFIED BY 'password';

-- GOOD: Restrict to specific hosts
CREATE USER 'sensitive_user'@'192.168.1.100' IDENTIFIED BY 'password';
CREATE USER 'sensitive_user'@'app.company.com' IDENTIFIED BY 'password';

Regular Privilege Audits

-- Find users with global privileges
SELECT user, host FROM mysql.user WHERE Super_priv = 'Y';

-- Find users with GRANT OPTION
SELECT user, host FROM mysql.user WHERE Grant_priv = 'Y';

-- Find users who can create other users
SELECT user, host FROM mysql.user WHERE Create_user_priv = 'Y';

Privilege Troubleshooting

Common Issues

Access Denied Errors

-- Check if user exists
SELECT user, host FROM mysql.user WHERE user = 'username';

-- Check user privileges
SHOW GRANTS FOR 'username'@'host';

-- Check host matching (% vs localhost vs IP)
SELECT user, host FROM mysql.user WHERE user = 'username';

Privilege Not Working

-- Reload privileges after manual changes
FLUSH PRIVILEGES;

-- Check for conflicting privileges
SHOW GRANTS FOR 'username'@'host';

-- Verify privilege inheritance
SELECT * FROM mysql.db WHERE user = 'username';

Missing Database Access

-- Check database exists
SHOW DATABASES;

-- Check database-level privileges
SELECT * FROM mysql.db WHERE user = 'username' AND db = 'database_name';

-- Grant missing privileges
GRANT SELECT ON database_name.* TO 'username'@'host';

Privilege Management Scripts

Audit Script

-- Create privilege audit view
CREATE OR REPLACE VIEW privilege_audit AS
SELECT 
    u.user,
    u.host,
    CASE WHEN u.Super_priv = 'Y' THEN 'SUPER' ELSE '' END AS super_priv,
    CASE WHEN u.Grant_priv = 'Y' THEN 'GRANT' ELSE '' END AS grant_priv,
    CASE WHEN u.Create_user_priv = 'Y' THEN 'CREATE_USER' ELSE '' END AS create_user_priv,
    d.db,
    CONCAT(
        CASE WHEN d.Select_priv = 'Y' THEN 'SELECT,' ELSE '' END,
        CASE WHEN d.Insert_priv = 'Y' THEN 'INSERT,' ELSE '' END,
        CASE WHEN d.Update_priv = 'Y' THEN 'UPDATE,' ELSE '' END,
        CASE WHEN d.Delete_priv = 'Y' THEN 'DELETE,' ELSE '' END
    ) AS db_privileges
FROM mysql.user u
LEFT JOIN mysql.db d ON u.user = d.user AND u.host = d.host
ORDER BY u.user, u.host, d.db;

-- Use the audit view
SELECT * FROM privilege_audit;

Privilege Cleanup Script

-- Find and remove unused users
SELECT 
    CONCAT('DROP USER ''', user, '''@''', host, ''';') AS cleanup_command
FROM mysql.user 
WHERE user NOT IN ('root', 'mysql.sys', 'mysql.session', 'mysql.infoschema')
AND user NOT LIKE 'app_%'  -- Keep application users
AND user NOT LIKE 'backup_%'  -- Keep backup users
AND Create_user_priv = 'N'
AND Super_priv = 'N';

Next Steps

After mastering access control:

  1. Security Best Practices: Implement comprehensive security
  2. Database Operations: Apply privileges to database management
  3. Monitoring: Monitor privilege usage and security
  4. Backup & Recovery: Secure backup operations

Quick Reference

Essential Privilege Commands

-- Grant database access
GRANT SELECT, INSERT, UPDATE, DELETE ON db.* TO 'user'@'host';

-- Grant all privileges
GRANT ALL PRIVILEGES ON db.* TO 'user'@'host';

-- Grant with grant option
GRANT SELECT ON db.* TO 'user'@'host' WITH GRANT OPTION;

-- Show user privileges
SHOW GRANTS FOR 'user'@'host';

-- Revoke privileges
REVOKE INSERT, UPDATE ON db.* FROM 'user'@'host';

-- Reload privileges
FLUSH PRIVILEGES;