How To Create a New User and Grant Permissions in MySQL cấp quyền (ok)

https://www.digitalocean.com/community/tutorials/how-to-create-a-new-user-and-grant-permissions-in-mysql

How To Create a New User and Grant Permissions in MySQL

MySQL

• 

• By Etel SverdlovLast Validated onSeptember 29, 2020 Originally Published onJune 12, 2012 12.3mviews

English Deutsch Español Français Bahasa Indonesia 日本語 Português РусскийEnglish

• English

• Deutsch

• Español

• Français

• Bahasa Indonesia

• 日本語

• Português

• Русский

Introduction

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.

What the Highlights Mean

Throughout this tutorial, any lines that the user needs to enter or customize will be highlighted! The rest should mostly be copy-and-pastable.

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

Copy

Note: When adding users within the MySQL shell in this tutorial, we will specify the user’s host as localhost and not the server’s IP address. localhost is a hostname which means “this computer,” and MySQL treats this particular hostname specially: when a user with that host logs into MySQL it will attempt to connect to the local server by using a Unix socket file. Thus, localhost is typically used when you plan to connect by SSHing into your server or when you’re running the local mysql client to connect to the local MySQL server.

At this point newuser has no permissions to do anything with the databases. In fact, even if newuser 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';

Copy

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.

Please note that in this example we are granting newuser full root access to everything in our database. While this is helpful for explaining some MySQL concepts, it may be impractical for most use cases and could put your database’s security at high risk.

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;

Copy

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 full access to a designated database (or if no database is selected, global access 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';

Copy

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

Copy

Note that when revoking permissions, the syntax requires that you use FROM, instead of TO as we used when granting permissions.

You can review a user’s current permissions by running the following:

SHOW GRANTS FOR 'username'@'localhost';

Copy

Just as you can delete databases with DROP, you can use DROP to delete a user altogether:

DROP USER 'username'@'localhost';

Copy

To test out your new user, log out by typing:

quit

Copy

and log back in with this command in terminal:

mysql -u [username] -p

Copy

Conclusion

After completing this tutorial, you should have a sense of 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 database, or you may want to learn more about some higher-level MySQL configurations.

For more information about the basics of MySQL, we encourage you to check out the following tutorials:

• How To Create and Manage Databases in MySQL and MariaDB on a Cloud Server

• How To Import and Export Databases and Reset a Root Password in MySQL

• How To Migrate a MySQL Database Between Two Servers

• How To Set Up Master Slave Replication in MySQL

Was this helpful?YesNo 56Report an issueAbout the authorsEtel Sverdlov

Director of Community @DigitalOcean.

Still looking for an answer?

Ask a questionSearch for more help

• Comments

56 Comments

• B

• I

• UL

• OL

• Link

• Code

• Highlight

• Table

Sign In to Comment

• 3chris96043 February 13, 2013You can grant multiple privileges in one command by separating them with commas: eg: "GRANT UPDATE, SELECT ON [database name].[table name] TO ‘[username]’@‘localhost’;"Reply Report

• 1f.bagnardi March 6, 2013This code from above has a backtick before localhost. It should be a single quote. GRANT ALL PRIVILEGES ON * . * TO 'newuser'@‘localhost';Reply Report

• 1etel March 6, 2013UpdatedReply Report

• 0digitalocean114189 March 12, 2013Hi, 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'.Reply Report

• 0neilh20 March 13, 2013This 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.Reply Report

• 0ClockworkHero March 31, 2013There's no link at the start of this tutorial to the first tutorial. Can you please add that link?Reply Report

• 0nulikiran4u May 23, 2013usefulReply Report

• 0ravuri.srinivasarao7 June 28, 2013how to give permission to only select views in requried user pls send me urgentReply Report

• 0kamaln7 June 28, [e-mail]7: Please read the second part of the article: "How To Grant Different User Permissions"Reply Report

• 2bluethrustweb July 28, 2013Is 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?Reply Report

• 0kamaln7 July 28, 2013@bluethrustweb: Yes, of course: GRANT CREATE ON `database` TO 'user'@'host';Reply Report

• 0mzengaekamkulu August 2, 2013Am 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?Reply Report

• 0kamaln7 August 2, 2013@mzengaekamkulu: What do you mean? You have to create a user for each app you use so it can connect to the mysql server.Reply Report

• 0bda.awk October 8, 2013How to configure remote access to my mysql?Reply Report

• 0info268212 October 11, 2013where the *heck* is Part 1 of the Tut?Reply Report

• 0kamaln7 October 12, 2013@info: Part 1: https://www.digitalocean.com/community/articles/a-basic-mysql-tutorialA Basic MySQL Tutorialby Etel SverdlovMySQL is a powerful database management system used for organizing and retrieving data. This tutorial explains how to access the MySQL Shell, how to create and delete a new database, how to create and delete a new table, and how to add, delete, and update columns and rows.Reply Report

• 0mario.medarz October 19, 2013Very easy and clear! Thanks for these worthy and handy tutorials!Reply Report

• 0fernandoaleman October 21, 2013You 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’;Reply Report

• 0kamaln7 October 22, 2013@fernandoaleman: Thanks! I've correct the article.Reply Report

• 3petre.octavian December 12, 2013Hi, 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?