Shulou(Shulou.com)06/01 Report--

Editor to share with you the method of mysql to add permissions to users, I believe that most people do not know much about it, so share this article for your reference, I hope you can learn a lot after reading this article, let's go to know it!

Mysql adds permissions to users: first create a user with the code [create user username identified by 'password], and then assign permissions to the user with the code [grant permissions on database. Data table to 'user' @ 'hostname'].

The method of adding permissions to users by mysql:

First, create new users under Mysql

Syntax:

Create user username identified by 'password'

Example

Create user xiaogang identified by '123456'

Newly created users do not have any permissions by default.

Second, how to assign permissions to users

Syntax:

Grant permissions on database. Data table to 'user' @ 'hostname'

Example: assign all permissions to xiaogang

Grant all on *. * to 'xiaogang'@'%'

At this point, xiaogang has all the permissions.

Third, how to more accurately control the rights of users?

1. Grant permissions on database. Data table to 'user' @ 'hostname'

Example: let xiaogang have the right to query tmp1 table in tmp database

Grant select on temp.temp1 to 'xiaogang'@'%'; / / at this point xiaogang has the right to query the temp1 with a small temp.

For example:

Mysql > grant select,insert,update,delete,create,drop on vtdc.employee to joe@10.163.225.87 identified by '123'

Assign the user joe from 10.163.225.87 the right to select,insert,update,delete,create,drop and other operations on the employee table of the database vtdc, and set the password to 123.

Mysql > grant all privileges on vtdc.* to joe@10.163.225.87 identified by '123'

Assign user joe from 10.163.225.87 permissions to perform all operations on all tables in the database vtdc, and set the password to 123.

Mysql > grant all privileges on *. * to joe@10.163.225.87 identified by '123'

Assign user joe from 10.163.225.87 to perform all operations on all tables in all databases, and set the www.111cn.net to 123.

Mysql > grant all privileges on *. * to joe@localhost identified by '123'

Assign the local user joe permission to perform all operations on all tables in all databases and set the password to 123.

4. How to revoke the authority. Generally speaking, only the root user has the right.

Syntax:

1.revoke permissions on database. Data table from 'user' @ 'hostname'

Example: take back all the permissions of xiaogang

Revoke all on *. * from 'xiaogang' @'%'

All right, let me summarize the steps, a very specific process.

Step 1: start and stop the mysql service

Net stop mysqlnet start mysql

Step 2: log in to mysql directly

The syntax is as follows: mysql-u username-p user password

Type the command mysql-uroot-p, enter and prompt you to enter your password, enter 123456, and then enter the mysql. The prompt for mysql is:

Mysql >

Note that if you are connected to another machine, you need to add a parameter-h machine IP

Step 3: add new users

Format: grant permission on database. * to user name @ login host identified by "password"

For example, add a user's user1 password to password1, so that it can log in on the local computer, and have the authority to query, insert, modify and delete all databases. First use the root user to connect to mysql, and then type the following command:

Grant select,insert,update,delete on *. * to user1@localhost Identified by "password1"

If you want the user to be able to log in to mysql on any machine, change localhost to "%".

If you don't want user1 to have a password, you can issue another command to remove the password.

Grant select,insert,update,delete on mydb.* to user1@localhost identified by ""

Step 4: operate the database

Log in to mysql and run the following commands at the mysql prompt, each ending with a semicolon

The above is all the contents of the method of adding permissions to users by mysql. Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.