How to assign permissions to mysql users and mysql users

How to assign permissions to mysql users and mysql users

1. Create a new user in Mysql

Syntax:

1. create user Username identified by 'Password ';

Example: create user xiaogang identified by '20150901 ';

By default, new users do not have any permissions.

2. How to assign permissions to users

Syntax:

1. grant permission on database. data table to 'user' @ 'hostname ';

For example, assign all permissions to xiaogang.

Grant all on *. * to 'xiaohang' @ '% ';

At this time, xiaogang has all the permissions.

3. How can I control user permissions more accurately?

1. grant permission on database. data table to 'user' @ 'hostname ';

For example, let xiaogang have the permission to query table tmp1 in the tmp database;

Grant select on temp. temp1 to 'xiaogang '@' % '; // at this time, xiaogang has the permission to query temp1.

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 permission to perform select, insert, update, delete, create, drop, and other operations on the database's vtdc employee table, and set the password to 123.

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

Assign the user joe from 10.163.225.87 the permission 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 '000000 ′;

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

Mysql> grant all privileges on *. * to joe @ localhost identified by '000000 ′;

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

4. How to revoke permissions generally means that a root user has this permission

Syntax:

1. revoke permission on database. data table from 'user' @ 'hostname ';

For example, revoke all permissions of xiaogang.

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

Now I will summarize the specific process.

Step 1: start and stop the mysql Service

Net stop mysql

Net start mysql

Step 2: log on to mysql directly

Syntax: mysql-u user name-p User Password

Enter the mysql-uroot-p command, press enter and prompt you to enter the password, enter 123456, and then press enter to enter mysql. The mysql prompt is:

Mysql>

Note: If you are connecting to another machine, you need to add a parameter-h Machine IP address.

Step 3: Add new users

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

For example, you can add a user user1 with the password password1 so that the user can log on to the machine and have the permission 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 log on to mysql on any machine, change localhost to "% ".

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

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

Step 4: Operate the database

Log on to mysql and run the following commands at the mysql prompt. Each Command ends with a semicolon.
From: http://www.111cn.net/database/mysql/49129.htm