Create user assignment permissions in MySQL

Test environment: CentOS6.8 and MySQL5.5.4

A demand

In the course of project development, you may need to open your own database to others, but for security reasons, you cannot open other databases on your own server at the same time. You can create a new user to give that user specific database permissions.

Two implementations

1 New User

Root User login Mysqlmysql-uroot-penter password://new user Mysql>insert into Mysql.user (Host,user,password) VALUES (" localhost "," Buff ", Password (" Buff "));//Refresh System Permissions table Mysql>flush privileges;

This creates a user named: buff, Password: buff.

2 Landing Test

mysql>exit//User Buff Login Mysqlmysql-ubuff-penter password:mysql>

Indicates that the new user buff landed successfully.

3 User Authorization

Root User login Mysqlmysql-uroot-penter password://Create a database for user Buff Bluebuffmysql>create db bluebuff;//authorized user Buff Have all permissions for database Bluebuff mysql>grant all privileges on bluebuff.* to [email protected] identified by ' Buff '; mysql>flush privileges;

4 Login Test

User Buff Login Database mysql-ubuff-penter privileges://display database mysql>show databases;

As shown in the results, the user buff authorization was successful

5 Modifying the password of the user buff

Root User Login Mysqlmysql-uroot-penter password://Modify the password of the user Buff Mysql>update table Mysql.user set Password=password (' Bu Ffer ') where user= ' buff ' and host= ' localhost ';
Mysql>flush privileges;

6 Deleting a user

Root User login Mysqlmysql-uroot-penter password://Delete user buffmysql>delete from mysql.user where user = ' buff ' and Host = ' localhost '; mysql>flush privileges;

7 Deleting a database

Mysql>drop database Bluebuff;

Create user assignment permissions in MySQL