User Management MySQL: Guide to Create, Manage and Permissions

Contents [hide show]

Understanding User Management in MySQL
Creating and Managing Users
Granting and Revoking Permissions
Using Roles for Managing Permissions

Managing users and their privileges in MySQL is critical to database security and efficiency. In this guide, we will cover the basics of creating and managing users, as well as granting and revoking privileges, with practical examples.

Understanding User Management in MySQL

User management in MySQL involves creating user accounts, assigning specific privileges, and ensuring secure access to the database.

Creating and Managing Users

User creation in MySQL involves defining a username, host, and password.

What is localhost ?

Creating a New User

Code

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

CREATE USER: This command creates a new user.
'new_user'@'localhost': Here, 'new_user' is the username, and 'localhost' specifies that the user can connect from the local machine. For remote access, replace 'localhost' with an IP address or %.
IDENTIFIED BY 'password': Sets the user’s password. Replace 'password' with a secure password.

Setting Passwords

Code

ALTER USER 'new_user'@'localhost' IDENTIFIED BY 'new_password';

ALTER USER: Modifies an existing user.
'new_password': The new password for the user.

Deleting Users

Code

DROP USER 'new_user'@'localhost';

DROP USER: Removes a user account from MySQL.
This command deletes the 'new_user' account.

Granting and Revoking Permissions

Permissions define what actions a user can perform in the database.

Granting Permissions

Granting Database-Specific Privileges

Code

GRANT SELECT, INSERT ON mydb.* TO 'new_user'@'localhost';

GRANT: Assigns specific privileges.
SELECT, INSERT: Types of privileges. SELECT allows reading data; INSERT permits adding data.
mydb.*: Applies privileges to all tables (*) in the mydb database.

Granting All Privileges

Code

GRANT ALL PRIVILEGES ON *.* TO 'new_user'@'localhost';

Grants all available privileges on all databases and tables.

Revoking Permissions

Revoking Specific Privileges

Code

REVOKE INSERT ON mydb.* FROM 'new_user'@'localhost';

REVOKE: Removes specific privileges.
Removes the INSERT privilege from new_user on mydb.

Revoking All Privileges

Code

REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'new_user'@'localhost';

Removes all privileges from the user.

Applying Changes

Code

FLUSH PRIVILEGES;

Refreshes MySQL’s memory to apply the privilege changes.

Using Roles for Managing Permissions

Roles in MySQL are named collections of privileges, simplifying privilege management.

Creating Roles

Code

CREATE ROLE 'manager';

CREATE ROLE: Creates a new role, here named 'manager'.

Granting Privileges to a Role

Code

GRANT SELECT, INSERT ON mydb.* TO 'manager';

Assigns SELECT and INSERT privileges to the 'manager' role for the mydb database.

Assigning Roles to Users

Code

GRANT 'manager' TO 'new_user'@'localhost';

Grants all privileges associated with the 'manager' role to 'new_user'.