MySql - Creating and managing users

Creating and Managing Users (CREATE USER) in MySQL

MySQL is one of the most widely used relational database management systems. It supports multiple users with varying levels of access and privileges. Managing these users securely and efficiently is critical for maintaining data integrity and system security. The CREATE USER statement allows database administrators to create new user accounts in MySQL. This document provides a comprehensive guide to creating, managing, and securing MySQL users using SQL commands, including examples and best practices.

Understanding User Accounts in MySQL

In MySQL, a user account is identified using a combination of a username and the host from which the user can connect. The general format is:

'username'@'host'

This allows administrators to create the same username for different hosts with separate privileges if required.

Examples

'john'@'localhost'
'admin'@'192.168.0.10'
'developer'@'%'

In the above examples, localhost limits the user to connect from the same machine, while % allows access from any host.

Creating Users with CREATE USER Statement

The CREATE USER statement adds new MySQL accounts. A user must have the CREATE USER privilege to run this statement.

Basic Syntax

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

Example: Create a Local User

CREATE USER 'alice'@'localhost' IDENTIFIED BY 'securepassword';

This creates a user named alice who can only connect from the local machine.

Example: Create a Remote User

CREATE USER 'bob'@'%' IDENTIFIED BY 'bobpass123';

This user can connect from any host. Use this only if necessary, as it opens access to all remote locations.

Setting Passwords

MySQL stores passwords securely using hashing mechanisms. You can define passwords using IDENTIFIED BY or use SET PASSWORD for later modification.

Example: Changing Password

ALTER USER 'alice'@'localhost' IDENTIFIED BY 'newpassword';

Example: Using SET PASSWORD

SET PASSWORD FOR 'alice'@'localhost' = 'strongerpassword';

Note: In MySQL 5.7 and later, it is recommended to use ALTER USER.

Granting Privileges to Users

Creating a user alone does not give any access to databases or tables. Use the GRANT command to assign permissions.

Granting All Privileges on a Database

GRANT ALL PRIVILEGES ON mydatabase.* TO 'alice'@'localhost';

Granting Specific Privileges

GRANT SELECT, INSERT, UPDATE ON mydatabase.customers TO 'bob'@'%';

Reloading Privileges

After making changes, reload them using:

FLUSH PRIVILEGES;

Viewing Existing Users

To list all user accounts:

SELECT User, Host FROM mysql.user;

This will return all usernames along with the host restrictions.

Viewing User Privileges

To view privileges granted to a user:

SHOW GRANTS FOR 'alice'@'localhost';

Altering Existing Users

The ALTER USER statement is used to change account properties like authentication, password expiration, or lock status.

Example: Change Password Policy

ALTER USER 'bob'@'%' PASSWORD EXPIRE INTERVAL 90 DAY;

Example: Lock and Unlock Accounts

ALTER USER 'bob'@'%' ACCOUNT LOCK;
ALTER USER 'bob'@'%' ACCOUNT UNLOCK;

Require Secure Connection (SSL)

ALTER USER 'alice'@'localhost' REQUIRE SSL;

Dropping Users

To remove a user account from MySQL:

DROP USER 'bob'@'%';

This completely deletes the user and their privileges.

Host-Based Access Control

MySQL allows user access based on the host. You can control which IP addresses or domains can connect to the server for each user.

Examples

'user1'@'192.168.1.100'   -- specific IP
'user2'@'%.mycompany.com' -- any host in the mycompany.com domain
'user3'@'%'               -- all hosts (least secure)

Host-based access is useful for restricting users to specific environments or internal networks.

Using Roles to Manage Permissions

MySQL supports roles (from version 8.0) to group privileges and assign them to users.

Create a Role

CREATE ROLE 'readonly';

Grant Privileges to Role

GRANT SELECT ON mydatabase.* TO 'readonly';

Assign Role to User

GRANT 'readonly' TO 'viewer'@'localhost';
SET DEFAULT ROLE 'readonly' TO 'viewer'@'localhost';

View Role Assignments

SHOW GRANTS FOR 'viewer'@'localhost';

Account Resource Limits

You can define resource limitations on a per-user basis to control the number of queries, connections, or updates.

CREATE USER 'limited'@'localhost'
IDENTIFIED BY 'limitpass'
WITH MAX_QUERIES_PER_HOUR 100
     MAX_UPDATES_PER_HOUR 50
     MAX_CONNECTIONS_PER_HOUR 20
     MAX_USER_CONNECTIONS 5;

This prevents overuse of resources by specific accounts.

Password Management Policies

MySQL 5.7+ supports password expiration, reuse restrictions, and complexity checks using the validate_password plugin.

Enable the Plugin

INSTALL PLUGIN validate_password SONAME 'validate_password.so';

Set Password Policies

SET GLOBAL validate_password.length = 12;
SET GLOBAL validate_password.mixed_case_count = 1;
SET GLOBAL validate_password.number_count = 1;

Force Password Expiry

ALTER USER 'alice'@'localhost' PASSWORD EXPIRE;

Managing Users with MySQL Workbench

For those using a graphical interface, MySQL Workbench offers a user-friendly way to manage users and their privileges:

• Open Workbench
• Navigate to “Users and Privileges”
• Create users, set host, assign roles and permissions through GUI

This is especially useful for beginners or those managing many accounts visually.

Auditing User Activities

MySQL Enterprise Edition includes an Audit plugin for tracking user activities. For community users, general logs or third-party tools are required.

SET GLOBAL general_log = 'ON';
SET GLOBAL general_log_file = '/var/log/mysql/general.log';

Monitor actions like login attempts, queries executed, and privilege use.

Best Practices for User Management

• Use strong, unique passwords for each user.
• Limit access using the principle of least privilege.
• Regularly audit user accounts and privileges.
• Disable or delete unused user accounts.
• Use roles to simplify privilege assignments.
• Avoid using wildcard (%) unless necessary.
• Monitor login attempts for unauthorized access.

Backup and Recovery of User Privileges

User information is stored in the mysql database. To back up user privileges:

mysqldump -u root -p mysql user db tables_priv > user_privileges.sql

To restore:

mysql -u root -p mysql < user_privileges.sql

Managing users in MySQL is a fundamental skill for any DBA or developer. The CREATE USER command, combined with GRANT, ALTER USER, and DROP USER, gives full control over account creation, modification, and deletion. Proper use of roles, resource limits, and password policies enhances security and simplifies administration. Whether using SQL commands or GUI tools like MySQL Workbench, always follow best practices for secure, efficient user management in MySQL.