• notice
  • Congratulations on the launch of the Sought Tech site

MYSQL user rights verification and management method detailed

This article describes the MySQL user authority verification and management methods with examples.Share it with everyone for your reference, as follows:

1.Mysql permissions are verified in two stages

1.The server checks whether the connection is allowed: user name, password, and host address.

2.Check whether each request is authorized to be implemented.

2.Mysql permission list

Permissions Permission level Permission description
create Database, table or index Create database, table or index permissions
drop Database or table Delete database or table permissions
grant option Database, table or saved program Give permission options
references Database or table Foreign key permissions
alter Table Change the table, such as adding fields, indexes, modifying fields, etc.
delete Table Delete data permission
index Table Index permission
insert Table Insert permission
select Table Query permissions
update Table Update permissions
create view View Create view permission
show view View View view permissions
alter routine Stored procedure Change stored procedure permissions
create routine Stored procedure Create stored procedure permission
execute Stored procedure Execute stored procedure permission
file File access on the server host File access permissions
create temporary tables Server Management Create temporary table permission
lock tables Server Management Lock table permissions
create user Server Management Create user permissions
proccess Server Management View process permissions
reload Server Management The authority to execute commands such as flush-hosts, flush-logs, flush-privileges, flush-status, flush-tables, flush-threads, refresh, reload, etc.
replication client Server Management Copy permissions
replication slave Server Management Copy permissions
show databases Server Management View database permissions
shutdown Server Management Close database permissions
super Server Management Execute kill thread permission

3.Mysql user authority management operations

1.Permission query:

(1) View all users of mysql and their permissions:

select * from mysql.user\G;

(formatted display)

(2) View the current mysql user permissions:

show grants;

(3) View the permissions of a user:

show grants for username@host;

Example:

show grants for root@localhost;

2.Mysql user creation:

Method 1: Use the create user command to create.

create user'username'@'host' identified by'password';

Example:

create user'wjt'@'localhost' identified by'wujuntian';

Method 2: Insert a user record directly into the data table mysql.user.

Example:

The code is as follows:
insert into mysql.user set user='wujuntian',host=' localhost',password=password('123123');

Note:

For the second method, remember to execute flush privileges.Secondly, after mysql5.7, the password field of the mysql.user table has been replaced by authentication_string, so "password" should be changed to "authentication_string", and the password must be encrypted using the password function.

3.Mysql user deletion:

drop user'username'@'host';

4.Mysql user permissions granted:

The newly created user does not have permission by default, and needs to use the grant command to grant permission.

Complete format of grant instruction:

grant permission list on database name.data table name to'user name'@'host' identified by'password' with grant option;

Example:

grant all privileges on *.* to'wjt'@'localhost' identified by "wujuntian" with grant option;

You can use "*" to indicate all databases or all data tables, and "%" to indicate any host address.

You can use grant to repeatedly add permissions to users to superimpose permissions.

with grant option: This option means that the user can grant the permissions he owns to others.

Remember to refresh the permissions after authorization:

flush privileges;

5.Mysql user permission recovery:

Revoke command format:

revoke permission list on database name.data table name from username@host;

Example:

revoke select on test.user from wjt@localhost;

Note:

In fact, when the GRANT statement is executed, if the target account does not exist in the permission table, the account is created; if it already exists, the permission is added.

Usage permissions cannot be reclaimed, that is, REVOKE user permissions cannot delete users.

6.Rename the account:

rename user'old user name'@'老host' to'new user name'@'新host';

Example:

rename user'wujuntian'@'localhost' to'ajun'@'localhost';

7.Mysql user password modification:

Method 1: Use the set password command.

set password for'username'@'host' = password('new password');

Example:

set password for'root'@'localhost' = password('123456');

Method 2: Modify the password (or authentication_string) field in the mysql.user table.

Example:

The code is as follows:
update mysql.user set password=password('123123') where user ='root' and host='localhost';

Note:

This method must execute the "flush privileges;" command to refresh the permissions, otherwise the password modification will not take effect.After Mysql5.7, "password" should be changed to "authentication_string".

