According to an industry survey, MySQL is one of the most popular database management systems, with a 42% market share.
You can find it in almost all popular online business applications and SaaS products that handle business processes and operations. A critical aspect of managing a MySQL database is understanding how to create users and assign the correct privileges.
This short tutorial will help you through the process of how to create user in MySQL and then assigning it appropriate privileges. We’ll start with the user creation and then discuss some simple user management tips.
Table Of Contents
- How to Create a New MySQL User
- Understanding Privileges in MySQL
- MySQL User Management
- Troubleshooting Common Issues
- Conclusion
- FAQs
How to Create a New MySQL User
Users have a central role in MYSQL operations. The user roles are used to determine and allocate access privileges across the tables in a MySQL database. Let’s look at the prerequisites before diving into the steps of the user creation process.
Prerequisites
Before diving into user creation and privilege management in MySQL, ensure you have the following:
- You have MySQL installed on your server.
- You have a root account with MySQL master user privileges.
Creating a new MySQL user is a fundamental task in managing your MySQL databases. Here are the steps to create a new MySQL user:
Step #1: Login to MySQL Shell
You need to be logged into the MySQL server to create a new user.
This is typically done through the command line. So, launch the terminal and enter the following command to log into the MySQL shell as the root user:
# mysql -u root -p
You will be asked for your password. After authentication, you’ll be logged into the MySQL shell.
Step #2: Create a New User
To create a new user, you’ll use the CREATE USER statement. The basic syntax for creating a new user is:
CREATE USER 'username'@'host' IDENTIFIED WITH mysql_native_password BY 'password';
Here, you need to replace:
- username with the desired username for the new account.
- host with the location from which the user will connect to the MySQL server. You can use localhost if the user will connect from the same machine as the MySQL server. Alternatively, use a specific IP address for a remote machine or % for any host.
For instance:
CREATE USER 'username'@'ip_address' IDENTIFIED BY 'password';
OR
CREATE USER 'username'@'%' IDENTIFIED BY 'password';
- password with a strong, secure password for the new user.
Suppose you want to create a user named Tom who will connect from the machine where the MySQL server is installed and have a password ‘mypassword’. In this case, the statement would be:
mysql> CREATE USER 'Tom'@'localhost' IDENTIFIED WITH mysql_native_password BY 'mypassword';
Always assign a strong password, particularly when creating an account that allows connection from any device.
Once you have created a user, we recommend listing all users to verify that the user is indeed present in the list. For this, run the following statement:
mysql> SELECT USER FROM mysql.user;
Understanding Privileges in MySQL
In MySQL, user privileges are the permissions granted to users to perform specific tasks within the database system. These privileges are crucial for database security and operational efficiency.
Here’s an overview of what user privileges in MySQL entail:
Types of Privileges
MySQL privileges can be categorized broadly into the following types:
Administrative Privileges: These include permissions like CREATE, DROP, ALTER, and GRANT. They are used for creating, modifying, or deleting databases and tables and granting or revoking user privileges.
Data Manipulation Language (DML) Privileges: These include SELECT, INSERT, UPDATE, and DELETE. They allow users to read, insert, modify, and delete data within a table’s rows.
Data Definition Language (DDL) Privileges: These privileges allow users to change the structure of database objects through statements like CREATE, DROP, and ALTER.
Data Control Language (DCL) Privileges: These are GRANT and REVOKE, used for managing the privileges of other users.
Granting Privileges
Privileges are granted using the GRANT statement. For instance, the following statement grants SELECT privilege on a database named db1 to a user user1:
mysql> GRANT SELECT ON db1.* TO 'user1'@'localhost';
Scoped Privileges
Privileges in MySQL can be granted at different levels, providing control over what the user can access at a specific level. Typically, MySQL user privileges are at these levels:
Global: Privileges apply to all databases on the MySQL server.
Database: Database privileges apply to all tables within a specific database.
Table: These privileges are specific to a table within a database.
Column: These privileges are limited to specific columns within a table.
Routine: These privileges apply to stored routines (procedures and functions).
Also Read: 3 Simple Methods to Delete Duplicate Rows in MySQL Database
MySQL User Management
Without appropriate permissions, a newly created user account can log into the MySQL instance but won’t be able to retrieve data or execute tasks. Let’s delve deeper into granting SQL privileges to users.
Grant Permissions to a MySQL User Account
Granting permissions to a MySQL user involves using the GRANT statement.
This statement allows you to define precisely what a user can do within the MySQL database system. The permissions can be set for various scopes, such as global, database, table, column, or routine level.
Here’s how to grant permissions in different cases:
Grant Global Permissions
Global permissions apply to all databases on the MySQL server.
Use the following statement to grant SELECT permission on all databases to a user:
mysql> GRANT SELECT ON *.* TO 'username'@'localhost';
Grant Database-Specific Permissions
You need to specify the database’s name when granting permissions on a database. For instance, use the following statement when you need to grant ALL privileges on database mydb to a user:
mysql> GRANT ALL PRIVILEGES ON mydb.* TO 'username'@'localhost';
Grant Table-Specific Permissions
If you want to grant table-level privilege, include the database and table name in the statement. For instance, here’s the statement for granting INSERT and UPDATE privileges on the table users in database mydb:
mysql> GRANT INSERT, UPDATE ON mydb.users TO 'username'@'localhost';
Grant Column-Specific Permissions
Remember to include the column name(s) when granting permissions on specific columns of a table. For instance, the following statement allows a user to run SELECT and UPDATE statements on the email column in the users table:
mysql> GRANT SELECT(email), UPDATE(email) ON mydb.users TO 'username'@'localhost';
Grant Stored Procedure/Routine Permissions
Use EXECUTE to grant permissions about the stored procedures and functions. For instance, use the following to grant EXECUTE privilege on a stored procedure add_user in mydb:
mysql> GRANT EXECUTE ON PROCEDURE mydb.add_user TO 'username'@'localhost';
Grant Permissions with WITH GRANT OPTION
You can apply the privileges granted to a user to other users with the following statement:
mysql> GRANT SELECT ON mydb.* TO 'username'@'localhost' WITH GRANT OPTION;
Finalize the Changes
After granting permissions for database access, you need to execute FLUSH PRIVILEGES; to apply these changes immediately.
Important Notes
- Use the hostname from which the user will connect (use localhost for local connections or an IP address for remote connections).
- Be cautious with GRANT ALL PRIVILEGES, as it gives user full access to the specified database or table.
- Regularly review user permissions to maintain database security.
Display MySQL User Account Privileges
Use the SHOW GRANTS statement to display the list of privileges of a database user account,
mysql> SHOW GRANTS FOR 'username'@'localhost';
Replace username with the user’s name and host with the hostname (like ‘localhost’ or an IP address).
For instance, to view the privileges for a user named Tom accessing MySQL from the local machine, you would use:
mysql> SHOW GRANTS FOR ‘Tom’@'localhost';
Remember, you need administrative access to execute SHOW GRANTS.
Revoke Permissions from a MySQL User Account
Use the REVOKE statement in MySQL to revoke permissions for a MySQL user account. The syntax of the statement is as follows:
mysql> REVOKE privilege_type ON object FROM 'username'@'host';
- privilege_type: The privilege you want to remove (like SELECT, UPDATE).
- object: The database or table from which you’re revoking the privilege (database.table or database.* for all tables in a database).
- username: The user’s name.
- host: The host’s name (like ‘localhost’ or an IP address).
Here are a few examples of how to revoke privileges in different scenarios:
Revoke a Specific Privilege
Use the following statement to revoke the SELECT privilege on all databases for the user Tom:
mysql> REVOKE SELECT ON *.* FROM 'Tom'@'localhost';
Revoke Multiple Privileges
The syntax of the statement to revoke both SELECT and INSERT privileges on a table mytable in mydb database is as follows:
mysql> REVOKE SELECT, INSERT ON mydb.mytable FROM 'john'@'localhost';
Revoke All Privileges
Use the following statement syntax to revoke all privileges from a user on a specific database:
mysql> REVOKE ALL PRIVILEGES ON mydb.* FROM 'john'@'localhost';
Revoke Global Privileges
If a user has global privileges, you can revoke them with the following statement:
mysql> REVOKE ALL PRIVILEGES ON *.* FROM 'john'@'localhost';
Remember to apply the changes immediately by running the FLUSH PRIVILEGES; statement.
mysql> FLUSH PRIVILEGES;
Remember to specify the correct privileges and database objects to avoid removing more access than intended.
Change a MySQL User Account Password
Changing a MySQL user account password is a straightforward process that can be done using the ALTER USER statement.
mysql> ALTER USER 'username'@'host' IDENTIFIED BY 'new_password';
- Replace username with the user’s name.
- Replace host with ‘localhost’ (or the user’s host address).
- Replace new_password with the new password.
For instance, the following statement changes the password for the user ‘Tom’ on localhost:
mysql> ALTER USER 'Tom'@'localhost' IDENTIFIED BY 'new_password';
Delete MySQL Users
To delete a MySQL user, you can use the DROP USER statement. Here’s a basic example:
mysql> DROP USER 'username'@'localhost';
Replace ‘username’@’localhost’ with the actual username and host for the user you want to delete.
Troubleshooting Common Issues
When managing MySQL users, you might encounter various issues or errors. Before trying more specific solutions, you should first make sure that:
- The MySQL server is up and running.
- You’re logged in with sufficient privileges. Log out of your current user account and log back in as the root user.
- The syntax in your SQL statements is correct. There are minor syntax variations between MySQL versions. Ensure you’re using the correct syntax for your version.
Here are some common issues, along with typical error messages and their solutions:
Creating a User
Error: ERROR 1396 (HY000): Operation CREATE USER failed for ‘username’@’host’
Cause: You are trying to create a user that already exists, or the username is not properly formatted.
Solution: Check if the user already exists using SELECT User, Host FROM mysql.user;. Ensure the username and host are correctly formatted. Use quotes around the username and host.
Deleting a User
Error: ERROR 1396 (HY000): Operation DROP USER failed for ‘username’@’host’
Cause: The user does not exist or is not specified correctly.
Solution: Verify that the user exists with the exact host specified. Make sure the username and host are in quotes and correctly formatted.
Granting Privileges
Error: ERROR 1142 (42000): GRANT command denied to user ‘username’@’host’ for table ‘tablename’
Cause: The user trying to grant privileges does not have the GRANT OPTION or sufficient privileges on the given database/table.
Solution: Log in as a user with sufficient privileges, typically root. Verify your privileges with SHOW GRANTS, and ensure you have the GRANT OPTION.
Revoking Privileges
Error: ERROR 1141 (42000): There is no such grant defined for user ‘username’ on host ‘host’ on table ‘tablename’
Cause: You are trying to revoke a privilege the user does not have or have incorrectly specified the user/host.
Solution: Confirm the current privileges of the user with SHOW GRANTS FOR ‘username’@’host’;. Specify the correct user, host, and privileges in the revoke statement.
Understanding these common errors and their resolutions can significantly streamline your MySQL user and privilege management tasks.
Also Read: How To Rename a Database in MySQL: 3 Easy Methods
Also Read: How to Import a CSV File Into a MySQL Database in 2 Simple Methods
Conclusion
Managing users and privileges in MySQL is crucial for database security and functionality. By following the steps outlined in this guide, you can effectively manage your MySQL users.
RedSwitches helps customers by offering customizable bare metal servers.
If you’re searching for a reliable server infrastructure for your projects, we provide competitive, dedicated server pricing and ensure swift delivery, typically on the same day of order approval. Whether you require a dedicated server, a traffic-friendly 10Gbps dedicated server, or a high-performance instant dedicated server, we are your trusted hosting partner!
FAQs
Q. How do I grant only specific privileges instead of all?
Replace ALL PRIVILEGES with specific privileges like SELECT, INSERT, etc., in the GRANT command.
Q. What does ‘@localhost’ mean in the commands?
It specifies that the user can connect to the MySQL server from the local machine. For remote access, replace ‘localhost’ with the remote host’s IP address.
Q. How do I ensure my MySQL server is secure?
Regularly update your MySQL version, use strong passwords, and limit privileges based on user roles.
Q. What is MySQL and why is it used?
MySQL is an open-source relational database management system widely used for web applications. It is known for its reliability, scalability, and ease of use, making it a popular choice for managing data.
Q. How do I install MySQL?
You can install MySQL by downloading the MySQL Installer from the official MySQL website and following the installation instructions provided. Alternatively, you can use package managers like apt or yum to install MySQL on Linux systems.
Q. How can I create a new user in MySQL?
You can create a new user in MySQL using the CREATE USER statement in the MySQL shell. After connecting to MySQL, you can use the CREATE USER command to create a new user account with the desired username and password.
Q. What are user privileges in MySQL?
User privileges in MySQL determine what actions a user is allowed to perform on a database. These privileges can include the ability to create, modify, or delete database objects, as well as the ability to perform administrative tasks.
Q. How do I grant permissions to a MySQL user?
You can grant permissions to a MySQL user using the GRANT statement in the MySQL shell. This allows you to specify which privileges to grant and which database objects the user can access.
Q. How can I revoke privileges from a MySQL user?
To revoke privileges from a MySQL user, you can use the REVOKE statement in the MySQL shell. This allows you to remove specific privileges from a user, restricting their access to certain database objects.
Q. Can I grant multiple privileges to a MySQL user?
Yes, you can grant multiple privileges to a MySQL user using the GRANT statement with the appropriate syntax. This allows you to assign various privileges to a user, such as SELECT, INSERT, UPDATE, and DELETE.
Q. How do I grant all privileges to a MySQL user?
To grant all privileges to a MySQL user, you can use the GRANT ALL statement in the MySQL shell. This will grant the user full access to the specified database, allowing them to perform all actions on its objects.
Q. What is the process for reloading the grant tables in MySQL?
To reload the grant tables in MySQL after making changes to user privileges, you can use the FLUSH PRIVILEGES statement in the MySQL shell. This ensures that the changes take effect immediately without the need to restart the MySQL server.
Q. How can I create a MySQL database and grant privileges to a specific user?
To create a MySQL database and grant privileges to a specific user, you can use the CREATE DATABASE statement to create the database and then use the GRANT statement to assign the necessary privileges to the user for that database.
