MySQL User Management : Helpful Guide to Securing Database Environment

MySQL

Sep 07, 2023

In this article, we’ll see about MySQL User Management.

Table of Contents

MySQL is a popular open-source relational database management system that is widely used for storing, retrieving, and manipulating data. To ensure the security and reliability of your data, it is essential to understand the basics of MySQL user management.

We will discuss the basics of MySQL user management, including how to create and manage users, assign permissions, and ensure the security of your data.

What is MySQL User Management

MySQL User management is used to protect the database from unauthorized users

MySQL users are at the core of database security and access control.

In MySQL, a user is an account that allows an individual or an application to connect to the database server and perform various operations on databases and tables. Each user is associated with specific privileges that dictate what actions they can perform within the database.

Understanding MySQL Users

MySQL users are at the core of database security and access control. Before we delve into the intricacies of MySQL user management, let’s begin by understanding the fundamentals.

What Are MySQL Users?

Types of MySQL Users

MySQL users come in various forms, each serving a specific purpose in the database ecosystem:

Root User: The root user, also known as the superuser, has unrestricted access to the entire MySQL server. It can perform any operation, granting or revoking privileges, creating and deleting users, and more. Due to its immense power, the root user should be used sparingly and only for essential administrative tasks.
Database User: A database user is a regular MySQL user account created for individuals or applications to interact with a specific database. Database users have privileges limited to the databases and tables assigned to them, ensuring a level of isolation and security.
Application User: These users are typically created for web applications, scripts, or services that require access to a MySQL database. By creating distinct application users with the minimum required privileges, you can enhance security by limiting the potential damage in case of a breach.

Creating and Managing Users

MySQL allows you to create and manage multiple users, each with a unique username and password.

At the command line, connect to the server as the MySQL root user, supplying the appropriate password at the password prompt:

$> mysql -u root -p
Enter password: (enter root password here)

After connecting to the server, you can add new accounts.

Create user:

To create a new user in MySQL, you use the CREATE USER statement, which requires the username, host, and password for the new user. For example:

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

In this statement:

'username' should be replaced with the desired username.
'localhost' indicates that the user can only connect from the local machine. You can specify different hostnames or use '%' them to allow connections from anywhere.
'password' should be replaced with a strong and secure password for the user.

You can also create users that can connect from any host by specifying the host as ‘%’:

CREATE USER 'newuser'@'%' IDENTIFIED BY 'password';

Once a user is created, you can modify the user’s information using the ALTER USER statement or delete the user using the DROP USER statement.

Within phpMyAdmin, MySQL user management is controlled via the User Accounts tab from the main page. Users can be created, edited, and removed. See the above screenshot of the phpMyAdmin tool.

Show Users List

To show a list of users in the MySQL server, run the following statement

select user from mysql.user;

Change Password for User:

To change the password for the user, run the following command:

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

Remove User

To remove or drop the user, run the following command

DROP USER 'newuser'@'localhost';

Privileges in MySQL

Privilege is a right to perform an action on a database that must be granted to users in MySQL User Management.

Privileges are the permissions granted to MySQL users, determining what actions they can perform on the databases and tables. MySQL user management provides a robust privilege system that enables fine-grained control over user actions. Some common privileges include:

SELECT: Allows users to retrieve data from tables.
INSERT Grants the ability to add new records to tables.
UPDATE: Enables users to modify existing records in tables.
DELETE: Allows users to remove records from tables.
CREATE: Permits the creation of new databases and tables.
ALTER: Allows users to modify the structure of existing tables.
DROP: Grants the ability to delete databases and tables.
GRANT OPTION: Allows users to grant privileges to other users.

In the upcoming sections, we will explore how to create MySQL users, assign privileges, and manage their passwords effectively for MySQL user management. Let’s get started with creating MySQL users.

Managing Privileges

In MySQL, each user is assigned specific permissions that determine what they can do with the data in the database. To assign permissions to a user, you use the GRANT statement.

Assign specific permission to the user

For example, to grant SELECT and UPDATE permissions to the user ‘newuser’ on the ’employees’ table, you would use the following statement:

GRANT SELECT, UPDATE ON employees TO 'newuser'@'localhost';

In this example, the user ‘newuser’ can perform SELECT and UPDATE operations on the ’employees’ table.

Assign all permissions to the user:

To assign all privileges to the user, run the following command:

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

Revoke Permission

To revoke (or cancel) permissions from a user, you use the REVOKE statement. For example:

REVOKE SELECT, UPDATE ON employees FROM 'newuser'@'localhost';

In this example, the SELECT and UPDATE permissions are revoked from the user ‘newuser’ on the ’employees’ table.

Revoke database-level privileges:

To revoke permission on a specific database only for the user, run the following command

REVOKE CREATE,DROP
  ON expenses.*
  FROM 'newuser'@'localhost';

Here “expenses.*” is the name of the database

Revoke table-level privileges:

To revoke permission on a specific table only of the database for the user, run the following command

REVOKE INSERT,UPDATE,DELETE
  ON expenses.homerent
  FROM 'newuser'@'localhost';

Here, the “homerent” is the table name of the expenses database

Show Grants

To show existing permission for the user, run the following command:

SHOW GRANTS FOR 'newuser'@'localhost';

To show all grants of the currently logged user, run the following command:

SHOW GRANTS;

Flush Permissions

To flush all privileges for the user, run the following command

FLUSH PRIVILEGES;

Ensuring Data Security

MySQL provides several features to ensure the security of your data, including encryption, authentication, and access control. To encrypt data in MySQL, you can use the AES_ENCRYPT() function to encrypt sensitive data, such as passwords, before storing them in the database.

To ensure that only authorized users can access the data in the database, you can use the authentication mechanism provided by MySQL, which requires users to provide a valid username and password before accessing the data.

In addition to authentication, MySQL also provides access control features, such as granting and revoking permissions, to ensure that only authorized users can access and modify the data in the database.

By properly managing users and assigning permissions, you can ensure that your data is secure and only accessible to authorized users.

Best Practices for MySQL User Management:

Use Strong Passwords: Always use strong, complex passwords for MySQL users to prevent unauthorized access.
Limit Host Access: Be cautious about allowing user connections from remote hosts ('%'). Restrict access to specific IP addresses or domains whenever possible.
Grant Minimal Privileges: Follow the principle of least privilege. Only grant the necessary privileges to each user to minimize security risks.
Avoid Using the Root User: Refrain from using the root user for routine tasks. Create dedicated users for specific purposes.

Credits

Image by creativeart on Freepik

References:

Conclusion

In conclusion, MySQL user management is an essential component of a secure and reliable database.

Understanding the basics of creating and managing users, assigning permissions, and ensuring the security of your data will help you to create a robust and secure database for MySQL user management that can accommodate the needs of your organization.

Effective MySQL user management is essential for ensuring the security, integrity, and performance of your database system.

By following best practices for MySQL user management, you can ensure that your data is safe, secure, and accessible only to authorized users.

Blog