Mysql base fuck
Create user 'admin'@'%' identified by '***'; - Alter password alter user 'dev_xl'@'%' IDENTIFIED with mysql_native_password by '****'; Rename user 'admin_xl'@'192.168.200.16' to 'admin_XL '@'%'; -- Refresh PRIVILEGES FLUSH PRIVILEGES; Grant all PRIVILEGES on *.* to 'admin_XL '@'%'; -- Revoke all PRIVILEGES on *.* from 'admin_XL '@'%'; -- Select current_user() -- select current_role()Copy the codeI. Identification
1.1 Assessment item A
A) Assessment content
Users who log in should be identified and authenticated. The identification must be unique and the identification information must be complex and should be changed periodically
B) Rectification suggestions
The password must be a string of at least eight characters and consists of digits, letters, and special symbols. The password must be changed periodically and the maximum change time is 90 days.
C) Rectification process
1. Understanding of user identity authentication:
The mysql database identifies a user differently from other databases. Not only the username, but the username+host combination. For example, the following two root, host is different, actually two different users. Check whether there are duplicate users in the mysql.user table. You are advised to delete or rename the root user.
2. Check empty instructions: user, host, authentication_string fields are all empty or %.
3. Password complexity plug-in
Mysql > install mysql8.0; mysql > install mysql8.0;
INSTALL PLUGIN validate_password SONAME 'validate_password.so';
Copy the codeOpen /etc/my.cnf and add the following to mysqld to restart
[mysqld]
plugin-load=validate_password.so
validate_password_policy=1
validate-password=FORCE_PLUS_PERMANENT
Copy the code4. Explanation of password complexity:
After installing the plug-in, view the parameters.
mysql> show variables like 'validate%'; +--------------------------------------+--------+ | Variable_name | Value | +--------------------------------------+--------+ | validate_password_check_user_name | ON | | validate_password_dictionary_file | | | validate_password_length | 8 | | validate_password_mixed_case_count | 1 | | validate_password_number_count | 1 | | validate_password_policy | MEDIUM | | validate_password_special_char_count | 1 | + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- + 7 rows in the set (0.09 SEC)Copy the code1. Validate_password_policy indicates the password policy. The value can be: The default value is MEDIUM 0 or LOW. The password length is specified by parameter VALIDate_password_length. 1 or MEDIUM The password must meet the LOW policy and contain at least one digit, lowercase letters, and lowercase letters. Uppercase letters and special characters 2 or STRONG meet the MEDIUM policy and the password cannot be stored in dictionary file. 2. Validate_password_dictionary_file # Specifies the dictionary file used to configure passwords. When validATE_password_policy is set to STRONG, you can configure a password dictionary file. Passwords in the dictionary file cannot be used. Validate_password_length # specifies the minimum password length. The default value is 8. The minimum value is 0. 4. Default is 1 and minimum is 0; The default is to have at least one lowercase and one uppercase letter. When validATE_password_policy is set to MEDIUM or STRONG, the password must contain at least one number of digits. If validATE_PASSword_special_char_count is set to MEDIUM or STRONG, the password must contain at least one special character. The default value of 1 is 0Copy the code5. Change password regularly:
Change the password validity period to 90 days in /etc/my.cnf.
mysql> show global variables like 'default_password_lifetime'; +---------------------------+-------+ | Variable_name | Value | +---------------------------+-------+ | Default_password_lifetime | 90 | + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- + 1 row in the set (0.01 SEC)Copy the codeIf password_lifetime is null, the validity period of the current password is a global variable used by the user. If default_password_lifetime is 0, the validity period is forever.
In other words, all of these items fit, and the only thing you need to do is change your password periodically.
mysql> select user,host,password_lifetime,password_last_changed from mysql.user; +-----------+------+-------------------+-----------------------+ | user | host | password_lifetime | password_last_changed | +-----------+------+-------------------+-----------------------+ | admin_fqr | % | NULL | 2021-03-01 13:58:58 | | admin_xl | % | NULL | 2021-03-01 13:57:48 | | dev | % | NULL | 2021-02-01 17:24:28 | | dev_db | % | NULL | 2021-03-01 10:58:23 | | dev_xl | % | NULL | 2021-03-01 14:01:56 | | log_db | % | NULL | 2021-03-01 10:58:23 | | log_xl | % | NULL | 2021-03-01 14:01:28 | | system_db | % | NULL | 2021-03-01 10:58:23 | + -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + 8 rows in the set (0.00 SEC)Copy the code2.2 Assessment item B
A) Assessment content
It should have the function of handling login failures. It should configure and enable measures such as ending sessions, limiting the number of illegal login times, and automatically exiting when the login connection times out.
B) Rectification suggestions
You are advised to configure the login failure processing function for the database. You are advised to configure and enable measures such as ending a session, limiting the number of illegal login attempts, and automatically logging out when the login connection times out. You can lock a user for 30 minutes after five login failures. The login timeout is set to 30 minutes.
C) Rectification process
Mysql max_connect_error
After investigation, it was found that this maximum connection error is not the same concept as a true input password error. Details you can see this article: www.cnblogs.com/kerrycode/p…
Therefore, it makes no sense to change this parameter to 5, which is essentially because the same IP generates too many broken database connections in a short period of time for logins to be denied if they exceed this value.
mysql> show variables like "%max_connect_error%"; +--------------------+-------+ | Variable_name | Value | +--------------------+-------+ | max_connect_errors | 100 | + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- + 1 row in the set (0.00 SEC)Copy the code2, mysql plug-in installation
Therefore, this requires both CONNECTION_CONTROL (session control limiting login attempts) and CONNECTION_CONTROL_FAILED_LOGIN_ATTEMPTS.
mysql> Install plugin connection_control soname "connection_control.so";
Query OK, 0 rows affected (0.05 sec)
mysql> INSTALL PLUGIN CONNECTION_CONTROL_FAILED_LOGIN_ATTEMPTS SONAME 'connection_control.so';
Query OK, 0 rows affected (0.00 sec)
Copy the codeView the active status of two plug-ins:
mysql> select plugin_name,plugin_status from INFORMATION_SCHEMA.PLUGINS where PLUGIN_NAME LIKE 'connection%'; +------------------------------------------+---------------+ | plugin_name | plugin_status | +------------------------------------------+---------------+ | CONNECTION_CONTROL | ACTIVE | | CONNECTION_CONTROL_FAILED_LOGIN_ATTEMPTS | ACTIVE | +------------------------------------------+---------------+ 2 rows In the set (0.00 SEC)Copy the codeMysql > select * from ‘mysql’;
mysql> show variables like "%connection_control%"; +-------------------------------------------------+---------+ | Variable_name | Value | +-------------------------------------------------+---------+ | connection_control_failed_connections_threshold | 5 | | connection_control_max_connection_delay | 1800000 | | connection_control_min_connection_delay | 1800000 | + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- + 3 rows in the set (0.01 SEC)Copy the codeConnection_control_failed_connections_threshold: The number of consecutive failed connection attempts allowed by the client before the server adds a delay for subsequent connection attempts.
Connection_control_min_connection_delay: The amount of delay to be added for each consecutive connection failure that exceeds the threshold.
Connection_control_max_connection_delay: maximum delay to be added.
Of course, it is best to add parameters to the my.cnf file. This is the modified parameter.
connection_control_failed_connections_threshold=5
connection_control_min_connection_delay=1800000
connection_control_max_connection_delay=1800000
Copy the code4. Timeout function:
Mysql > select * from ‘timeout’;
Wait_timeout: This parameter is in seconds. The default is 28880
Interactive_timeout: interactive login timeout
connect_timeout =10
Mysql uses wait_timeout when using interactive operations, that is, when you open the mysql black window to perform operations, which is already configured by default. Interactive_timeout is used when using non-interactive operations, such as your application’s JDBC connection to a database.
wait_timeout=1800
interactive_timeout=1800
connect_timeout=10
Copy the code2.3 Assessment item C
A) Assessment content
When conducting remote management, necessary measures should be taken to prevent the identification information from being eavesdropped during network transmission.
B) Rectification suggestions
It is recommended that fortnite + SSH be used for remote database management to prevent authentication information from being eavesdropped during network transmission.
C) Rectification process
1. About SSH: Check whether SSL is enabled
mysql> show variables like "%have_ssl%";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| have_ssl | YES |
+---------------+-------+
1 row in set (0.00 sec)
Copy the code2.4 Assessment item D
A) Assessment content
Two or more combination of authentication technologies such as password, cryptography and biotechnology should be used to authenticate users, and at least one of the authentication technologies should be implemented by cryptography.
B) Rectification suggestions
It is suggested that the database should use two or more authentication technologies such as password, password technology and biological technology to authenticate users, and at least one of the authentication technologies should be implemented by password technology.
It is recommended to use SSH + Fortress to achieve.
Access control
2.1 Assessment item A
A) Assessment content
Accounts and permissions should be assigned to logged-in users;
B) Rectification suggestions
You are advised to assign accounts and permissions to login users by the administrator. That is, you have to divide it up into several roles, and that’s going to be done later on with test D.
2.2 Assessment item B
A) Assessment content
Rename or delete the default account and change the default password of the default account
B) Rectification suggestions
You are advised to rename or delete the default account, change the default password, or disable the default account. I’ll skip it briefly.
2.3 Assessment item C
A) Assessment content
Delete or disable redundant or expired accounts in time to avoid the existence of shared accounts.
B) Rectification suggestions
You are advised to delete or disable redundant or expired accounts to avoid sharing accounts. Create an account for each person as required. Look at measure D
2.4 Assessment item D
A) Assessment content:
The minimum authority required by the management user should be granted to achieve the separation of authority of the management user;
B) Rectification Suggestions:
You are advised to create roles such as Operator, auditor, and security administrator. For security management, security policy makers, operators, and auditors only need to have the permission to view audit logs. Minimize user permissions and separate management user permissions.
C) Rectification process:
1. Create three roles: operator, auditor, and administrator.
Operator: Responsible for business level development, corresponding to us developers.
Auditor: You only need to have the audit permission on logs
Administrator: has all permissions and makes security policies.
mysql> create role 'system_db','dev_db','log_db'; Query OK, 0 rows affected (0.02sec)Copy the code2. Create a user and assign all permissions to the administrator role
Do not assign file, process, or super permissions to anyone other than the administrator.
mysql> GRANT ALL privileges on *.* to 'system_db'@'%' with grant option; Query OK, 0 rows affected (0.01sec) mysql> create user 'admin'@'%' identified by 'giantAdmin@2021'; Query OK, 0 rows affected (0.01sec) MYSQL > grant 'system_db' to 'admin'@'%'; Query OK, 0 rows affected (0.00 SEC)Copy the code3. View the rights of the role
mysql> show grants for 'admin'@'%';
+--------------------------------------+
| Grants for admin@% |
+--------------------------------------+
| GRANT USAGE ON *.* TO `admin`@`%` |
| GRANT `system_db`@`%` TO `admin`@`%` |
+--------------------------------------+
2 rows in set (0.00 sec)
Copy the code[note] : USAGE is the connection (login) permission, create a user, will automatically grant its USAGE permission (default grant). This permission cannot be reclaimed regardless of it.
4. Create users and assign permissions to development roles.
For developers:
1) Give users the operation of adding, deleting and modifying the database;
2) allow users to create, modify, and delete table structures; Mysql foreign key permission, temporary table permission, index permission, mysql view permission, mysql stored procedure permission;
3) Assign global permissions to the user: reload, process. Viewing All Processes
mysql> create user 'dev_xl'@'%' identified by 'giantDev@xl2021'; Query OK, 0 rows affected (0.02sec) MYSQL > grant 'dev_db' to 'dev_xl'@'%'; Query OK, 0 rows affected (0.01 SEC) = = = = = = = = = = = = here is specific for a library permissions for = = = = = = = = = = = = = = = = 1, the permissions for wiedp library = = = = mysql > grant select,insert,update,delete on wiedp.* to 'dev_db'@'%'; Query OK, 0 rows affected (0.01 SEC) mysql > grant create, drop, alter, references, the create view, show view, create the routine, the alter routine,create temporary tables,index,execute on wiedp.* to 'dev_db'@'%'; Query OK, 0 rows affected (0.01sec) mysql> grant reload,process on *.* to 'dev_db'@'%'; Query OK, 0 rows affected (0.00 SEC) 2, permissions for wiedp_sub = = = = = = mysql > grant the select, insert, update, delete on wiedp_sub. * the to 'dev_db'@'%'; Query OK, 0 rows affected (0.01 SEC) mysql > grant create, drop, alter, references, the create view, show view, create the routine, the alter routine,create temporary tables,index,execute on wiedp_sub.* to 'dev_db'@'%'; Query OK, 0 rows affected (0.01 SEC) = = = 3, according to official permission = = = = mysql > grant the select, insert, update, and delete on official. * the to 'dev_db'@'%'; Query OK, 0 rows affected (0.01 SEC) mysql > grant create, drop, alter, references, the create view, show view, create the routine, the alter routine,create temporary tables,index,execute on official.* to 'dev_db'@'%'; Query OK, 0 rows affected (0.00 SEC)Copy the code5. Create auditors and assign roles
1) Allocate auditors’ query and insert and delete rights to the Auditlog library.
mysql> create user 'log_xl'@'%' identified by 'giantLog@xl2021'; Grant 'log_db' to 'log_xl'@'%'; Query OK, 0 rows affected (0.02sec) mysql> grant 'log_db' to 'log_xl'@'%'; Query OK, 0 rows affected (0.01sec) === affected (0.01sec) === mysql> grant INSERT, SELECT,delete on auditlog.t_audit to 'log_db'; Query OK, 0 rows affected (0.00 SEC) mysql> grant reload,process on *.* to 'log_db'@'%'; Query OK, 0 rows affected (0.00 SEC)Copy the code6. Activate the character
Note that after a role is created, it is not activated directly. There are several ways to activate the role, but one of the recommended ways is to configure parameters in the configuration file.
After the user logs in, the user can check whether the role exists. Check whether the parameter is off. Change the bit on and the role is automatically activated after login.
mysql> select current_role(); +----------------+ | current_role() | +----------------+ | NONE | +----------------+ mysql> show global variables like 'activate_all_roles_on_login'; +-----------------------------+-------+ | Variable_name | Value | +-----------------------------+-------+ | Activate_all_roles_on_login | OFF | + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- + 1 row in the set (0.00 SEC)Copy the codeThree, security audit
3.1 Assessment item A
A) Assessment requirements
The security audit function should be enabled to cover all users and audit important user behaviors and security events
B) Rectification suggestions
It is recommended that the security audit function be enabled for the database to cover all users and audit important user behaviors and security events.
C) Rectification process
1. View the audit configuration
mysql> show global variables like 'log_timestamps'; +----------------+-------+ | Variable_name | Value | +----------------+-------+ | log_timestamps | UTC | + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- + 1 row in the set (0.01 SEC) mysql > show global variables like '% general %'; +------------------+-----------------------------------------+ | Variable_name | Value | +------------------+-----------------------------------------+ | general_log | OFF | | general_log_file | /opt/software/mysql8/data/localhost.log | +------------------+-----------------------------------------+ 2 rows in set (0.01 SEC)Copy the code【解析】 : the initial parameters are as follows:
Log_timestamps Indicates the time from which the audit logs are recorded. UTC is the global time
General_log indicates whether the audit function is enabled.
General_log_file specifies the file to store audit information
2. Enable the audit function
Add the contents to the /etc/my.cnf file and restart mysql
general_log=on
general_log_file=/home/mysql8/data/auditlog.log
log_timestamps=system
Copy the code3. After the restart, check the configuration again
mysql> show global variables like 'log_timestamps'; +----------------+--------+ | Variable_name | Value | +----------------+--------+ | log_timestamps | SYSTEM | + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- + 1 row in the set (0.00 SEC) mysql > show global variables like '% general %'; +------------------+----------------------------------------+ | Variable_name | Value | +------------------+----------------------------------------+ | general_log | ON | | general_log_file | /opt/software/mysql8/data/auditlog.log | +------------------+----------------------------------------+ 2 rows in set (0.00 SEC)Copy the code4. Test the audit function
Look at the auditlog file after operating on the table, there are records
ORDINAL_POSITION, SUBPARTITION_ORDINAL_POSITION 2021-03-01T11:55:09.393973+08:00 9 Init DB test 2021-03-01T11:55:09.400381+08:00 9 Query SELECT * FROM 'test'. 'user_test' LIMIT 0,1000 2021-03-01t11:55:09.421726 +08:00 10 Connect [email protected] on using TCP/IP 2021-03-01T11:55:09.425147+08:00 10 Query SET NAMES UTF8MB4 2021-03-01T11:55:09.432346+08:00 10 Init DB test 2021-03-01T11:55:09.436096+08:00 10 Init DB testCopy the code5. Set init-connect
At this point, the auditing function is set up, but this logs all operations to the database, so next we will implement a relatively complete auditing function with init-connect +binlog.
(a) Create databases and tables to store connection logs
mysql> create database auditlog; Query OK, 1 row affected (0.01sec) mysql> create table auditlog.t_audit(-> id int not null auto_increment, -> thread_id int not null, -> login_time timestamp, -> localname varchar(50) default null, -> matchname varchar(50) default null, -> primary key (id) ->)ENGINE=InnoDB default charset=utf8 comment 'audit user login info '; Query OK, 0 rows affected, 1 warning (0.03 SEC)Copy the code(b) Authorize different roles to operate the audit table
mysql> grant insert on auditlog.t_audit to 'dev_db'; Query OK, 0 rows affected (0.01sec) MYSQL > grant INSERT, SELECT,delete on auditlog.t_audit to 'log_db'; Query OK, 0 rows affected (0.00 SEC)Copy the code6. Set init-connect
Configure the following information in /etc/my. CNF to complete log audit
init-connect='insert into auditlog.t_audit(id,thread_id,login_time,localname,matchname) values(null,connection_id(),now(),user(),current_user()); 'Copy the code7. The test results are as follows:
【解析】 : why is it necessary to make a table to record user login information after the audit function is enabled?
1. No matter whether SQL has syntax errors, as long as it is executed, it will be recorded, resulting in a large number of useless information recorded, and it is difficult to filter later. Therefore, after adding a table, we can filter out the thread_ID of the login user who performs the operation in this period according to a general operation time. Then, we can quickly screen out the user who performs a specific operation by looking up the user whose thread_ID is logged in in the table.
2, because the newly created table ordinary users do not have the permission to delete records, and the log file can delete records, so if there is no audit table, if ordinary users can contact the host where the log is stored, they can manually delete.
Reference article: blog.csdn.net/Smile_coder…
3.2 Assessment item B
A) Assessment content:
The audit record should include the date of the event, users, event type, event success, and other information related to the audit.
B) Main checkpoints:
Whether or not to include this information
C) Rectification process:
As long as the audit function is enabled, both built-in audit and plug-ins can meet this requirement in recorded information.
3.3 Assessment item C
A) Assessment content:
Audit records should be protected and backed up regularly to avoid unexpected deletion, modification, or overwriting
B) Rectification process:
If audit records are stored in files, you need to restrict the permissions of these log files on the operating system to allow only the database administrator to access and modify the files. Also restrict file_priv permissions in MYSQL.
If audit records are stored in database tables, permissions should also be set on the database tables so that only the database administrator can access, modify, and so on audit records.
3.4 Assessment item D
A) Assessment content:
The audit process should be protected against unauthorized interruptions
B) Rectification process:
The audit process needs to be configured. One is my.cnf. In this case, the operating system needs to restrict the permission of the configuration file, and only the database management can modify it. (Also restrict mysql file_priv permissions).
The other one is those variables. It seems that you need super permission to set global variables, so you need to check which accounts have super permission.
Iv. Intrusion prevention
4.1 Assessment Content
You must set the terminal access mode or network address range to restrict the management terminals that can be managed through the network.
4.2 Rectification Suggestions
You are advised to set the terminal access mode or network address range and allow only specific IP addresses or address segments to log in. Only remote login through SSH is allowed on the Intranet.
4.3 Rectification Process
You should check to see if the user’s login address imposes AN IP limit on all users. If you restrict access to the database to a specific terminal IP on the firewall, this is ok.
Set the previous host network segment to a specified IP address segment. Note that the to_host of the user table and the role table must be the same
5. Trust verification
5.1 Assessment Content
Can be based on the trusted root system boot program of computing devices, system program, important configuration parameters and application of trusted verification, and in the application of the key links for dynamic trusted authentication, after detected its credibility is damaged to call the police, and to verify the result audit records sent to the security management center.
5.2 Rectification Suggestions
Future can consider the reliable technology based on the trusted root on the boundary of equipment system boot program, system, important configuration parameters and border protection application trusted authentication, etc. And in the application of the key links for dynamic trusted authentication, after detected its credibility is damaged to call the police, and to verify the result audit records sent to the security management center.
The cost is too high to suggest rectification
6. Data integrity
6.1 Assessment Item A
A) Assessment content:
Verify or password technology should be used to ensure the integrity of important data during transmission, including but not limited to authentication data, important business data, important audit data, important configuration data, important video data, and important personal information.
B) Rectification Suggestions:
It is recommended to adopt fortress and SSH technology to ensure the integrity of important data during transmission, including but not limited to authentication data, important business data, important audit data, important configuration data, important video data, and important personal information.
6.2 Assessment item B
A) Assessment content:
The verification or password technology is used to ensure the integrity of important data, including but not limited to authentication data, important service data, important audit data, important configuration data, important video data, and important personal information.
B) Rectification Suggestions:
You are advised to use MD5 or other similar technologies to ensure the integrity of important data in stored procedures, including but not limited to authentication data, important service data, important audit data, important configuration data, important video data, and important personal information. It is suggested to encrypt and store personal information to achieve partial rectification.
This means that sensitive information, such as user passwords and mobile phone numbers, needs to be encrypted and stored.
Vii. Data confidentiality
7.1 Assessment Item A
1) Assessment content:
Cryptographic technology shall be adopted to ensure the confidentiality of important data during transmission, including but not limited to identification data, important business data and important personal information.
2) Rectification Suggestions:
It is recommended to adopt fortress and SSH technology to ensure the confidentiality of important data during transmission, including but not limited to identification data, important business data and important personal information.
7.2 Assessment item B
1) Assessment content:
Cryptography should be used to ensure the confidentiality of important data in stored procedures, including but not limited to authentication data, important business data, and important personal information.
2) Rectification Suggestions:
You are advised to use encryption methods such as hash algorithm and salt to encrypt files to ensure the confidentiality of important data during stored procedures, including but not limited to authentication data, important service data, and important personal information. It is recommended that personal information be encrypted and stored to achieve partial rectification, as well as data integrity.
8. Data backup and restoration
8.1 Assessment item A
1) Assessment content:
Local data backup and recovery of important data should be provided;
2) Rectification Suggestions:
Recommended local data backup and recovery functions for critical data;
8.2 Assessment item B
1) Assessment content:
The remote real-time backup function should be provided to back up important data to the backup site in real time by using the communication network.
2) Rectification Suggestions:
It is recommended to provide the remote real-time backup function, and use the communication network to back up important data to the backup site in real time. Remote Dr Is implemented. It is recommended to perform remote backup in other areas of Ali Cloud
8.3 Assessment item C
1) Assessment content:
Thermal redundancy of important data processing systems should be provided to ensure high availability of the system.
2) Rectification Suggestions:
You are advised to use hot redundancy or high availability databases to ensure high availability of the system.