MySQL database security permission control management philosophy

MySQL database security permission control management philosophy

Web account authorization case studies
A. Account authorization for the master database user in the production environment:
Grant select, INSERT, UPDATE, delete on blog. * TO 'blog '@ 10.0.0.% 'identified by 'oldboy456 ';
B. Authorization from database users in the production environment:
Grant select on blog. * TO 'blog '@ '10. 0.0. %' identified by 'oldboy456 ';
Of course, in addition to SELECT authorization, the slave database can also add read-only parameters such as read-only.

2.4 Production Environment read/write splitting account settings
In addition to the IP address, we recommend that you set the read/write splitting user for developers so that they can be used conveniently. Therefore, the read/write splitting address looks the same except for the IP address. This is a user-friendly design that reflects the professional O & M or DBA personnel.
Master database (write service as much as possible): blog oldboy456 ip: 10.0.0.179 port 3306
Slave database (read service available today): blog oldboy456 ip: 10.0.0.180 port 3306
Tip: the permissions of the two accounts are different.
Tip: In terms of database design, developers should prioritize database reading. If the database is not read, you can consider the primary database when it times out, from the perspective of program design, it is also necessary to make a comprehensive experience based on the busy degree of the master database. The specific situation is determined based on the business project requirements.

3. Database Client Access Control
1. Change the default mysql client port. For example, if the phpadmin Management port is 9999, the same applies to other clients.
2: The database web client is uniformly deployed on 1-2 servers that do not provide external services. The ip address and port 9999 can only be accessed from the Intranet.
3. Do not resolve the public network domain name, use the host for access or internal IP address
The 4phpadmin site directory is independent of all other site root directories and can only be accessed by the specified domain name or IP address.
5. restrict the use of web-connected accounts to manage databases, and specify the account to access based on user role settings.
6. Assign management accounts by development personnel and related personnel based on positions and roles
7: Set access to the specified account (apache/nginx Authentication + two Logon Restrictions for mysql users)
8. Unify the logon endpoints of all database accounts. Prohibit all developers from uploading phpadmin and other databases for management.
9 activate vpn, jump server, and internal IP address management database
System Layer Control
1. restrict or prohibit developers from managing ssh root, refine permissions through sudo, and use log auditing
2. Perform Read and Write Permission control on configuration files such as phpadmin config.
3: retrieve and consume all phpadmin web connections of the specified server
4. Do not allow non-administrators to manage the database web client Server permissions.
5. Database reading/writing splitting
Supplementary Rules: a large number of tests, statistics, backup, and other operations on the database, such as select, must be performed independently from the database without providing external select.
Use Cases for splitting the Master/Slave architecture production environment from servers to business:
M --> s1 = provide services to external users (Browse posts, blogs, and articles)
--> S2 => provide services to external users (Browse posts, blogs, and articles)
--> S3 ==> provide services to external users (Browse posts, blogs, and articles)
--> S2 => provides services to internal users (background access, script tasks, data analysis, and browsing by developers)
--> S5 ==> Database Backup Service (enable the binlog function of the slave server to implement Incremental backup and recovery)

This article permanently updates the link address: