Select any two versions of this STIG to compare the individual requirements
Select any old version/release of this STIG to view the previous requirements
Determine if an organization-level authentication/access mechanism providing account management and automation for all users, groups, roles, and any other principals has been configured. To determine if a MySQL Server has any external authentication plugins, connect as a mysql administrator (root) and run the following query: SELECT PLUGIN_NAME, PLUGIN_STATUS FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_NAME LIKE '%ldap%' OR PLUGIN_NAME LIKE '%pam%' OR PLUGIN_NAME LIKE '%authentication_windows %'; One or more of the following plugins must be installed and in the listed results: authentication_ldap_simple authentication_ldap_sasl authentication_pam authentication_windows If at least one of the above plugins is not installed, then no organization-level authentication/access is in place, and this is a finding. Depending on the plugin in use, review its configuration. For a list of global variables, run the following query: SELECT VARIABLE_NAME, VARIABLE_VALUE FROM performance_schema.global_variables WHERE VARIABLE_NAME LIKE 'auth%' ; If the LDAP plugin is installed, check the ldap_host and mapping. For the LDAP plugin, global variables showing the configuration for authentication to ldap hosts and binding to organizational users should look similar to the following: authentication_ldap_simple_server_host=127.0.0.1 authentication_ldap_simple_bind_base_dn="dc=example,dc=com" authentication_ldap_sasl_server_host=127.0.0.1 authentication_ldap_sasl_bind_base_dn="dc=example,dc=com" If the ldap_host is not a valid authentication host or the mapping to the base_dn maps is not correct, this is a finding. Determine the accounts (SQL Logins) managed by PAM. Run the statement: SELECT `user`.`Host`, `user`.`user`, `user`.`plugin`, `user`.`authentication_string` from mysql.user where plugin like 'authentication_pam'; For PAM, the string consists of a PAM service name, optionally followed by a PAM group mapping list consisting of one or more keyword/value pairs each specifying a PAM group name and a MySQL user name. If not defined, this is a finding. If the windows plugin is installed, the organization mapping details will be defined within the user "authentication string". Determine the accounts (SQL logins) managed by Windows. Run the statement: Review the accounts SELECT `user`.`Host`, `user`.`user`, `user`.`plugin`, `user`.`authentication_string` from mysql.user where plugin like 'authentication_windows; Verify that the Windows user, group, and windows role in the authentication_string map to proper organizational users. If not, this is a finding. To determine the accounts (MySQL accounts) actually managed by MySQL Server. Run the statement: SELECT `user`.`Host`, `user`.`User`, `user`.`plugin`, `user`.`authentication_string` from mysql.user where plugin not like 'auth%' and `user`.`User` not like 'mysql.%'; If any accounts listed by the query are not listed in the documentation and authorized, this is a finding.
Integrate MySQL database server 8.0 security with an organization-level authentication/access mechanism using MySQL external authentication for Microsoft AD or LDAP, or Linux PAMs thus providing account management for all users, groups, roles, and any other principals. If native mysql users are required, document the need and justification; describe the measures taken to ensure the use of MySQL Server authentication is kept to a minimum; describe the measures taken to safeguard passwords; list or describe the MySQL logins used. For each MySQL database server 8.0 managed account that is not documented and approved, either transfer it to management by the external mechanism, or document the need for it and obtain approval, as appropriate. Install appropriate external authentication plugin, for example to install LDAP. INSTALL PLUGIN authentication_ldap_sasl SONAME 'authentication_ldap_sasl.so'; INSTALL PLUGIN authentication_ldap_simple SONAME 'authentication_ldap_simple.so'; Configure the plugin, for example: SET PERSIST authentication_ldap_sasl_server_host='127.0.0.1'; SET PERSIST authentication_ldap_sasl_bind_base_dn='dc=example,dc=com'; SET PERSIST authentication_ldap_simple_server_host='127.0.0.1'; SET PERSIST authentication_ldap_simple_bind_base_dn='dc=example,dc=com'; Create users with proper organizational mapping, for example: CREATE USER 'betsy'@'localhost' IDENTIFIED WITH authentication_ldap_simple BY 'uid=betsy_ldap,ou=People,dc=example,dc=com'; Assign appropriate permissions via grants on objects or to roles, etc. See https://dev.mysql.com/doc/refman/8.0/en/grant.html. For example: GRANT ALL ON db1.* TO 'betsy'@'localhost'; GRANT 'role1', 'role2' TO 'user1'@'localhost', 'user2'@'localhost'; GRANT SELECT ON world.* TO 'role3'; For accounts not required in the MySQL Server: DROP USER <user_name>;
Determine whether the system documentation specifies limits on the number of concurrent MySQL database server 8.0 sessions. Review the concurrent-sessions settings in the MySQL database server and/or the applications using it, and/or the system software supporting it. MySQL global variable max_user_connections limits the number of simultaneous connections that can be made by any given account. To check global (default) concurrent-sessions settings in the MySQL database server, run the following query: SELECT VARIABLE_NAME, VARIABLE_VALUE FROM performance_schema.global_variables WHERE VARIABLE_NAME LIKE 'max_user_connections' ; If the value of MAX_USER_CONNECTIONS is 0 (unlimited) or greater than the site-specific maximum number of sessions, this is a finding. Retrieve the settings for concurrent sessions for each user with the query: SELECT user, host, max_user_connections FROM mysql.user WHERE user not like 'mysql.%' and user not like 'root'; If the user account has a nonzero MAX_USER_CONNECTIONS resource limit, the session MAX_USER_CONNECTIONS value is set to that limit. Otherwise, the session max_user_connections value is set to the global value. If the DBMS settings for concurrent sessions for each user is greater than the site-specific maximum number of sessions and nonzero, this is a finding.
The MySQL Database Server 8.0 is capable of enforcing this restriction. If not configured to do so, configure it to do so. Connect to the MySQL Database as an administrator. To set the global default to 50: SET PERSIST max_user_connections=50; Additionally, max user connections can be set per user as well as for a given period of time. GRANT ALL ON customer.* TO 'francis'@'localhost' WITH MAX_CONNECTIONS_PER_HOUR 5; MAX_USER_CONNECTIONS 2;
Verify, using vendor and system documentation if necessary, that the Database Management System (DBMS) is configured to use MySQL auditing features, or that a third-party product or custom code is deployed and configured to satisfy this requirement. Check MySQL auditing to determine whether organization-defined auditable events are being audited by the system. SELECT PLUGIN_NAME, plugin_status FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_NAME LIKE 'audit_log' ; If the results are not 'audit_log' and plugin_status='ACTIVE' , this is a finding. Next, determine if the audit log is encrypted: SELECT VARIABLE_NAME, VARIABLE_VALUE FROM performance_schema.global_variables WHERE VARIABLE_NAME LIKE 'audit_log_encryption' ; If nothing is returned or the value for audit_log_encryption is not AES, this is a finding. Review the audit files in the file systems. Run the following command using the audit log location from above and review its output: ls -l <directory where audit log files are located>/audit*log* For example, if the values returned by - "select @@datadir, @@audit_log_file; " are /usr/local/mysql/data/, audit.log ls -l /usr/local/mysql/data/audit.log Example output: -rw-r----- 1 _mysql _mysql 3935888 Apr 25 12:34 audit.20190425T173437.log.enc -rw-r----- 1 _mysql _mysql 2336 Apr 25 12:35 audit.20190425T173527.log.enc -rw-r----- 1 _mysql _mysql 13763984 Apr 30 14:04 audit.log.enc Next, verify the log files have set permissions the log_destination: If the user owner is not "mysql", this is a finding. If the group owner is not "mysql", this is a finding. If the file is more permissive than "640", this is a finding. Check that the files end with the ".enc" file extension. If they do not, this means they are in plaintext, and this is a finding. Run following command to verify the directory permissions and review its output: ls -l /usr/local/mysql/data Example output: drwxr-x--- _mysql _mysql 1760 Apr 26 09:55 data Next, verify the log files have set permissions for the log_destination: If the user owner is not "mysql", this is a finding. If the group owner is not "mysql", this is a finding. If more permissive than "750", this is a finding. If there are no audit log files, then organizational auditable events are not being audited, and this is a finding. To confirm that MySQL audit is capturing sufficient information to establish the identity of the user/subject or process, perform a successful auditable action and an auditable action that results in an SQL error, and then view the results in the audit file, whichever is in use. If no audit event is returned for the auditable actions just performed, this is a finding.
Configure DBMS auditing to audit standard and organization-defined auditable events, with the audit record to include what type of event occurred. Use this process to ensure auditable events are captured: Configure MySQL database server 8.0 for auditing and configure audit settings to include required events as part of the audit record. To install MySQL Enterprise Audit: Run the audit_log_filter_linux_install.sql script located in the sharedirectory of your MySQL installation. This can be determined by running – select @@basedir; For example if the basedir is /usr/local/mysql shell> bin/mysql -u root -p < /usr/local/mysql/share/audit_log_filter_linux_install.sql Verify the plugin installation by running: SELECT PLUGIN_NAME, PLUGIN_STATUS FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_NAME LIKE 'audit%'; The value for audit_log should return ACTIVE. To prevent the plugin from being removed at runtime, add the --audit-log option under the [mysqld] option group in the MySQL configuration file (/etc/my.cnf) with a setting of FORCE_PLUS_PERMANENT. audit-log=FORCE_PLUS_PERMANENT Restart the server to apply the configuration change. By default, rule-based audit log filtering logs no auditable events for any users. To produce log-everything behavior with rule-based filtering, create a filter to enable logging of all events and assign it to the audit all accounts. Run the following statements to filter all activity for all users: SELECT audit_log_filter_set_filter('log_all', '{ "filter": { "log": true } }'); SELECT audit_log_filter_set_user('%', 'log_all'); SELECT audit_log_filter_set_user('%', 'log_all');
If a MySQL Server Audit is not in use for audit purposes, this is a finding unless a third-party product is being used that can perform detailed auditing for MySQL Server. Review system documentation to determine whether MySQL Server is required to audit any events and any fields, in addition to those in the standard audit. If there are none specified, this is not a finding. If MySQL Server Audit is in use, compare the audit specification(s) with the documented requirements. If any such requirement is not satisfied by the audit specification(s) (or by supplemental, locally-deployed mechanisms), this is a finding.
Design and deploy an audit configuration that captures all auditable events and data items. Create rule SELECT audit_log_filter_set_filter('log_all', '{ "filter": { "log": true } }'); SELECT audit_log_filter_set_user('%', 'log_all'); If a third-party tool is used for auditing, it must contain all the required information including, but not limited to, events, type, location, subject, date and time and by whom the change occurred. Implement additional custom audits to capture the additional organizationally required information.
Review locations of audit logs, both internal to the database and database audit logs located at the operating system level. Verify there are appropriate controls and permissions to protect the audit information from unauthorized access. Run this script in the database to find the path and file name: SELECT VARIABLE_NAME, VARIABLE_VALUE FROM performance_schema.global_variables WHERE VARIABLE_NAME LIKE 'audit_log_file'; If there is no path for audit_log_file, then the audit files are located in the datadir. Run the this script to find the data directory: SELECT VARIABLE_NAME, VARIABLE_VALUE FROM performance_schema.global_variables WHERE VARIABLE_NAME LIKE 'datadir'; From the OS command line, run: ls -l <directory where audit log files are located> ls -l <directory where audit log files are located> | grep -i <audit_file_name> For example if the values returned by - "select @@datadir, @@audit_log_file; " are /usr/local/mysql/data/, audit.log ls -l /usr/local/mysql/data/ See below for an example: Note: .enc file extension means the files are encrypted. ls -l <directory where audit log files are located>/ | grep -i audit -rw-r----- 1 _mysql _mysql 10083871 Apr 16 15:38 audit.20190416T203832.log -rw-r----- 1 _mysql _mysql 398709 Apr 18 10:34 audit.20190418T153446.log -rw-r----- 1 _mysql _mysql 15237 Apr 18 10:44 audit.20190418T154402.log -rw-r----- 1 _mysql _mysql 876206 Apr 24 14:00 audit.20190424T190008.log -rw-r----- 1 _mysql _mysql 30208 Apr 24 14:10 audit.20190424T191044.log.enc If the owner and group are not "mysql" or "_mysql", this is a finding. If the directory or file permissions are more permissive than owner having Read/Write (RW) and group having Read (R) access to the audit files, aka "750", this is a finding.
Apply controls and modify permissions to protect database audit log data from unauthorized access, whether stored in the database itself or at the OS level. sudo vi /etc/my.cnf [mysqld] audit-log=FORCE_PLUS_PERMANENT audit-log-format=JSON audit-log-encryption=AES After changing the my.cnf, restart the server. If not performed already, set the audit log password. SELECT audit_log_encryption_password_set(password); Set appropriate permissions on the directory and audit files. sudo chown mysql <audit directory path> sudo chgrp mysql <audit directory path> Change permissions chmod 750 <directory path>
Review locations of audit logs, both internal to the database and database audit logs located at the operating system level. Verify there are appropriate controls and permissions to protect the audit information from unauthorized access. Run this script in the database to find the path and file name: SELECT VARIABLE_NAME, VARIABLE_VALUE FROM performance_schema.global_variables WHERE VARIABLE_NAME LIKE 'audit_log_file'; If there is no path for audit_log_file then the audit files are located in the datadir. Run the this script to find the data directory: SELECT VARIABLE_NAME, VARIABLE_VALUE FROM performance_schema.global_variables WHERE VARIABLE_NAME LIKE 'datadir'; From the OS command line, run: ls -l <directory where audit log files are located> ls -l <directory where audit log files are located> | grep -i <audit_file_name> For example if the values returned by - "select @@datadir, @@audit_log_file; " are /usr/local/mysql/data/, audit.log ls -l /usr/local/mysql/data/audit See below for an example: Note: .enc file extension means the files are encrypted. ls -l <directory where audit log files are located>/ | grep -i audit -rw-r----- 1 _mysql _mysql 10083871 Apr 16 15:38 audit.20190416T203832.log -rw-r----- 1 _mysql _mysql 398709 Apr 18 10:34 audit.20190418T153446.log -rw-r----- 1 _mysql _mysql 15237 Apr 18 10:44 audit.20190418T154402.log -rw-r----- 1 _mysql _mysql 876206 Apr 24 14:00 audit.20190424T190008.log -rw-r----- 1 _mysql _mysql 30208 Apr 24 14:10 audit.20190424T191044.log.enc If the owner and group are not "mysql" or" _mysql", this is a finding. If the directory or file permissions are more permissive than owner having Read/Write (RW) and group having Read (R) access to the audit files, aka "750", this is a finding.
Apply controls and modify permissions to protect database audit log data from unauthorized access, whether stored in the database itself or at the OS level. sudo vi /etc/my.cnf [mysqld] audit-log=FORCE_PLUS_PERMANENT audit-log-format=JSON audit-log-encryption=AES After changing the my.cnf restart the server. If not performed already, set the audit log password. SELECT audit_log_encryption_password_set(password); Set appropriate permissions on the directory and audit files. sudo chown mysql <audit directory path> sudo chgrp mysql <audit directory path> Change permissions chmod 750 <directory path>
Review locations of audit logs, both internal to the database and database audit logs located at the operating system level. Verify there are appropriate controls and permissions to protect the audit information from unauthorized access. Run this script in the database to find the path and file name: SELECT VARIABLE_NAME, VARIABLE_VALUE FROM performance_schema.global_variables WHERE VARIABLE_NAME LIKE 'audit_log_file'; If there is no path for audit_log_file then the audit files are located in the datadir. Run the this script to find the data directory: SELECT VARIABLE_NAME, VARIABLE_VALUE FROM performance_schema.global_variables WHERE VARIABLE_NAME LIKE 'datadir'; From the OS command line, run: ls -l <directory where audit log files are located> ls -l <directory where audit log files are located> | grep -i <audit_file_name> For example if the values returned by - "select @@datadir, @@audit_log_file; " are /usr/local/mysql/data/, audit.log ls -l /usr/local/mysql/data/ See below for an example: Note: .enc file extension means the files are encrypted. ls -l <directory where audit log files are located>/ | grep -i audit -rw-r----- 1 _mysql _mysql 10083871 Apr 16 15:38 audit.20190416T203832.log -rw-r----- 1 _mysql _mysql 398709 Apr 18 10:34 audit.20190418T153446.log -rw-r----- 1 _mysql _mysql 15237 Apr 18 10:44 audit.20190418T154402.log -rw-r----- 1 _mysql _mysql 876206 Apr 24 14:00 audit.20190424T190008.log -rw-r----- 1 _mysql _mysql 30208 Apr 24 14:10 audit.20190424T191044.log.enc If the owner and group are not "mysql" or "_mysql", this is a finding. If the directory or file permissions are more permissive than owner having Read/Write (RW) and group having Read (R) access to the audit files, aka "750", this is a finding.
Apply controls and modify permissions to protect database audit log data from unauthorized access, whether stored in the database itself or at the OS level. sudo vi /etc/my.cnf [mysqld] audit-log=FORCE_PLUS_PERMANENT audit-log-format=JSON audit-log-encryption=AES After changing the my.cnf, restart the server. If not performed already, set the audit log password. SELECT audit_log_encryption_password_set(password); Set appropriate permissions on the directory and audit files. sudo chown mysql <audit directory path> sudo chgrp mysql <audit directory path> Change permissions chmod 750 <directory path>
Obtain the list of authorized MySQL Server accounts in the system documentation. Determine if any accounts are shared. A shared account is defined as a username, hostname, and password that are used by multiple individuals to log in to SQL Server. An example of a shared account is the MySQL Server root account – root@localhost. If accounts are determined to be shared, determine if individuals are first individually authenticated. If individuals are not individually authenticated before using the shared account (e.g., by the operating system or possibly by an application making calls to the database), this is a finding. The key is individual accountability. If this can be traced, this is not a finding. If accounts are determined to be shared, determine if they are directly accessible to end users. If so, this is a finding. Review contents of audit logs, traces, and data tables to confirm the identity of the individual user performing the action is captured. If shared identifiers are found, and not accompanied by individual identifiers, this is a finding. Note: Privileged installation accounts like root@localhost may be required to be accessed by the DBA or other administrators for system maintenance. In these cases, each use of the account must be logged in some manner to assign accountability for any actions taken during the use of the account.
Remove user-accessible shared accounts and use individual user IDs. Build/configure applications to ensure successful individual authentication prior to shared account access. Ensure each user's identity is received and used in audit data in all relevant circumstances. Design, develop, and implement a method to log use of any account to which more than one person has access. Restrict interactive access to shared accounts to the fewest persons possible.
Check MySQL auditing to determine whether organization-defined auditable events are being audited by the system. SELECT PLUGIN_NAME, plugin_status FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_NAME LIKE 'audit_log' ; If nothing is returned OR if the results are not "audit_log" and "plugin_status='ACTIVE'" , this is a finding. Next determine if the audit lot is encrypted. SELECT VARIABLE_NAME, VARIABLE_VALUE FROM performance_schema.global_variables WHERE VARIABLE_NAME LIKE 'audit_log_encryption' ; If nothing is returned OR the value for audit_log_encryption is not "AES", this is a finding.
Deploy a MySQL Database Server 8.0 that supports the DoD minimum set of auditable events. Configure the MySQL Database Server 8.0 to generate audit records for at least the DoD minimum set of events. sudo vi /etc/my.cnf [mysqld] audit-log=FORCE_PLUS_PERMANENT audit-log-format=JSON audit-log-encryption=AES After changing the my.cnf, restart the server. SELECT audit_log_encryption_password_set(password); Create auditing rules - for example: Connect to MySQL and Use functions to define audit rules and audited users audit_log_filter_set,audit_log_filter_set_user To log all auditable events: SELECT audit_log_filter_set_filter('log_all', '{ "filter": { "log": true } }'); And to apply this log_all filter to all users: SELECT audit_log_filter_set_user('%', 'log_all');
Check MySQL settings and documentation to determine whether designated personnel are able to select which auditable events are being audited. To list out users who have rights to administrative access for auditing, run this query: SELECT * FROM INFORMATION_SCHEMA.USER_PRIVILEGES where PRIVILEGE_TYPE in ('AUDIT_ADMIN', 'SUPER'); If any of the roles or users returned have permissions that are not documented, or the documented audit maintainers do not have permissions, this is a finding.
Configure the MySQL Database Server 8.0 settings to allow designated personnel to select which auditable events are audited. Grant permissions to users who need rights to create auditing rules. GRANT AUDIT_ADMIN ON *.* TO '<auditusername>'@'<host_specification>'; For example: GRANT AUDIT_ADMIN ON *.* TO 'auditusername'@'%';
Review the system documentation to determine if MySQL Server is required to audit the retrieval of privilege/permission/role membership information. Check if MySQL audit is configured and enabled. The my.cnf file will set the variable audit_file. To further check, execute the following query: SELECT PLUGIN_NAME, PLUGIN_STATUS FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_NAME LIKE 'audit%'; The status of the audit_log plugin must be "active". If it is not "active", this is a finding. Review audit filters and associated users by running the following queries: SELECT `audit_log_filter`.`NAME`, `audit_log_filter`.`FILTER` FROM `mysql`.`audit_log_filter`; SELECT `audit_log_user`.`USER`, `audit_log_user`.`HOST`, `audit_log_user`.`FILTERNAME` FROM `mysql`.`audit_log_user`; All currently defined audits for the MySQL server instance will be listed. If no audits are returned, this is a finding. To check if the audit filters that are in place are generating records when privileges/permissions are retrieved, run the following query: select * from mysql.proxies_priv; Review the audit log by running the Linux command: sudo cat <directory where audit log files are located>/audit.log|egrep proxies_prim For example if the values returned by - "select @@datadir, @@audit_log_file;" are /usr/local/mysql/data/, audit.log ls -l /usr/local/mysql/data/audit.log The audit data will look similar to the example below: { "timestamp": "2020-08-19 21:03:39", "id": 13, "class": "general", "event": "status", "connection_id": 9, "account": { "user": "root", "host": "localhost" }, "login": { "user": "root", "os": "", "ip": "::1", "proxy": "" }, "general_data": { "command": "Query", "sql_command": "select", "query": "select * from mysql.proxies_priv\nLIMIT 0, 1000", "status": 0 } }, If the audit event is not present, this is a finding.
If currently required, configure the MySQL Database Server to produce audit records when audit when privileges/permissions are retrieved. See the supplemental file "MySQL80Audit.sql".
Review the system documentation to determine if MySQL Server is required to audit when unsuccessful attempts to retrieve privileges/permissions occur. Check if MySQL audit is configured and enabled. The my.cnf file will set the variable audit_file. To further check, execute the following query: SELECT PLUGIN_NAME, PLUGIN_STATUS FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_NAME LIKE 'audit%'; The status of the audit_log plugin should be "active". If it is not "active", this is a finding. Review audit filters and associated users by running the following queries: SELECT `audit_log_filter`.`NAME`, `audit_log_filter`.`FILTER` FROM `mysql`.`audit_log_filter`; SELECT `audit_log_user`.`USER`, `audit_log_user`.`HOST`, `audit_log_user`.`FILTERNAME` FROM `mysql`.`audit_log_user`; All currently defined audits for the MySQL server instance will be listed. If no audits are returned, this is a finding. To check if the audit filters that are in place are generating records to audit when unsuccessful attempts to retrieve privileges/permissions occur, run the following query using a user that does not have privileges so that it fails: select * from mysql.proxies_priv; ERROR: 1142: SELECT command denied to user 'auditme'@'localhost' for table 'proxies_priv' Review the audit log by running the command: sudo cat <directory where audit log files are located>/audit.log|egrep proxies_priv For example if the values returned by "select @@datadir, @@audit_log_file; " are /usr/local/mysql/data/, audit.log sudo cat /usr/local/mysql/data/audit.log |egrep proxies_priv The audit data will look similar to the example below: { "timestamp": "2020-08-19 21:10:39", "id": 1, "class": "general", "event": "status", "connection_id": 13, "account": { "user": "auditme", "host": "localhost" }, "login": { "user": "auditme", "os": "", "ip": "::1", "proxy": "" }, "general_data": { "command": "Query", "sql_command": "select", "query": "select * from mysql.proxies_priv", "status": 1142 } }, Note status is 1142, like the error. If the audit event is not present, this is a finding.
If currently required, configure the MySQL Database Server to produce audit records when unsuccessful attempts to retrieve privileges/permissions occur. See the supplemental file "MySQL80Audit.sql".
Review the system documentation to determine if MySQL Server is required to audit when security objects are accessed. Check if MySQL audit is configured and enabled. The my.cnf file will set the variable audit_file. To further check, execute the following query: SELECT PLUGIN_NAME, PLUGIN_STATUS FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_NAME LIKE 'audit%'; The status of the audit_log plugin should be "active". If it is not "active", this is a finding. Review audit filters and associated users by running the following queries: SELECT `audit_log_filter`.`NAME`, `audit_log_filter`.`FILTER` FROM `mysql`.`audit_log_filter`; SELECT `audit_log_user`.`USER`, `audit_log_user`.`HOST`, `audit_log_user`.`FILTERNAME` FROM `mysql`.`audit_log_user`; All currently defined audits for the MySQL server instance will be listed. If no audits are returned, this is a finding. To check if the audit filters in place are generating records to audit when security objects are accessed, run the following query: select * from mysql.proxies_priv; Review the audit log by running the Linux command: sudo cat <directory where audit log files are located>/audit.log|egrep proxies_prim For example if the values returned by - "select @@datadir, @@audit_log_file; " are /usr/local/mysql/data/, audit.log sudo cat /usr/local/mysql/data/audit.log |egrep proxies_priv The audit data will look similar to the example below: { "timestamp": "2020-08-19 21:03:39", "id": 13, "class": "general", "event": "status", "connection_id": 9, "account": { "user": "root", "host": "localhost" }, "login": { "user": "root", "os": "", "ip": "::1", "proxy": "" }, "general_data": { "command": "Query", "sql_command": "select", "query": "select * from mysql.proxies_priv\nLIMIT 0, 1000", "status": 0 } }, If the audit event is not present, this is a finding.
If currently required, configure the MySQL Database Server to produce audit records when security objects are accessed. See the supplemental file "MySQL80Audit.sql".
Review the system documentation to determine if MySQL Server is required to audit when unsuccessful attempts to access security objects occur. Check if MySQL audit is configured and enabled. The my.cnf file will set the variable audit_file. To further check, execute the following query: SELECT PLUGIN_NAME, PLUGIN_STATUS FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_NAME LIKE 'audit%'; The status of the audit_log plugin must be "active". If it is not "active", this is a finding. Review audit filters and associated users by running the following queries: SELECT `audit_log_filter`.`NAME`, `audit_log_filter`.`FILTER` FROM `mysql`.`audit_log_filter`; SELECT `audit_log_user`.`USER`, `audit_log_user`.`HOST`, `audit_log_user`.`FILTERNAME` FROM `mysql`.`audit_log_user`; All currently defined audits for the MySQL server instance will be listed. If no audits are returned, this is a finding. To check if the audit filters in place are generating records when unsuccessful attempts to access security objects occur, run the following query with a user that does not have privileges so that it will fail: select * from mysql.proxies_priv; ERROR: 1142: SELECT command denied to user 'auditme'@'localhost' for table 'proxies_priv' Review the audit log by running the command: sudo cat <directory where audit log files are located>/audit.log|egrep proxies_priv For example if the values returned by - "select @@datadir, @@audit_log_file; " are /usr/local/mysql/data/, audit.log sudo cat /usr/local/mysql/data/audit.log |egrep proxies_priv For example if the values returned by - "select @@datadir, @@audit_log_file; " are /usr/local/mysql/data/, audit.log sudo cat /usr/local/mysql/data/audit.log |egrep proxies_priv The audit data will look similar to the example below: { "timestamp": "2020-08-19 21:10:39", "id": 1, "class": "general", "event": "status", "connection_id": 13, "account": { "user": "auditme", "host": "localhost" }, "login": { "user": "auditme", "os": "", "ip": "::1", "proxy": "" }, "general_data": { "command": "Query", "sql_command": "select", "query": "select * from mysql.proxies_priv", "status": 1142 } }, Note status is 1142, like the error. If the audit event is not present, this is a finding.
If currently required, configure the MySQL Database Server to produce audit records when unsuccessful attempts to retrieve privileges/permissions occur. See the supplemental file "MySQL80Audit.sql".
If classification levels/security levels labeling is not required, this is not a finding. Review the system documentation to determine if MySQL Server is required to audit records when unsuccessful attempts to delete categories of information (e.g., classification levels/security levels) occur. Check if MySQL audit is configured and enabled. The my.cnf file will set the variable audit_file. To further check, execute the following query: SELECT PLUGIN_NAME, PLUGIN_STATUS FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_NAME LIKE 'audit%'; The status of the audit_log plugin must be "active". If it is not "active", this is a finding. Review audit filters and associated users by running the following queries: SELECT `audit_log_filter`.`NAME`, `audit_log_filter`.`FILTER` FROM `mysql`.`audit_log_filter`; SELECT `audit_log_user`.`USER`, `audit_log_user`.`HOST`, `audit_log_user`.`FILTERNAME` FROM `mysql`.`audit_log_user`; All currently defined audits for the MySQL server instance will be listed. If no audits are returned, this is a finding. Modify MySQL selects that check for changes to categories of information. Modify selects statements to audit when information categories are accessed using MySQL Audit by calling the audit_api_message_emit_udf() function and including the details related to the select. - An Example test - CREATE TABLE `test_trigger`.`info_cat_test` ( `id` INT NOT NULL, `name` VARCHAR(20) NULL, `desc` VARCHAR(20) NULL, `sec_level` CHAR(1) NULL, PRIMARY KEY (`id`)); DELIMITER $$ INSERT INTO `test_trigger`.`info_cat_test` (`id`, `name`, `desc`, `sec_level`) VALUES ('1', 'fred', 'engineer', 'H'); INSERT INTO `test_trigger`.`info_cat_test` (`id`, `name`, `desc`, `sec_level`) VALUES ('2', 'jill', 'program manager', 'M'); INSERT INTO `test_trigger`.`info_cat_test` (`id`, `name`, `desc`, `sec_level`) VALUES ('3', 'joe', 'maintenance', 'L'); SELECT `info_cat_test`.`id`, `info_cat_test`.`name`, `info_cat_test`.`desc`, `info_cat_test`.`sec_level`, IF(`info_cat_test`.`sec_level`= 'H', audit_api_message_emit_udf('sec_level_selected', 'audit_select_attempt', ' H level sec data was accessed', 'FOR ', name ), 'Not Audited') FROM `test_trigger`.`info_cat_test`; Review the audit log by running the Linux command: sudo cat <directory where audit log files are located>/audit.log | egrep sec_level_selected For example if the values returned by - "select @@datadir, @@audit_log_file; " are /usr/local/mysql/data/, audit.log sudo cat /usr/local/mysql/data/audit.log |egrep sec_level_priv The audit data will look similar to the example below: { "timestamp": "2020-08-20 21:19:21", "id": 1, "class": "message", "event": "user", "connection_id": 9, "account": { "user": "root", "host": "localhost" }, "login": { "user": "root", "os": "", "ip": "::1", "proxy": "" }, "message_data": { "component": "sec_level_selected", "producer": "audit_select_attempt", "message": " H level sec data was accessed", "map": { "FOR ": "fred" } } }, If the audit event is not present, this is a finding.
If currently required, configure the MySQL Database Server with stored procedures that use selects that call audit_api_message_emit_udf() function to produce audit records when selection of categories of information occurs. Add security level details in an additional column. Add the component for adding information to the audit log. INSTALL COMPONENT "file://component_audit_api_message_emit”; create schema test_trigger; Modify selections adding the audit_api_message_emit_udf to be called, as well as providing the details for the audit event. Transparently enforcing the use of MySQL stored procedures is required. See the supplemental file "MySQL80Audit.sql".
If classification levels/security levels labeling is not required, this is not a finding. Review the system documentation to determine if MySQL Server is required to audit records when unsuccessful attempts to access categories of information (e.g., classification levels/security levels) occur. Check if MySQL audit is configured and enabled. The my.cnf file will set the variable audit_file. To further check, execute the following query: SELECT PLUGIN_NAME, PLUGIN_STATUS FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_NAME LIKE 'audit%'; The status of the audit_log plugin should be "active". If it is not "active", this is a finding. Review audit filters and associated users by running the following queries: SELECT `audit_log_filter`.`NAME`, `audit_log_filter`.`FILTER` FROM `mysql`.`audit_log_filter`; SELECT `audit_log_user`.`USER`, `audit_log_user`.`HOST`, `audit_log_user`.`FILTERNAME` FROM `mysql`.`audit_log_user`; All currently defined audits for the MySQL server instance will be listed. If no audits are returned, this is a finding. Modify MySQL selects that check for changes to categories of information. Modify selects statements to audit when information categories are access using MySQL Audit by calling the audit_api_message_emit_udf() function and including the details related to the select. - An Example test - CREATE TABLE `test_trigger`.`info_cat_test` ( `id` INT NOT NULL, `name` VARCHAR(20) NULL, `desc` VARCHAR(20) NULL, `sec_level` CHAR(1) NULL, PRIMARY KEY (`id`)); DELIMITER $$ INSERT INTO `test_trigger`.`info_cat_test` (`id`, `name`, `desc`, `sec_level`) VALUES ('1', 'fred', 'engineer', 'H'); INSERT INTO `test_trigger`.`info_cat_test` (`id`, `name`, `desc`, `sec_level`) VALUES ('2', 'jill', 'program manager', 'M'); INSERT INTO `test_trigger`.`info_cat_test` (`id`, `name`, `desc`, `sec_level`) VALUES ('3', 'joe', 'maintenance', 'L'); Create a view using the where clause similar to that shown in the select. If inappropriate access is attempted, in this case H level, the select statement will write to the Audit log using the emit function. SELECT `info_cat_test`.`id`, `info_cat_test`.`name`, `info_cat_test`.`desc`, `info_cat_test`.`sec_level` FROM `test_trigger`.`info_cat_test` where IF(`info_cat_test`.`sec_level`= 'H', CAST(audit_api_message_emit_udf('sec_level_H_ATTEMPTED_selected', 'audit_select_attempt', ' H level sec data was accessed', 'FOR ', name ) as CHAR), 'Not Audited') <> 'OK’; The above test will write an audit event related to the selection of H sec_level data. Review the audit log by running the Linux command: sudo cat <directory where audit log files are located>/audit.log | egrep sec_level_H_ATTEMPTED_selected For example if the values returned by - "select @@datadir, @@audit_log_file; " are /usr/local/mysql/data/, audit.log sudo cat /usr/local/mysql/data/audit.log |egrep sec_level_H_ATTEMPTED_selected If the audit event similar to the example below is not present, this is a finding. The audit data will look similar to the example below: Not Audited') <> 'OK'\nLIMIT 0, 1000", "sql_command": "select" } }, { "timestamp": "2020-08-21 14:04:53", "id": 2, "class": "message", "event": "user", "connection_id": 9, "account": { "user": "root", "host": "localhost" }, "login": { "user": "root", "os": "", "ip": "::1", "proxy": "" }, "message_data": { "component": "sec_level_H_ATTEMPTED_selected", "producer": "audit_select_attempt", "message": " H level sec data was accessed", "map": { "FOR ": "fred" } } },
If currently required, configure the MySQL Database Server with views that use selects that call audit_api_message_emit_udf() function to produce audit records when selection of categories of information occurs. Add security level details in an additional column if necessary. Add the component for adding information to the audit log. INSTALL COMPONENT "file://component_audit_api_message_emit”; To transparently enforce the use of MySQL view is required. See the supplemental file "MySQL80Audit.sql".
Check that MySQL Server Audit is being used for the STIG compliant audit. Check if MySQL audit is configured and enabled. The my.cnf file will set the variable audit_file. To further check, execute the following query: SELECT PLUGIN_NAME, PLUGIN_STATUS FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_NAME LIKE 'audit%'; The status of the audit_log plugin should be "active". If it is not "active", this is a finding. Review audit filters and associated users by running the following queries: SELECT `audit_log_filter`.`NAME`, `audit_log_filter`.`FILTER` FROM `mysql`.`audit_log_filter`; SELECT `audit_log_user`.`USER`, `audit_log_user`.`HOST`, `audit_log_user`.`FILTERNAME` FROM `mysql`.`audit_log_user`; All currently defined audits for the MySQL server instance will be listed. If no audits are returned, this is a finding. Determine if rules are in place to capture the following types of commands related to permissions by running the command: select * from mysql.audit_log_filter; If the template SQL filter was used, it will have the name log_stig. Review the filter value. It will show filters for events of the type field general_sql_command.str for the following SQL statement types: grant grant_roles revoke revoke_all revoke_roles drop_role alter_user_default_role create_role drop_role grant_roles revoke_roles set_role create_user alter_user drop_user alter_user alter_user_default_role create_user drop_user rename_user show_create_user
Configure the MySQL Database Server to audit when privileges/permissions are added. Add the following events to the MySQL Server Audit being used for the STIG compliance audit: grant grant_roles revoke revoke_all revoke_roles drop_role alter_user_default_role create_role drop_role grant_roles revoke_roles set_role create_user alter_user drop_user alter_user alter_user_default_role create_user drop_user rename_user show_create_user See the supplemental file "MySQL80Audit.sql".
Check that MySQL Server Audit is being used for the STIG compliant audit. Check if MySQL audit is configured and enabled. The my.cnf file will set the variable audit_file. To further check, execute the following query: SELECT PLUGIN_NAME, PLUGIN_STATUS FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_NAME LIKE 'audit%'; The status of the audit_log plugin must be "active". If it is not "active", this is a finding. Review audit filters and associated users by running the following queries: SELECT `audit_log_filter`.`NAME`, `audit_log_filter`.`FILTER` FROM `mysql`.`audit_log_filter`; SELECT `audit_log_user`.`USER`, `audit_log_user`.`HOST`, `audit_log_user`.`FILTERNAME` FROM `mysql`.`audit_log_user`; All currently defined audits for the MySQL server instance will be listed. If no audits are returned, this is a finding. Determine if rules are in place to capture the following types of commands related to permissions by running the command: select * from mysql.audit_log_filter; If the template SQL filter was used, it will have the name log_stig. Review the filter values. It will show filters for events of the type of field general_sql_command.str for the following SQL statement types: grant grant_roles revoke revoke_all revoke_roles drop_role alter_user_default_role create_role drop_role grant_roles revoke_roles set_role create_user alter_user drop_user alter_user alter_user_default_role create_user drop_user rename_user show_create_user
Configure the MySQL Database Server to audit when privileges/permissions are added. Add the following events to the MySQL Server Audit that is being used for the STIG compliance audit: grant grant_roles revoke revoke_all revoke_roles drop_role alter_user_default_role create_role drop_role grant_roles revoke_roles set_role create_user alter_user drop_user alter_user alter_user_default_role create_user drop_user rename_user show_create_user See the supplemental file "MySQL80Audit.sql".
Check that MySQL Server Audit is being used for the STIG compliant audit. Check if MySQL audit is configured and enabled. The my.cnf file will set the variable audit_file. To further check, execute the following query: SELECT PLUGIN_NAME, PLUGIN_STATUS FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_NAME LIKE 'audit%'; The status of the audit_log plugin must be "active". If it is not "active", this is a finding. Review audit filters and associated users by running the following queries: SELECT `audit_log_filter`.`NAME`, `audit_log_filter`.`FILTER` FROM `mysql`.`audit_log_filter`; SELECT `audit_log_user`.`USER`, `audit_log_user`.`HOST`, `audit_log_user`.`FILTERNAME` FROM `mysql`.`audit_log_user`; All currently defined audits for the MySQL server instance will be listed. If no audits are returned, this is a finding. Determine if rules are in place to capture the following types of commands related to permissions by running: select * from mysql.audit_log_filter; If the template SQL filter was used, it will have the name log_stig. Review the filter values it will show filters for events of the type of the field general_sql_command.str for the following SQL statement types: grant grant_roles revoke revoke_all revoke_roles drop_role alter_user_default_role create_role drop_role grant_roles revoke_roles set_role create_user alter_user drop_user alter_user alter_user_default_role create_user drop_user rename_user show_create_user
Configure the MySQL Database Server to audit when privileges/permissions are added. Add the following events to the MySQL Server Audit that is being used for the STIG compliance audit: grant grant_roles revoke revoke_all revoke_roles drop_role alter_user_default_role create_role drop_role grant_roles revoke_roles set_role create_user alter_user drop_user alter_user alter_user_default_role create_user drop_user rename_user show_create_user See the supplemental file "MySQL80Audit.sql".
Check that MySQL Server Audit is being used for the STIG compliant audit. Check if MySQL audit is configured and enabled. The my.cnf file will set the variable audit_file. To further check, execute the following query: SELECT PLUGIN_NAME, PLUGIN_STATUS FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_NAME LIKE 'audit%'; The status of the audit_log plugin must be "active". If it is not "active", this is a finding. Review audit filters and associated users by running the following queries: SELECT `audit_log_filter`.`NAME`, `audit_log_filter`.`FILTER` FROM `mysql`.`audit_log_filter`; SELECT `audit_log_user`.`USER`, `audit_log_user`.`HOST`, `audit_log_user`.`FILTERNAME` FROM `mysql`.`audit_log_user`; All currently defined audits for the MySQL server instance will be listed. If no audits are returned, this is a finding. Determine if rules are in place to capture the following types of commands related to permissions by running: select * from mysql.audit_log_filter; If the template SQL filter was used, it will have the name log_stig. Review the filter values. It will show filters for events of the type of the field general_sql_command.str for the following SQL statement types: grant grant_roles revoke revoke_all revoke_roles drop_role alter_user_default_role create_role drop_role grant_roles revoke_roles set_role create_user alter_user drop_user alter_user alter_user_default_role create_user drop_user rename_user show_create_user
Configure the MySQL Database Server to audit when privileges/permissions are added. Add the following events to the MySQL Server Audit that is being used for the STIG compliance audit: grant grant_roles revoke revoke_all revoke_roles drop_role alter_user_default_role create_role drop_role grant_roles revoke_roles set_role create_user alter_user drop_user alter_user alter_user_default_role create_user drop_user rename_user show_create_user See the supplemental file "MySQL80Audit.sql".
Review the system documentation to determine if MySQL Server is required to audit when security objects are modified. Check if MySQL audit is configured and enabled. The my.cnf file will set the variable audit_file. To further check, execute the following query: SELECT PLUGIN_NAME, PLUGIN_STATUS FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_NAME LIKE 'audit%'; The status of the audit_log plugin must be "active". If it is not "active", this is a finding. Review audit filters and associated users by running the following queries: SELECT `audit_log_filter`.`NAME`, `audit_log_filter`.`FILTER` FROM `mysql`.`audit_log_filter`; SELECT `audit_log_user`.`USER`, `audit_log_user`.`HOST`, `audit_log_user`.`FILTERNAME` FROM `mysql`.`audit_log_user`; All currently defined audits for the MySQL server instance will be listed. If no audits are returned, this is a finding. To check if the audit filters that are in place are generating records when security objects are modified, run the following, which will test auditing without destroying data: update mysql.global_grants set host='%' where PRIV='XXXX’; Review the audit log by running the Linux command: sudo cat <directory where audit log files are located>/audit.log|egrep global_grants For example if the values returned by "select @@datadir, @@audit_log_file; " are /usr/local/mysql/data/, audit.log sudo cat /usr/local/mysql/data/audit.log |egrep global_grants For example if the values returned by "select @@datadir, @@audit_log_file; " are /usr/local/mysql/data/, audit.log sudo cat /usr/local/mysql/data/audit.log |egrep global_grants The audit data will look similar to the example below: { "timestamp": "2020-08-19 21:32:27", "id": 2, "class": "general", "event": "status", "connection_id": 9, "account": { "user": "root", "host": "localhost" }, "login": { "user": "root", "os": "", "ip": "::1", "proxy": "" }, "general_data": { "command": "Query", "sql_command": "update", "query": "update mysql.global_grants set host='%' where PRIV='XXXX'", "status": 0 } } If the audit event is not present, this is a finding.
If currently required, configure the MySQL Database Server to produce audit records when security objects are modified. See the supplemental file "MySQL80Audit.sql".
Review the system documentation to determine if MySQL Server is required to audit when unsuccessful attempts to modify security objects occur. Check if MySQL audit is configured and enabled. The my.cnf file will set the variable audit_file. To further check, execute the following query: SELECT PLUGIN_NAME, PLUGIN_STATUS FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_NAME LIKE 'audit%'; The status of the audit_log plugin must be "active". If it is not "active", this is a finding. Review audit filters and associated users by running the following queries: SELECT `audit_log_filter`.`NAME`, `audit_log_filter`.`FILTER` FROM `mysql`.`audit_log_filter`; SELECT `audit_log_user`.`USER`, `audit_log_user`.`HOST`, `audit_log_user`.`FILTERNAME` FROM `mysql`.`audit_log_user`; All currently defined audits for the MySQL server instance will be listed. If no audits are returned, this is a finding. To check if the audit filters in place are generating records when unsuccessful attempts to modify security objects occur, run the following as a user without administrator-level privileges: update mysql.global_grants set host='%' where PRIV='XXXX’; Review the audit log by running the Linux command: sudo cat <directory where audit log files are located>/audit.log|egrep global_grants For example if the values returned by - "select @@datadir, @@audit_log_file; " are /usr/local/mysql/data/, audit.log sudo cat /usr/local/mysql/data/audit.log |egrep global_grants The audit data will look similar to the example below and contain a non-zero status value: { "timestamp": "2020-08-19 21:32:27", "id": 2, "class": "general", "event": "status", "connection_id": 9, "account": { "user": "root", "host": "localhost" }, "login": { "user": "root", "os": "", "ip": "::1", "proxy": "" }, "general_data": { "command": "Query", "sql_command": "update", "query": "update mysql.global_grants set host='%' where PRIV='XXXX'", "status": 1421 } } If the audit event is not present, this is a finding.
If currently required, configure the MySQL Database Server to produce audit records when unsuccessful attempts to modify security objects occur. See the supplemental file "MySQL80Audit.sql".
If classification levels/security levels labeling is not required, this is not a finding. Review the system documentation to determine if MySQL Server is required to audit records when deletion of categories of information (e.g., classification levels/security levels) occurs. Check if MySQL audit is configured and enabled. The my.cnf file will set the variable audit_file. To further check, execute the following query: SELECT PLUGIN_NAME, PLUGIN_STATUS FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_NAME LIKE 'audit%'; The status of the audit_log plugin must be "active". If it is not "active", this is a finding. Review audit filters and associated users by running the following queries: SELECT `audit_log_filter`.`NAME`, `audit_log_filter`.`FILTER` FROM `mysql`.`audit_log_filter`; SELECT `audit_log_user`.`USER`, `audit_log_user`.`HOST`, `audit_log_user`.`FILTERNAME` FROM `mysql`.`audit_log_user`; All currently defined audits for the MySQL server instance will be listed. If no audits are returned, this is a finding. Create MySQL insert, update, and delete triggers that check for changes to categories of information. If the trigger before data indicates an attempt to delete such information, the trigger must be written to prevent the delete as well as optionally write to the MySQL Audit by calling the audit_api_message_emit_udf() function and including the details related to the attempt. Note: To call from a trigger requires a minimal stored procedure as well. Once the trigger has been created, check if the audit filters in place are generating records when categories of information are deleted. - An Example test - CREATE TABLE `test_trigger`.`info_cat_test` ( `id` INT NOT NULL, `name` VARCHAR(20) NULL, `desc` VARCHAR(20) NULL, `sec_level` CHAR(1) NULL, PRIMARY KEY (`id`)); use test_trigger; DELIMITER $$ CREATE TRIGGER test_trigger.audit_delete BEFORE DELETE ON `test_trigger`.`info_cat_test` FOR EACH ROW BEGIN IF OLD.sec_level = 'H' THEN CALL audit_api_message_emit_sp(OLD.name); END IF; END$$ DELIMITER ; DELIMITER $$ CREATE TRIGGER audit_insert BEFORE INSERT ON `test_trigger`.`info_cat_test` FOR EACH ROW BEGIN IF NEW.sec_level = 'H' THEN CALL audit_api_message_emit_sp(NEW.name); END IF; END$$ DELIMITER ; DELIMITER $$ CREATE TRIGGER audit_update BEFORE UPDATE ON `test_trigger`.`info_cat_test` FOR EACH ROW BEGIN IF OLD.sec_level = 'H' THEN CALL audit_api_message_emit_sp(OLD.name); END IF; IF NEW.sec_level = 'H' THEN CALL audit_api_message_emit_sp(NEW.name); END IF; END$$ DELIMITER ; INSERT INTO `test_trigger`.`info_cat_test` (`id`, `name`, `desc`, `sec_level`) VALUES ('1', 'fred', 'engineer', 'H'); INSERT INTO `test_trigger`.`info_cat_test` (`id`, `name`, `desc`, `sec_level`) VALUES ('2', 'jill', 'program manager', 'M'); INSERT INTO `test_trigger`.`info_cat_test` (`id`, `name`, `desc`, `sec_level`) VALUES ('3', 'joe', 'maintenance', 'L'); delete from `test_trigger`.`info_cat_test` where id=1; // this fails as the trigger defines that sec_level of H can not be deleted. update`test_trigger`.`info_cat_test` set sec_level=‘H’ where id=2; delete from `test_trigger`.`info_cat_test` where id=3; Review the audit log by running the Linux command: sudo cat <directory where audit log files are located>/audit.log | egrep audit_change_attempt If the audit event is not present, this is a finding.
If currently required, configure the MySQL Database Server with update, insert, and delete triggers that call audit_api_message_emit_udf() function to produce audit records when unsuccessful attempts to modify categories of information occur. Add security level details in an additional column. Add the component for adding information to the audit log. INSTALL COMPONENT "file://component_audit_api_message_emit”; create schema test_trigger; Create a stored procedure to allow the audit_api_message_emit_udf to be called as well as providing the details for the audit event. DELIMITER $$ CREATE PROCEDURE audit_api_message_emit_delete_sp(name CHAR(20)) BEGIN DECLARE aud_msg VARCHAR(255); select audit_api_message_emit_udf('sec_level_trigger', 'TRIGGER audit_change_attempt', 'Attempt was made to change H level sec data', 'FOR ', name ) into aud_msg; END$$ DELIMITER ; See the supplemental file "MySQL80Audit.sql".
If classification levels/security levels labeling is not required, this is not a finding. Review the system documentation to determine if MySQL Server is required to audit records when unsuccessful attempts to modify categories of information (e.g., classification levels/security levels) occur. Check if MySQL audit is configured and enabled. The my.cnf file will set the variable audit_file. To further check, execute the following query: SELECT PLUGIN_NAME, PLUGIN_STATUS FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_NAME LIKE 'audit%'; The status of the audit_log plugin should be "active". If it is not "active", this is a finding. Review audit filters and associated users by running the following queries: SELECT `audit_log_filter`.`NAME`, `audit_log_filter`.`FILTER` FROM `mysql`.`audit_log_filter`; SELECT `audit_log_user`.`USER`, `audit_log_user`.`HOST`, `audit_log_user`.`FILTERNAME` FROM `mysql`.`audit_log_user`; All currently defined audits for the MySQL server instance will be listed. If no audits are returned, this is a finding. Create MySQL insert, update, and delete triggers that check for changes to categories of information. If the trigger before data indicates an attempt to delete such information, the trigger should be written to prevent the delete as well as optionally write to the MySQL Audit by calling the audit_api_message_emit_udf() function and including the details related to the attempt. Note: To call from a trigger requires a minimal stored procedure as well. Once the trigger has been created, check if the audit filters that are in place are generating records when categories of information are deleted. - An Example test - CREATE TABLE `test_trigger`.`info_cat_test` ( `id` INT NOT NULL, `name` VARCHAR(20) NULL, `desc` VARCHAR(20) NULL, `sec_level` CHAR(1) NULL, PRIMARY KEY (`id`)); use test_trigger; DELIMITER $$ CREATE TRIGGER audit_delete BEFORE DELETE ON `test_trigger`.`info_cat_test` FOR EACH ROW BEGIN IF OLD.sec_level = 'H' THEN CALL audit_api_message_emit_sp(OLD.name); SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'ERROR - THIS DATA IS LEVEL H can not delete’; END IF; END$$ DELIMITER ; DELIMITER $$ CREATE TRIGGER audit_insert BEFORE INSERT ON `test_trigger`.`info_cat_test` FOR EACH ROW BEGIN IF NEW.sec_level = 'H' THEN CALL audit_api_message_emit_sp(NEW.name); SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'ERROR - THIS DATA IS LEVEL H can not insert’; END IF; END$$ DELIMITER ; DELIMITER $$ CREATE TRIGGER audit_update BEFORE UPDATE ON `test_trigger`.`info_cat_test` FOR EACH ROW BEGIN IF OLD.sec_level = 'H' THEN CALL audit_api_message_emit_sp(OLD.name); END IF; IF NEW.sec_level = 'H' THEN CALL audit_api_message_emit_sp(NEW.name); END IF; SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'ERROR - THIS DATA IS LEVEL H can not update'; END$$ DELIMITER ; INSERT INTO `test_trigger`.`info_cat_test` (`id`, `name`, `desc`, `sec_level`) VALUES ('1', 'fred', 'engineer', 'H'); INSERT INTO `test_trigger`.`info_cat_test` (`id`, `name`, `desc`, `sec_level`) VALUES ('2', 'jill', 'program manager', 'M'); INSERT INTO `test_trigger`.`info_cat_test` (`id`, `name`, `desc`, `sec_level`) VALUES ('3', 'joe', 'maintenance', 'L'); delete from `test_trigger`.`info_cat_test` where id=1; // this fails as the trigger defines that sec_level of H can not be deleted. update`test_trigger`.`info_cat_test` set sec_level=‘H’ where id=2; delete from `test_trigger`.`info_cat_test` where id=3; Review the audit log by running the Linux command: sudo cat <directory where audit log files are located>/audit.log | egrep audit_change_attempt For example if the values returned by - "select @@datadir, @@audit_log_file; " are /usr/local/mysql/data/, audit.log sudo cat /usr/local/mysql/data/audit.log |egrep audit_change_attempt If the audit event is not present, this is a finding.
If currently required, configure the MySQL Database Server with update, insert, and delete triggers that call audit_api_message_emit_udf() function to produce audit records when unsuccessful attempts to modify categories of information occur. Add security level details in an additional column. Add the component for adding information to the audit log. INSTALL COMPONENT "file://component_audit_api_message_emit”; create schema test_trigger; Create a stored procedure to allow the audit_api_message_emit_udf to be called as well as providing the details for the audit event. DELIMITER $$ CREATE PROCEDURE audit_api_message_emit_delete_sp(name CHAR(20)) BEGIN DECLARE aud_msg VARCHAR(255); select audit_api_message_emit_udf('sec_level_trigger', 'TRIGGER audit_change_attempt', 'Attempt was made to change H level sec data', 'FOR ', name ) into aud_msg; END$$ DELIMITER ; See the supplemental file "MySQL80Audit.sql".
Review the system documentation to determine if MySQL Server is required to audit when privileges/permissions are deleted. Check if MySQL audit is configured and enabled. The my.cnf file will set the variable audit_file. To further check, execute the following query: SELECT PLUGIN_NAME, PLUGIN_STATUS FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_NAME LIKE 'audit%'; The status of the audit_log plugin must be "active". If it is not "active", this is a finding. Review audit filters and associated users by running the following queries: SELECT `audit_log_filter`.`NAME`, `audit_log_filter`.`FILTER` FROM `mysql`.`audit_log_filter`; SELECT `audit_log_user`.`USER`, `audit_log_user`.`HOST`, `audit_log_user`.`FILTERNAME` FROM `mysql`.`audit_log_user`; All currently defined audits for the MySQL server instance will be listed. If no audits are returned, this is a finding. To check if the audit filters in place are generating records when privileges/permissions are deleted, run the following, which will test auditing without destroying data: delete from mysql.procs_priv where 1=2; Review the audit log by running the Linux command: sudo cat <directory where audit log files are located>/audit.log|egrep procs_priv For example if the values returned by - "select @@datadir, @@audit_log_file; " are /usr/local/mysql/data/, audit.log sudo cat /usr/local/mysql/data/audit.log |egrep procs_priv The audit data will look similar to the example below: { "timestamp": "2020-08-19 21:24:26", "id": 2, "class": "general", "event": "status", "connection_id": 9, "account": { "user": "root", "host": "localhost" }, "login": { "user": "root", "os": "", "ip": "::1", "proxy": "" }, "general_data": { "command": "Query", "sql_command": "delete", "query": "delete from procs_priv", "status": 0 } } If the audit event is not present, this is a finding.
If currently required, configure the MySQL Database Server to produce audit records when privileges/permissions are deleted. See the supplemental file "MySQL80Audit.sql".
Review the system documentation to determine if MySQL Server is required to audit when unsuccessful attempts to delete privileges/permissions occur. Check if MySQL audit is configured and enabled. The my.cnf file will set the variable audit_file. To further check, execute the following query: SELECT PLUGIN_NAME, PLUGIN_STATUS FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_NAME LIKE 'audit%'; The status of the audit_log plugin must be "active". If it is not "active", this is a finding. Review audit filters and associated users by running the following queries: SELECT `audit_log_filter`.`NAME`, `audit_log_filter`.`FILTER` FROM `mysql`.`audit_log_filter`; SELECT `audit_log_user`.`USER`, `audit_log_user`.`HOST`, `audit_log_user`.`FILTERNAME` FROM `mysql`.`audit_log_user`; All currently defined audits for the MySQL server instance will be listed. If no audits are returned, this is a finding. To check if the audit filters in place are generating records when unsuccessful attempts to delete privileges/permissions occur, run the following, which will test auditing without destroying data but as a user without administrative privileges so that it fails: delete from mysql.procs_priv where 1=2; Review the audit log by running the Linux command: sudo cat <directory where audit log files are located>/audit.log|egrep procs_priv For example if the values returned by - "select @@datadir, @@audit_log_file; " are /usr/local/mysql/data/, audit.log sudo cat /usr/local/mysql/data/audit.log |egrep procs_priv The audit data will look similar to the example below and contain a non-zero status value: { "timestamp": "2020-08-19 21:24:26", "id": 2, "class": "general", "event": "status", "connection_id": 9, "account": { "user": "root", "host": "localhost" }, "login": { "user": "root", "os": "", "ip": "::1", "proxy": "" }, "general_data": { "command": "Query", "sql_command": "delete", "query": "delete from procs_priv", "status": 1142 } } If the audit event is not present, this is a finding.
Configure the MySQL Database Server to audit when unsuccessful attempts to delete privileges/permissions occur. See the supplemental file "MySQL80Audit.sql".
Review the system documentation to determine if MySQL Server is required to audit when security objects are deleted. Check if MySQL audit is configured and enabled. The my.cnf file will set the variable audit_file. To further check, execute the following query: SELECT PLUGIN_NAME, PLUGIN_STATUS FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_NAME LIKE 'audit%'; The status of the audit_log plugin must be "active". If it is not "active", this is a finding. Review audit filters and associated users by running the following queries: SELECT `audit_log_filter`.`NAME`, `audit_log_filter`.`FILTER` FROM `mysql`.`audit_log_filter`; SELECT `audit_log_user`.`USER`, `audit_log_user`.`HOST`, `audit_log_user`.`FILTERNAME` FROM `mysql`.`audit_log_user`; All currently defined audits for the MySQL server instance will be listed. If no audits are returned, this is a finding. To check if the audit filters in place are generating records when security objects are deleted, run the following, which will test auditing. Note: This is destructive. Back up the database table prior to testing so it can be restored. drop mysql.procs_priv; Review the audit log by running the Linux command: sudo cat <directory where audit log files are located>/audit.log|egrep DROP For example if the values returned by - "select @@datadir, @@audit_log_file; " are /usr/local/mysql/data/, audit.log sudo cat /usr/local/mysql/data/audit.log |egrep DROP The audit data will look similar to the example below: { "timestamp": "2020-08-21 17:06:02", "id": 1, "class": "general", "event": "status", "connection_id": 9, "account": { "user": "root", "host": "localhost" }, "login": { "user": "root", "os": "", "ip": "::1", "proxy": "" }, "general_data": { "command": "Query", "sql_command": "drop_table", "query": "DROP TABLE `mysql`.`proxies_priv`", "status": 0 } }, If the audit event is not present, this is a finding.
Configure the MySQL Database Server to audit when security objects are deleted. See the supplemental file "MySQL80Audit.sql".
Review the system documentation to determine if MySQL Server is required to audit when unsuccessful attempts to delete security objects occur. Check if MySQL audit is configured and enabled. The my.cnf file will set the variable audit_file. To further check, execute the following query: SELECT PLUGIN_NAME, PLUGIN_STATUS FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_NAME LIKE 'audit%'; The status of the audit_log plugin must be "active". If it is not "active", this is a finding. Review audit filters and associated users by running the following queries: SELECT `audit_log_filter`.`NAME`, `audit_log_filter`.`FILTER` FROM `mysql`.`audit_log_filter`; SELECT `audit_log_user`.`USER`, `audit_log_user`.`HOST`, `audit_log_user`.`FILTERNAME` FROM `mysql`.`audit_log_user`; All currently defined audits for the MySQL server instance will be listed. If no audits are returned, this is a finding. To check if the audit filters in place are generating records when security objects are deleted, run the following, which will test auditing as a user with administrator-level privileges: drop table mysql.columns_priv; ERROR: 1142: DROP command denied to user 'newuser'@'localhost' for table 'columns_priv' Review the audit log by running the Linux command: sudo cat <directory where audit log files are located>/audit.log|egrep DROP For example if the values returned by - "select @@datadir, @@audit_log_file; " are /usr/local/mysql/data/, audit.log sudo cat /usr/local/mysql/data/audit.log |egrep DROP The audit data will look similar to the example below and contain a non-zero status value: { "timestamp": "2020-08-21 17:21:12", "id": 0, "class": "general", "event": "status", "connection_id": 17, "account": { "user": "newuser", "host": "localhost" }, "login": { "user": "newuser", "os": "", "ip": "::1", "proxy": "" }, "general_data": { "command": "Query", "sql_command": "drop_table", "query": "drop table `mysql`.audit_log_user", "status": 1142 } }, If the audit event is not present, this is a finding.
Configure the MySQL Database Server to audit when unsuccessful attempts to delete security objects occur. See the supplemental file "MySQL80Audit.sql".
Review DBMS documentation to verify that audit records can be produced when categories of information are deleted. If the DBMS is not capable of this, this is a finding. Review the DBMS/database security and audit configurations to verify that audit records are produced when categories of information are deleted. If they are not produced, this is a finding.
Deploy a MySQL Database Server 8.0 capable of producing the required audit records when categories of information are deleted. Configure the MySQL Database Server 8.0 to produce audit records when categories of information are deleted.
If classification levels/security levels labeling is not required, this is not a finding. Review the system documentation to determine if MySQL Server is required to audit records when unsuccessful attempts to delete categories of information (e.g., classification levels/security levels) occur. Check if MySQL audit is configured and enabled. The my.cnf file will set the variable audit_file. To further check, execute the following query: SELECT PLUGIN_NAME, PLUGIN_STATUS FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_NAME LIKE 'audit%'; The status of the audit_log plugin must be "active". If it is not "active", this is a finding. Review audit filters and associated users by running the following queries: SELECT `audit_log_filter`.`NAME`, `audit_log_filter`.`FILTER` FROM `mysql`.`audit_log_filter`; SELECT `audit_log_user`.`USER`, `audit_log_user`.`HOST`, `audit_log_user`.`FILTERNAME` FROM `mysql`.`audit_log_user`; All currently defined audits for the MySQL server instance will be listed. If no audits are returned, this is a finding. Create MySQL Delete triggers that check for changes to categories of information. If the trigger before data indicates an attempt to delete such information, the trigger should be written to prevent the delete as well as optionally write to the MySQL Audit by calling the audit_api_message_emit_udf() function and including the details related to the attempt. Note: To call from a trigger requires a minimal stored procedure as well. Once the trigger has been created, check if the audit filters that are in place are generating records when categories of information are deleted. - An Example test - CREATE TABLE `test_trigger`.`info_cat_test` ( `id` INT NOT NULL, `name` VARCHAR(20) NULL, `desc` VARCHAR(20) NULL, `sec_level` CHAR(1) NULL, PRIMARY KEY (`id`)); DELIMITER $$ CREATE TRIGGER test_trigger.audit_delete_attempt BEFORE DELETE ON `test_trigger`.`info_cat_test` FOR EACH ROW BEGIN IF OLD.sec_level = 'H' THEN CALL audit_api_message_emit_sp(OLD.name); SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'ERROR - THIS DATA IS LEVEL H'; END IF; END$$ DELIMITER ; INSERT INTO `test_trigger`.`info_cat_test` (`id`, `name`, `desc`, `sec_level`) VALUES ('1', 'fred', 'engineer', 'H'); INSERT INTO `test_trigger`.`info_cat_test` (`id`, `name`, `desc`, `sec_level`) VALUES ('2', 'jill', 'program manager', 'M'); INSERT INTO `test_trigger`.`info_cat_test` (`id`, `name`, `desc`, `sec_level`) VALUES ('3', 'joe', 'maintenance', 'L'); delete from `test_trigger`.`info_cat_test` where id=1; // this fails as the trigger defines that sec_level of H can not be deleted. delete from `test_trigger`.`info_cat_test` where id=2; delete from `test_trigger`.`info_cat_test` where id=3; Review the audit log by running the Linux command: sudo cat <directory where audit log files are located>/audit.log | egrep sec_level_trigger For example if the values returned by - "select @@datadir, @@audit_log_file; " are /usr/local/mysql/data/, audit.log sudo cat /usr/local/mysql/data/audit.log |egrep sec_level_trigger If the audit event is not present, this is a finding.
If currently required, configure the MySQL Database Server with delete triggers which prevent unauthorized deletes and call audit_api_message_emit_udf() function to produce audit records when unsuccessful attempts to delete categories of information occur. Add security level details in an additional column. Add the component for adding information to the audit log. INSTALL COMPONENT "file://component_audit_api_message_emit”; create schema test_trigger; Create a stored procedure to allow the audit_api_message_emit_udf to be called as well as providing the details for the audit event. DELIMITER $$ CREATE PROCEDURE audit_api_message_emit_sp(name CHAR(20)) BEGIN DECLARE aud_msg VARCHAR(255); select audit_api_message_emit_udf('sec_level_trigger', 'TRIGGER audit_delete_attempt', 'Attempt was made to delete H level sec data', 'FOR ', name ) into aud_msg; END$$ DELIMITER ;
Review the system documentation to determine if MySQL Server is required to audit when successful logons or connections occur. Check if MySQL audit is configured and enabled. The my.cnf file will set the variable audit_file. To further check, execute the following query: SELECT PLUGIN_NAME, PLUGIN_STATUS FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_NAME LIKE 'audit%'; The status of the audit_log plugin must be "active". If it is not "active", this is a finding. Review audit filters and associated users by running the following queries: SELECT `audit_log_filter`.`NAME`, `audit_log_filter`.`FILTER` FROM `mysql`.`audit_log_filter`; SELECT `audit_log_user`.`USER`, `audit_log_user`.`HOST`, `audit_log_user`.`FILTERNAME` FROM `mysql`.`audit_log_user`; All currently defined audits for the MySQL server instance will be listed. If no audits are returned, this is a finding. To check if the audit filters in place are generating records when successful logons or connections occur, log in to MySQL and then log out. Below is an example using MySQL Shell: % mysqlsh —sql MySQL SQL > \connect newuser@localhost Creating a session to 'newuser@localhost' MySQL localhost:33060+ ssl SQL > \quit Bye! Review the audit log by running the Linux command: Note, "status": 0 for each indicates successful. sudo cat <directory where audit log files are located>/audit.log | egrep "\"event\": \”connect\"" For example if the values returned by - "select @@datadir, @@audit_log_file; " are /usr/local/mysql/data/, audit.log sudo cat /usr/local/mysql/data/audit.log |egrep "\"event\": \”connect\"" The audit data will look similar to the example below: Logging in - connecting { "timestamp": "2020-08-21 17:47:09", "id": 0, "class": "connection", "event": "connect", "connection_id": 19, "account": { "user": "newuser", "host": "localhost" }, "login": { "user": "newuser", "os": "", "ip": "::1", "proxy": "" }, "connection_data": { "connection_type": "plugin", "status": 0, "db": "" } }, Logging out - disconnection sudo cat <directory where audit log files are located>/audit.log | egrep "\"event\": \"disconnect\”" Example output: { "timestamp": "2020-08-21 17:47:11", "id": 1, "class": "connection", "event": "disconnect", "connection_id": 19, "account": { "user": "newuser", "host": "localhost" }, "login": { "user": "newuser", "os": "", "ip": "::1", "proxy": "" }, "connection_data": { "connection_type": "plugin" } },
If currently required, configure the MySQL Database Server to produce audit records when successful logons or connections occur. See the supplemental file "MySQL80Audit.sql".
Review the system documentation to determine if MySQL Server is required to audit when unsuccessful logons or connection attempts occur. Check if MySQL audit is configured and enabled. The my.cnf file will set the variable audit_file. To further check, execute the following query: SELECT PLUGIN_NAME, PLUGIN_STATUS FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_NAME LIKE 'audit%'; The status of the audit_log plugin must be "active". If it is not "active", this is a finding. Review audit filters and associated users by running the following queries: SELECT `audit_log_filter`.`NAME`, `audit_log_filter`.`FILTER` FROM `mysql`.`audit_log_filter`; SELECT `audit_log_user`.`USER`, `audit_log_user`.`HOST`, `audit_log_user`.`FILTERNAME` FROM `mysql`.`audit_log_user`; All currently defined audits for the MySQL server instance will be listed. If no audits are returned, this is a finding. To check if the audit filters that are in place are generating records when unsuccessful logons or connection attempts occur: Log in to MySQL and then log out. For example, using MySQL Shell: % mysqlsh —sql MySQL SQL > \connect notauser@localhost Creating a session to 'notauser@localhost' Please provide the password for 'notauser@localhost': MySQL Error 1045: Access denied for user 'notauser'@'localhost' (using password: YES) Review the audit log by running the Linux command: Note, "status": 1045 for each indicates failed attempt. sudo cat <directory where audit log files are located>/audit.log | egrep notauser For example if the values returned by - "select @@datadir, @@audit_log_file; " are /usr/local/mysql/data/, audit.log sudo cat /usr/local/mysql/data/audit.log |egrep notauser The audit data will look similar to the example below: { "timestamp": "2020-08-21 17:54:08", "id": 0, "class": "connection", "event": "connect", "connection_id": 20, "account": { "user": "", "host": "localhost" }, "login": { "user": "notauser", "os": "", "ip": "::1", "proxy": "" }, "connection_data": { "connection_type": "plugin", "status": 1045, "db": "" } },
If currently required, configure the MySQL Database Server to audit when unsuccessful logons or connections attempts occur. See the supplemental file "MySQL80Audit.sql".
Review the system documentation to determine if MySQL Server is required to audit for all privileged activities or other system-level access. Check if MySQL audit is configured and enabled. The my.cnf file will set the variable audit_file. To further check, execute the following query: SELECT PLUGIN_NAME, PLUGIN_STATUS FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_NAME LIKE 'audit%'; The status of the audit_log plugin must be "active". If it is not "active", this is a finding. Review audit filters and associated users by running the following queries: SELECT `audit_log_filter`.`NAME`, `audit_log_filter`.`FILTER` FROM `mysql`.`audit_log_filter`; SELECT `audit_log_user`.`USER`, `audit_log_user`.`HOST`, `audit_log_user`.`FILTERNAME` FROM `mysql`.`audit_log_user`; All currently defined audits for the MySQL server instance will be listed. If no audits are returned, this is a finding. Determine if rules are in place to capture the following types of commands related to permissions by running: select * from mysql.audit_log_filter; If the template SQL filter was used, it will have the name "log_stig". Review the filter values. It will show filters for events of the type of the field general_sql_command.str for the following SQL statement types: grant grant_roles revoke revoke_all revoke_roles drop_role alter_user_default_role create_role drop_role grant_roles revoke_roles set_role create_user alter_user drop_user alter_user alter_user_default_role create_user drop_user rename_user show_create_user
Configure the MySQL Database Server to audit for all privileged activities or other system-level access. Add the following events to the MySQL Server Audit: grant grant_roles revoke revoke_all revoke_roles drop_role alter_user_default_role create_role drop_role grant_roles revoke_roles set_role create_user alter_user drop_user alter_user alter_user_default_role create_user drop_user rename_user show_create_user See the supplemental file "MySQL80Audit.sql".
Review the system documentation to determine if MySQL Server is required to audit for unsuccessful attempts to execute privileged activities or other system-level access. Check if MySQL audit is configured and enabled. The my.cnf file will set the variable audit_file. To further check, execute the following query: SELECT PLUGIN_NAME, PLUGIN_STATUS FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_NAME LIKE 'audit%'; The status of the audit_log plugin must be "active". If it is not "active", this is a finding. Review audit filters and associated users by running the following queries: SELECT `audit_log_filter`.`NAME`, `audit_log_filter`.`FILTER` FROM `mysql`.`audit_log_filter`; SELECT `audit_log_user`.`USER`, `audit_log_user`.`HOST`, `audit_log_user`.`FILTERNAME` FROM `mysql`.`audit_log_user`; All currently defined audits for the MySQL server instance will be listed. If no audits are returned, this is a finding. Determine if rules are in place to capture the following types of commands related to permissions by running: select * from mysql.audit_log_filter; If the template SQL filter was used, it will have the name log_stig. Review the filter values it will show filters for events of type of the field general_sql_command.str for the following SQL statement types: grant grant_roles revoke revoke_all revoke_roles drop_role alter_user_default_role create_role drop_role grant_roles revoke_roles set_role create_user alter_user drop_user alter_user alter_user_default_role create_user drop_user rename_user show_create_user
Configure the MySQL Database Server to audit for unsuccessful attempts to execute privileged activities or other system-level access. Add the following events to the MySQL Server Audit: grant grant_roles revoke revoke_all revoke_roles drop_role alter_user_default_role create_role drop_role grant_roles revoke_roles set_role create_user alter_user drop_user alter_user alter_user_default_role create_user drop_user rename_user show_create_user See the supplemental file "MySQL80Audit.sql".
Check if MySQL audit is configured and enabled. The my.cnf file will set the variable audit_file. To further check, execute the following query: SELECT PLUGIN_NAME, PLUGIN_STATUS FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_NAME LIKE 'audit%'; The status of the audit_log plugin must be "active". If it is not "active", this is a finding. Review audit filters and associated users by running the following queries: SELECT `audit_log_filter`.`NAME`, `audit_log_filter`.`FILTER` FROM `mysql`.`audit_log_filter`; SELECT `audit_log_user`.`USER`, `audit_log_user`.`HOST`, `audit_log_user`.`FILTERNAME` FROM `mysql`.`audit_log_user`; All currently defined audits for the MySQL server instance will be listed. If no audits are returned, this is a finding. To check if the audit filters that are in place are generating records when successful logons or connections occur, log in to MySQL and then log out. Below is an example using MySQL Shell: % mysqlsh —sql MySQL SQL > \connect newuser@localhost Creating a session to 'newuser@localhost' MySQL localhost:33060+ ssl SQL > \quit Bye! Review the audit log by running the Linux command: "status": 0 for each indicates successful. "connection_id": 19 is the same as the connections process id and there will be matching disconnect event with the same connection_id number. This can be used to differentiate multiple connections using the same login. Each connect and disconnect has a timestamp tag with the time in Coordinated Universal Time (UTC). sudo cat <directory where audit log files are located>/audit.log | egrep "\"event\": \”connect\"" For example if the values returned by - "select @@datadir, @@audit_log_file; " are /usr/local/mysql/data/, audit.log sudo cat /usr/local/mysql/data/audit.log |egrep "\"event\": \”connect\"" The audit data will look similar to the example below: Logging in - connecting { "timestamp": "2020-08-21 17:47:09", "id": 0, "class": "connection", "event": "connect", "connection_id": 19, "account": { "user": "newuser", "host": "localhost" }, "login": { "user": "newuser", "os": "", "ip": "::1", "proxy": "" }, "connection_data": { "connection_type": "plugin", "status": 0, "db": "" } }, Logging out - disconnection sudo cat <directory where audit log files are located>/audit.log | egrep "\"event\": \"disconnect\”" { "timestamp": "2020-08-21 17:47:11", "id": 1, "class": "connection", "event": "disconnect", "connection_id": 19, "account": { "user": "newuser", "host": "localhost" }, "login": { "user": "newuser", "os": "", "ip": "::1", "proxy": "" }, "connection_data": { "connection_type": "plugin" } },
If currently required, configure the MySQL Database Server to produce audit records when successful logons or connections occur. See the supplemental file "MySQL80Audit.sql".
Review the system documentation to determine if MySQL Server is required to audit the concurrent logons/connections by the same user from different workstations. Check if MySQL audit is configured and enabled. The my.cnf file will set the variable audit_file. To further check, execute the following query: SELECT PLUGIN_NAME, PLUGIN_STATUS FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_NAME LIKE 'audit%'; The status of the audit_log plugin must be "active". If it is not "active", this is a finding. Review audit filters and associated users by running the following queries: SELECT `audit_log_filter`.`NAME`, `audit_log_filter`.`FILTER` FROM `mysql`.`audit_log_filter`; SELECT `audit_log_user`.`USER`, `audit_log_user`.`HOST`, `audit_log_user`.`FILTERNAME` FROM `mysql`.`audit_log_user`; All currently defined audits for the MySQL server instance will be listed. If no audits are returned, this is a finding. To check if the audit filters that are in place are generating records when multiple connections occur: Run multiple connections from the same user without logging out and from different IP addresses. Review the audit log: sudo cat <directory where audit log files are located>/audit.log | egrep <username> For example if the values returned by - "select @@datadir, @@audit_log_file; " are /usr/local/mysql/data/, audit.log and the user is fewconnects then sudo cat /usr/local/mysql/data/audit.log |egrep fewconnects { "connection_type": "ssl", "status": 0, "db": "", "connection_attributes": { "_pid": "9132", "_os": "macos10.14", "_platform": "x86_64", "_client_version": "8.0.20", "_client_name": "libmysql", "program_name": "mysqlsh" } } }, { "timestamp": "2020-08-31 18:03:41", "id": 0, "class": "connection", "event": "connect", "connection_id": 28, "account": { "user": "fewconnects", "host": "localhost" }, "login": { "user": "fewconnects", "os": "", "ip": "", "proxy": "" }, "connection_data": { "connection_type": "ssl", "status": 0, "db": "", "connection_attributes": { "_pid": "9132", "_os": "macos10.14", "_platform": "x86_64", "_client_version": "8.0.20", "_client_name": "libmysql", "program_name": "mysqlsh" } } } { "timestamp": "2020-08-31 18:11:05", "id": 12, "class": "connection", "event": "connect", "connection_id": 38, "account": { "user": "fewconnects", "host": "localhost" }, "login": { "user": "fewconnects", "os": "", "ip": "93.122.141.147", "proxy": "" }, "connection_data": { "connection_type": "ssl", "status": 0, "db": "", "connection_attributes": { "_pid": "903", "_os": "macos10.15", "_platform": "x86_64", "_client_version": "8.0.20", "_client_name": "libmysql", "program_name": "MySQLWorkbench" } } }, Note that each connection has a different connection_id - indicating distinctly auditing multiple connections. Here there are connections from mysqlsh and MySQLWorkbench; the event type is "event": “connect” and the "user": "fewconnects", "os": "", "ip": “127.0.0.1” and "login": { "user": "fewconnects", "os": "", "ip": “93.122.141.147” - that is with different IPs from the different workstations. If the audit events are not present, this is a finding. If currently required, configure the MySQL Database Server to produce audit records when connections occur. See the supplemental file "MySQL80Audit.sql".
If currently required, configure the MySQL Database Server to produce audit records when connections occur. See the supplemental file "MySQL80Audit.sql".
Review the system documentation to determine if MySQL Server is required to generate audit records when successful accesses to objects occur. Check if MySQL audit is configured and enabled. The my.cnf file will set the variable audit_file. To further check, execute the following query: SELECT PLUGIN_NAME, PLUGIN_STATUS FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_NAME LIKE 'audit%'; The status of the audit_log plugin must be "active". If it is not "active", this is a finding. Review audit filters and associated users by running the following queries: SELECT `audit_log_filter`.`NAME`, `audit_log_filter`.`FILTER` FROM `mysql`.`audit_log_filter`; SELECT `audit_log_user`.`USER`, `audit_log_user`.`HOST`, `audit_log_user`.`FILTERNAME` FROM `mysql`.`audit_log_user`; All currently defined audits for the MySQL server instance will be listed. If no audits are returned, this is a finding. To check if the audit filters in place are generating records to audit when certain objects are accessed: Run a query and other access types on that object. select * from <schemaname>/<tablename>; Review the audit log by running the Linux command: sudo cat <directory where audit log files are located>/audit.log|egrep <tablename> For example if the values returned by "select @@datadir, @@audit_log_file; " are /usr/local/mysql/data/, audit.log sudo cat /usr/local/mysql/data/audit.log |egrep <tablename> If the audit event is not present, this is a finding.
Configure the MySQL Database Server to audit when successful accesses to objects occur. See the supplemental file "MySQL80Audit.sql".
Review the system documentation to determine if MySQL Server is required to generate audit records when unsuccessful accesses to objects occur. Check if MySQL audit is configured and enabled. The my.cnf file will set the variable audit_file. To further check, execute the following query: SELECT PLUGIN_NAME, PLUGIN_STATUS FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_NAME LIKE 'audit%'; The status of the audit_log plugin must be "active". If it is not "active", this is a finding. Review audit filters and associated users by running the following queries: SELECT `audit_log_filter`.`NAME`, `audit_log_filter`.`FILTER` FROM `mysql`.`audit_log_filter`; SELECT `audit_log_user`.`USER`, `audit_log_user`.`HOST`, `audit_log_user`.`FILTERNAME` FROM `mysql`.`audit_log_user`; All currently defined audits for the MySQL server instance will be listed. If no audits are returned, this is a finding. To check if the audit filters in place are generating records to audit when certain objects access is unsuccessful: Connect a user without access to an object. Run a failed query or other failed access types on that object. select * from <schemaname>/<tablename>; Review the audit log by running the Linux command: sudo cat <directory where audit log files are located>/audit.log|egrep <tablename> For example if the values returned by "select @@datadir, @@audit_log_file; " are /usr/local/mysql/data/, audit.log sudo cat /usr/local/mysql/data/audit.log |egrep <tablename> The record will show the failed attempt which is note by a non-zero status value. If the audit event is not present, this is a finding.
Configure the MySQL Database Server to audit when unsuccessful accesses to objects occur. See the supplemental file "MySQL80Audit.sql".
Review the system documentation to determine if MySQL Server is required to generate audit records for all direct access to the database(s). Check if MySQL audit is configured and enabled. The my.cnf file will set the variable audit_file. To further check, execute the following query: SELECT PLUGIN_NAME, PLUGIN_STATUS FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_NAME LIKE 'audit%'; The status of the audit_log plugin must be "active". If it is not "active", this is a finding. Review audit filters and associated users by running the following queries: SELECT `audit_log_filter`.`NAME`, `audit_log_filter`.`FILTER` FROM `mysql`.`audit_log_filter`; SELECT `audit_log_user`.`USER`, `audit_log_user`.`HOST`, `audit_log_user`.`FILTERNAME` FROM `mysql`.`audit_log_user`; All currently defined audits for the MySQL server instance will be listed. If no audits are returned, this is a finding. To check if the audit filters in place are generating records to audit all direct access to the database(s): Run any access to the database. Review the audit log by running the Linux command: sudo cat <directory where audit log files are located>/audit.log For example if the values returned by - "select @@datadir, @@audit_log_file; " are /usr/local/mysql/data/, audit.log sudo cat /usr/local/mysql/data/audit.log If the audit events are not present, this is a finding.
If currently required, configure the MySQL Database Server to produce audit records for all direct access to the database(s). See the supplemental file "MySQL80Audit.sql".
The database supports PKI-based authentication by using digital certificates over TLS in addition to the native encryption and data integrity capabilities of these protocols. Review MySQL Database Server 8.0 configuration to verify DBMS user account certificates are valid by performing RFC 5280-compliant certification path validation. Run the following command to determine the certificate in use along with other details: select @@ssl_ca, @@ssl_capath, @@ssl_cert, @@ssl_cipher, @@ssl_crl, @@ssl_crlpath, @@ssl_fips_mode, @@ssl_key; If ssl_crl is not set to a CRL file, this is a finding. If ssl_crlpath is empty then use the default, which is the datadir path. To get that path run select @@datadir. Next verify the existence of the CRL file. If the CRL file does not exist, this is a finding. Next, verify that require_secure_transport is ON by running: select @@require_secure_transport; If require_secure_transport is not 1 for ON, this is a finding. If the certificate is not a DoD approved certificate, or if no certificate is listed, this is a finding. Confirm Issuer and Subject map to the username. Run the following script: SELECT `user`.`Host`, `user`.`User`, `user`.`ssl_type`, CAST(`user`.`x509_issuer` as CHAR) as Issuer, CAST(`user`.`x509_subject` as CHAR) as Subject FROM `mysql`.`user`; If user accounts are not being mapped to authenticated identities, this is a finding.
Configure the DBMS to validate certificates by constructing a certification path with status information to an accepted trust anchor. Configure the database server to support Transport Layer Security (TLS) protocols. mysql> set persist require_secure_transport=ON; Set system variables on the server side specify DoD approved certificate and key files the server uses when permitting clients to establish encrypted connections: ssl_ca: The path name of the Certificate Authority (CA) certificate file. (ssl_capath is similar but specifies the path name of a directory of CA certificate files.) ssl_cert: The path name of the server public key certificate file. This certificate can be sent to the client and authenticated against the CA certificate that it has. ssl_key: The path name of the server private key file. For example, to enable the server for encrypted connections with certificates, start it with these lines in the my.cnf file, changing the file names as necessary: [mysqld] ssl_ca=ca.pem ssl_cert=server-cert.pem ssl_key=server-key.pem Alter users to require X509 certificates Below is an example to add X509 as a requirement. For a new user CREATE USER 'jeffrey'@'localhost' REQUIRE X509; AND SUBJECT '/C=US/ST=Texas/L=Houston/O=SomeCompany/CN=Johan Smith' AND ISSUER '/C=US/ST=Texas/L=Houston/O=SomeCompany/CN=Some CA'; Or to add to an existing user ALTER USER 'johansmith'@'%' REQUIRE X509 AND SUBJECT '/C=US/ST=Texas/L=Houston/O=SomeCompany/CN=Johan Smith' AND ISSUER '/C=US/ST=Texas/L=Houston/O=SomeCompany/CN=Some CA';
Review DBMS configuration to determine whether appropriate access controls exist to protect the DBMS’s private key. If strong access controls do not exist to enforce authorized access to the private key, this is a finding. MySQL stores certificates in PEM formatted files. Verify User ownership, Group ownership, and permissions on the ssl_files. select @@ssl_ca, @@ssl_capath, @@ssl_cert, @@ssl_cipher, @@ssl_crl, @@ssl_crlpath, @@ssl_fips_mode, @@ssl_key; If ssl_path or ssl_crlpath are not defined the locations default to the datadir. To determine the datadir select @@datadir; Example if path is <directory where audit log files are located>/ sudo sh -c 'ls -l <directory where data files are located>/*.pem' For example if the value returned by "select @@datadir;' is /usr/local/mysql/data/ sudo sh -c 'ls -l /usr/local/mysql/data/*.pem' Password: -rw------- 1 _mysql _mysql 1676 Feb 25 11:09 <directory where audit log files are located>/ca-key.pem -rw-r--r-- 1 _mysql _mysql 1112 Feb 25 11:09 <directory where audit log files are located>/ca.pem -rw-r--r-- 1 _mysql _mysql 1112 Feb 25 11:09 <directory where audit log files are located>/client-cert.pem -rw------- 1 _mysql _mysql 1680 Feb 25 11:09 <directory where audit log files are located>/client-key.pem -rw------- 1 _mysql _mysql 1676 Feb 25 11:09 <directory where audit log files are located>/private_key.pem -rw-r--r-- 1 _mysql _mysql 452 Feb 25 11:09 <directory where audit log files are located>/public_key.pem -rw-r--r-- 1 _mysql _mysql 1112 Feb 25 11:09 <directory where audit log files are located>/server-cert.pem -rw------- 1 _mysql _mysql 1680 Feb 25 11:09 <directory where audit log files are located>/server-key.pem If the User owner is not "mysql", this is a finding. If the Group owner is not "mysql", this is a finding. For public certs and keys, permissions should be "rw" for mysql and "readonly" for mysql group and world. These files by default are named "ca.pem", "client-cert.pem", "public_key.pem", and "server-cert.pem". If not, this is a finding. For private certs and keys, permissions should be "rw" for mysql and "no rights" for mysql group or world. These files by default are named "ca-key.pem", "client-key.pem", "private_key.pem", and "server-key.pem". If not, this is a finding. Review system configuration to determine whether FIPS mode has been enabled. select @@ssl_fips_mode; - OFF: Disable FIPS mode. - ON: Enable FIPS mode. - STRICT: Enable “strict” FIPS mode. If FIPS mode is not "ON" or "STRICT", this is a finding. If the server-key.pem has a password, verify when starting mysqld in a console there is prompt requiring the passphrase for the server-key.
Implement strong access and authentication controls to protect the database’s private key. Configure the database to support Transport Layer Security (TLS) protocols and the put in place file systems permissions on authentication and signing credentials, including private keys. Put keys in place in the datadir, or define their locations using ssl_capath and ssl_crlpath. Ensure proper permissions are set to protect the private keys and certificates. Change directory ssl_capath, ssl_crlpath, or the default datadir path. To determine the file paths: select @@ssl_capath, @@ssl_crlpath, @@ssl_key, @@datadir; Ensure OS account mysql owns all the pem and key files. $ chown mysql *.pem $ chgrp mysql *.key $ chmod 600 *.key" $ chmod 600 *.pem Optionally, allow access to public keys. $ chmod 644 client-cert.pem client-key.pem $chmod 644 public_key.pem server-cert.pem If the server-key.pem has a password, provide this password when prompted during a console startup. The server will not start without this password if the server key is password protected. Edit the mysql configuration file. [mysqld] ssl-fips-mode=ON If the OpenSSL FIPS Object Module is not available, ssl_fips_mode to ON or STRICT at startup causes the server to produce an error message and exit.
Review MySQL Database Server 8.0 configuration to verify DBMS user accounts are being mapped directly to unique identifying information within the validated PKI certificate. Confirm Issuer and Subject map to the username. Run the following script: SELECT `user`.`Host`, `user`.`User`, `user`.`ssl_type`, CAST(`user`.`x509_issuer` as CHAR) as Issuer, CAST(`user`.`x509_subject` as CHAR) as Subject FROM `mysql`.`user`; If user accounts are not being mapped to authenticated identities, this is a finding.
Configure the MySQL Database Server 8.0 to map the authenticated identity directly to the MySQL Database Server 8.0 user account. Alter users to require X509 certificates. Below is an example to add X509 as a requirement. For a new user: CREATE USER 'jeffrey'@'localhost' REQUIRE X509; AND SUBJECT '/C=US/ST=Texas/L=Houston/O=SomeCompany/CN=Johan Smith' AND ISSUER '/C=US/ST=Texas/L=Houston/O=SomeCompany/CN=Some CA'; Or to add to an existing user: ALTER USER 'johansmith'@'%' REQUIRE X509 AND SUBJECT '/C=US/ST=Texas/L=Houston/O=SomeCompany/CN=Johan Smith' AND ISSUER '/C=US/ST=Texas/L=Houston/O=SomeCompany/CN=Some CA';
If DBMS authentication using passwords is not employed, this is not a finding. If the DBMS is configured to inherit password complexity and lifetime rules from the operating system or access control program, this is not a finding. Review the MySQL Database Server 8.0 settings relating to password complexity. Determine whether the following rules are enforced. If any are not, this is a finding. a. Minimum of 15 characters, including at least one of each of the following character sets: - Uppercase - Lowercase - Numeric - Special characters (e.g., ~ ! @ # $ % ^ & * ( ) _ + = - ' [ ] / ? > <) b. Minimum number of characters changed from previous password: 50 percent of the minimum password length; that is, eight. Review the DBMS settings relating to password lifetime. Determine whether the following rules are enforced. If any are not, this is a finding. a. Password lifetime limits for interactive accounts: Minimum 24 hours, maximum 60 days. b. Password lifetime limits for non-interactive accounts: Minimum 24 hours, maximum 365 days. c. Number of password changes before an old one may be reused: Minimum of five. Connect as an admin. SELECT component_urn FROM mysql.component where component_urn='file://component_validate_password' group by component_urn; If the "validate password" component is installed the result will be file://component_validate_password. If "validate password" component is not installed, this is a finding. If the "component_validate_password" is installed, review the password policies to ensure required password complexity is met. Run the following to review the password policy: SELECT VARIABLE_NAME, VARIABLE_VALUE FROM performance_schema.global_variables where VARIABLE_NAME like 'valid%password%' or VARIABLE_NAME like 'password_%' ; For example the results may look like the following: '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' 'password_reuse_interval','0' 'password_require_current','OFF' 'password_history','0' If these results do not meet password complexity requirements listed above, this is a finding.
If the use of passwords is not needed, configure the MySQL Database Server 8.0 to prevent their use if it is capable of this; if it is not, institute policies and procedures to prohibit their use. If the MySQL Database Server 8.0 can inherit password complexity rules from the operating system or access control program, configure it to do so. Otherwise, use MySQL Database Server 8.0 configuration parameters and/or custom code to enforce the following rules for passwords: a. Minimum of 15 characters, including at least one of each of the following character sets: - Uppercase - Lowercase - Numeric - Special characters (e.g., ~ ! @ # $ % ^ & * ( ) _ + = - ' [ ] / ? > <) b. Minimum number of characters changed from previous password: 50 percent of the minimum password length; that is, eight. c. Password lifetime limits for interactive accounts: Minimum 24 hours, maximum 60 days. d. Password lifetime limits for non-interactive accounts: Minimum 24 hours, maximum 365 days. e. Number of password changes before an old one may be reused: Minimum of five. As the database admin: INSTALL COMPONENT 'file://component_validate_password'; # Set Password Policies example: set persist validate_password.check_user_name='ON'; set persist validate_password.dictionary_file='<FILENAME OF DICTIONARY FILE'; set persist validate_password.length=15; set persist validate_password.mixed_case_count=1; set persist validate_password.special_char_count=2; set persist validate_password.number_count=2; set persist validate_password.policy='STRONG'; set persist password_history = 5; set persist password_reuse_interval = 365; SET GLOBAL default_password_lifetime = 180; Optional set persist password_require_current=YES This can also be set at the account level: ALTER USER 'jeffrey'@'localhost' PASSWORD HISTORY 5 PASSWORD REUSE INTERVAL 365 DAY; ALTER USER 'jeffrey'@'localhost' PASSWORD EXPIRE INTERVAL 90 DAY;
MySQL stores and displays its user passwords in encrypted form. Nevertheless, this should be verified by reviewing the relevant system views, along with the other items to be checked here. Ask the database administrator (DBA) to review the list of DBMS database objects, database configuration files, associated scripts, and applications defined within, and external to, the DBMS that accesses the database. The list must also include files, tables, or settings used to configure the operational environment for the DBMS and for interactive DBMS user accounts. Ask the DBA and/or information system security officer (ISSO) to determine if any DBMS database objects, database configuration files, associated scripts, and applications defined within or external to the DBMS that access the database, and DBMS/user environment files/settings/tables, contain database passwords. If any do, confirm that DBMS passwords stored internally or externally to the DBMS are encoded or encrypted. If any passwords are stored in clear text, this is a finding. Ask the DBA/System Administrator (SA)/Application Support staff if they have created an external password store for applications, batch jobs, and scripts to use. Verify that all passwords stored there are encrypted. If a password store is used and any password is not encrypted, this is a finding. Run this query to determine which MySQL Server authentication methods are enabled: SELECT PLUGIN_NAME, PLUGIN_STATUS FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_NAME LIKE '%ldap%' OR PLUGIN_NAME LIKE '%ldap%' OR PLUGIN_NAME LIKE '%pam%' OR PLUGIN_NAME like '%password'; If the results return any of the following values: 'mysql_native_password','ACTIVE' 'sha256_password','ACTIVE' 'caching_sha2_password’,’ACTIVE’ Next, determine if any accounts have been created that use passwords. SELECT user, host, `user`.`plugin` FROM `mysql`.`user` where (user.plugin like '%password') AND NOT (user like 'mysql.%' or user ='root'); For the mysql or mysqlsh command line tools, which can be configured to accept a plain-text password, and any other essential tool with the same limitation, verify that the system documentation explains the need for the tool, who uses it, and any relevant mitigations; and that AO approval has been obtained; if not, this is a finding. Request evidence that all users of the tool are trained in the importance of not using the plain-text password option; how to keep the password hidden; and adherence to this practice. If they are not, this is a finding.
Develop, document, and maintain a list of DBMS database objects, database configuration files, associated scripts, and applications defined within or external to the DBMS that access the database, and DBMS/user environment files/settings in the System Security Plan. Record whether they do or do not contain DBMS passwords. If passwords are present, ensure they are encoded or encrypted and protected by host system security. Where possible, alter the authentication mode to X509 or LDAP SASL/Kerberos: Just X509 certificate - for example ALTER USER 'jeffrey'@'localhost' REQUIRE X509; Specific X509 example: ALTER USER 'jeffrey'@'localhost' REQUIRE SUBJECT '/C=SE/ST=Stockholm/L=Stockholm/ O=MySQL demo client certificate/ CN=client/emailAddress=client@example.com' AND ISSUER '/C=SE/ST=Stockholm/L=Stockholm/ O=MySQL/CN=CA/emailAddress=ca@example.com' AND CIPHER 'EDH-RSA-DES-CBC3-SHA’; LDAP SASL example: CREATE USER 'boris'@'localhost' IDENTIFIED WITH authentication_ldap_sasl AS 'uid=boris_ldap,ou=People,dc=example,dc=com'; If password authentication is necessary, then for mysql and mysqlsh command lines which cannot be configured not to accept a plain-text password when mixed-mode authentication is enabled, and any other essential tool with the same limitation: 1) Document the need for it, who uses it, any relevant mitigations, and obtain AO approval. 2) Train all users of the tool in the importance of not using the plain-text password option and in how to keep the password hidden.
Review configuration settings for encrypting passwords in transit across the network. If passwords are not encrypted, this is a finding. If it is determined that passwords are passed unencrypted at any point along the transmission path between the source and destination, this is a finding. To check, run the following SQL: SELECT VARIABLE_NAME, VARIABLE_VALUE FROM performance_schema.global_variables WHERE VARIABLE_NAME IN ('require_secure_transport') ; If the require_secure_transport VARIABLE_VALUE is not 'ON' (1), this is a finding. If 1 (On), then only SSL connections are permitted; next examine the certificate used. Run the following command to determine the certificate in use along with other details: select @@ssl_ca, @@ssl_capath, @@ssl_cert, @@ssl_cipher, @@ssl_crl, @@ssl_crlpath, @@ssl_fips_mode, @@ssl_key; If the certificate is not a DoD certificate, or if no certificate is listed, this is a finding.
Configure encryption for transmission of passwords across the network. If the database does not provide encryption for logon events natively, employ encryption at the OS or network level. Ensure passwords remain encrypted from source to destination. connect to MySQL as admin (root) mysql> set persist require_secure_transport=ON; Set system variables on the server side specify DoD approved certificate and key files the server uses when permitting clients to establish encrypted connections: ssl_ca: The path name of the Certificate Authority (CA) certificate file. (ssl_capath is similar but specifies the path name of a directory of CA certificate files.) ssl_cert: The path name of the server public key certificate file. This certificate can be sent to the client and authenticated against the CA certificate that it has. ssl_key: The path name of the server private key file. For example, to enable the server for encrypted connections with certificates, start it with these lines in the my.cnf file, changing the file names as necessary: [mysqld] ssl_ca=ca.pem ssl_cert=server-cert.pem ssl_key=server-key.pem
If all interaction with the user for purposes of authentication is handled by a software component separate from the MySQL Database Server 8.0, this is not a finding. If any application, tool, or feature associated with the MySQL Database Server 8.0/database displays any authentication secrets (to include PINs and passwords) during or after the authentication process, this is a finding. MySQL command line option --password (or -p) obscures feedback on the typed in password. Ensure users are trained to use alternatives to command line password parameters, if they are not, this is a finding.
Modify and configure each non-compliant application, tool, or feature associated with the MySQL Database Server 8.0/database so that it does not display authentication secrets. Use -p (--password) without providing a password for the mysql command line tool. Configure or modify applications to prohibit display of passwords in clear text. Use OS pluggable password manager integration to protect passwords using keyrings. Following is an example: $ /usr/local/mysql/bin/mysql -uroot -p Enter password: $ mysqlsh --user=user --password Please provide the password for 'user@localhost':
Check MySQL settings to determine whether users are restricted from accessing objects and data they are not authorized to access. Review the system documentation to determine the required levels of protection for DBMS server securables, by type of login. Review the permissions actually in place on the server. If the actual permissions do not match the documented requirements, this is a finding. The following tables contain access control data. Run these scripts: For information about database-level privileges: The server uses the user and db tables in the mysql database at both the first and second stages of access control. SELECT * FROM mysql.db; SELECT * FROM mysql.user; During the second stage of access control, the server performs request verification to ensure each client has sufficient privileges for each request it issues. These provide finer privilege control at the table and column levels. SELECT * FROM mysql.tables_priv; SELECT * FROM mysql.columns_priv; For verification of requests that involve stored routines. SELECT * FROM mysql.procs_priv; Information about proxy accounts SELECT * from mysql.proxies_priv; Lists current assignments of dynamic global privileges to user accounts. SELECT * from mysql.global_grants; Lists default user roles SELECT * FROM mysql.default_roles; Lists edges for role subgraphs, showing roles assigned to other roles hierarchy. SELECT * FROM mysql.role_edges; To inspect permissions on specific table(s): WITH tableprivs AS (SELECT user, host, 'mysql.tables_priv' as PRIV_SOURCE , DB as _db, Table_Name as _obj , ' ' as _col FROM mysql.tables_priv where Table_name like '%' ), colprivs AS (SELECT User, Host, 'mysql.columns_priv' as PRIV_SOURCE , DB as _db, table_name as _obj , column_name as _col FROM mysql.columns_priv WHERE Table_name like '%' ) SELECT user,host, PRIV_SOURCE , _db as _db, _obj, _col FROM ( SELECT user,host, PRIV_SOURCE, _db, _obj, _col FROM colprivs UNION SELECT user,host, PRIV_SOURCE, _db, _obj, _col FROM tableprivs) as tt group by user, host, PRIV_SOURCE, _db, _obj, _col; To inspect specific user, role or user using role: Example User or role SHOW GRANTS FOR 'app_developer'@'%'; User with Role SHOW GRANTS FOR 'u1'@'localhost' USING 'r1'; If appropriate access controls are not implemented to restrict access to authorized users and to restrict the access of those users to objects and data they are authorized to see, this is a finding.
Configure the MySQL Database Server 8.0 settings and access controls to permit user access only to objects and data that the user is authorized to view or interact with, and to prevent access to all other objects and data. Use GRANT, REVOKE, ALTER statements to add and remove permissions on server-level securables, bringing them into line with the documented requirements.
Review the MySQL documentation and configuration to determine it is configured in accordance with DoD security configuration and implementation guidance, including STIGs, NSA configuration guides, CTOs, DTMs, and IAVMs. If the MySQL is not configured in accordance with security configuration settings, this is a finding.
Configure MySQL in accordance with security configuration settings by reviewing the Operation System and MySQL documentation and applying the necessary configuration parameters to meet the configurations required by the STIG, NSA configuration guidelines, CTOs, DTMs, and IAVMs.
Review vendor documentation and vendor websites to identify vendor-provided demonstration or sample databases, database applications, objects, and files. Note: MySQL does not include any in MySQL Database Server 8.0. Review the MySQL Database Server 8.0 to determine if any of the demonstration and sample databases, database applications, or files are installed in the database or are included with the MySQL Database Server 8.0 application. If any are present in the database or are included with the MySQL Database Server 8.0 application, this is a finding. Check database/schema content of MySQL with the following command: SELECT * FROM information_schema.SCHEMATA where SCHEMA_NAME not in ('mysql','information_schema', 'sys', 'performance_schema'); If this system is identified as production, gather a listing of databases from the server and look for any matching the following general demonstration database names: sakila world world_x menagerie If any of these databases exist, this is a finding.
MySQL 8.0 contains no demo databases by default. If demo schemas (aka databases) were added, remove them by executing: mysql -u root -p --execute="DROP DATABASE 'schema_name'"
Review the list of components and features installed with the MySQL Database Server 8.0. List options MySQL Plugins/Components SELECT * FROM information_schema.PLUGINS where plugin_library is NOT NULL; Compare the feature listing against the required plugins listing. If any plugins are installed, but are not required, this is a finding. SELECT * FROM mysql.component; Compare the feature listing against the required components listing. If any components are installed, but are not required, this is a finding.
Uninstall unused components or features that are installed and can be uninstalled. Remove any database objects and applications that are installed to support them. After review of installed plugin components uninstall unused plugins. To do this while the server is running using the UNINSTALL PLUGIN; command: Remove any plugin that is loaded at startup from the my.cnf file. For example - ddl_rewriter is discovered but are not being used. Follow these removal instructions. Remove this line from my.cnf: plugin-load-add=ddl_rewriter.so Remove any plugin that is not loaded at startup using the --plugin-load parameter from the my.cnf or on the command line. UNINSTALL PLUGIN <plugin_name>; UNINSTALL PLUGIN ddl_rewriter; Remove any component not in use UNINSTALL COMPONENT component_name [, component_name ] ...; For example - The audit message emit function is not being called, the component is not needed. UNINSTALL COMPONENT "file://component_audit_api_message_emit";
Review the list of components and features installed with the MySQL Database Server 8.0. List options MySQL Plugins/Components SELECT * FROM information_schema.PLUGINS where plugin_library is NOT NULL; Compare the feature listing against the required plugins listing. If any plugins are installed, but are not required, this is a finding. SELECT * FROM mysql.component; Compare the feature listing against the required components listing. If any components are installed, but are not required, this is a finding.
Uninstall unused components or features that are installed and can be uninstalled. Remove any database objects and applications that are installed to support them. After review of installed plugin components uninstall unused plugins. To do this while the server is running using the UNINSTALL PLUGIN; command: Remove any plugin that is loaded at startup from the my.cnf file. For example - ddl_rewriter is discovered but are not being used. Follow these removal instructions. Remove this line from my.cnf: plugin-load-add=ddl_rewriter.so Remove any plugin that is not loaded at startup using the --plugin-load parameter from the my.cnf or on the command line. UNINSTALL PLUGIN <plugin_name>; UNINSTALL PLUGIN ddl_rewriter; Remove any component not in use UNINSTALL COMPONENT component_name [, component_name ] ...; For example - The audit message emit function is not being called, the component is not needed. UNINSTALL COMPONENT "file://component_audit_api_message_emit";
Review the MySQL Database Server 8.0 settings and local documentation for functions, ports, protocols, and services that are not approved. If any are found, this is a finding. Run the following SQL to list ports: SELECT VARIABLE_NAME, VARIABLE_VALUE FROM performance_schema.global_variables WHERE VARIABLE_NAME in ('port', 'mysqlx_port', 'admin_port'); The default ports for MySQL for organizational connects are: Classic MySQL - 3306 MySQL X - 33060 MySQL Admin Port - 33062 (disabled by default) If any these are in conflict with guidance, and not explained and approved in the system documentation, this is a finding. Run the following to determine if a local socket/pipe are in use: SELECT VARIABLE_NAME, VARIABLE_VALUE FROM performance_schema.global_variables where VARIABLE_NAME like '%pipe%' or VARIABLE_NAME = 'socket' or VARIABLE_NAME = 'mysqlx_socket'; Values for classic and xprotocol will be returned. For example on Linux: 'socket','/tmp/mysql.sock' 'mysqlx_socket','/tmp/mysqlx.sock' Windows 'named_pipe', 'ON' If these are in conflict with guidance, and not explained and approved in the system documentation, this is a finding.
Disable functions, ports, protocols, and services that are not approved. Change mysql options related to network, ports, and protocols for the server and additionally consider refining further at user account level. vi my.cnf [mysqld] port=<port value> admin_address=<addr> admin_port=<port value> mysqlx_port=<port value> socket={file_name|pipe_name} If admin_address is not defined then access via the admin port is disabled. Additionally the X Plugin can be disabled at startup by either setting mysqlx=0 in the MySQL configuration file, or by passing in either --mysqlx=0 or --skip-mysqlx when starting the MySQL server. Restart mysqld
Review MySQL Database Server 8.0 settings to determine whether organizational users are uniquely identified and authenticated when logging on/connecting to the system. Using SQL, search for external authentication plugins: SELECT PLUGIN_NAME, PLUGIN_STATUS FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_NAME LIKE '%ldap%' OR PLUGIN_NAME LIKE '%ldap%' OR PLUGIN_NAME LIKE '%pam%'; This listing will show what is enabled. In addition to MySQL password-based internal accounts, there is also support for external accounts: Linux PAM (Pluggable Authentication Modules) Windows Active Directory (only for Windows MySQL servers) Native LDAP auth_socket Review the configuration of the plugin; for link of accounts and permissions to organizational level, run this SQL: SELECT VARIABLE_NAME, VARIABLE_VALUE FROM performance_schema.global_variables WHERE VARIABLE_NAME LIKE 'auth%' ; This will show external configuration setup for authentication using an organizational authentication source. Review users using organizational authentication. Review the "authentication_string" for proper mapping: SELECT `user`.`Host`, `user`.`user`, `user`.`plugin`, `user`.`authentication_string` from mysql.user where plugin like 'auth%'; If organizational users are not uniquely identified and authenticated, this is a finding. If accounts are determined to be shared, determine if they are directly accessible to end users. If so, this is a finding.
Configure MySQL Database Server 8.0 settings to uniquely identify and authenticate all organizational users who log on/connect to the system. Remove user-accessible shared accounts and use individual user names. Configure applications to ensure successful individual authentication prior to shared account access. Ensure each user's identity is received and used in audit data in all relevant circumstances. Install appropriate auth plugin, for example LDAP. INSTALL PLUGIN authentication_ldap_sasl SONAME 'authentication_ldap_sasl.so'; INSTALL PLUGIN authentication_ldap_simple SONAME 'authentication_ldap_simple.so'; Configure SET PERSIST authentication_ldap_sasl_server_host='127.0.0.1'; SET PERSIST authentication_ldap_sasl_bind_base_dn='dc=example,dc=com'; SET PERSIST authentication_ldap_simple_server_host='127.0.0.1'; SET PERSIST authentication_ldap_simple_bind_base_dn='dc=example,dc=com'; Create users with proper organizational mapping, for example: CREATE USER 'betsy'@'localhost' IDENTIFIED WITH authentication_ldap_simple BY 'uid=betsy_ldap,ou=People,dc=example,dc=com'; Assign appropriate roles and grants.
Review DBMS configuration to verify it is using NIST FIPS validated cryptographic modules for cryptographic operations. To check for FIPS validated cryptographic modules for all operations, run this script in the database: SELECT VARIABLE_NAME, VARIABLE_VALUE FROM performance_schema.global_variables where variable_name = 'ssl_fips_mode'; The result will be either "ON" or "STRICT". If not, then NIST FIPS validated modules are not being used, and this is a finding.
Utilize NIST FIPS validated cryptographic modules for all cryptographic operations. See Use MySQL Server OpenSSL FIPS mode. See https://dev.mysql.com/doc/refman/8.0/en/fips-mode.html Turn on MySQL FIPS mode and restart mysqld Edit my.cnf [mysqld] ssl_fips_mode=ON or [mysqld] ssl_fips_mode=STRICT ON: Enable FIPS mode. STRICT: Enable “strict” FIPS mode.
Review MySQL Database Server 8.0 settings to determine if users uniquely identify and authenticate all non-organizational users who log on to the system. select host, user FROM mysql.user WHERE user not in ('mysql.infoschema', 'mysql.session', 'mysql.sys'); If accounts are determined to be shared, determine if individuals are first individually authenticated. If the documentation indicates that this is a public-facing, read-only (from the point of view of public users) database that does not require individual authentication, this is not a finding. If non-organizational users are not uniquely identified and authenticated, this is a finding.
Configure MySQL Database Server 8.0 settings to uniquely identify and authenticate all non-organizational users who log on to the system. Ensure all logins are uniquely identifiable and authenticate all non-organizational users who log on to the system. This likely would be done by ensuring mapping of MySQL accounts to individual accounts. Verify server documentation to ensure accounts are documented and unique.
Check MySQL settings and documentation to verify that administrative functionality is separate from user functionality. As Database Administrator (DBA) (“root"), list all roles and permissions for the database: > mysql -u root -p SELECT user,host, 'Global Priv', Select_priv, Insert_priv, Update_priv, Delete_priv, Create_priv, Drop_priv, Reload_priv, Shutdown_priv, Process_priv, File_priv, Grant_priv, References_priv, Index_priv, Alter_priv, Show_db_priv, Super_priv, Create_tmp_table_priv, Lock_tables_priv, Execute_priv, Repl_slave_priv, Repl_client_priv, Create_view_priv, Show_view_priv, Create_routine_priv, Alter_routine_priv, Create_user_priv, Event_priv, Trigger_priv, Create_tablespace_priv, Create_role_priv, Drop_role_priv FROM mysql.user WHERE 'Y' IN (Select_priv, Insert_priv, Update_priv, Delete_priv, Create_priv, Drop_priv, Reload_priv, Shutdown_priv, Process_priv, File_priv, Grant_priv, References_priv, Index_priv, Alter_priv, Show_db_priv, Super_priv, Create_tmp_table_priv, Lock_tables_priv, Execute_priv, Repl_slave_priv, Repl_client_priv, Create_view_priv, Show_view_priv, Create_routine_priv, Alter_routine_priv, Create_user_priv, Event_priv, Trigger_priv, Create_tablespace_priv, Create_role_priv, Drop_role_priv) AND user not in ('mysql.infoschema', 'mysql.session'); If any non-administrative role has permissions, other than mysql.infoschema and mysql.session, this is a finding. If administrator and general user functionality are not separated, this is a finding.
Configure MySQL Database Server 8.0 to separate database administration and general user functionality. Revoke or remove users with admin and user mixed permissions. Review MySQL documentation related to access controls for users and admins: https://dev.mysql.com/doc/refman/8.0/en/access-control.html.
Determine elements of security functionality (lists of permissions, additional authentication information, stored procedures, application specific auditing, etc.) which are being housed inside the MySQL server. For any elements found, check MySQL to determine if these objects or code implementing security functionality are located in a separate security domain, such as a separate database, schema, or table created specifically for security functionality. In more generic data terms, MySQL is a single database per instance with multiple schemas. MySQL uses the term database and schema interchangeably. Run the following query to list all the user-defined schemas. SELECT `SCHEMATA`.`SCHEMA_NAME` FROM `information_schema`.`SCHEMATA` where `SCHEMA_NAME` not in ('mysql', 'information_schema', 'performance_schema','sys'); Review the database structure to determine where security-related functionality is stored. If security-related database objects or code are not kept separate, this is a finding.
Check the server documentation, locate security-related database objects and code in a separate database, schema, table, or other separate security domain from database objects and code implementing application logic. Schemas, also referred to as databases, are analogous to separate namespaces or containers used to store database objects. Security permissions apply to schemas, making them an important tool for separating and protecting database objects based on access rights. Schemas reduce the work required, and improve the flexibility, for security-related administration of a database. A MySQL schema is a named container for database objects, which allows objects to be grouped into separate namespaces. Where possible, locate security-related database objects and code in a separate database, schema, or other separate security domain from database objects and code implementing application logic. In all cases, use GRANT, REVOKE, … , DROP ROLE statements to add and remove permissions on administrative/server-level and schema/database-level, or database object security-related objects to provide effective isolation.
Review the procedures for the refreshing of development/test data from production. Review any scripts or code that exists for the movement of production data to development/test systems, or to any other location or for any other purpose. Verify that copies of production data are not left in unprotected locations. If the code that exists for data movement does not comply with the organization-defined data transfer policy and/or fails to remove any copies of production data from unprotected locations, this is a finding.
Modify any code used for moving data from production to development/test systems to comply with the organization-defined data transfer policy, and to ensure copies of production data are not left in unsecured locations.
Review the permissions granted to users via the operating system/file system on the instance files, database files, database redo, undo, archive, bin and audit log files, and database backup files. If any user/role who is not an authorized system administrator with a need to know or database administrator with a need to know, or a system account for running DBMS processes permitted to read/view any of these files, this is a finding. Note: When the instance and database directories are created by mysql installations packages, the permissions are secure and should not be changed. Run ls -l on the various files and directory. Permissions should be limited to the mysql user and mysql group. Use the following queries/commands to find the locations of instance directory, database directory, transaction logs directory, archive logs directory, audit logs directory, and backup files location. SELECT VARIABLE_NAME, VARIABLE_VALUE FROM performance_schema.global_variables WHERE VARIABLE_NAME LIKE '%dir' or VARIABLE_NAME LIKE '%file' order by VARIABLE_NAME; Regarding Linux default installation: Proper permissions are shown below. If the permissions are more permissive for a Location Type, this is a finding. The following table shows directory and file permissions for the generic binary distribution installation of MySQL Enterprise Edition for Linux x86-64 on Oracle Linux that is described in this guide. As indicated previously, most of the MySQL installation can be owned by root. The exceptions are the data directory, the error log file, the mysql-files directory, the pid file, and the socket file, to which the mysql user must have write access. Files and resources to which the mysql user requires read access include configuration files (/etc/my.cnf) and the MySQL binaries (for example /usr/local/mysql/bin). Desc/Typical Location Owner Directory File Permissions Permissions --------------------- ----- ----------- ----------- Client and utility programs directory /usr/local/mysql/bin root drwxr-xr-x mysqld server /usr/local/mysql/bin root drwxr-xr-x -rwxr-xr-x MySQL configuration file /etc/my.cnf root drwxr-xr-x -rw-r--r-- Data directory /usr/local/mysql/data mysql drwxr-x--- Error log file <directory where audit log files are located>/host_name.err mysql drwxr-x--- -rw------- secure_file_priv directory /usr/local/mysql/mysql-files mysql drwxr-x--- mysqld systemd service file /usr/lib/systemd/system/mysqld.service root drwxr-xr-x -rw-r--r-- systemd tmpfiles configuration file /usr/lib/tmpfiles.d/mysql.conf root drwxr-xr-x -rw-r--r-- pid file <directory where audit log files are located>/mysqld.pid mysql drwxr-x--- -rw-r----- socket file /tmp/mysql.sock mysql drwxrwxrwt srwxrwxrwx Unix manual pages directory /usr/local/mysql/man root drwxr-xr-x Include Header files directory /usr/local/mysql/include root drwxr-xr-x Libraries directory /usr/local/mysql/lib root drwxr-xr-x Miscellaneous support files directory /usr/local/mysql/support-files root drwxr-xr-x Miscellaneous files directory /usr/local/mysql/share root drwxr-xr-x
Configure the permissions granted by the operating system/file system on the database files, database log files, and database backup files so that only relevant system accounts and authorized system administrators and database administrators with a need to know are permitted to read/view these files. Remove any unauthorized permission grants from MySQL data, audit, certificate, key, or other directories.
Determine if MySQL is configured to require SSL. SELECT VARIABLE_NAME, VARIABLE_VALUE FROM performance_schema.global_variables WHERE VARIABLE_NAME like 'require_secure_transport'; If require_secure_transport is not "ON", this is a finding. Determine if MySQL is configured to require the use of FIPS compliant algorithms. SELECT VARIABLE_NAME, VARIABLE_VALUE FROM performance_schema.global_variables WHERE VARIABLE_NAME = 'ssl_fips_mode'; If ssl_fips_mode is not "ON", this is a finding.
Connect as a mysql administrator mysql> set persist require_secure_transport=ON; Turn on MySQL FIPS mode (ON or STRICT) and restart mysqld Edit my.cnf [mysqld] ssl_fips_mode=ON or ssl_fips_mode=STRICT
If the application owner and Authorizing Official have determined that encryption of data at rest is NOT required, this is not a finding. Review settings to ensure appropriate controls are applied to protect the confidentiality and integrity of data at rest in the database. Using SQL determine if all data-at-rest is encrypted. SELECT VARIABLE_NAME, VARIABLE_VALUE FROM performance_schema.global_variables where variable_name = 'audit_log_encryption'; If "audit_log_encryption" is not set to "AES", this is a finding. SELECT VARIABLE_NAME, VARIABLE_VALUE FROM performance_schema.global_variables where variable_name = 'binlog_encryption'; If "binlog_encrypt" is not set to "ON", this is a finding. SELECT VARIABLE_NAME, VARIABLE_VALUE FROM performance_schema.global_variables where variable_name = 'innodb_redo_log_encrypt'; If "innodb_redo_log_encrypt" is not set to "ON", this is a finding. SELECT VARIABLE_NAME, VARIABLE_VALUE FROM performance_schema.global_variables where variable_name = 'innodb_undo_log_encrypt'; If "innodb_undo_log_encrypt" is not set to "ON", this is a finding. SELECT VARIABLE_NAME, VARIABLE_VALUE FROM performance_schema.global_variables WHERE VARIABLE_NAME like 'general_log'; If "general_log"is not "OFF", this is a finding. Find encryption status for all mysql table and tablespaces. SELECT `INNODB_TABLESPACES`.`NAME`, `INNODB_TABLESPACES`.`ENCRYPTION` FROM `information_schema`.`INNODB_TABLESPACES`; If any tablespace other than innodb_temporary does not have ENCRYPTION set to "Y (yes)", this is a finding. SELECT VARIABLE_NAME, VARIABLE_VALUE FROM performance_schema.global_variables where variable_name = 'table_encryption_privilege_check'; If "innodb_redo_log_encrypt" is not set to "ON", this is a finding.
Apply appropriate MySQL Database 8.0 controls to protect the confidentiality and integrity of data at rest in the database. sudo vi /etc/my.cnf [mysqld] audit-log=FORCE_PLUS_PERMANENT audit-log-format=JSON audit-log-encryption=AES Turn on binlog encryption set persist binlog_encryption=ON; Turn on undo and redo log encryption set persist innodb_redo_log_encrypt=ON; set persist innodb_undo_log_encrypt=ON; Enable encryption for a new file-per-table tablespace, specify the ENCRYPTION option in a CREATE TABLE statement. The following example assumes that innodb_file_per_table is enabled. mysql> CREATE TABLE t1 (c1 INT) ENCRYPTION='Y'; To enable encryption for an existing file-per-table tablespace, specify the ENCRYPTION option in an ALTER TABLE statement. mysql> ALTER TABLE t1 ENCRYPTION='Y'; To disable encryption for file-per-table tablespace, set ENCRYPTION='N' using ALTER TABLE. mysql> ALTER TABLE t1 ENCRYPTION='N'; Disable the general_log SET PERSIST general_log = 'OFF';
Review MySQL Database Server 8.0 code (stored procedures, functions, triggers), application code, settings, column and field definitions, triggers, and constraints to determine whether the database is protected against invalid input. If code exists that allows invalid data to be acted upon or input into the database, this is a finding. If column/field definitions do not exist in the database, this is a finding. If columns/fields do not contain constraints and validity checking where required, this is a finding. Where a column/field is noted in the system documentation as necessarily free-form, even though its name and context suggest that it should be strongly typed and constrained, the absence of these protections is not a finding. Where a column/field is clearly identified by name, caption or context as Notes, Comments, Description, Text, etc., the absence of these protections is not a finding. MySQL Workbench Schema and Table Inspectors are effective tools for performing the review process, as are the MySQL Information Schema, and MySQL Schema tables.
Use parameterized queries, constraints, foreign keys, etc., to validate data input. Modify MySQL SQL Server to properly use the correct column data types as required in the database.
Review MySQL source code (trigger procedures, functions) and application source code, to identify cases of dynamic code execution. Any user input should be handled through prepared statements or calls that bind parameters versus generating SQL. If dynamic code execution is employed in circumstances where the objective could practically be satisfied by static execution with strongly typed parameters, this is a finding.
Where dynamic code execution is employed in circumstances where the objective could practically be satisfied by static execution with strongly typed parameters, modify the code to do so.
Review MySQL Database Server 8.0 source code (stored procedures, functions, and triggers) and application source code to identify cases of dynamic code execution. Determine if the MySQL Enterprise Firewall is installed and trained to recognize normal behavior and block or alert of abnormal requests. Run the following command: SHOW GLOBAL VARIABLES LIKE 'mysql_firewall_mode'; Review firewall users and Mode. SELECT * FROM INFORMATION_SCHEMA.MYSQL_FIREWALL_USERS; If no rows are returned and no firewall allow lists are active, this is a finding. If LEARNING is returned, the firewall is building an allow list for the userhost user. If PROTECTING is returned, the firewall will only permit SQL on the allow list for the userhost user to execute. If DETECTING is returned, the firewall will write to the firewall log SQL not on the allow list for the userhost user to execute. If dynamic code execution is employed without protective measures against code injection, this is a finding.
Where dynamic code execution is used, modify the code to implement protections against code injection. Enable the MySQL Enterprise Firewall by running this script, which is located in the mysql home share sub directory. mysql -u root -p mysql < linux_install_firewall.sql Train the firewall for users where dynamic code injection is possible, for examples applications that allow user input. CALL mysql.sp_set_firewall_mode('fwuser@localhost', 'RECORDING'); Once the allowlist for the user/host has been captured, the firewall can be placed in PROTECTING (active blocking) or DETECTING(logging) mode. CALL mysql.sp_set_firewall_mode('fwuser@localhost', 'PROTECTING'); CALL mysql.sp_set_firewall_mode('fwuser@localhost', 'DETECTING');
Determine if an audit is configured and enabled. The my.cnf file will set the variable audit_file. Review the my.cnf file for the following entries: [mysqld] plugin-load-add=audit_log.so audit-log=FORCE_PLUS_PERMANENT If these entries are not present. This is a finding. Execute the following query: SELECT PLUGIN_NAME, PLUGIN_STATUS FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_NAME LIKE 'audit%'; The status of the "audit_log plugin" must be "active". If it is not "active", this is a finding. Review audit filters and associated users by running the following queries: SELECT `audit_log_filter`.`NAME`, `audit_log_filter`.`FILTER` FROM `mysql`.`audit_log_filter`; SELECT `audit_log_user`.`USER`, `audit_log_user`.`HOST`, `audit_log_user`.`FILTERNAME` FROM `mysql`.`audit_log_user`; All currently defined audits for the MySQL server instance will be listed. If no audits are returned, this is a finding.
Configure the MySQL Audit to automatically start during system startup. Add to the my.cnf: [mysqld] plugin-load-add=audit_log.so audit-log=FORCE_PLUS_PERMANENT audit-log-format=JSON
Check users with permissions to administer MySQL Auditing. select * from information_schema.user_privileges where privilege_type = 'AUDIT_ADMIN'; If unauthorized accounts have these the AUDIT_ADMIN privilege, this is a finding.
Remove audit-related permissions from individuals and roles not authorized to have them. REVOKE AUDIT_ADMIN on *.* FROM <user>;
Check users with permissions to administer MySQL Auditing. select * from information_schema.user_privileges where privilege_type = 'AUDIT_ADMIN'; If unauthorized accounts have the AUDIT_ADMIN privilege, this is a finding. Check that a keyring plugin is installed. SELECT * FROM information_schema.PLUGINS where plugin_name like 'keyring%'; If no keyring is installed, this is a finding. Check if the audit files are encrypted. To check for data encryption at rest settings in MySQL: SELECT VARIABLE_NAME, VARIABLE_VALUE FROM performance_schema.global_variables where variable_name = 'audit_log_encryption'; If "audit_log_encryption" is not set to "AES", this is a finding.
Remove audit-related permissions from individuals and roles not authorized to have them. REVOKE AUDIT_ADMIN on *.* FROM <user>; Set audit log format to use AES encryption. sudo vi /etc/my.cnf [mysqld] early-plugin-load=keyring_file.so audit-log=FORCE_PLUS_PERMANENT audit-log-format=JSON audit-log-encryption=AES Note: First instantiate the keyring plugin which is needed to store the audit encryption key. The example above has an "early-plugin-load=keyring_file.so" entry in the my.cnf file. A keyring plugin must be present before adding the "audit-log-encryption=AES" entry or the database will not start. Below are valid key ring plugins: For dev test - not encrypted early-plugin-load=keyring_file.so Encrypted file early-plugin-load=keyring_encrypted_file.so keyring_encrypted_file_data=/usr/local/mysql/mysql-keyring/keyring-encrypted keyring_encrypted_file_password=password KMIP early-plugin-load=keyring_okv.so keyring_okv_conf_dir=/usr/local/mysql/mysql-keyring-okv Oracle Cloud Vault early-plugin-load=keyring_oci.so keyring_oci_user=ocid1.user.oc1..longAlphaNumericString keyring_oci_tenancy=ocid1.tenancy.oc1..longAlphaNumericString keyring_oci_compartment=ocid1.compartment.oc1..longAlphaNumericString keyring_oci_virtual_vault=ocid1.vault.oc1.iad.shortAlphaNumericString.longAlphaNumericString keyring_oci_master_key=ocid1.key.oc1.iad.shortAlphaNumericString.longAlphaNumericString keyring_oci_encryption_endpoint=shortAlphaNumericString-crypto.kms.us-ashburn-1.oraclecloud.com keyring_oci_management_endpoint=shortAlphaNumericString-management.kms.us-ashburn-1.oraclecloud.com keyring_oci_vaults_endpoint=vaults.us-ashburn-1.oci.oraclecloud.com keyring_oci_secrets_endpoint=secrets.vaults.us-ashburn-1.oci.oraclecloud.com keyring_oci_key_file=file_name keyring_oci_key_fingerprint=12:34:56:78:90:ab:cd:ef:12:34:56:78:90:ab:cd:ef Hashicorp early-plugin-load=keyring_hashicorp.so keyring_hashicorp_role_id='ee3b495c-d0c9-11e9-8881-8444c71c32aa' keyring_hashicorp_secret_id='0512af29-d0ca-11e9-95ee-0010e00dd718' keyring_hashicorp_store_path='/v1/kv/mysql'
Check users with permissions to administer MySQL Auditing. select * from information_schema.user_privileges where privilege_type = 'AUDIT_ADMIN'; If unauthorized accounts have these the AUDIT_ADMIN privilege, this is a finding.
This requirement is a permanent finding and cannot be fixed. An appropriate mitigation for the system must be implemented, but this finding cannot be considered fixed.
Review Server documentation to determine the authorized owner and users or groups with modify rights for this SQL instance's binary files. Additionally check the owner and users or groups with modify rights for shared software library paths on disk. If any unauthorized users are granted modify rights, this is a finding. A plugin located in a plugin library file can be loaded at runtime with the INSTALL PLUGIN statement. The statement also registers the plugin in the mysql.plugin table to cause the server to load it on subsequent restarts. For this reason, INSTALL PLUGIN requires the INSERT privilege for the mysql.plugin table, and UNINSTALL requires DELETE. Run the following statement to check for table specific privileges: SELECT * FROM information_schema.TABLE_PRIVILEGES where (table_schema='mysql' and table_name=`plugin`) or (table_schema='mysql' and table_name='component';) If privilege_type is INSERT or DELETE for an unauthorized user, this is a finding. Run the following statement to check for global privileges: select * from information_schema.user_privileges where privilege_type='INSERT' or privilege_type='DELETE'; If privilege_type is INSERT or DELETE for an unauthorized user, this is a finding.
Remove permissions from users who should not have insert or update access to the mysql.plugin or mysql.component table.
Review procedures for controlling and granting access to use of the DBMS software installation account. If access or use of this account is not restricted to the minimum number of personnel required, or if unauthorized access to the account has been granted, this is a finding.
Develop, document, and implement procedures to restrict and track use of the MySQL Database Server 8.0 software installation account.
Review the MySQL Database Server 8.0 software library directory and note other root directories located on the same disk directory or any subdirectories. To list directory variables run: show variables where variable_name like '%dir%'; If any non-MySQL Database Server 8.0 software directories exist on the datadir, basedir, or other non tmpdir directories, examine or investigate their use. If any of the directories are used by other applications, including third-party applications that use the MySQL Database Server 8.0, this is a finding. Only applications that are required for the functioning and administration, not use, of the MySQL Database Server 8.0 should be located in the same disk directory as the DBMS software libraries. If other applications are located in the same directory as the DBMS, this is a finding. To determine where the mysql configuration file(s) are being stored and which configuration file(s) was used for which variables, run the following command: SELECT t1.*, VARIABLE_VALUE FROM performance_schema.variables_info t1 JOIN performance_schema.global_variables t2 ON t2.VARIABLE_NAME=t1.VARIABLE_NAME where length(t1.variable_path) > 0; If result of VARIABLE_PATH shows that configuration values are not stored in files dedicated directories separate from the host os or other applications, this is a finding.
Install all applications on directories separate from the DBMS software library directory. Relocate any directories or reinstall other application software that currently shares the DBMS software library directory. If it is determined that configuration (options files) are inappropriately located, take the steps to move and protect these files and reconfigure mysqld startup commands to point to new the file location by setting the "--defaults-file" to point to the new location and filename for the mysql configuration file.
MySQL database objects do not have an owner. MySQL is a single instance and single database with multiple schemas (aliased to be called either schema or database). Permissions are based on schemas and schema objects and privileges include grants to objects or grants to allow users to further grants access to objects. To reiterate, there is not an object owner just rights assigned to schemas and the objects within them. To determine rights to objects via schema, table, or user privileges run the following: SELECT * FROM `information_schema`.`SCHEMA_PRIVILEGES`; SELECT * FROM `information_schema`.`TABLE_PRIVILEGES`; SELECT * FROM `information_schema`.`COLUMN_PRIVILEGES`; SELECT * FROM `information_schema`.`USER_PRIVILEGES`; SELECT * FROM `information_schema`.`ROLE_COLUMN_GRANTS`; SELECT * FROM `information_schema`.`ROLE_TABLE_GRANTS`; On a per-user basis, for example: show grants for 'test'@'%'; If any database objects are found to have access by users not authorized to the database objects, this is a finding.
Assign ownership of authorized objects to authorized object owner accounts. Review user accounts with the GRANT OPTION. REVOKE GRANT OPTION to limit users with grant privileges.
The server must only use approved network communication libraries, ports, and protocols. Obtain a list of all approved network libraries, communication ports, and protocols from the server documentation. Verify that the protocols are enabled for the instance. Run the following SQL to list ports: SELECT VARIABLE_NAME, VARIABLE_VALUE FROM performance_schema.global_variables WHERE VARIABLE_NAME in ('port', 'mysqlx_port', 'admin_port'); The default ports for MySQL for organizational connects are: Classic MySQL Protocol - 3306, MySQL X Protocol 33060, MySQL Admin Port (disabled by default) If these are in conflict with guidance, and not explained and approved in the system documentation, this is a finding. Run the following to determine if a local socker/pipe are in use: SELECT VARIABLE_NAME, VARIABLE_VALUE FROM performance_schema.global_variables where VARIABLE_NAME like '%pipe%' or VARIABLE_NAME = 'socket' or VARIABLE_NAME = 'mysqlx_socket'; Values are for classic and xprotocol will be returned. For example on Linux 'socket','/tmp/mysql.sock' 'mysqlx_socket','/tmp/mysqlx.sock' Windows 'named_pipe', 'ON'; If these are in conflict with guidance, and not explained and approved in the system documentation, this is a finding. Run the following statement to inspect port settings: SELECT VARIABLE_NAME, VARIABLE_VALUE FROM performance_schema.global_variables WHERE VARIABLE_NAME LIKE '%port%' or VARIABLE_NAME LIKE '%port' order by VARIABLE_NAME; Linux local socket select @@socket; Windows local pipe select @@named_pipe; If any ports or protocols are used that are not specifically approved in the server documentation, this is a finding.
Disable each prohibited network function, port, protocol, or service prohibited by the PPSM guidance. Change mysql options related to network, ports, and protocols for the server and additionally consider refining further at user account level. To set ports properly, edit the mysql configuration file and change ports or protocol settings. vi my.cnf [mysqld] port=<port value> admin_port=<port value> mysqlx_port=<port value> socket=/path/to/socket To turn off TCP/IP: skip_networking=ON If admin_address is not defined then access via the admin port is disabled. Additionally the X Plugin can be disabled at startup/restart by either setting mysqlx=0 in the MySQL configuration file, or by passing in either "--mysqlx=0" or "--skip-mysqlx" when starting the MySQL server.
MySQL requires users (other than root) to be explicitly granted the CREATE ROUTINE privilege in order to install logical modules. To obtain a listing of users and roles who are authorized to create, alter, or replace stored procedures and functions from the server documentation. Execute the following query for server level permissions: SELECT `user`.`Host`, `user`.`User` FROM `mysql`.`user` where `Create_routine_priv`='Y' OR `Alter_routine_priv` = 'Y'; If any users or role permissions returned are not authorized to modify the specified object or type, this is a finding. If any user or role membership is not authorized, this is a finding. Execute the following query for database schema level permission (db is the schema name): SELECT `db`.`Host`, `db`.`User`, `db`.`Db` FROM `mysql`.`db` where `db`.`Create_routine_priv`='Y' OR `db`.`Alter_routine_priv` = 'Y'; If any users or role permissions returned are not authorized to modify the specified object or type, this is a finding. If any user or role membership is not authorized, this is a finding.
MySQL requires users (other than root) to be explicitly granted the CREATE ROUTINE privilege in order to install logical modules. Check user grants using the SHOW GRANTS and look for appropriate assignment of CREATE ROUTINE. For example - REVOKE CREATE ROUTINE ON mydb.* TO 'someuser'@'somehost';
Review the security configuration of the MySQL Database Server 8.0 and database(s). If it does not enforce access restrictions associated with changes to the configuration of the MySQL Database Server 8.0 or database(s), this is a finding. MySQL configuration can be set two ways: 1) The mysql configuration file. This file must be owned and permissions to read or write to it limited to the mysql OS user only. 2) Via a SET command within the server itself. These commands may be limited by limiting "server administration" privileges. User privileges can be shown using the SHOW GRANTS [FOR user]. This data is written to mysqld-auto.cnf file. See the mysql secure configuration guide for more information. Run the following command to check the mysql the linux permissions on my.cnf: ls -l /etc/my.cnf The permissions must be: File or Resource Location Owner Directory Permissions File Permissions MySQL configuration file /etc/my.cnf root drwxr-xr-x -rw-r--r-- If the permissions are more permissive than the above, this is a finding. As of mysql 8.0 configuration variables can also be set and changed using persist system variable settings that save to a file named mysqld-auto.cnf This file is in the mysql data dir. See the example below. sudo ls -l /usr/local/mysql-commercial-8.0.16-macos10.14-x86_64/data/mysqld-auto.cnf -rw-r----- 1 _mysql _mysql 2721 May 13 14:00 /usr/local/mysql-commercial-8.0.16-macos10.14-x86_64/data/mysqld-auto.cnf If the permissions of the mysqld-auto.cnf are more permissive, this is a finding.
Configure the MySQL Database Server 8.0 to enforce access restrictions associated with changes to the configuration of the MySQL Database Server 8.0 or database(s). Check and change file permissions on the MySQL Configuration file so the mysql OS user owns and file and is the only user with read-write permissions. Use the SHOW GRANTS statements to audit who has SUPER permissions and remove any users with excess privileges. For my.cnf, change ownership and permissions to: File or Resource Location Owner Directory Permissions File Permissions MySQL configuration file /etc/my.cnf root drwxr-xr-x -rw-r--r-- The mysqld-auto.cnf is created and managed by the mysql instance, as such permissions should be correct. If not correct, change "owner" to "mysql" and "rw r" to "640".
Determine if an audit is configured to capture denied actions. Check if MySQL audit is configured and enabled. The my.cnf file will set the variable audit_file. To further check, execute the following query: SELECT PLUGIN_NAME, PLUGIN_STATUS FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_NAME LIKE 'audit%'; The status of the audit_log plugin must be "active". If it is not "active", this is a finding. Review audit filters and associated users by running the following queries: SELECT `audit_log_filter`.`NAME`, `audit_log_filter`.`FILTER` FROM `mysql`.`audit_log_filter`; SELECT `audit_log_user`.`USER`, `audit_log_user`.`HOST`, `audit_log_user`.`FILTERNAME` FROM `mysql`.`audit_log_user`; All currently defined audits for the MySQL server instance will be listed. If no audits are returned, this is a finding. Connect and run commands as a low-privilege user. For example attempt to change system variables, user name, or another user's password, all of which should fail: set persist wait_timeout=28000; rename user passme to cantchange; SET PASSWORD FOR passme = 'sfsdfsdf'; Review the audit log and inspect event data containing identity and user subject details by running the Linux command: sudo cat <directory where audit log files are located>/audit.log For example, if the values returned by "select @@datadir, @@audit_log_file; " are /usr/local/mysql/data/, audit.log sudo cat /usr/local/mysql/data/audit.log { "timestamp": "2020-08-31 20:10:21", "id": 1, "class": "general", "event": "status", "connection_id": 38, "account": { "user": "fewconnects", "host": "localhost" }, "login": { "user": "fewconnects", "os": "", "ip": "127.0.0.1", "proxy": "" }, "general_data": { "command": "Query", "sql_command": "set_option", "query": "set persist wait_timeout=28000", "status": 1227 } }, { "timestamp": "2020-08-31 20:10:48", "id": 1, "class": "general", "event": "status", "connection_id": 38, "account": { "user": "fewconnects", "host": "localhost" }, "login": { "user": "fewconnects", "os": "", "ip": "127.0.0.1", "proxy": "" }, "general_data": { "command": "Query", "sql_command": "rename_user", "query": "rename user passme to cantchange", "status": 1227 } }, , "host": "localhost" }, "login": { "user": "fewconnects", "os": "", "ip": "127.0.0.1", "proxy": "" }, "general_data": { "command": "Query", "sql_command": "set_password", "query": "SET PASSWORD FOR `passme`@`%`=<secret>", "status": 1044 } }, Note each has a non-zero status, 1227, 1227, and 1044 respectively. If the audit log does not contain records of its enforcement of access restrictions associated with changes to the configuration of the DBMS or database(s), this is a finding.
If currently required, configure the MySQL Database Server to produce audit records when enforcement of access restrictions is associated with changes to the configuration of the DBMS or database(s). Refer to the supplemental file "MySQL80Audit.sql".
Check the server documentation for the SQL Audit file size configurations. Locate the Audit file path and drive. SELECT VARIABLE_NAME, VARIABLE_VALUE FROM performance_schema.global_variables WHERE VARIABLE_NAME = 'audit_log_file' OR VARIABLE_NAME= 'datadir' OR VARIABLE_NAME = 'audit_log_rotate_on_size'; If the value of audit_log_file contains a path, for example: /var/log/mysql/audit.log This is the location of the audit log, and the location to assess the storage capacity. If the value of audit_log_file is the filename alone, for example: audit.log The audit logs are located in the path returned by datadir. Calculate the space needed based on the audit file size and number of audit files to be stored simultaneously. Note that MySQL does not delete log files; that requires third-party tools or custom scripts. If the calculated product of the "audit_log_rotate_on_size" times the number of audit files allowed will exceed the size of the storage location, this is a finding.
Review the MySQL Audit file location, ensure the destination has enough space available to accommodate the maximum total size of all files that could be written. Use a script or third-party tool to manage the maximum number of audit log files that are to be stored, staying within the number of logs the system was sized to support. Use compression and JSON format to reduce file growth. Update the location for audit_log_file in the MySQL configuration file, for example: [mysqld] audit-log-file=/var/log/mysql/audit.log audit-log-format=JSON audit-log-compression=GZIP
Review the system documentation for a description of how audit records are off-loaded. Check that the OS or software is in place to copy or transfer the specified audit record content to a centralized audit log repository. If it is not, this is a finding. Check that permissions are set on the either the MySQL audit log read functions (users granted AUDIT_ADMIN or MySQL Audit Files and on the target repository to enable the required transfer of audit data. If not, this is a finding. Verify that the specified audit record content is indeed copied or transferred to the central repository. If it is not, this is a finding.
If necessary, employ SQL code calls to the audit log read functions or other software to copy or transfer the specified audit record content to the repository. Ensure that permissions are set to enable transfer of the data. Some SQL may require the AUDIT_ADMIN permission be granted to the MySQL user account used for transferring the data. Based on the setup, allocate sufficient audit file/table space to support peak demand. For example to set to 1 GB: set persist audit_log_rotate_on_size=1024*1024*1024; If using file copies to move audit logs, only audit.<timestamp>.log* formatted files should be copied as audit.log* are still being written to. If audit data is copied using a SQL function, the audit files still require removal using some alternative method on the OS filesystem, for example a third-party tool or a scheduled script. If, after the preceding steps, the transfer is not succeeding, diagnose and repair the problem.
Review OS, or third-party logging application settings to determine whether a warning will be provided when 75 percent of DBMS audit log storage capacity is reached. If no warning will be provided, this is a finding.
Modify OS, or third-party logging application settings to alert appropriate personnel when 75 percent of audit log storage capacity is reached.
Review MySQL Server settings, OS, or third-party logging software settings to determine whether a real-time alert will be sent to the appropriate personnel when auditing fails for any reason. If real-time alerts are not sent upon auditing failure, this is a finding.
Configure the system to provide immediate real-time alerts to appropriate support staff when an audit log failure occurs.
Verify that the MySQL is using Kerberos Authentication. On the server: SELECT PLUGIN_NAME, PLUGIN_STATUS FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_NAME LIKE '%ldap%'; On the client(s) where Kerberos will authenticate, check how long the ticket is cached. First check whether Kerberos authentication works correctly: Use kinit to authenticate to Kerberos, for example. kinit bredon@MYSQL.LOCAL The command authenticates for the Kerberos principal named bredon@MYSQL.LOCAL. Enter the principal's password when the command prompts for it. The KDC returns a TGT that is cached on the client side for use by other Kerberos-aware applications. Use klist to check whether the TGT was obtained correctly. The output should be similar to this: Ticket cache: FILE:/tmp/krb5cc_244306 Default principal: bredon@MYSQL.LOCAL Valid starting Expires Service principal 03/23/2020 08:18:33 03/23/2020 18:18:33 krbtgt/MYSQL.LOCAL@MYSQL.LOCAL If the ticket expiration time exceeds the desired maximum expiration, and Kerberos is enabled, check the LDAP server for the maximum lifetime of the Kerberos service Tickets expiration policy. If the lifetime exceeds the desired expiration time, this is a finding.
Modify system settings to implement the organization-defined limit on the lifetime of cached authenticators. Configure the MySQL server for GSSAPI/Kerberos LDAP authentication plugin to use the GSSAPI/Kerberos authentication method. Following is an example of plugin-related settings the server my.cnf file might contain: [mysqld] plugin-load-add=authentication_ldap_sasl.so authentication_ldap_sasl_auth_method_name="GSSAPI" authentication_ldap_sasl_server_host=198.51.100.10 authentication_ldap_sasl_server_port=389 authentication_ldap_sasl_bind_root_dn="cn=admin,cn=users,dc=MYSQL,dc=LOCAL" authentication_ldap_sasl_bind_root_pwd="password" authentication_ldap_sasl_bind_base_dn="cn=users,dc=MYSQL,dc=LOCAL" authentication_ldap_sasl_user_search_attr="sAMAccountName" Create account(s) using Kerberos Authentication. For example: CREATE USER 'bredon@MYSQL.LOCAL' IDENTIFIED WITH authentication_ldap_sasl BY '#krb_grp=proxied_krb_user'; CREATE USER 'proxied_krb_user' IDENTIFIED WITH mysql_no_login; GRANT ALL ON krb_user_db.* TO 'proxied_krb_user'; GRANT PROXY ON 'proxied_krb_user' TO 'bredon@MYSQL.LOCAL’;
Determine all situations where a user must reauthenticate. Check if the mechanisms that handle such situations use the following SQL: To make a single user reauthenticate, an existing connection must be present: To search for a specific user: SELECT * FROM information_schema.PROCESSLIST where user ='<name> and host like '%'; To review all connections: SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST; Note the ID(s) (processlist_id) of the connection(s) for the user that must reauthenticate. To make a user reauthenticate, run the following for each ID returned above (as they can have multiple connections): KILL CONNECTION processslist_id; If the provided SQL does not force reauthentication, this is a finding.
Modify and/or configure MySQL and related applications and tools so that users are always required to reauthenticate when changing role or escalating privileges. To make a single user reauthenticate, the following must be present: KILL CONNECTION processslist_id;
Use the following query to discover database object access rights: Users with DDL rights on database objects At Instance Level SELECT * FROM `mysql`.`user` WHERE (`mysql`.`user`.`user` not like 'mysql.%') AND ( `user`.`Create_priv` = 'Y' OR `user`.`Drop_priv` = 'Y' OR `user`.`Grant_priv` = 'Y' OR `user`.`References_priv` = 'Y' OR `user`.`Index_priv` = 'Y' OR `user`.`Alter_priv` = 'Y' OR `user`.`Super_priv` = 'Y' OR `user`.`Execute_priv` = 'Y' OR `user`.`Create_view_priv` = 'Y' OR `user`.`Create_routine_priv` = 'Y' OR `user`.`Alter_routine_priv` = 'Y' OR `user`.`Create_user_priv` = 'Y' OR `user`.`Event_priv` = 'Y' OR `user`.`Trigger_priv` = 'Y' OR `user`.`Create_role_priv` = 'Y' OR `user`.`Drop_role_priv` = 'Y') ; At DB/Schema Level - Users with DDL rights on database objects Ensure only administrative users are returned in the result set. SELECT * FROM mysql.db where (`db`.`Grant_priv` = 'Y' OR `db`.`References_priv`= 'Y' OR `db`.`Index_priv`= 'Y' OR `db`.`Alter_priv`= 'Y' OR `db`.`Create_tmp_table_priv`= 'Y' OR `db`.`Lock_tables_priv`= 'Y' OR `db`.`Create_view_priv`= 'Y' OR `db`.`Show_view_priv`= 'Y' OR `db`.`Create_routine_priv`= 'Y' OR `db`.`Alter_routine_priv`= 'Y' OR `db`.`Execute_priv`= 'Y' OR `db`.`Event_priv`= 'Y' OR `db`.`Trigger_priv`) and user not like 'mysql.%'; Ensure only administrative users are returned in the result set. Use the following query to discover database users who have been delegated the right to grant permissions to other users: Execute the following SQL statements to audit this setting: SELECT `USER_PRIVILEGES`.`GRANTEE`, `USER_PRIVILEGES`.`TABLE_CATALOG`, `USER_PRIVILEGES`.`PRIVILEGE_TYPE`, `USER_PRIVILEGES`.`IS_GRANTABLE` FROM `information_schema`.`USER_PRIVILEGES` where `USER_PRIVILEGES`.`IS_GRANTABLE`='YES'; Ensure only administrative users are returned in the result set. If any of these rights are not documented and authorized, this is a finding.
To correct object ownership: To revoke any unauthorized permissions: REVOKE priv_type [(column_list)] [, priv_type [(column_list)]] ... ON [object_type] priv_level FROM user_or_role [, user_or_role] ... REVOKE ALL [PRIVILEGES], GRANT OPTION FROM user_or_role [, user_or_role] ... REVOKE PROXY ON user_or_role FROM user_or_role [, user_or_role] ... REVOKE role [, role ] ... FROM user_or_role [, user_or_role ] ...
Review the server documentation to obtain a listing of accounts used for executing external processes. Execute the following query to obtain a listing of accounts currently configured for use by external processes. SHOW PROCEDURE STATUS where security_type <> 'INVOKER'; SHOW FUNCTION STATUS where security_type <> 'INVOKER'; If DEFINER accounts are returned that are not documented and authorized, this is a finding. If elevation of MySQL privileges using DEFINER is documented, but not implemented as described in the documentation, this is a finding. If the privilege-elevation logic can be invoked in ways other than intended, or in contexts other than intended, or by subjects/principals other than intended, this is a finding.
Remove any procedures that are not authorized. Drop the procedure or function using DROP PROCEDURE <proc_name>; DROP FUNCTION <function_name>;
Review the server documentation to obtain a listing of accounts used for executing external processes. Execute the following query to obtain a listing of accounts currently configured for use by external processes. SHOW PROCEDURE STATUS where security_type <> 'INVOKER'; SHOW FUNCTION STATUS where security_type <> 'INVOKER'; If DEFINER accounts are returned that are not documented and authorized, this is a finding. If elevation of MySQL privileges using DEFINER is documented, but not implemented as described in the documentation, this is a finding. If the privilege-elevation logic can be invoked in ways other than intended, or in contexts other than intended, or by subjects/principals other than intended, this is a finding.
Remove any procedures that are not authorized. Drop the procedure or function using DROP PROCEDURE <proc_name>; DROP FUNCTION <function_name>;
If security labeling is not required, this is not a finding. For data that have been labeled with a column indicating data is classified read-only views can be created and secured via access privileges such that a user can only view the data that have a specific tag or tags (e.g., user [x] can only view records that are labeled with the tag of classified). If security labeling requirements have been specified, but neither a third-party solution nor a MySQL Views and Stored Procedures are used to implement row level security solution is implemented that reliably maintains labels on information in storage, this is a finding.
Deploy MySQL Views and Stored Procedures or a third-party software, or add custom data structures, data elements, and application code, to provide reliable security labeling of information in storage.
If security labeling is not required, this is not a finding. If security labeling requirements have been specified, check for a MySQL solution using views and Stored Procedures to implement a row-level security solution that reliably maintains labels on information in storage. For data that have been labeled with a column indicating data is classified read-only, views can be created and secured via access privileges such that a user should only be able to view the data that have a specific tag or tags (e.g., user [x] can only view records that are labeled with the tag of classified). If a MySQL solution through the use of views and stored procedures or a third party solution does not exist, this is a finding.
Deploy MySQL Views and Stored Procedures or a third-party software, or add custom data structures, data elements and application code, to provide reliable security labeling of information in storage.
If security labeling is not required, this is not a finding. If security labeling requirements have been specified, check for a MySQL solution using views and Stored Procedures to implement a row-level security solution that reliably maintains labels on information in storage. For data that have been labeled with a column indicating data is classified read-only, views can be created and secured via access privileges such that a user can only view the data that have a specific tag or tags (e.g., user [x] can only view records that are labeled with the tag of classified). If a MySQL solution through the use of views and stored procedures or a third party solution does not exist, this is a finding.
Deploy MySQL Views and Stored Procedures or a third-party software, or add custom data structures, data elements and application code, to provide reliable security labeling of information in storage.
Review system documentation to obtain the organization's definition of circumstances requiring automatic session termination. If the documentation explicitly states that such termination is not required or is prohibited, this is not a finding. Determine the situations where a user must reauthenticate. Check if the mechanisms that handle such situations use the following SQL: To make a single user reauthenticate, an existing connection must be present: To search for a specific user: SELECT * FROM information_schema.PROCESSLIST where user ='<name> and host like '%'; To review all connections: SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST; Note the ID(s) (processlist_id) of the connection(s) for the user that must reauthenticate. To make a user reauthenticate, run the following for each ID returned above (as they can have multiple connections). KILL CONNECTION processslist_id; If the provided SQL does not force reauthentication, this is a finding.
Modify and/or configure MySQL and related applications and tools so that users are always required to reauthenticate when changing role or escalating privileges. To make a single user reauthenticate, the following must be present: KILL CONNECTION processslist_id;
If the data owner does not have a strict requirement for ensuring data integrity and confidentiality is maintained at every step of the data transfer and handling process, this is not a finding. Run the following: select @@require_secure_transport; The value should be 1 (ON) versus 0 (OFF), if the value is 0 (OFF), this is a finding.
Turn on require_secure_transport. In this mode the server permits only TCP/IP connections encrypted using TLS/SSL, or connections that use a socket file (on UNIX) or shared memory (on Windows). The server rejects nonsecure connection attempts, which fail with an ER_SECURE_TRANSPORT_REQUIRED error. set persist require_secure_transport=ON;
Detailed information on the NIST Cryptographic Module Validation Program (CMVP) is available at the following website: http://csrc.nist.gov/groups/STM/cmvp/index.html. Review system documentation to determine whether cryptography for classified or sensitive information is required by the information owner. If the system documentation does not specify the type of information hosted on MySQL: classified, sensitive, and/or unclassified, this is a finding. If classified or sensitive information does not exist within MySQL Server, this is not a finding. Verify that the operating system provides the OpenSSL FIPS Object Module, and is configured to require the use of OpenSSL of FIPS compliant algorithms, available at MySQL runtime. If the Security Setting for FIPS mode option is "Disabled" on the server's OS, this is a finding. If cryptography is being used by MySQL, verify that the cryptography is NIST FIPS certified by running the following SQL query: Determine if MySQL is running in FIPS mode. select @@ssl_fips_mode; If ssl_fips_mode is not "ON" or "STRICT", this is a finding. View the versions of TLS, then review the cipher suites in use for the versions returned by statement: SELECT VARIABLE_NAME, VARIABLE_VALUE FROM performance_schema.global_variables WHERE VARIABLE_NAME = 'tls_version'; If the results include less than version TLS 1.2, for example TLS 1.0 or 1.1, this is a finding. If the results include TLS 1.2 view the supported ciphers on the MySQL Server, run select * from performance_schema.global_status where variable_name= 'Ssl_cipher_list'; If the results include TLS 1.3 view the supported ciphers on the MySQL Server, run SELECT VARIABLE_NAME, VARIABLE_VALUE FROM performance_schema.global_variables WHERE VARIABLE_NAME = 'tls_ciphersuites'; If any results list show an uncertified NIST FIPS 140-2 algorithm type, this is a finding. Check MySQL certificate PEM file(s) for compliance with DoD requirements by running this command: openssl x509 -in server-cert.pem -text -noout If any PEM file is not in compliance, this is a finding.
Configure cryptographic functions to use NSA-approved cryptography-compliant algorithms. Turn on MySQL FIPS mode. Edit my.cnf [mysqld] ssl_fips_mode=ON or [mysqld] ssl_fips_mode=STRICT To restrict TLS versions: [mysqld] tls_version='TLSv1.2,TLSv1.3' Example to define ciphers for TLSv1.2: [mysqld] ssl_ciphers='ECDHE-ECDSA-AES128-GCM-SHA256:ECDHE-ECDSA-AES256-GCM-SHA384:ECDHE-RSA-AES128-GCM-SHA256:ECDHE-RSA-AES256-GCM-SHA384:DHE-RSA-AES128-GCM-SHA256:DHE-DSS-AES128-GCM-SHA256:DHE-DSS-AES256-GCM-SHA384:DHE-RSA-AES256-GCM-SHA384:ECDHE-ECDSA-CHACHA20-POLY1305:ECDHE-RSA-CHACHA20-POLY1305' If TLSv1.3 is enabled, the "tls_ciphersuites" setting must contain all or a subset of the following ciphers based on certificates being used by server and client. Enabling FIPS mode will limit the OpenSSL library to operate within the FIPS object module. Example to define TLS ciphers for TLSv1.3: [mysqld] tls_ciphersuites='TLS_AES_128_GCM_SHA256:TLS_AES_256_GCM_SHA384:TLS_CHACHA20_POLY1305_SHA256:TLS_AES_128_CCM_SHA256:TLS_AES_128_CCM_8_SHA256' After adding any entries to the my.cnf file, restart mysqld. Create and use DOD-approved certificates for asymmetric keys used by the database.
ALL cryptography is provided via OpenSSL and can be verified in FIPS mode. Run this command: SELECT VARIABLE_NAME, VARIABLE_VALUE FROM performance_schema.global_variables where variable_name = 'ssl_fips_mode'; If the VARIABLE_VALUE does not return "ON" or "STRICT", this is a finding. In general, STRICT imposes more restrictions than ON, but MySQL itself has no FIPS-specific code other than to specify to OpenSSL the FIPS mode value. The exact behavior of FIPS mode for ON or STRICT depends on the OpenSSL version.
Implement NIST FIPS validated cryptographic modules to provision digital signatures. Turn on MySQL FIPS mode and restart mysqld Edit my.cnf [mysqld] ssl_fips_mode=ON or [mysqld] ssl_fips_mode=STRICT In general, STRICT imposes more restrictions than ON, but MySQL itself has no FIPS-specific code other than to specify to OpenSSL the FIPS mode value. The exact behavior of FIPS mode for ON or STRICT depends on the OpenSSL version.
ALL cryptography is provided via OpenSSL and can be verified in FIPS mode. Run this command: SELECT VARIABLE_NAME, VARIABLE_VALUE FROM performance_schema.global_variables where variable_name = 'ssl_fips_mode'; If the VARIABLE_VALUE does not return "ON" or "STRICT", this is a finding. In general, STRICT imposes more restrictions than ON, but MySQL itself has no FIPS-specific code other than to specify to OpenSSL the FIPS mode value. The exact behavior of FIPS mode for ON or STRICT depends on the OpenSSL version.
Implement NIST FIPS validated cryptographic modules to provision digital signatures. Turn on MySQL FIPS mode and restart mysqld Edit my.cnf [mysqld] ssl_fips_mode=ON or [mysqld] ssl_fips_mode=STRICT In general, STRICT imposes more restrictions than ON, but MySQL itself has no FIPS-specific code other than to specify to OpenSSL the FIPS mode value. The exact behavior of FIPS mode for ON or STRICT depends on the OpenSSL version.
ALL cryptography is provided via OpenSSL and can be verified in FIPS mode. Run this command: SELECT VARIABLE_NAME, VARIABLE_VALUE FROM performance_schema.global_variables where variable_name = 'ssl_fips_mode'; If the VARIABLE_VALUE does not return "ON" or "STRICT", this is a finding. In general, STRICT imposes more restrictions than ON, but MySQL itself has no FIPS-specific code other than to specify to OpenSSL the FIPS mode value. The exact behavior of FIPS mode for ON or STRICT depends on the OpenSSL version.
Implement NIST FIPS validated cryptographic modules to provision digital signatures. Turn on MySQL FIPS mode and restart mysqld Edit my.cnf [mysqld] ssl_fips_mode=ON or [mysqld] ssl_fips_mode=STRICT In general, STRICT imposes more restrictions than ON, but MySQL itself has no FIPS-specific code other than to specify to OpenSSL the FIPS mode value. The exact behavior of FIPS mode for ON or STRICT depends on the OpenSSL version.
To run MySQL in SSL mode, obtain a valid certificate signed by a single certificate authority. Before starting the MySQL database in SSL mode, verify the certificate used is issued by a valid DoD certificate authority. Run this command: openssl x509 -in <path_to_certificate_pem_file> -text | grep -i "issuer" If there is any issuer present in the certificate that is not a DoD-approved certificate authority, this is a finding.
Remove any certificate that was not issued by a valid DoD certificate authority. Contact the organization's certificate issuer and request a new certificate that is issued by a valid DoD certificate authorities.
Review the system documentation to determine whether the organization has defined the information at rest that is to be protected from modification, which must include, at a minimum, PII and classified information. If no information is identified as requiring such protection, this is not a finding. Review the configuration of the MySQL 8.0 Database Server, operating system/file system, and additional software as relevant. If any of the information defined as requiring cryptographic protection from modification is not encrypted in a manner that provides the required level of protection, this is a finding. To check for data encryption at rest settings in MySQL: SELECT VARIABLE_NAME, VARIABLE_VALUE FROM performance_schema.global_variables where variable_name = 'audit_log_encryption'; If the value for audit_log_encryption is not AES, this is a finding. SELECT VARIABLE_NAME, VARIABLE_VALUE FROM performance_schema.global_variables where variable_name = 'binlog_encryption'; If the value for binlog_encryption is not "ON", this is a finding. SELECT VARIABLE_NAME, VARIABLE_VALUE FROM performance_schema.global_variables where variable_name = 'innodb_redo_log_encrypt'; If the value for binlog_innodb_redo_log_encrypt is not "ON", this is a finding. SELECT VARIABLE_NAME, VARIABLE_VALUE FROM performance_schema.global_variables where variable_name = 'innodb_undo_log_encrypt'; If the value for innodb_undo_log_encrypt is not "ON", this is a finding. SELECT VARIABLE_NAME, VARIABLE_VALUE FROM performance_schema.global_variables where variable_name = 'table_encryption_privilege_check'; If the value for table_encryption_privilege_check is not "ON", this is a finding. SELECT `INNODB_TABLESPACES`.`NAME`, `INNODB_TABLESPACES`.`ENCRYPTION` FROM `information_schema`.`INNODB_TABLESPACES`; If tables or tablespaces are not encrypted and the value is not "Y", this is a finding.
Configure the MySQL Database Server 8.0, operating system/file system, and additional software as relevant, to provide the required level of cryptographic protection. Enable the MySQL Key Ring for securely managing encryption keys with KMIP or other supported protocols. Change TABLESPACES, TABLES to put in place encryption. ALTER TABLESPACE <tablespacename> ENCRYPTION = 'Y'; ALTER TABLE <tablespacename> ENCRYPTION = 'Y'; Require all new tables and tablespaces to be encrypted. set persist table_encryption_privilege_check=ON; Require AUDIT LOG encryption sudo vi /etc/my.cnf [mysqld] audit-log=FORCE_PLUS_PERMANENT audit-log-format=JSON audit-log-encryption=AES Require BINLOG encryption set persist binlog_encryption=ON; Require REDO and UNDO log encryption set persist innodb_redo_log_encrypt=ON; set persist innodb_undo_log_encrypt=ON; Turn off insecure logging (use the audit log above to track activity). SET PERSIST general_log = 'OFF';
Review the system documentation to determine whether the organization has defined the information at rest that is to be protected from modification, which must include, at a minimum, PII and classified information. If no information is identified as requiring such protection, this is not a finding. Review the configuration of the MySQL 8.0 Database Server, operating system/file system, and additional software as relevant. If any of the information defined as requiring cryptographic protection from modification is not encrypted in a manner that provides the required level of protection, this is a finding. To check for data encryption at rest settings in MySQL: SELECT VARIABLE_NAME, VARIABLE_VALUE FROM performance_schema.global_variables where variable_name = 'audit_log_encryption'; If the value for audit_log_encryption is not "AES", this is a finding. SELECT VARIABLE_NAME, VARIABLE_VALUE FROM performance_schema.global_variables where variable_name = 'binlog_encryption'; If the value for binlog_encryption is not "ON", this is a finding. SELECT VARIABLE_NAME, VARIABLE_VALUE FROM performance_schema.global_variables where variable_name = 'innodb_redo_log_encrypt'; If the value for binlog_innodb_redo_log_encrypt is not "ON", this is a finding. SELECT VARIABLE_NAME, VARIABLE_VALUE FROM performance_schema.global_variables where variable_name = 'innodb_undo_log_encrypt'; If the value for innodb_undo_log_encrypt is not "ON", this is a finding. SELECT VARIABLE_NAME, VARIABLE_VALUE FROM performance_schema.global_variables where variable_name = 'table_encryption_privilege_check'; If the value for table_encryption_privilege_check is not "ON", this is a finding. SELECT `INNODB_TABLESPACES`.`NAME`, `INNODB_TABLESPACES`.`ENCRYPTION` FROM `information_schema`.`INNODB_TABLESPACES`; If tables or tablespaces are not encrypted and the value is not "Y", this is a finding.
Configure the MySQL Database Server 8.0, operating system/file system, and additional software as relevant, to provide the required level of cryptographic protection. Enable the MySQL Key Ring for securely managing encryption keys with KMIP or other supported protocols. Change TABLESPACES, TABLES to put in place encryption. ALTER TABLESPACE <tablespacename> ENCRYPTION = 'Y'; ALTER TABLE <tablespacename> ENCRYPTION = 'Y'; Require all new tables and tablespaces to be encrypted. set persist table_encryption_privilege_check=ON; Require AUDIT LOG encryption sudo vi /etc/my.cnf [mysqld] audit-log=FORCE_PLUS_PERMANENT audit-log-format=JSON audit-log-encryption=AES Require BINLOG encryption set persist binlog_encryption=ON; Require REDO and UNDO log encryption set persist innodb_redo_log_encrypt=ON; set persist innodb_undo_log_encrypt=ON; Turn off insecure logging - (use the audit log above to track activity) SET PERSIST general_log = 'OFF';
To check the version of the installed MySQL, run the following SQL statement: select @@version; The result will show the version, for example: 8.0.22-commercial Obtain evidence that software MRU updates are consistently applied to MySQL Server within the time frame defined for each update. To be considered supported, Oracle must report that the version is supported by security patches to known vulnerability. Review the MySQL Support dates at the following link: https://www.oracle.com/support/lifetime-support/resources.html Review the MySQL Release notes page: https://dev.mysql.com/doc/relnotes/mysql/8.0/en/ If MySQL Enterprise Edition 8.0 is not at the latest version, this is a finding.
Institute and adhere to policies and procedures to ensure that patches are consistently applied to MySQL within the time allowed.
Review the MySQL Server to ensure it behaves in a predictable and documented manner that reflects organizational and system objectives when invalid inputs are received. To determine if table check constraints that have been put in place: SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS; If input validation is required beyond those enforced by the datatype and no constraints exist for data input, this is a finding.
Configure the MySQL Server to behave in a predictable and documented manner that reflects organizational and system objectives when invalid inputs are received. To validate data at the database table level modify tables by adding constraints CHECK constraint is a type of integrity constraint in SQL within the create or alter table statement. [CONSTRAINT [symbol]] CHECK (expr) [[NOT] ENFORCED] For example CREATE TABLE checker (i tinyint, CONSTRAINT i_must_be_between_7_and_12 CHECK (i BETWEEN 7 AND 12 ) ); Adding a constraint to an existing table ALTER TABLE <table_name> ADD [CONSTRAINT [symbol]] CHECK (condition) [[NOT] ENFORCED]
Review the version and release information. To check the version of the installed MySQL, run the following SQL statement: select @@version; The result will show the version. For example: 8.0.22-commercial Access the vendor website or use other means to verify the version is still supported. Oracle lifetime support: https://www.oracle.com/us/assets/lifetime-support-technology-069183.pdf Scroll down to Oracle MySQL Releases (approximately page 28). If the Oracle MySQL version or any of the software components are not supported by the vendor, this is a finding.
Remove or decommission all unsupported software products. Upgrade unsupported DBMS or unsupported components to a supported version of the product.