Tutorial
How To Create a New User and Grant Permissions in MySQL
Introduction
MySQL is an open-source relational database management system. It is commonly deployed as part of the LAMP stack (which stands for Linux, Apache, MySQL, and PHP) and, as of this writing, is the most popular open-source database in the world.
This guide outlines how to create a new MySQL user and grant them the permissions needed to perform a variety of actions.
Prerequisites
In order to follow along with this guide, you’ll need access to a MySQL database. This guide assumes that this database is installed on a virtual private server running Ubuntu 20.04, though the principles it outlines should be applicable regardless of how you access your database.
If you don’t have access to a MySQL database and would like to set one up yourself, you can follow one of our guides on How To Install MySQL. Again, regardless of your server’s underlying operating system, the methods for creating a new MySQL user and granting them permissions will generally be the same.
You could alternatively spin up a MySQL database managed by a cloud provider. For details on how to spin up a DigitalOcean Managed Database, see our product documentation.
Please note that any portions of example commands that you need to change or customize will be highlighted like this throughout this guide.
Creating a New User
Upon installation, MySQL creates a root user account which you can use to manage your database. This user has full privileges over the MySQL server, meaning it has complete control over every database, table, user, and so on. Because of this, it’s best to avoid using this account outside of administrative functions. This step outlines how to use the root MySQL user to create a new user account and grant it privileges.
In Ubuntu systems running MySQL 5.7 (and later versions), the root MySQL user is set to authenticate using the auth_socket plugin by default rather than with a password. This plugin requires that the name of the operating system user that invokes the MySQL client matches the name of the MySQL user specified in the command. This means that you need to precede the mysql command with sudo to invoke it with the privileges of the root Ubuntu user in order to gain access to the root MySQL user:
Note: If your root MySQL user is configured to authenticate with a password, you will need to use a different command to access the MySQL shell. The following will run your MySQL client with regular user privileges, and you will only gain administrator privileges within the database by authenticating with the correct password:
Once you have access to the MySQL prompt, you can create a new user with a CREATE USER statement. These follow this general syntax:
After CREATE USER, you specify a username. This is immediately followed by an @ sign and then the hostname from which this user will connect. If you only plan to access this user locally from your Ubuntu server, you can specify localhost. Wrapping both the username and host in single quotes isn’t always necessary, but doing so can help to prevent errors.
You have several options when it comes to choosing your user’s authentication plugin. The auth_socket plugin mentioned previously can be convenient, as it provides strong security without requiring valid users to enter a password to access the database. But it also prevents remote connections, which can complicate things when external programs need to interact with MySQL.
As an alternative, you can leave out the WITH authentication_plugin portion of the syntax entirely to have the user authenticate with MySQL’s default plugin, caching_sha2_password. The MySQL documentation recommends this plugin for users who want to log in with a password due to its strong security features.
Run the following command to create a user that authenticates with caching_sha2_password. Be sure to change sammy to your preferred username and password to a strong password of your choosing:
Note: There is a known issue with some versions of PHP that causes problems with caching_sha2_password. If you plan to use this database with a PHP application — phpMyAdmin, for example — you may want to create a user that will authenticate with the older, though still secure, mysql_native_password plugin instead:
If you aren’t sure, you can always create a user that authenticates with caching_sha2_plugin and then ALTER it later on with this command:
After creating your new user, you can grant them the appropriate privileges.
Granting a User Permissions
The general syntax for granting user privileges is as follows:
The PRIVILEGE value in this example syntax defines what actions the user is allowed to perform on the specified database and table. You can grant multiple privileges to the same user in one command by separating each with a comma. You can also grant a user privileges globally by entering asterisks (*) in place of the database and table names. In SQL, asterisks are special characters used to represent “all” databases or tables.
To illustrate, the following command grants a user global privileges to CREATE, ALTER, and DROP databases, tables, and users, as well as the power to INSERT, UPDATE, and DELETE data from any table on the server. It also grants the user the ability to query data with SELECT, create foreign keys with the REFERENCES keyword, and perform FLUSH operations with the RELOAD privilege. However, you should only grant users the permissions they need, so feel free to adjust your own user’s privileges as necessary.
You can find the full list of available privileges in the official MySQL documentation.
Run this GRANT statement, replacing sammy with your own MySQL user’s name, to grant these privileges to your user:
Note that this statement also includes WITH GRANT OPTION. This will allow your MySQL user to grant any permissions that it has to other users on the system.
Warning: Some users may want to grant their MySQL user the ALL PRIVILEGES privilege, which will provide them with broad superuser privileges akin to the root user’s privileges, like so:
Such broad privileges should not be granted lightly, as anyone with access to this MySQL user will have complete control over every database on the server.
Many guides suggest running the FLUSH PRIVILEGES command immediately after a CREATE USER or GRANT statement in order to reload the grant tables to ensure that the new privileges are put into effect:
However, according to the official MySQL documentation, when you modify the grant tables indirectly with an account management statement like GRANT, the database will reload the grant tables immediately into memory, meaning that the FLUSH PRIVILEGES command isn’t necessary in our case. On the other hand, running it won’t have any negative effect on the system.
If you need to revoke a permission, the structure is almost identical to granting it:
Note that when revoking permissions, the syntax requires that you use FROM, instead of TO which you used when granting the permissions.
You can review a user’s current permissions by running the SHOW GRANTS command:
Just as you can delete databases with DROP, you can use DROP to delete a user:
After creating your MySQL user and granting them privileges, you can exit the MySQL client:
In the future, to log in as your new MySQL user, you’d use a command like the following:
The -p flag will cause the MySQL client to prompt you for your MySQL user’s password in order to authenticate.
Conclusion
By following this tutorial, you’ve learned how to add new users and grant them a variety of permissions in a MySQL database. From here, you could continue to explore and experiment with different permissions settings for your MySQL user, or you may want to learn more about some higher-level MySQL configurations.
For more information about the basics of MySQL, you can check out the following tutorials:
Want to launch a high-availability MySQL cluster in a few clicks? DigitalOcean offers worry-free MySQL managed database hosting. We’ll handle maintenance and updates and even help you migrate your database from external servers, cloud providers, or self-hosted solutions. Leave the complexity to us, so you can focus on building a great application.
About the author(s)
This textbox defaults to using Markdown to format your answer.
You can type !ref in this text area to quickly search our full set of tutorials, documentation & marketplace offerings and insert the link!
Become a contributor for community
Get paid to write technical tutorials and select a tech-focused charity to receive a matching donation.
DigitalOcean Documentation
Full documentation for every DigitalOcean product.
Resources for startups and SMBs
The Wave has everything you need to know about building a business, from raising funding to marketing your product.
The developer cloud
Scale up as you grow — whether you're running one virtual machine or ten thousand.
Get started for free
Sign up and get $200 in credit for your first 60 days with DigitalOcean.*
*This promotional offer applies to new accounts only.
You can grant multiple privileges in one command by separating them with commas: eg: “GRANT UPDATE, SELECT ON [database name].[table name] TO ‘[username]’@‘localhost’;”
This code from above has a backtick before localhost. It should be a single quote.
GRANT ALL PRIVILEGES ON * . * TO ‘newuser’@‘localhost’;
Updated
Hi, nice intro. It was useful. I noticed that for the REVOKE command, one has to use FROM, not TO. Also, might be helpful for new users to know that they can use ‘%’ as a wildcard instead of ‘localhost’.
This worked for me. However to be able to use MySql Workbench it seems it wants another version of the user. I needed to do the following (which has taken me a few hours of playing around with to get right) mysql> select user,host from mysql.user; <–to see users mysql> GRANT ALL ON . to user@’%’ IDENTIFIED BY ‘user-pwd’; mysql> FLUSH PRIVILEGES; mysql> select user,host from mysql.user; mysql>quit Also need to comment out or change the bind-address to <droplet address>. This does reduce security. sudo nano /etc/mysql/my.cnf ;bind-address=127.0.0.1 exit and $service mysql start $service mysql stop
then get access on <droplet-ip> from my Sql workbench using user/user-pwd on std port for adminstering, creating and querying.
There’s no link at the start of this tutorial to the first tutorial. Can you please add that link?
useful
how to give permission to only select views in requried user pls send me urgent
@ravuri.srinivasarao7: Please read the second part of the article: “How To Grant Different User Permissions”
Is there a way to just give permission to create a new table within the specified database, but not allow the creation of a new database?
@bluethrustweb: Yes, of course:
GRANT CREATE ON
databaseTO ‘user’@‘host’;Am a new comer in mysql server, I don’t even why are creating these users and grant to them privileges! may i have some description plz?
@mzengaekamkulu: What do you mean? You have to create a user for each app you use so it can connect to the mysql server.
How to configure remote access to my mysql?
where the heck is Part 1 of the Tut?
@info: Part 1: <a href=“https://www.digitalocean.com/community/articles/a-basic-mysql-tutorial”>https://www.digitalocean.com/community/articles/a-basic-mysql-tutorial</a>
Very easy and clear! Thanks for these worthy and handy tutorials!
You have an error in your REVOKE command. Instead of:
REVOKE [type of permission] ON [database name].[table name] TO ‘[username]’@‘localhost’;
it should be:
REVOKE [type of permission] ON [database name].[table name] FROM ‘[username]’@‘localhost’;
@fernandoaleman: Thanks! I’ve correct the article.
Hi,
I have done all the above but I can’t seem to log with the new user with:
mysql -u [username]-p
I have the error:
ERROR 1045 (28000): Access denied for user ‘prosper202’@‘localhost’ (using password: YES)
What may be the problem?
Hi there,
In case that anyone hits this problem in the future as well, here are a few steps you can take to troubleshoot and resolve this issue:
Check Username and Password:
Verify User Exists in MySQL:
prosper202is allowed to connect. Iflocalhostis not listed, you’ll need to update the host from which the user can connect or add a new entry forlocalhost.Check Privileges:
Reset the Password:
'newpassword'with a secure password of your choice.Make sure you are using the correct syntax for the command line. There should be a space between the
-uflag and the username, and no space between-pand the password if you are directly inputting it (though it’s more secure to enter the password when prompted):After you hit enter, you should be prompted to enter the password.
Ok I solved the issue!
The problem was that I changed the localhost to my servers IP/Name instead of letting it as it is …
Thank you
Is it possible to let a user only be able to edit certen parts of a table?
Example, user1 has admin rights of table1, talbe2, table3, where ID = 3
@digitalocean: As far as I know, no, it’s not possible.
Great brother Etel Sverdlov!
Great article! My hat is off to you my good sir!
Questions; what is the cmd(s) for showing what privileges all users have? Such as the “li -l” cmd that list all directories, permissions, and user groups?
It appears that both:
mysql> show grants for ‘username’@‘%’;
or
mysql> show grants for ‘username’@‘localhost’;
Would show what “privileges” the user has, however, after following this article and granting all privileges to a user, the show grants cmd provides me with the following:
±-----------------------------------------------+ | Grants for username@% | ±-----------------------------------------------+ | GRANT ALL PRIVILEGES ON . TO ‘username’@‘%’ … | ±-----------------------------------------------+ 1 row in set (0.00 sec)
If I am reading this correctly, this user has been granted all privileges for all databases?
@miller.t.chris
Right, because in the tutorial, we ran:
GRANT ALL PRIVILEGES ON * . * TO ‘newuser’@‘localhost’;
hello when I am typing the command
CREATE USER ‘www’@‘172.16.49.101’ IDENTIFIED BY ‘myreallysecurepassword’; i get the error?
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘USER ‘www’@‘172.16.49.101’ IDENTIFIED BY ‘myreallysecurepassword’’ at line 1
You guys have an article for everything…so psycho its awesome!
Here are a few lines to create a MySQL user to handle only the db of his website (change mydatabase, myuser, mypassword and 165.65.65.100)
Help me a lot, thanks sweet!
Awesome! Glad to hear it :)
thanks for this great article.
This comment has been deleted
Really, really, really good article/guide! I was wondering how to create users for my database, well. Now I almost now. I have a question though. Is it possible to create users and grants through the database in forms instead of code? I have been working with MySQL for years, but only creatings tables, posts, editing and stuff like that - im scared I might f*ck it up and accidenlty do something wrong so my site wont respond.
If not - where should I put the code to create users and granst? I need this for my admin-system, instead of on my site, I’m gonna use the database to grant selected people access. The login-system is much more ‘safer’ than I could ever programme it.
This code (down from here) where should I put that? CREATE USER ‘newuser’@‘localhost’ IDENTIFIED BY ‘password’;
Technically all you have to do for the new user is create a new database and then give them privileges on that db… It might make sense as a security precaution to only grant all privs on the new db and not globally. You are basically cloning the root account… Twice as many breach vectors no?
It’s not working on CentOS 7 with MariaDB. Query OK, 0 rows affected (0.00 sec)
might be nice to add an example of creating a database and granting all permissions to “just that database” to a user.
For followers, it’s like this (create user, as described) then
create database database_name; grant all on database_name.* to ‘username’@‘localhost’;
This comment has been deleted
hello. i have done this, but now getting ’ Internal Server Error http://178.62.20.142/
Thanks for the article - I know roughly the commands but I always screw up the syntax slightly, so I always come here to make sure I get it right.
it was happent to me use only root and pwd, so far, well just learnt how to create a new user and set permission , it helped me a lot, thanks!
This comment has been deleted
This comment has been deleted
GRANT ALL PRIVILEGES ON TP1.(TABLE1,TABLE2) TO ‘bader’@‘localhost’ IDENTIFIED BY ‘123’ ; IS THAT CORRECT?
Thanks for the great tutorial. One comment: apparently FLUSH PRIVILEGES is unnecessary in most cases:
http://dbahire.com/stop-using-flush-privileges/
Just want to put it out there that if you intend to have multiple databases setup, it is always best to have one single user for each db. Something like this:
GRANT ALL PRIVILEGES ON thedatabase . * TO ‘thedatabase_user’@‘localhost’;
This isolates the databases so if one is compromised, the other wont be. Setting . is just asking for trouble.
This code works for me Thanks :-)
Useful intro to grants of mysql. Thx!
When testing a new user just created, after quit (exiting). Login as that user using the following command: mysql -u [username] -p, this is what I’m getting, it didn’t prompt me for the password:
mysql> mysql -u student1 -ppassword ->
I’m trying to fix the following issue:
mysql> use mydb; ERROR 1044 (42000): Access denied for user ‘root’@‘::1’ to database ‘mydb’
I granted all privileges to student1.
Thank You!!!