User Management Guide

Introduction

Managing MySQL users and their privileges is crucial for database security. This guide covers creating users, assigning permissions, and implementing access control using phpMyAdmin.

Understanding MySQL User System

User Components

1. Username: User identifier
2. Host: Where user can connect from
3. Password: Authentication credential
4. Privileges: What user can do

User Format

'username'@'host'

Examples:

• 'admin'@'localhost' - Can only connect from local machine
• 'user'@'%' - Can connect from any host
• 'user'@'192.168.1.%' - Can connect from specific subnet

Accessing User Management

In phpMyAdmin

1. Open phpMyAdmin
2. Click “User accounts” tab (top menu)
3. View all existing users

Information Displayed:

• Username
• Host
• Password (set/not set)
• Global privileges
• Grant option
• Actions (Edit, Export, Remove)

Creating New Users

Method 1: Using phpMyAdmin Interface

Steps:

1. Click “User accounts” tab
2. Click “Add user account” link
3. Fill in user details:

Login Information:

• User name: Enter username
• Host name: Select from dropdown
  • Local: localhost
  • Any host: %
  • Use text field: Enter specific IP/hostname
• Password: Enter password
• Re-type: Confirm password
• Authentication: Keep as “MySQL native”

Database for user account:

• None: Just create user
• Grant all: Give full access to database
• Create database with same name and grant privileges

Global privileges:

• Check specific privileges (see privileges section below)
• Or check “Check all” for superuser

1. Click “Go” to create user

Method 2: Using SQL

Basic User Creation:

CREATE USER 'username'@'localhost' IDENTIFIED BY 'password';

Examples:

-- Local user only
CREATE USER 'app_user'@'localhost' IDENTIFIED BY 'secure_password';

-- Remote user
CREATE USER 'remote_user'@'192.168.1.100' IDENTIFIED BY 'password';

-- User from any host
CREATE USER 'any_user'@'%' IDENTIFIED BY 'password';

-- User with strong authentication
CREATE USER 'admin'@'localhost' IDENTIFIED BY 'StrongP@ssw0rd!';

User Privileges

Privilege Levels

1. Global: Apply to all databases
2. Database: Apply to specific database
3. Table: Apply to specific table
4. Column: Apply to specific columns
5. Routine: Apply to stored procedures/functions

Common Privileges

Data Privileges

• SELECT: Read data
• INSERT: Add new rows
• UPDATE: Modify existing data
• DELETE: Remove rows

Structure Privileges

• CREATE: Create databases/tables
• ALTER: Modify table structure
• DROP: Delete databases/tables
• INDEX: Create/remove indexes

Administration Privileges

• CREATE USER: Create new users
• GRANT OPTION: Grant privileges to others
• RELOAD: Flush/reload logs and caches
• SHUTDOWN: Shut down MySQL server
• PROCESS: View running processes
• SUPER: Superuser operations

All Privileges

• ALL PRIVILEGES: All available permissions

Granting Privileges

Using phpMyAdmin

Grant Global Privileges:

1. User accounts → Click “Edit privileges” for user
2. Check desired privileges
3. Click “Go”

Grant Database-Specific Privileges:

1. User accounts → Click user
2. Scroll to “Database-specific privileges”
3. Select database
4. Check desired privileges
5. Click “Go”

Grant Table-Specific Privileges:

1. Select database in left sidebar
2. Select table
3. Click “Privileges” tab
4. Add user and select privileges

Using SQL

Grant All Privileges on Database:

GRANT ALL PRIVILEGES ON database_name.* TO 'username'@'localhost';

Grant Specific Privileges:

GRANT SELECT, INSERT, UPDATE ON database_name.* TO 'username'@'localhost';

Grant Privileges on Specific Table:

GRANT SELECT, UPDATE ON database_name.table_name TO 'username'@'localhost';

Grant Privileges on Specific Columns:

GRANT SELECT (column1, column2), UPDATE (column1)
ON database_name.table_name TO 'username'@'localhost';

Grant with Grant Option (user can grant privileges to others):

GRANT ALL PRIVILEGES ON database_name.* TO 'username'@'localhost'
WITH GRANT OPTION;

Apply Changes:

FLUSH PRIVILEGES;

Common User Scenarios

Scenario 1: Read-Only User

Purpose: User can only view data

CREATE USER 'readonly'@'localhost' IDENTIFIED BY 'password';
GRANT SELECT ON database_name.* TO 'readonly'@'localhost';
FLUSH PRIVILEGES;

Scenario 2: Application User

Purpose: User for web application with full data access but no structure changes

CREATE USER 'app_user'@'localhost' IDENTIFIED BY 'secure_password';
GRANT SELECT, INSERT, UPDATE, DELETE ON database_name.* TO 'app_user'@'localhost';
FLUSH PRIVILEGES;

Scenario 3: Developer User

Purpose: Full access to specific database for development

CREATE USER 'developer'@'localhost' IDENTIFIED BY 'dev_password';
GRANT ALL PRIVILEGES ON dev_database.* TO 'developer'@'localhost';
FLUSH PRIVILEGES;

Scenario 4: Backup User

Purpose: User for backup operations only

CREATE USER 'backup_user'@'localhost' IDENTIFIED BY 'backup_password';
GRANT SELECT, LOCK TABLES, SHOW VIEW, EVENT, TRIGGER ON *.* TO 'backup_user'@'localhost';
FLUSH PRIVILEGES;

Scenario 5: Remote Administrator

Purpose: Admin access from specific IP

