Users and Roles Management in MySQL
Users and Roles Management in MySQL is the process of creating users, assigning
permissions, and managing access to databases and tables to ensure security.
1. Create a User
Create a new MySQL user:
CREATE USER 'john'@'localhost'
IDENTIFIED BY 'password123';
'john' = username
'localhost' = host from which the user can connect
'password123' = user's password
2. View Existing Users
SELECT User, Host
FROM [Link];
Displays all MySQL users.
3. Change a User Password
ALTER USER 'john'@'localhost'
IDENTIFIED BY 'newpassword123';
4. Delete a User
DROP USER 'john'@'localhost';
Removes the user account.
Privileges Management
Privileges determine what actions a user can perform.
5. Grant Privileges
Give a user permission to access a database:
GRANT ALL PRIVILEGES
ON company.*
TO 'john'@'localhost';
Or grant specific permissions:
GRANT SELECT, INSERT
ON [Link]
TO 'john'@'localhost';
6. Show User Privileges
SHOW GRANTS FOR 'john'@'localhost';
Displays all permissions assigned to the user.
7. Revoke Privileges
Remove permissions from a user:
REVOKE INSERT
ON [Link]
FROM 'john'@'localhost';
8. Apply Changes
FLUSH PRIVILEGES;
Reloads privilege tables (often unnecessary in modern MySQL after GRANT/REVOKE, but
commonly taught).
Roles in MySQL
A role is a collection of privileges that can be assigned to one or more users.
9. Create a Role
CREATE ROLE 'manager';
10. Grant Privileges to a Role
GRANT SELECT, INSERT, UPDATE
ON company.*
TO 'manager';
11. Assign a Role to a User
GRANT 'manager'
TO 'john'@'localhost';
12. Set a Default Role
SET DEFAULT ROLE 'manager'
TO 'john'@'localhost';
The role becomes active automatically when the user logs in.
13. Remove a Role
REVOKE 'manager'
FROM 'john'@'localhost';
Summary
Command Purpose
CREATE USER Create a new user
ALTER USER Change user information/password
DROP USER Delete a user
GRANT Give privileges
REVOKE Remove privileges
SHOW GRANTS Display privileges
CREATE ROLE Create a role
GRANT role TO user Assign role to user
SET DEFAULT ROLE Activate role automatically
DROP ROLE Delete a role
In one sentence: User and role management in MySQL controls who can access the database
and what actions they are allowed to perform through users, privileges, and roles.