How To Create a New User and Grant Permissions in MySQL

MySQL, one of the most popular open-source relational database management systems, is widely used in web development and server environments. Managing user accounts and permissions is crucial to maintaining the security and efficiency of your database. This guide will walk you through the step-by-step process of creating a new MySQL user, granting them the appropriate permissions, and managing their access effectively.

What You'll Learn

Prerequisites

Before you start, ensure you have the following:

• Access to a MySQL database: Installed and configured on your server or local machine.

• Administrative privileges: Ability to log in as the root user or a user with sufficient permissions.

• Command-line knowledge: Familiarity with basic terminal commands.

Step 1: Accessing MySQL as Root

Begin by logging into MySQL with the root account:

sudo mysql
                            

If your MySQL instance requires password authentication, use the following command and enter your password when prompted:

mysql -u root -p
                            

Step 2: Creating a New User

Create a new user with the CREATE USER statement:

CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'strong_password';
                            

Authentication Options

• Recommended Method (MySQL 8.0+):

  sql Copy

  
  CREATE USER 'newuser'@'localhost' IDENTIFIED WITH caching_sha2_password BY 'strong_password';
                                      

• PHP Compatibility Method:

  For older PHP-based applications that require mysql_native_password:

  sql Copy

  
  CREATE USER 'newuser'@'localhost' IDENTIFIED WITH mysql_native_password BY 'strong_password';
                                      

Step 3: Granting User Permissions

Permissions determine what actions a user can perform. Use the GRANT statement to assign specific privileges.

Basic Syntax

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

Example: Granting Comprehensive Permissions

To give full control over all databases and tables, use:

GRANT CREATE, ALTER, DROP, INSERT, UPDATE, DELETE,
        SELECT, REFERENCES, RELOAD
ON *.*
TO 'newuser'@'localhost'
WITH GRANT OPTION;
                            

Common Permission Scenarios

• Database-Specific Permissions:

  Grant all privileges on a specific database:

  sql Copy

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

• Read-Only Access:

  Allow a user to view data without modifying it:

  sql Copy

  
  GRANT SELECT ON mydatabase.* TO 'newuser'@'localhost';
                                      

Additional User Management Commands

Reviewing User Permissions

Check the permissions assigned to a specific user:

SHOW GRANTS FOR 'newuser'@'localhost';
                            

Revoking Permissions

To remove specific permissions:

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

Deleting a User

Remove a user from the database entirely:

DROP USER 'username'@'localhost';
                            

Logging In as the New User

Test the new user account by logging in:

mysql -u newuser -p
                            

You’ll be prompted to enter the password you set for newuser.

Best Practices

• Use strong, unique passwords for all database users.

• Assign only necessary permissions to each user based on their role.

• Avoid using root credentials for day-to-day operations.

• Regularly review and audit user permissions to ensure security.

Troubleshooting Tips

• Verify the correct authentication plugin for your user account.

• Double-check the spelling of the username, hostname, and password.

• Ensure the user has the required permissions for the tasks they need to perform.

Conclusion

Creating and managing users in MySQL is a foundational skill for database administrators and developers. By following this guide, you can establish secure, well-defined access controls for your database, ensuring both functionality and security. Implementing best practices and maintaining vigilance over permissions will help protect your system from unauthorized access.