How to assign permissions to MySQL users

Create a new user under 1,mysql

Grammar:

1.create user username identified by ' password ';

Example: Create user Xiaogang identified by ' 123456 ';

The newly created user, by default, does not have any permissions.

2. How to assign permissions to users

Grammar:

1.grant permissions on the database. Data table to ' user ' @ ' hostname ';

Example: Assigning all permissions to a Xiaogang

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

This time, Xiaogang has all the authority.

3 How to control the user's permissions more precisely?

1.grant permissions on the database. Data table to ' user ' @ ' hostname ';

Example: Let Xiaogang have the permission to query the TMP database TMP1 table;

Grant SELECT on Temp.temp1 to ' Xiaogang '% '; At this time Xiaogang has the ability to query the Temp1 of temp small.

For example:

Mysql>grant Select,insert,update,delete,create,drop on Vtdc.employee to [e-mail protected] identified by ' 123′;

Assign the user Joe from 10.163.225.87 the ability to perform operations such as Select,insert,update,delete,create,drop on the employee table VTDC the database, and set the password to 123.

Mysql>grant all privileges in vtdc.* to [e-mail protected] identified by ' 123′;

For users from 10.163.225.87, Joe assigns permissions to all operations on the database VTDC all tables, and sets the password to 123.

Mysql>grant all privileges on * * to [e-mail protected] identified by ' 123′;

For users from 10.163.225.87, Joe assigns permissions to all the tables in all databases and sets the password (www.111cn.net) to 123.

Mysql>grant all privileges on * * to [e-mail protected] identified by ' 123′;

Assign the native user Joe permission to all operations on all tables in all databases, and set the password to 123.

4. How to recover permissions, generally referred to as a root user to have this permission

Grammar:

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

Example: Recovering all permissions for a Xiaogang

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

Okay, here's a few steps to summarize a very specific process.

First step: Start and stop of MySQL service

net stop MySQL

net start MySQL

Step two: Login to MySQL directly

The syntax is as follows: Mysql-u user name-p user Password

Type the command mysql-uroot-p, enter the password after entering, enter 123456, and then enter into the MySQL, MySQL prompt is:

Mysql>

Note that if you are connecting to another machine, you need to add a parameter-H machine IP

Step three: Add new users

Format: Grant permissions on database. * To User name @ login host identified by "password"

For example, add a user user1 password to Password1, so that it can log on to the computer, and all databases have query, insert, modify, delete permissions. First, use the root user to connect to MySQL, and then type the following command:

Grant Select,insert,update,delete on * * to [e-mail protected] identified by "Password1";

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

If you do not want to User1 have a password, you can make another command to remove the password.

Grant Select,insert,update,delete on mydb.* to [e-mail protected] identified by "";

Fourth step: Manipulating the Database

Log in to MySQL, and then run the following command at the prompt of MySQL, with each command ending with a semicolon
From:http://www.111cn.net/database/mysql/49129.htm

How to assign permissions to MySQL users