This article provides a complete overview of how to create a new user account in MySQL and grant different types of privileges on a MySQL database. Learn the basics of user account management and find hints.
Introduction
First, let’s figure out why we need users and privileges. When you install MySQL Server on your system and create a MySQL database, you run the required commands in the MySQL shell as root or administrative user, meaning that you have the full power to control every aspect of your database.
However, once you decide to let more people access and modify your MySQL database, you need to create users and grant them privileges. By doing so, you can give permissions or introduce restrictions within the databases or certain database objects. To put it simply, you will decide on who can do what and with what object types.
This article provides insight into how to create a new user and grant appropriate privileges on a MySQL database. But not only that. You will also find out more about the creation of custom privileges and learn what types of privileges exist in MySQL. Moreover, you will have extra practice on how to revoke privileges and remove users from MySQL.
Contents
On top of that, you will have an opportunity to view examples within MySQL shell (MySQL command-line client) as well as within dbForge Studio for MySQL, a GUI tool for MySQL and MariaDB database development, management, and administration.
1. Create a new MySQL user account via MySQL Shell
2. Grant privileges and add permissions to user
3. Show all MySQL user account privileges
4. Revoke all privileges from user
5. Remove user from MySQL
6. Manage users and privileges via dbForge Studio for MySQL
7. Create a user account via Security Manager
8. Grant and revoke privileges via Security Manager
How to Create a New MySQL User Account via MySQL Shell
To get started, you need to connect to your MySQL Server instance and log in as a root user via MySQL command-line interface:
mysql -u root -pWhen you do, you also need to type the password for the root account and press Enter:
Enter password: ********To create a new MySQL user account via the MySQL shell, you need to execute the CREATE USER statement. Let’s have a look at its basic syntax:
CREATE USER [IF NOT EXISTS] 'new_user_name'@'host_name'
IDENTIFIED BY 'user_password'In the syntax above, replace new_user_name with the name of the new user and host_name with the name of the host from which the user connects to the MySQL Server.
Optionally, set the host_name to ‘localhost’ if you want the user to be able to connect to MySQL Server only from the localhost, which means “this computer”. If that’s not the case, you can use the remote machine IP address as hostname, for instance:
CREATE USER 'new_user_name'@'10.8.0.5'
IDENTIFIED BY 'user_password';If you want the user to be able to connect from any host, use the ‘%’ wildcard as host_name.
Finally, set a password for the new user after the IDENTIFIED BY keywords.
Note that the IF NOT EXISTS option allows to ensure that the same user has not been created before.
Once you are done with the new user creation, remember to grant privileges to the user to let them access the MySQL database. Otherwise, the user will not have any permissions to reach or manipulate the database in any way.
How to Grant Privileges and Add Permissions to User
To provide a user with access to the database and give permissions, you generally need to use the following GRANT statement:
GRANT permission_type
ON privilege_level
TO 'new_user_name'@'host_name';Although the above-mentioned syntax is rather basic and doesn’t show all subtleties, it points to the conclusion that there are multiple types of privileges (or permissions) that can be provided to a new user. Hence, let’s illustrate the most common types of permissible privileges that can be used for the GRANT and REVOKE statement:
- ALL PRIVILEGES – The user gains all privileges at an access-specified level.
- CREATE – The user gains permission to create databases and tables.
- DROP – The user gains permission to drop databases and tables.
- DELETE – The user gains permission to delete rows from a specific table.
- INSERT – The user gains permission to insert rows into a specific table.
- SELECT – The user gains permission to read a database.
- UPDATE – The user gains permission to update table rows.
Thus, we have clarified what types of permissions exist and defined what to put in the first part of the GRANT command. Now, let’s talk about the second part that follows the ON keyword, namely, the privilege level. By means of the privilege level, you can determine which MySQL objects can be manipulated by the user account: all databases, a specified database, specified tables, certain columns, or certain stored routines within a database. In the following paragraph, we will talk about that in greater detail and provide examples.
Grant Privileges on Database to User
To grant all privileges to a user account on all databases via MySQL command prompt, you need to assign global privileges and use the *.* syntax after the ON keyword:
GRANT ALL PRIVILEGES
ON *.*
TO new_user_name@host_name;In this example, the new user is granted the maximum privilege level possible: they gain the permission to read, modify, execute commands and perform any task across all databases and tables. Be careful as this can compromise your database security and lead to negative consequences.
Instead, you might want to grant limited permissions. For instance, you would like to allow your new user access only a certain table within the database:
GRANT ALL PRIVILEGES
ON database_name.table_name
TO user_name@host_name;In this case, the user is granted table-level privileges, which apply to all columns within the table. Hence, they gain permission to read, edit, and modify the table as required.
However, it may also be necessary to restrict such access and give the possibility to perform certain operations within specified database objects.
For example, below, you can see that the user is granted multiple permissions: they have permission to use the SELECT statement across two columns in the database, execute UPDATE on a third column, and run INSERT across the fourth column within the same database:
GRANT
SELECT (column1,column2),
UPDATE(column3),
INSERT (column4)
ON database_name
TO user_name@host_name;Just as you are finished providing the database access to the new users, make sure to reload all the privileges by running:
FLUSH PRIVILEGES;After that, your changes will take effect.
Show All MySQL User Account Privileges
To display the privileges granted to MySQL user accounts, you need to apply the SHOW GRANTS command:
SHOW GRANTS FOR user_name@host_name;The output of the command looks similar to the following:
+---------------------------------------------------------------------------+
| Grants for user_name@host_name |
+---------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'user_name'@'host_name' |
| GRANT ALL PRIVILEGES ON `database_name`.* TO 'user_name'@'host_name' |
+---------------------------------------------------------------------------+
2 rows in set (0.00 sec)Also, you can learn more about how to create a list of privileges of MySQL database users.
How to Revoke All Privileges from User
If you need to revoke privileges from the user account on a database, apply the syntax that is similar to the one you used when granting permissions:
REVOKE permission_type
ON privilege_level
FROM 'user_name'@'host_name';So, if you, for example, intend to revoke all permissions from the user on a specific database, use the following:
REVOKE ALL PRIVILEGES
ON database_name.*
FROM 'user_name'@'host_name';Remove User from MySQL
If you decide to remove a MySQL user account, execute the DROP USER command through the command line:
DROP USER [IF EXISTS] 'user_name'@'host_name'The command above will remove the user account together with all of its privileges.
Manage Users and Privileges via dbForge Studio for MySQL
While it is feasible to manage users and their privileges via a command-line interface of MySQL Server, it is a lot more convenient and secure to accomplish the same tasks with the help of a reliable database administration tool.
The reason for that is that accurate management of user accounts plays an important role in database security, and since the number of such accounts can reach large numbers, it can get difficult for a DBA to have stable control over them.
Therefore, if you are looking to strengthen your database security and improve administration, you can use a professional tool for easy management of MySQL user accounts. dbForge Studio for MySQL is a universal solution for database development, management, and administration that has an integrated Security Manager tool.
The Security Manager tool is designed to simplify administration and avoid errors. Within a handy graphical interface, you get the possibility to create, edit, and delete user accounts as well as grant or revoke privileges either at global or object levels with just a few clicks. To open Security Manager, go to the Administration tab of the Start page and select Manage Server Security:
After that, the Security Manager window opens. It consists of two parts — the left one displays a list of all user accounts, and the right one allows you to enter and modify the user account’s data.
As can be seen above, the user account’s data is divided into six groups placed on separate tabs. They are as follows:
- General — contains the name, host, password, the maximum quantity of connections/queries/ updates per hour related to the user account.
- Roles — contains roles that may be assigned to a user account (this refers to MariaDB connections only).
- Users — allows applying granted privileges of other users to the user account.
- Global Privileges — allows setting global privileges for the user account.
- Object Privileges — allows setting object privileges for the user account.
- SSL – contains SSL-connection options related to the user account.
Create a User Account via Security Manager
Creating a new user account within Security Manager is as easy as ABC. Click the Create User button above the list of user accounts, and immediately start inserting the necessary information and setting options on the corresponding tabs:
On the General tab enter the aforementioned user account parameters into the corresponding fields and click Save on the toolbar. That’s all, you can now see the newly-created account in the left part of the Security Manager window.
Next time you need to modify any user’s parameters, select the required user in the left part of the Security Manager window and effortlessly edit its parameters in the right part.
Grant and Revoke Privileges via Security Manager
With Security Manager, the management of user accounts becomes time-saving and efficient. Let’s consider an example. Supposing you would like to grant some global privileges (e.g., Create and Insert) and object ones to the Justin@% user. Instead of going into the details of the GRANT statement and running the command via the command prompt, you can simply navigate to the tabs inside dbForge Studio for MySQL and select the necessary options.
On the Users tab of Security Manager, you can choose to apply the privileges granted to other users with a simple mouse click in the corresponding checkbox:
On the Global Privileges tab, select the appropriate checkboxes to grant the required global privileges and clear the selection to revoke the privileges from the user account. In the example below, we choose to provide the Justin@% user with the CREATE and INSERT privileges that apply to all databases:
Next, switch to the Object Privileges tab. Here, you can grant privileges at the object level. First, select the necessary schema on the left and expand the schema tree. Select the objects (such as tables, views, etc.) and specify the required privileges by selecting the checkboxes.
At the bottom of the window, you can see a list of object privileges for the current user account. Besides, you can click the Cancel icon to revoke the selected privileges or revoke all object privileges.
As you can see on the screenshot below, the Justin@% user account obtains permission to run SELECT, INSERT, AND UPDATE scripts across the Actor table. Also, the enabled GRANT OPTION allows the user to grant permissions on this table to other users:
You can save the changes immediately by clicking Save or preview the script by clicking Script Changes to refresh all the changes in your memory:
What’s more, dbForge Studio for MySQL allows you to create a new user based on the existing one, therefore, saving your time and effort. To do this, right-click the user account and select Duplicate Object on the shortcut menu:
Conclusion
To sum up, we have provided a detailed overview of how to best manage users and privileges in MySQL. This information should be useful for beginners as well as experienced DBA’s as it contains basic information and useful tips on efficient management of users’ accounts in MySQL databases. Use dbForge Studio for MySQL to bring your database security to a whole new level and significantly boost your database administration. Also, you can watch this video tutorial:
