MySQL User Rights Management

Rights Management:

User:
System level: The user is a user name ===> user name: password
What can I do?
1. Login System
2. Management System objects
Mysql:
is no longer a simple user name and password
User name @ ' host range ' host range is called whitelist
Host Range:

Local Connection::
localhost
**

Remote connection: * *
10.0.0.200
Oldboy.com
10.0.0.% Network Segment
10.0.0.5%
% XXXX is not recommended for

When developing the application user, provide to the management:
[Email protected] ' 10.0.0.% ' Password: xxxx

Permissions: All Privileges
SELECT, INSERT, UPDATE, DELETE, CREATE, RELOAD,
SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER,
SHOW DATABASES, SUPER, CREATE temporary TABLES, DROP
LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT,
Create VIEW, SHOW view, create ROUTINE,
ALTER ROUTINE, create USER, EVENT, TRIGGER, create tablespace

Permissions that developers might use:
Create update Insert Select CREATE VIEW create ROUTINE SHOW view
CREATE Temporary TABLES ALTER

Grant permission on permission scope to user identified by ' password '

.
Py.
Py.t1
Grant Create,update,insert,select, create VIEW on py. to [e-mail protected] ' 192.168.12.% ' identified by ' 123 ';

Select User,host,password from Mysql.user;

Developer Request User process:
1. IP segment
2, user name, password requirements
3. What permissions are required

When you create a database in MySQL, you usually set the character sets, and generally we set it to UTF-8, but there is usually a database proofing rule.
There's a lot to choose from. Selectable Utf8_general_ci

Here are the demonstration operations:
How to support UTF-8 encoding when creating a MySQL database

1, with tools
CharSet Character set: Select UTF8
Collation Database validation rules: Select Utf8_general_ci

2. Using SQL statements
Gbk:create DATABASE test1 DEFAULT CHARACTER SET GBK COLLATE gbk_chinese_ci;
UTF-8: CREATE DATABASE test2 DEFAULT CHARACTER SET UTF8 COLLATE utf8_general_ci;

A database of UTF8 mydb is created:
CREATE DATABASE mydb CHARACTER SET UTF8 COLLATE utf8_general_ci;

Specify partial permissions to the user:
Add a test1 user with a password of 123456, can log in on any host, and have the ability to query, add, modify and delete all databases:
Grant Select,insert,update,delete on mydb.* to [e-mail protected] '% ' identified by ' 123456 ';
FLUSH privileges; #刷新系统权限表

Authorization Test1 All permissions:
GRANT all on mydb . * to username @localhost identified by password ';

To view the password for the created user:"All MySQL users are stored in the Mysql.user table"
Mysql> select User,host,password from Mysql.user;
ERROR 1054 (42S22): Unknown column ' Password ' in ' Field list '

View the table of the database which fields can be queried:authentication_string
Help show;
Create
SHOW CREATE TABLE tbl_name;
SELECT * from Mysql.user;

Error cause resolution:
mysql5.7 after the Mysql.user table does not have the password field, but instead uses the authentication_string.
Select user,host,authentication_string from Mysql.user;
Equivalent to
Use MySQL;
Select user,host,authentication_string from user;

Update Mysql.user Setauthentication_string=password ("New password") where user= "test" and host= "localhost";
Flush privileges;

To set the root password for MySQL:
Enter Update Mysql.user set Authentication_string=password (' Password ') where user= ' account '; If the password is 123.
Finally enter flush privileges; Press ENTER to tell MySQL to refresh the system permissions, at which time the changed password will take effect.

Update Mysql.user set Authentication_string=password (123456) where user= "root" and host= "localhost";

MySQL Skips password verification:
It is best to run cmd as Administrator, enter the bin directory, and then stop the server, enter net stop MySQL,
Re-enter Mysqld--skip-grant-tables (enter, start MySQL, meaning to tell MySQL does not require login verification).
Then turn off cmd and turn it back on.
Input mysql-uroot-p continuous Press two times the Enter button will go directly to the MySQL console interface, at this time found that the login root account does not require a password.

To delete a user from the Mysql.user table:
Delete from Mysql.user Where user= "user name" and host= "localhost";
Flush privileges;

76855370

Section:
First, create the database:

1, CREATE database name;

2. GRANT select,insert,update,delete,create,drop,alter on database name. * To database name @localhost identified by ' password ';

3. SET PASSWORD for

' Database name ' @ ' localhost ' = old_password (' password ');

Execute 3 commands in turn to complete the database creation. Note: The Chinese "password" and "database" are the user's own needs to set.

Second, change the password:
Mysql-u root-p
Update Mysql.user Setauthentication_string=password ("New password") where user= "test" and host= "localhost";
Flush privileges;
mysql5.7 after the Mysql.user table does not have the password field, but instead uses the authentication_string.

