June 12, 2012

Beginner

How To Create a New User and Grant Permissions in MySQL

Tagged In: My Sql

What the Red Means

The lines that the user needs to enter or customize will be in red in this tutorial!

The rest should mostly be copy-and-pastable.

About MySQL

MySQL is an open source database management software that helps users store, organize, and later retrieve data. It has a variety of options to grant specific users nuanced permissions within the tables and databases—this tutorial will give a short overview of a few of the many options.

How to Create a New User

In Part 1 of the MySQL Tutorial, we did all of the editing in MySQL as the root user, with full access to all of the databases. However, in the cases where more restrictions may be required, there are ways to create users with custom permissions.

Let’s start by making a new user within the MySQL shell:
CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password';

Sadly, at this point newuser has no permissions to do anything with the databases. In fact, if newuser even tries to login (with the password, password), they will not be able to reach the MySQL shell.

Therefore, the first thing to do is to provide the user with access to the information they will need.
GRANT ALL PRIVILEGES ON * . * TO 'newuser'@'localhost';

The asterisks in this command refer to the database and table (respectively) that they can access—this specific command allows to the user to read, edit, execute and perform all tasks across all the databases and tables.

Once you have finalized the permissions that you want to set up for your new users, always be sure to reload all the privileges.
FLUSH PRIVILEGES;

Your changes will now be in effect.

How To Grant Different User Permissions

Here is a short list of other common possible permissions that users can enjoy.

  • ALL PRIVILEGES- as we saw previously, this would allow a MySQL user all access to a designated database (or if no database is selected, across the system)

  • CREATE- allows them to create new tables or databases

  • DROP- allows them to them to delete tables or databases

  • DELETE- allows them to delete rows from tables

  • INSERT- allows them to insert rows into tables

  • SELECT- allows them to use the Select command to read through databases

  • UPDATE- allow them to update table rows

  • GRANT OPTION- allows them to grant or remove other users' privileges

To provide a specific user with a permission, you can use this framework:
 GRANT [type of permission] ON [database name].[table name] TO ‘[username]’@'localhost’;

If you want to give them access to any database or to any table, make sure to put an asterisk (*) in the place of the database name or table name.

Each time you update or change a permission be sure to use the Flush Privileges command.

If you need to revoke a permission, the structure is almost identical to granting it:
 REVOKE [type of permission] ON [database name].[table name] FROM ‘[username]’@‘localhost’;

Just as you can delete databases with DROP, you can use DROP to delete a user altogether:
 DROP USER ‘demo’@‘localhost’;

To test out your new user, log out by typing
 quit 
and log back in with this command in terminal:
mysql -u [username]-p




By Etel Sverdlov

Share this Tutorial

Vote on Hacker News

Try this tutorial on an SSD cloud server.

Includes 512MB RAM, 20GB SSD Disk, and 1TB Transfer for $5/mo! Learn more

Create an account or login:

23 Comments

Write Tutorial
  • Gravatar Chris about 1 year

    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’;"

  • Gravatar Sebastian about 1 year

    This code from above has a backtick before localhost. It should be a single quote. GRANT ALL PRIVILEGES ON * . * TO 'newuser'@‘localhost';

  • Gravatar Etel Sverdlov about 1 year

    Updated

  • Gravatar Jason about 1 year

    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'.

  • Gravatar neilh20 about 1 year

    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; 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 . 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 from my Sql workbench using user/user-pwd on std port for adminstering, creating and querying.

  • Gravatar Graham Freeman about 1 year

    There's no link at the start of this tutorial to the first tutorial. Can you please add that link?

  • Gravatar nulikiran4u 11 months

    useful

  • Gravatar ravuri.srinivasarao7 10 months

    how to give permission to only select views in requried user pls send me urgent

  • Gravatar Kamal Nasser 10 months

    @ravuri.srinivasarao7: Please read the second part of the article: "How To Grant Different User Permissions"

  • Gravatar bluethrustweb 9 months

    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?

  • Gravatar Kamal Nasser 9 months

    @bluethrustweb: Yes, of course: GRANT CREATE ON `database` TO 'user'@'host';

  • Gravatar mzengaekamkulu 9 months

    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?

  • Gravatar Kamal Nasser 9 months

    @mzengaekamkulu: What do you mean? You have to create a user for each app you use so it can connect to the mysql server.

  • Gravatar bda.awk 6 months

    How to configure remote access to my mysql?

  • Gravatar info 6 months

    where the *heck* is Part 1 of the Tut?

  • Gravatar Kamal Nasser 6 months

    @info: Part 1: https://www.digitalocean.com/community/articles/a-basic-mysql-tutorial

  • Gravatar mario.medarz 6 months

    Very easy and clear! Thanks for these worthy and handy tutorials!

  • Gravatar fernandoaleman 6 months

    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’;

  • Gravatar Kamal Nasser 6 months

    @fernandoaleman: Thanks! I've correct the article.

  • Gravatar petre.octavian 4 months

    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?

  • Gravatar petre.octavian 4 months

    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

  • Gravatar digitalocean 4 months

    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

  • Gravatar Kamal Nasser 4 months

    @digitalocean: As far as I know, no, it's not possible.

Leave a Comment

Create an account or login:
Ajax-loader