MySQL User and Rights management

User :
MySQL user does not have a relationship with the operating system
User-exposed, password-encrypted encryption method: password function

User account:
User name @ Host
User name: within 16 characters
Host:
Host name: www.benet.com, MySQL
ip:192.168.1.1
Network address:
192.168.1.0/255.255.255.0

Wildcard character:%,_
192.168.%.%
%.benet.com

Two ways to create a user:
1. Create with a command. Most basic permissions, usually after the establishment is complete, the permission list is automatically read
Syntax:
Create user [email protected] [identified by ' password ']
Example: Creating the users as test, The host is a wildcard character, the password is test
mysql> CREATE USER [email protected] '% ' identified by ' test ';

2. Insert user information directly into the Mysql.user table
INSERT INTO Mysql.user
Insert data does not re-read permissions, requires manual refresh
mysql> flush privileges;

Remove Users
Drop user ' username ' @ ' localhost ';
Rename User
RENAME user old username to new user name;

Permissions
Set user rights (new user when no user exists)
GRANT permission list on database name. Table name to User name @ source address [identified by ' Password ']
View the user's permissions
SHOW GRANTS for username @ domain name or IP
Revoke user's permissions
REVOKE permission list on database name. Table name from user name @ domain name or IP

action:
View User Rights
Mysql> SHOW GRANTS for [email protected] '% ';

+-----------------------------------------------------------------------------------------------------+|  grants for [email protected]%                                                                                       |+ -----------------------------------------------------------------------------------------------------+|  grant usage on *.* to  ' Test ' @ '% '  IDENTIFIED BY PASSWORD  ' * 94bdcebe19083ce2a1f959fd02f964c7af4cfc29 '  |+------------------------------------------------------------- ----------------------------------------+ 

Log in to the database using the test user
Viewing a database sees only one database
Mysql> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| Information_schema |
+-------------------+
Failed to create a database
mysql> CREATE DATABASE AA;
ERROR 1044 (42000): Access denied for user ' test ' @ '% ' to database ' AA '

Allow test user to create database
Mysql> GRANT CREATE on testdb.* to ' test ' @ '% ';


Create database with test user without error
mysql> CREATE DATABASE TestDB;
Query OK, 1 row Affected (0.00 sec)
Access to TestDB database creation table succeeded
mysql> use TestDB;
Database changed
Mysql> CREATE TABLE testtb (id INT UNSIGNED auto_increment not NULL, Name CHAR, PRIMARY KEY (id));
Query OK, 0 rows affected (0.21 sec)

Inserting data into the TESTTB table error
Mysql> INSERT into TestDB (Name) VALUES (' Zhangsan ');
ERROR 1142 (42000): INSERT command denied to user ' test ' @ ' localhost ' for table ' TestDB '

Grant test user permission to insert data into all tables in testdb.*
Mysql> GRANT INSERT on testdb.* to ' test ' @ '% ';

Test user re-connects to database (must reconnect database) Insert data successfully
Mysql> INSERT into TESTTB (Name) VALUES (' Zhangsan ');
Query OK, 1 row affected (0.05 sec)

Allow test users to add fields
Mysql> GRANT ALTER on testdb.* to ' test ' @ '% ';
Query OK, 0 rows Affected (0.00 sec)

mysql> FLUSH privileges;
Query OK, 0 rows Affected (0.00 sec)

Use the test user to insert an age field into the TESTTB table
mysql> ALTER TABLE testtb ADD age TINYINT UNSIGNED;


Set test user can only change age field
Mysql> SELECT * from TESTTB;
+----+----------+------+
| ID | Name | Age |
+----+----------+------+
| 1 | Zhangsan | NULL |
+----+----------+------+
Authorization test can change the age field
Mysql> GRANT UPDATE (age), TESTDB.TESTTB to ' test ' @ '% ';

Verify test change user age success, change name error
mysql> UPDATE testtb SET age=30 WHERE id=1;
Query OK, 1 row affected (0.06 sec)
Rows matched:1 changed:1 warnings:0

mysql> UPDATE testtb SET name=lisi WHERE id=1;
ERROR 1143 (42000): UPDATE command denied to user ' test ' @ ' localhost ' for column ' Name ' in table ' TESTTB '

To view your own permissions
Field-level permissions are stored separately
mysql> SHOW GRANTS for ' test ' @ '% ';

+-----------------------------------------------------------------------------------------------------+|  grants for [email protected]%                                                                                       |+ -----------------------------------------------------------------------------------------------------+|  grant usage on *.* to  ' Test ' @ '% '  IDENTIFIED BY PASSWORD  ' * 94bdcebe19083ce2a1f959fd02f964c7af4cfc29 '  | |  GRANT SELECT, INSERT, CREATE, ALTER ON  ' TestDB '. * TO  ' Test ' @ '% '                                        | |  GRANT UPDATE  (age)  ON  ' TestDB '. ' TESTTB '  TO  ' Test ' @ '% '                                                   |+------------------------------------------------------------ -----------------------------------------+

Recover User's SELECT permission
Mysql> REVOKE SELECT on testdb.* from ' Test ' @ '% ';

This article from "Plum blossom fragrance from bitter cold" blog, please be sure to keep this source http://wangjunkang.blog.51cto.com/8809812/1586089

MySQL User and Rights management