Detailed description of MySQL database authorization principles

Here we will discuss the MySQL database authorization principle, which also concerns the security of the majority of small and medium-sized enterprises databases.

MySQL is a cross-platform open-source relational database management system. It is the most cost-effective database we commonly use. It is small in size, fast in size, and low in total cost of ownership, in particular, the open source code feature gives users the most practical experience. Many users are not very familiar with the authorization principles in their applications. Next we will resolve this issue for everyone. MySQL is a cross-platform open-source relational database management system. It is the most cost-effective database we commonly use. It is small in size, fast in size, and low in total cost of ownership, in particular, the open source code feature gives users the most practical experience. Many users are not very familiar with the authorization principles in their applications. Next we will resolve this issue for everyone.

Server restart

When mysqld is started, all authorization table content is read into the memory and takes effect from that time.

Immediately applied by the server

Modifications made to the authorization table using GRANT, REVOKE, or set password will be immediately noticed by the server.

Directly modify the authorization table

If you manually modify the authorization table (using INSERT, UPDATE, and so on), you should execute a FLUSH
PRIVILEGES statement or run mysqladmin
Flush-privileges tells the server to load the authorization table again, otherwise your change will not take effect unless you restart the server.

Impact on existing customer connections

When the server notices that the authorization table has been changed, the existing customer connection has the following impact:

◆ The table and column permissions take effect in the next request of the customer.

◆ The database permission change takes effect in the next USE db_name command.

◆ Changes in global permissions and passwords take effect the next time a customer connects.

Authorization principles

No matter how careful you are, you will inevitably leave vulnerabilities when authorizing users. I hope the following content can help you. You should generally abide by these rules.

Only the root user has the permission to override the authorized table.

Do not grant the permission to rewrite the authorization table to other users except the root user (of course, if you can use another user instead of the root user for management to increase security ). In this way, you can overwrite the existing permissions by modifying the authorization table. Generate security vulnerabilities.

In general, you may not make this mistake, but when installing a new distribution,
After the initial authorization table. This vulnerability exists. If you do not know the content of the authorization table, you may make mistakes.

On Unix (Linux), after installing MySQL according to instructions in the manual, you must run the mysql_install_db script to create the mysql database and initial permissions containing the authorization table. On Windows, run the Setup program in the distribution to initialize the data directory and mysql database. It is assumed that the server is also running.

When you first install MySQL on a machine, the authorization table in the mysql database is initialized as follows:

◆ You can connect to the local host as root without specifying a password. Root users have all permissions (including management permissions) and can do anything. (By The Way, MySQL Super Users have the same name as Unix Super Users, and they have nothing to do with each other .)

◆ Anonymous Access authorized users can connect to a database named test and any database named test _ locally. Anonymous Users can do anything to the database, but have no management permission.

Generally, we recommend that you delete anonymous user records.

 
 

  
  
mysql> DELETE FROM user WHERE User=”"; 
 
 

Furthermore, you can delete any anonymous users in other authorization tables. Tables with User columns include db, tables_priv, and columns_priv.

In addition, set the password for the root user.

User, password, and host settings

◆ Use passwords for all MySQL users.

Remember, if other_user does not have a password, anyone can simply use mysql-u other_user
Db_name is used as a logon user. For client/server applications, it is common for customers to specify any user name. Before you run it, You can edit the mysql_install_db script to change the passwords of all users, or just the MySQL root Password, as shown in the following code:

 
 

  
  
shell> mysql -u root mysql  
  
  
mysql> UPDATE user SET Password=PASSWORD(’new_password’)  
  
  
-> WHERE user=’root’;  
  
  
mysql> FLUSH PRIVILEGES;