MySQL user and permission management?

Time:2021-9-22

View the current users in MySQL system

Through 1, we know that the user information of the database exists in the user table in MySQL database. We query the table and the results are as follows. The host field is the IP address that the current user can log in (if it is *, it means any address can log in), the user field is the user name, and the password is the user password.

mysql> select Host, User, Password from user;
Host User Password
localhost root *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9
127.0.0.1 root *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9
::1 root *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9

Custom user

Why do you need to customize users? We will consider the following aspects: authority and function.

The default root user belongs to the function of super administrator in MySQL. If everyone can get the root permission, it is prone to some operation errors. Light cases include problems with some data in the database, or the whole database will be destroyed.

The default root user belongs to the function of super administrator in MySQL and is easy to be used by people with malicious intentions.

The default root user belongs to the function of super administrator in MySQL. Given the corresponding permissions of the specified user, it can ensure that each user can only use the permissions within the user’s responsibilities, which can not only ensure the finer division of labor in the database, but also ensure the security of the database.

Create MySQL user

CREATE USER 'username'@'host' IDENTIFIED BY 'password';

//Grant corresponding permissions to the user (when privileges_name is all, it means that all permissions are granted; when privileges_name is specific to some permissions, privileges do not need to be added, and the meaning of withdrawing permissions below is the same)

GRANT privileges_name  privileges ON databasename.tablename TO 'username'@'host';

//Refresh permissions to enable newly created users to use

flush privileges;

//Revoke user rights

phpREVOKE privileges_name privilege ON databasename.tablename FROM 'username'@'host';

If we create the same user name, we need to delete the user name when we first created it. We can’t just delete the user table information under the MySQL database. We should use the drop user username command to delete the user. Then use flush privileges to refresh permissions. If you follow the above operations, you will be prompted that the user cannot log in and view the firewall status,CentOS 7 firewall settings reference link

delete user


drop USER 'username'@'host';

Refresh permissions


flush privilege

Modify user password

//Modify the specified user password

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

//Set the password of the currently logged in user

set password = password('newpassword');

CentOS setting up firewall

1. Centos6 setting firewall

a. View firewall status


service iptable status

b. Restart firewall


service iptables restart

c. Open port


/sbin/iptables -I INPUT -p tcp --dport 8080 -j ACCEPT

/etc/rc.d/init.d/iptables save

d. View open ports


/etc/init.d/iptables status

e. Turn off firewall

Servcie iptables stop

Chkconfig iptables off

f. Start firewall

Service iptables start (Provisional)

Chkconfig iptables on (permanent)

2. Centos7 setting firewall

a. View firewall status (3 ways)


1.firewall-cmd --state

2.systemctl list-unit-files|grep firewalld.service

3.systemctl status firewalld.service

b. Restart firewall


systemctl restart firewalld.service

c. Open port

firewall-cmd --zone=public --add-port=80/tcp --permanent

– zone # scope

– add port = 80 / TCP # add port in the format of port / communication protocol

– permanent # takes effect permanently. If there is no such parameter, it will become invalid after restart

d. View open ports


firewall-cmd --list-ports

e. Turn off firewall

Systemctl stop firewalld.service

Systemctl disable firewalld.service (permanently disabled)

f. Start firewall


systemctl start firewalld.service

This work adoptsCC agreement, reprint must indicate the author and the link to this article

Love the official account of “two technical circles of cards”.