Method 3: Use the grant command to modify the password during authorization:

grant select on database name.data table name to username@host identified by'new password' with grant option;

Example:

The code is as follows:
grant select on test.user to ajun@localhost identified by '111111' with grant option;

Method 4: Run the mysqladmin script file.

This file is generally in the bin directory under the mysql installation directory.Enter the directory and enter commands according to the following two specific conditions (only root users have this authority).

(1) The user has no password yet:

mysqladmin -u username password new password;

(2) The user has a password:

mysqladmin -u username -p password new password;

(After pressing Enter, you will be prompted to enter the old password.After you enter it, you can modify it successfully.)

Note:

When changing the password, you must use the PASSWORD function (mysqladmin and GRANT do not need to write in the two methods, it will be automatically added).

8.Forgot your password to log in to mysql:

Method 1:

Stop the running Mysql service first, enter the bin directory under the mysql installation directory in the command line window, and run the mysqld file under the -skip-grant-tables parameter (Linux system is safer to run the mysqld_safe file):

mysqld --skip-grant-tables

In this way, you can skip the access control of Mysql and enter the mysql database as an administrator on the console.In addition, open a command line window, enter the bin directory under the mysql installation directory, directly enter: mysql, press Enter, you can log in to mysql, and then you can reset the password (note: at this time, the "Mysql user password modification" Only the second method of the four methods can be used!).After the setting is successful, exit and restart the Mysql service.

Method 2: Modify the mysql configuration file my.ini.

Actually, the principle and method are the same.They use the --skip-grant-tables parameter provided by Mysql to skip Mysql's access control.Open the mysql configuration file my.ini, add "skip-grant-tables" under'[mysqld]', save, restart the Mysql service, and then you can log in to mysql without a password to modify the password.

The "mysql" database in Mysql stores all the permissions information data tables of Mysql users.When Mysql is started, all the contents of the permission table are read into the memory, and the contents in the memory are used directly for the permission judgment.The modification of the permission table with grant, revoke or set password will be immediately noticed by the server.The essence of GRANT operation is to refresh the permission after modifying the permission table.However, if you manually modify the privilege table, such as using insert, update, delete and other operation privilege tables, you should execute a flush privileges command, which will cause the server to re-read the privilege table content to the memory to make the modification effective.If you do not execute this command, you must restart the mysql service to take effect.Therefore, it is best to use grant, revoke or set password to operate the privilege table, which can save the trouble of executing the flush privileges command, and you will be crazy if you forget to execute this command...

Not only that, it is better to use the drop user and rename user commands to delete and rename users, instead of using the delete and update commands.The former will not only operate on the mysql.user data table, but also update the records of other permission tables, while the latter will only operate on the data in the mysql.user table, which will cause many problems, because the user's permission information does not only exist In the mysql.user table.For example, if you use delete to delete a user in the mysql.user table, but do not operate other permission data tables, then other permission data tables such as tables_priv have permission records for this user.Next time, if you want to use the create user command Creating a user with the same name will fail.You can only use the insert into command to insert records into the mysql.user table, or delete records related to the user name in other permission data tables.Using the update command to rename a user will also have a big problem.After renaming, the user loses a lot of permissions, and the records of the original user name in other permission tables become useless records, unless you have all permissions for each permission table.Perform the same update operation, but it is troublesome.So, use drop user, rename user, one command can let the system automatically do everything for you, why not do it!

Mysql permission check:

mysql first checks whether it has permission to a large area, and if not, check it in a small area.For example: first check whether there is select permission for this database, and if so, allow execution.If not, then check whether there is select permission on the table, until the finest granularity, and there is no permission, then refuse to execute.Therefore, the finer the granularity control, the more authorization verification steps, and the worse the performance.

Tags

Technical otaku

Sought technology together

Related Topic

1 Comments

author

order lipitor 20mg generic & lt;a href="https://lipiws.top/"& gt;lipitor 20mg price& lt;/a& gt; buy atorvastatin 80mg sale

Mcasvf

2024-03-11

Leave a Reply

+