Detailed introduction to permission management in MySQL

Permission Management

The MySQL permission system authenticates through the following two stages:

• Perform identity authentication on connected users. Legal users pass the authentication, and illegal users refuse to connect.

• Grant corresponding permissions to legal users who have passed the authentication. Users can perform corresponding operations on the database within the scope of these permissions.
  In the permission access process, it mainly involves the user table and db table under the mysql database. The data structure of the user table is as follows:
  
  The data structure of the db table is as follows:
  
  Description of the table: It contains user columns, permission columns, security columns and resource control columns. The most frequently used ones are the user column and the permission column. Permissions are divided into normal permissions and management permissions. Operations of ordinary permissions user database such as select_priv, insert_priv, etc. Management permissions are mainly used to manage database operations, such as process_priv, super_priv, etc.
  When a user connects, the access process of the permission table:

• First determine the connected IP and user from the three fields of host, user and password in the user table Whether the name and password exist in the table, if they exist, the verification is passed, otherwise the connection is refused.

• After passing the authentication, obtain the database permissions in the order of the following permission table: user->db->tables->priv->columns_priv. Global permissions cover local permissions. For example, if a user has select permission in the user table, then he will have select permission on all columns in all tables in all data.
  
  Detailed description of permission search: When the user passes permission authentication, when allocating permissions, the permissions are allocated in the order of user->db->tables_priv->columns_priv, that is, the permissions are checked first. Table user, if the corresponding permission in the user table is Y, then all database permissions corresponding to the user are Y, and db, tables_priv, and columns_priv will no longer be checked; if it is N, the user's specific database permissions will be searched in the db table. , if you get the Y permission in the db, you will not be searching. Otherwise, you will check tables_priv to see the specific table permissions corresponding to the database. If it is Y, you will not be searching. Otherwise, you will be checking the columns_priv table to see the corresponding specific column permissions. This is important when we grant user permissions.

Account management

Create user

To create a user, you can use grant syntax to create or directly operate the user table.
Method 1:
Directly operate the use table
insert into user(Host,User,Password) values(“127.0.0.1 ","test",password("51testit"));
Method 2:
Format CREATE USER 'username'@'host' IDENTIFIED BY 'password';
eg:CREATE USER 'test'@ '127.0.0.1' IDENTIFIED BY '51testit';

After creation, log in as follows:

MySQL encryption method: MYSQL323 encryption generates a 16-bit string. What survives in MySQLSHA1 is a 41-bit string, of which is not added to the actual password operation. Through observation, many users carry "", and the "*" is removed during the actual cracking process. In other words, the actual number of digits in the password encrypted by MySQLSHA1 is 40 digits.
Host field description

• The Host value can be the host name or IP number, or locahost represents the local host.

• You can use wildcard characters "%" and "_" in front of the host column value. "%" means matching any host. An empty Host value is equal to "%". For example, "%.myweb.com" matches all hosts in all mysql.com domains. The case is as follows:

##%pps pps, connect from any host%”Any user, connect from any host122.164.35.127ppspps, connect from 122.164.35.127122.164.35.%ppspps, connect from any host on 122.164.35. type subnet

View user permissions

For the test created above, I did not perform any authorization operations. When I used the test user to operate the database, I found:

No database operation permissions , how can I check the permissions a user has?
View permissions
Method one:
show grants for test@127.0.0.1;
Method two:
View the permissions recorded in the user table.
select * from mysql.user where user='test' and host='127.0.0.1' \G;

Grant permissions

The syntax format is as follows:
grant [privilege1|privilege2|all privileges] on dbName|.tableName or to user@hostName;
When granting xxpt selection permission to the test user:
grant select On xxpt. * to test.127.0.0.1;
The db table has changed and a new record has been added. But the user table has not changed. As follows:

At this time, you can use the test user to perform data query operations, but you cannot add, delete, or modify. So grant it all permissions on the xxpt database.
grant all privileges on xxpt.* to test@127.0.0.1;
User test permissions are as follows:

You can update data at this time

Management permissions Grant sueper, process, file
grant sueper, process, file On . to 'test'@'127.0.0.1';
Only the words following on in the statement can be .
Note: usage permission is used for login and cannot perform any operations.

Delete permissions

Use grant to add permissions and use revoke to recycle permissions. Of course, permissions can be revoked, and you can also directly operate on the user, db, tables_priv and columns_priv tables. However, the usage permission cannot be revoked.
To revoke test’s read and write permissions on all databases:
revoke select ,insert on . from test@127.0.0.1;

Change password

1. Use mysqladmin
2. Use set password for test@127.0.0.1 =password('isayhello');
3. Pass grant usage
grant usage on . to 'test '@'127.0.0.1' identified by 'isayhello';
4. Directly modify the user table
update user set Password=password("newpassword") where ***;

Supplementary permissions in mysql

Host	User	Analysis
myweb.wang	pps	pps, connect from myweb.wang

##DROPGRANT OPTIONREFERENCESALTER##Table Index permissionTableInsert permissionTableQuery PermissionTable##File access on the server hostFile Access Permissions##CREATE TEMPORARY TABLESLOCK TABLES##CREATE USERPROCESS##Server management Server ManagementReplication PermissionsServer ManagementReplication PermissionServer ManagementView database permissionsServer ManagementClose database permissionsServer ManagementExecute kill thread permissionHow MYSQL permissions are distributed, that is, what permissions can be set for tables, what permissions can be set for columns, etc. This can be explained from a table in the official documentation:

##Permissions	Permission Level	Permission Description
CREATE	Database, table or index	Create database , table or index permission
##Database or table	Delete database or table permissions
##Database, table or saved program	Grant permission options
Database or table
##Table	Change the table, such as adding fields, indexes, etc.	DELETE
Table	Delete data permission	INDEX
		INSERT
		SELECT
		UPDATE
	Update Permissions
CREATE VIEW	View	Create View Permission
SHOW VIEW	View	View View Permission
ALTER ROUTINE	Stored Procedure	Change stored procedure permissions
CREATE ROUTINE	Stored procedure	Create Stored Procedure Permission
EXECUTE	Stored Procedure	Permission to execute stored procedures
##FILE
Server Management	Create temporary table permissions
Server management	Lock table permission
Server management	Create User Permissions
Server Management	View process permissions	##RELOAD
	Permission to execute flush-hosts, flush-logs, flush-privileges, flush-status, flush-tables, flush-threads, refresh, reload and other commands	REPLICATION CLIENT
		REPLICATION SLAVE
		SHOW DATABASES
		SHUTDOWN
		SUPER

Permission distribution

Permissions that may be set'Select', 'Insert', 'Update', 'Delete', 'Create', 'Drop', 'Grant', 'References', 'Index', 'Alter''Select', 'Insert', 'Update', 'References' 'Execute', 'Alter Routine', 'Grant'

	Table permissions
	Column permissions
	Process Permissions
	The above is the detailed introduction of permission management in MySQL. For more related content, please pay attention to the PHP Chinese website (www.php.cn) !