Rights Management in MySQL

Rights Management

The MySQL privilege system is certified through the following two phases:

• Authentication is made to the connected user, the legitimate user is authenticated, and the user is denied the connection.

• By granting the appropriate permissions to legitimate users who are authenticated, the user can perform the appropriate operations on the database through these permissions.
  In the process of permission access, the user table and the DB table are mainly involved in MySQL database. The data structure of the user table is as follows:
  
  The DB table data structure is as follows:
  
  Description of the table: contains user columns, permission columns, security columns, and resource control columns. The most frequent use is the user columns and Permissions columns, the permissions are divided into normal permissions and administrative permissions. Normal permissions user database operations such as Select_priv, Insert_priv, and so on. Management permissions are mainly used to manage the database operations, such as Process_priv, Super_priv, and so on.
  The access process of the permission table when the user makes a connection:

• The connection's IP, user name, and password are determined first from the three fields in the user table, and the connection is denied if it exists, or if it is verified.

• After authentication, database permissions are obtained in the order of the following permission tables: User->db->tables->priv->columns_priv. Global permissions, overriding local permissions. For example, if a user has SELECT permissions in the user table, he will have the option to select all the columns in all the tables in the data.
  
  Permissions Lookup Detailed Description: When the user through the authority authentication, when the permission assignment, in accordance with the order of USER->DB->TABLES_PRIV->COLUMNS_PRIV permission assignment, that is, first check the permission table user, If the corresponding permissions in the user table are Y, at this time the users corresponding to all the database permissions are Y, will no longer check the DB, Tables_priv, Columns_priv, if n, to the DB table to find the user for specific database permissions, if you get the Y permission in db, do not find , otherwise check the TABLES_PRIV, see the database corresponding to the specific table permissions, if y, do not find, otherwise check the Columns_priv table, view the corresponding specific column permissions. This is important for us to grant user permissions.

Account Management Create User

Create users and use the grant syntax to create or manipulate the user table directly.
Method One:
Direct Operation Use Table
Insert into User ( Host , User , Password ) VALUES ("127.0.0.1", "Test", Password ("51testit"));
Method Two:
Format CREATE USER ' username ' @ ' host ' identified by ' password ';
Eg:create USER ' Test ' @ ' 127.0.0.1 ' identified by ' 51testit ';

Once created, log in as follows:

MySQL encryption method: MYSQL323 encryption is generated in the 16-bit string, and in MySQLSHA1 survival is a 41-bit string, which is not added to the actual cryptographic operation, by observing in many users are carried "", in the actual cracking process to remove the "*", That is to say, the actual number of MySQLSHA1 encrypted passwords is 40 bits.
Host Field Description

• The host value can be either the hostname or the IP number, or the locahost represents the local host.
• You can use the wildcard character "%" and "_" before the value of the host column, "%" to match any host, and the empty host value equals "%". such as "%.myweb.com", matches all hosts of all mysql.com domains. The cases are as follows:

Host	User	parsing
Myweb.wang	Pps	PPS, connecting from Myweb.wang
%	Pps	PPS, connecting from any host
%	”	Any user, from any host connection
122.164.35.127	Pps	PPS, connecting from 122.164.35.127
122.164.35.%	Pps	PPS, any host connection from the 122.164.35. Type subnet

View User Permissions

For the test created above, I did not perform any authorization, and when I used the test user to manipulate the database, I found:

How can I see what permissions a user has without the database operation permissions?
View Permissions
Method One:
Show grants for [email protected];
Method Two:
View the permissions that are logged in the user table.
SELECT * from Mysql.user where user= ' test ' and host= ' 127.0.0.1 ' \g;

granting permissions

The syntax format is as follows:
Grant [Privilege1|privilege2|all privileges] on dbname| . TableName or to [email protected];
When granting XXPT SELECT permissions to test users:
Grant SELECT on xxpt.* to test.127.0.0.1;
The DB table has changed, adding a new record. But the user table has not changed. As follows:

At this point, you can use the test user for data query operations, but not to increase, delete, change. It then grants all of its permissions on the XXPT database.
Grant all privileges the xxpt.* to [email protected];
User test permissions are as follows:

The data can now be updated

Administrative rights Sueper, process, file grant
Grant Sueper,process,file on . to ' test ' @ ' 127.0.0.1 ';
The statement can only be followed by .
Note: Usage permissions are used to log on, and no action is allowed.

Delete permissions

Use grant to add permissions and use revoke for permission reclamation. Of course, you can also manipulate the user, DB, Tables_priv, and Columns_priv tables directly by reclaiming permissions. However, usage permissions are not recoverable.
If you recycle test read-write access to all databases:
Revoke SELECT, insert on . from [email protected];

Change Password

1. Using Mysqladmin
2. Use set password for [email protected] =password (' Isayhello ');
3. Using Grant Usage
Grant usage on . to ' test ' @ ' 127.0.0.1 ' identified by ' Isayhello ';
4. Modify the user table directly
Update user set Password=password ("NewPassword") where * * *;

Add permissions in MySQL

Permissions	Permission Levels	Permission Description
CREATE	Database, table, or index	Create database, table, or index permissions
DROP	Database or table	Delete database or table permissions
GRANT OPTION	A database, table, or saved program	Granting permission Options
REFERENCES	Database or table
Alter	Table	Change tables, such as adding fields, indexes, etc.
DELETE	Table	Delete Data permissions
INDEX	Table	Index permissions
INSERT	Table	Insert Permissions
SELECT	Table	Query permissions
UPDATE	Table	Update permissions
CREATE VIEW	View	CREATE VIEW Permissions
SHOW VIEW	View	View View Permissions
ALTER ROUTINE	Stored Procedures	Change stored procedure permissions
CREATE ROUTINE	Stored Procedures	Create Stored Procedure permissions
EXECUTE	Stored Procedures	Execute Stored Procedure permissions
FILE	File access on the server host	File access Permissions
CREATE Temporary TABLES	Server Management	Create temporary table permissions
LOCK TABLES	Server Management	Lock table Permissions
CREATE USER	Server Management	Create User Rights
PROCESS	Server Management	View Process Permissions
RELOAD	Server Management	Perform flush-hosts, Flush-logs, Flush-privileges, Flush-status, Flush-tables, flush-threads, refresh, reload, and so on command permissions
REPLICATION CLIENT	Server Management	Copy Permissions
REPLICATION SLAVE	Server Management	Copy Permissions
SHOW DATABASES	Server Management	View Database Permissions
SHUTDOWN	Server Management	Turn off database permissions
SUPER	Server Management	Execute Kill Thread Permissions

How MySQL's permissions are distributed is what permissions are set on the table, what permissions are set on the column, and so on, which can be explained in a table in the official documentation:

Distribution of permissions	Possible settings for permissions
Table Permissions	' Select ', ' Insert ', ' Update ', ' Delete ', ' Create ', ' Drop ', ' Grant ', ' References ', ' Index ', ' Alter '
Column permissions	' Select ', ' Insert ', ' Update ', ' References '
Process permissions	' Execute ', ' Alter Routine ', ' Grant '

Rights Management in MySQL