How To Create a New User and Grant Permissions in MySQL

Introduction to To Create a New User and Grant Permissions in MySQL

MySQL is an open-source relational database management system, frequently used as part of the LAMP stack (Linux, Apache, MySQL, PHP). Therefore, it is a go-to choice for developers worldwide due to its efficiency and flexibility. Moreover, this system ensures robust functionality, allowing users to manage data securely and reliably. This guide outlines how to create a new MySQL user and grant permissions to perform various actions. Furthermore, by understanding these steps, you can maintain proper access control and prevent unauthorized changes. Consequently, mastering user and permission management is critical for both small projects and large-scale applications, ensuring database security and integrity.

Prerequisites To Create a New User and Grant Permissions in MySQL

To follow this guide, ensure you have:

• Access to a MySQL database.
• A MySQL database installed, preferably on an Ubuntu 20.04 VPS.
• Familiarity with terminal commands like chmod, mkdir, and cd.

Creating a New User

To create a new MySQL user, follow these steps:

1. 1. Access the MySQL prompt as the root user:

sudo mysql

1. 1. Create a user with the following syntax:

CREATE USER 'username'@'host' IDENTIFIED WITH authentication_plugin BY 'password';

1. 1. For example:

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

Alternatively, to use the mysql_native_password plugin:

CREATE USER 'sammy'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';

Granting a User Permissions

Use the GRANT statement to assign privileges:

GRANT PRIVILEGE ON database.table TO 'username'@'host';

For example, to grant global privileges:

GRANT ALL PRIVILEGES ON *.* TO 'sammy'@'localhost' WITH GRANT OPTION;

To reload privileges (optional):

FLUSH PRIVILEGES;

Revoke permissions:

REVOKE type_of_permission ON database_name.table_name FROM 'username'@'host';

To check user permissions:

SHOW GRANTS FOR 'username'@'host';

Deleting a User

To delete a user:

DROP USER 'username'@'localhost';

Logging In as a New User

To log in as the new user:

mysql -u sammy -p

Conclusion to Create a New User and Grant Permissions in MySQL

In this guide, you learned how to create new MySQL users and grant them specific permissions. Additionally, understanding permission settings is essential for managing database security and user access effectively. Experiment with different permission settings or explore advanced MySQL configurations for further learning. Moreover, try combining multiple permissions to create custom access levels for various users. This approach ensures better control over your database operations. As you gain experience, focus on mastering best practices for granting and revoking permissions. These skills are valuable for maintaining a secure and efficient MySQL environment in all projects.