Third, delete the user:
Mysql-u root-p
Delete from Mysql.user Where user= "user name" and host= "localhost";
Flush privileges;

Create user
Command: CREATE USER ' username ' @ ' host ' identified by ' password ';
Delete accounts and permissions:
Drop user username @ '% ';
Drop user username @ localhost;

Attention:
Create a super User Yan that only allows log on locally and allow permissions to be assigned to other users with a password of 111
GRANT all privileges on . to [email protected] ' localhost ' identified by ' 111 ' with GRANT OPTION;
Description of the GRANT command:
All privileges is a representation of all permissions, and you can also use permissions mentioned by SELECT, Update, and so on.
On to specify which libraries and tables the permissions are for.
. The preceding number is used to specify the database name, and the following number is used to specify the table name
To indicates that the permission is assigned to a user.
[email protected] '% ' means Feihong user, the host after the @, can be IP, IP segment, domain name, and%,localhost represents the local
% means anywhere. Note: There are some versions of this is not included in the local, previously encountered to a user set the% allowed to log anywhere, but not on the local login, this and the version has a relationship with this problem plus a localhost user can be.

With GRANT option This option means that the user can delegate the permissions they own to others.
Note: Often someone does not specify the WITH GRANT option when creating an operation user
Causes the user to later be unable to create a user with the grant command or to authorize another user.

Note: You can use grant to repeatedly add permissions to the user, permissions overlay, for example, you first added a SELECT permission to the user,
Then adding an INSERT permission to the user, the user has both select and insert permissions.

Four, for the user authorization:
You must give a message when setting permissions
1, permissions to grant
2, the database or table that is granted access
3, User name
Grant and revoke can control access at several levels
1, entire server, using grant all and revoke all
2, entire database, using on database.*
3, Feature table, using on database.table
4, a specific column
5, a specific stored procedure

Meaning of the value of the host column in the user table
% matches all hosts
localhost localhost will not be parsed into an IP address and connected directly via Unixsocket
The 127.0.0.1 will be connected via TCP/IP protocol and can only be accessed natively;
:: 1:: 1 is compatible with support IPv6, indicating the 127.0.0.1 with IPv4

Authorization format: Grant permission on database. To user name @ Login host identified by "password";
Eg:grant all privileges in. * to ' root ' @ ' 192.168.218.128 ' identified by ' hello ' with GRANT option;
Flush privileges; To refresh Permissions

The authorization test user has all the permissions of the TestDB database:
Grant all privileges the testdb.* to "test" @ "localhost" identified by "1234";
Flush privileges; #刷新系统权限表

Specify partial permissions to the user:
Grant Select,update on testdb.* to "test" @ "localhost" identified by "1234";
Flush privileges; #刷新系统权限表

For example:
Create a Web site user (program user)
Create a general program user, this user may need only select, INSERT, UPDATE, DELETE, create temporary tables and other permissions if there is a stored procedure also need to add execute permission, In general, the designated intranet segment 192.168.100 network segment.
GRANT Usage,select, INSERT, UPDATE, DELETE, SHOW VIEW, CREATE temporary tables,execute on test . * to [email protected] ' 192 .168.100.% ' identified by ' [email protected] ';

Create a normal user (query permission only)
GRANT usage,select on test . * to [e-mail protected] ' 192.168.100.% ' identified by ' [email protected] ';

V. To view the user's permissions:
SHOW GRANTS for ' test1 ' @ '% ';
SHOW GRANTS for ' root ' @ ' localhost ';
SHOW GRANTS for ' webuser ' @ ' 192.168.100.% ';

Six, recovery rights:
REVOKE DELETE on the mydb.* from ' test1 ' @ '% ';

Vii. Delete users:
Note Delete users do not use Delete to delete directly, because the user's permissions are not deleted after using Delete, and the new user with the same name inherits the previous permissions. It is a good practice to use the drop user command to delete users, such as to remove the ' webuser ' @ ' 192.168.100.% ' user with the following command:
DROP USER ' webuser ' @ ' 192.168.100.% ';
You can use the Pt-show-grants tool in the Percona-toolkit tool to help manage MySQL permissions

Eight, display the current user information:
Select User ();

Nine, MySQL rights assignment:
Permission distribution: Permissions that may be set

Table permissions:
' Select ', ' Insert ', ' Update ', ' Delete ', ' Create ', ' Drop ', ' Grant ', ' References ', ' Index ', ' Alter '

Column permissions:
' Select ', ' Insert ', ' Update ', ' References '

Procedure permissions:
' Execute ', ' Alter Routine ', ' Grant '

MySQL table and library operations, and database backup:
Https://www.cnblogs.com/jiangxiaobo/p/7089345.html

XI. Grant Operation MySQL stored procedure, function permissions:
Http://www.cnblogs.com/fslnet/p/3143344.html

MySQL User Rights Management