CREATE USER 'remote_admin'@'192.168.1.100' IDENTIFIED BY 'admin_password';
GRANT ALL PRIVILEGES ON *.* TO 'remote_admin'@'192.168.1.100'
WITH GRANT OPTION;
FLUSH PRIVILEGES;

Viewing User Privileges

Using phpMyAdmin

1. User accounts → Select user
2. View privileges by section:
   • Global privileges
   • Database-specific privileges
   • Table-specific privileges

Using SQL

Show All Privileges for User:

SHOW GRANTS FOR 'username'@'localhost';

Show Current User’s Privileges:

SHOW GRANTS;
-- or
SHOW GRANTS FOR CURRENT_USER;

Check Specific User:

SELECT * FROM mysql.user WHERE User = 'username'\G

Modifying User Privileges

Revoking Privileges

Using phpMyAdmin:

1. User accounts → Edit user
2. Uncheck privileges to revoke
3. Click “Go”

Using SQL:

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

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

-- Apply changes
FLUSH PRIVILEGES;

Changing User Password

Using phpMyAdmin:

1. User accounts → Edit user
2. Click “Change password”
3. Enter new password
4. Re-type password
5. Click “Go”

Using SQL:

-- MySQL 5.7.6+
ALTER USER 'username'@'localhost' IDENTIFIED BY 'new_password';

-- Older versions
SET PASSWORD FOR 'username'@'localhost' = PASSWORD('new_password');

-- Apply changes
FLUSH PRIVILEGES;

Change Current User Password:

ALTER USER USER() IDENTIFIED BY 'new_password';

Renaming User

RENAME USER 'old_username'@'localhost' TO 'new_username'@'localhost';

Deleting Users

Using phpMyAdmin

1. User accounts → Find user
2. Click “Remove” icon
3. Confirm deletion
4. Option: “Drop the databases that have the same name as the user”

Using SQL

DROP USER 'username'@'localhost';

Delete Multiple Users:

DROP USER 'user1'@'localhost', 'user2'@'localhost';

Security Best Practices

1. Strong Passwords

-- Good
'MySecureP@ssw0rd!2024'

-- Bad
'password', '123456', 'admin'

2. Principle of Least Privilege

Give users only the permissions they need:

-- Good: Specific privileges
GRANT SELECT, INSERT ON app_db.users TO 'app'@'localhost';

-- Bad: Excessive privileges
GRANT ALL PRIVILEGES ON *.* TO 'app'@'localhost';

3. Restrict Host Access

-- Good: Restrict to localhost
CREATE USER 'app'@'localhost' IDENTIFIED BY 'password';

-- Risky: Allow from anywhere
CREATE USER 'app'@'%' IDENTIFIED BY 'password';

4. Avoid Root for Applications

Never use root user in application connection strings:

// Bad
$mysqli = new mysqli("localhost", "root", "", "database");

// Good
$mysqli = new mysqli("localhost", "app_user", "password", "database");

5. Regular Audits

Periodically review users and privileges:

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

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

6. Remove Unused Users

-- Find users without recent connections
-- Review and drop unused users
DROP USER 'unused_user'@'localhost';

7. Set Password Expiration

-- Password expires in 90 days
ALTER USER 'username'@'localhost' PASSWORD EXPIRE INTERVAL 90 DAY;

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

Troubleshooting

Error: Access Denied

Causes:

• Wrong password
• Wrong host
• Insufficient privileges

Solutions:

-- Verify user exists
SELECT User, Host FROM mysql.user WHERE User = 'username';

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

-- Reset password if needed
ALTER USER 'username'@'localhost' IDENTIFIED BY 'new_password';

Error: User Already Exists

Solution:

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

-- Drop and recreate if needed
DROP USER 'username'@'localhost';
CREATE USER 'username'@'localhost' IDENTIFIED BY 'password';

Changes Not Taking Effect

Solution:

FLUSH PRIVILEGES;

Cannot Delete User

Check for active connections:

-- View current connections
SHOW PROCESSLIST;

-- Kill specific connection if needed
KILL connection_id;

-- Then drop user
DROP USER 'username'@'localhost';

Advanced User Management

Account Locking

Lock User Account:

ALTER USER 'username'@'localhost' ACCOUNT LOCK;

Unlock User Account:

ALTER USER 'username'@'localhost' ACCOUNT UNLOCK;

Failed Login Attempts

MySQL 8.0+:

-- Lock after 3 failed attempts for 2 days
CREATE USER 'username'@'localhost'
IDENTIFIED BY 'password'
FAILED_LOGIN_ATTEMPTS 3
PASSWORD_LOCK_TIME 2;

SSL/TLS Requirements

-- Require SSL connection
CREATE USER 'secure_user'@'%'
IDENTIFIED BY 'password'
REQUIRE SSL;

-- Require specific certificate
CREATE USER 'cert_user'@'%'
IDENTIFIED BY 'password'
REQUIRE X509;

Resource Limits

CREATE USER 'limited_user'@'localhost'
IDENTIFIED BY 'password'
WITH
    MAX_QUERIES_PER_HOUR 1000
    MAX_UPDATES_PER_HOUR 100
    MAX_CONNECTIONS_PER_HOUR 50
    MAX_USER_CONNECTIONS 10;

User Management Checklist

New User Setup

• Choose appropriate username
• Set strong password
• Restrict host appropriately
• Grant minimal necessary privileges
• Test user access
• Document user purpose

Regular Maintenance

• Review user list monthly
• Audit user privileges
• Remove unused accounts
• Update passwords periodically
• Check for security vulnerabilities
• Review access logs

Next Steps

• Security Best Practices
• Database Design
• Backup Strategies

Additional Resources

• MySQL User Account Management
• MySQL Privilege System
• MySQL Security Best Practices