![](https://scientifictools.org/courses/images/coverPhotos/14/17-14d028c31b8410c8a384ab4d23c8d33c9a8e9806.png)
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.
Creating a New User
- 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
- ALTER USER: Modifies an existing user.
- 'new_password': The new password for the user.
Deleting Users
- 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
- 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
- Grants all available privileges on all databases and tables.
Revoking Permissions
Revoking Specific Privileges
- REVOKE: Removes specific privileges.
- Removes the INSERT privilege from new_user on mydb.
Revoking All Privileges
- Removes all privileges from the user.
Applying Changes
- 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
- CREATE ROLE: Creates a new role, here named 'manager'.
Granting Privileges to a Role
- Assigns SELECT and INSERT privileges to the 'manager' role for the mydb database.
Assigning Roles to Users
- Grants all privileges associated with the 'manager' role to 'new_user'.