To perform any administrative task in MySQL, you first need to connect to the server, typically using the root account. You can access the MySQL command-line tool and then create a new user .
The general syntax for creating a user is:
CREATE USER 'username'@'host' IDENTIFIED WITH authentication_plugin BY 'password';
Username and Host: The host defines where the user can connect from. Using 'localhost' restricts connections to the same machine, while '%' allows connections from any host .
Authentication Plugin: MySQL 8.4 and later use the more secure caching_sha2_password as the default. If you encounter compatibility issues with older applications (like some PHP versions), you can specify mysql_native_password instead .
Once a user is created, you can grant them specific permissions using the GRANT statement .
GRANT PRIVILEGE ON database.table TO 'username'@'host';
Scope of Privileges: You can grant privileges at different levels. Use *.* for all databases and tables, database_name.* for all tables in a specific database, or database_name.table_name for a specific table .
Principle of Least Privilege: Avoid granting ALL PRIVILEGES unless absolutely necessary. Instead, grant only the permissions needed for a specific task, such as SELECT, INSERT, UPDATE, and DELETE for a standard user .
To view the permissions granted to a user, use:
SHOW GRANTS FOR 'username'@'host';
As the number of users and databases grows, managing permissions individually becomes complex. MySQL roles solve this by acting as a named collection of privileges that can be granted to users .
Create the Role: Roles are created without a host part in many cases, which defaults to '%'.
CREATE ROLE 'app_developer', 'app_read', 'app_write';
Grant Privileges to the Role: Treat the role like a user when granting privileges.
GRANT ALL ON app_db.* TO 'app_developer';
GRANT SELECT ON app_db.* TO 'app_read';
GRANT INSERT, UPDATE, DELETE ON app_db.* TO 'app_write'
Grant the Role to Users: This assigns all the role's privileges to the user in one step.
GRANT 'app_developer' TO 'dev1'@'localhost';
GRANT 'app_read', 'app_write' TO 'rw_user1'@'localhost';
A role's privileges are not active until the role is activated. You can check the active roles in your session with SELECT CURRENT_ROLE(); . To activate a role for the current session, use SET ROLE 'role_name'; . For a role to be automatically activated upon user login, you must set it as a default role: SET DEFAULT ROLE 'role_name' TO 'username'@'host'; .
MySQL offers robust features to enforce password security policies.
Manual Expiration: Force a user to change their password on next login.
ALTER USER 'username'@'localhost' PASSWORD EXPIRE;
Automatic Expiration: Establish a global policy. This sets passwords to expire every 90 days.
SET PERSIST default_password_lifetime = 90;
You can also override the global policy for specific users using PASSWORD EXPIRE INTERVAL ... or PASSWORD EXPIRE NEVER .
Prevent users from reusing old passwords. This global policy stops users from reusing any of their last 6 passwords or any password newer than 365 days .
SET PERSIST password_history = 6;
SET PERSIST password_reuse_interval = 365;
Dual Passwords: This feature allows you to change a password without causing application downtime. Set a new primary password while retaining the old one as a secondary, update your application configuration at your own pace, and then discard the old password .
ALTER USER 'appuser'@'localhost' IDENTIFIED BY 'new_password' RETAIN CURRENT PASSWORD;
/* After updating the application */
ALTER USER 'appuser'@'localhost' DISCARD OLD PASSWORD;
Account Locking: Protect against brute-force attacks by automatically locking accounts after too many failed login attempts .
CREATE USER 'user1'@'localhost'
IDENTIFIED BY 'securepass'
FAILED_LOGIN_ATTEMPTS 4
PASSWORD_LOCK_TIME UNBOUNDED;
Let's see how these concepts work together. Imagine setting up a new application database called project_db.
Create Roles for different access levels.
CREATE ROLE 'project_developer', 'project_readonly';
Assign Privileges to these roles.
GRANT ALL PRIVILEGES ON project_db.* TO 'project_developer';
GRANT SELECT ON project_db.* TO 'project_readonly';
Create User Accounts for your team members
CREATE USER 'alice'@'localhost' IDENTIFIED BY 'strong_password';
CREATE USER 'bob'@'%' IDENTIFIED WITH caching_sha2_password BY 'another_strong_password';
Grant Roles to the users.
GRANT 'project_developer' TO 'alice'@'localhost';
GRANT 'project_readonly' TO 'bob'@'%';
Set Default Roles so they are active on login.
SET DEFAULT ROLE 'project_developer' TO 'alice'@'localhost';
SET DEFAULT ROLE 'project_readonly' TO 'bob'@'%';
Enforce Password Policy for the readonly user.
ALTER USER 'bob'@'%' PASSWORD EXPIRE INTERVAL 90 DAY PASSWORD HISTORY 3;