MySQL is a cross-platform open source relational database management system. It is the most economical and affordable database we commonly use. Due to its small size, fast speed, and low total cost of ownership, especially the open source feature, it allows users to get the most practical results. It is true that many users do not know much about the authorization principles in applications. Let’s analyze it for everyone. MySQL is a cross-platform open source relational database management system. It is the most economical and affordable database we commonly use. Due to its small size, fast speed, and low total cost of ownership, especially the open source feature, it allows users to get the most practical results. It is true that many users do not know much about the authorization principles in applications. Let’s analyze it for everyone.
Server restart situation
When mysqld starts, all grant table contents are read into memory and become effective from that point on.
When applied immediately by the server
Modifications to the authorization table using GRANT, REVOKE or SET PASSWORD will be immediately noticed by the server.
Direct modification of the authorization form
If you modify the grant tables manually (using INSERT, UPDATE, etc.), you should execute a FLUSH PRIVILEGES statement or run mysqladmin flush-privileges to tell the server to reload the grant tables, otherwise your changes 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, existing client connections have the following effects:
·Table and column permissions take effect on the client's next request.
·Database permission changes will take effect on the next USE db_name command.
·Global permission changes and password changes take effect the next time the client connects.
Delegation principle
No matter how careful you are, you will inevitably leave loopholes when authorizing users. I hope the following content can give you some help. You should generally abide by these rules.
Only the root user has the right to rewrite the authorization table
Do not grant the right to rewrite the authorization table to other users except the root user (of course, if you can use another user to replace the root user for management, to increase security). Because of this, users can override existing permissions by overwriting the authorization table. Create security holes.
Normally you may not make this mistake, but after installing a new distribution, initial authorization table. This vulnerability exists and you may make mistakes if you don't understand the contents of the authorization form at this time.
On Unix (Linux), after installing MySQL according to the instructions in the manual, you must run the mysql_install_db script to establish the mysql database including the authorization table and initial permissions. 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 your machine, the authorization tables in the mysql database are initialized like this:
·You can connect as root from localhost without specifying a password. The root user has all rights (including administrative rights) and can do anything. (By the way, the MySQL superuser and the Unix superuser have the same name and have nothing to do with each other.)
·Anonymous access is granted to users who can connect locally to the database named test and any database name starting with test_. Anonymous users can do anything to the database but have no administrative rights.
Generally, it is recommended that you delete anonymous user records:
mysql> DELETE FROM user WHERE User="";
Going a step further, also delete any anonymous users in other authorization tables, the tables with User columns are db, tables_priv and columns_priv.
Also set a password for the root user.
About user, password and host settings
·Use passwords for all MySQL users.
Remember, if other_user does not have a password, anyone can log in as anyone else simply with mysql -u other_user db_name. For client/server applications, it is common practice for the client to specify any username. Before you run it, you can change the passwords for all users, or just the MySQL root password, by editing the mysql_install_db script, like this:
shell> mysql -u root mysql
mysql> UPDATE user SET Password=PASSWORD('new_password')
-> WHERE user='root';
mysql> FLUSH PRIVILEGES;