MySQL Security - From Data Protection to Regulation Compliance

MySQL Security From Data Protection to Regulation Compliance Olivier Dasini
MySQL Cloud Principal Solutions Architect EMEA [email protected] Blogs : www.dasini.net/blog/en : www.dasini.net/blog/fr Linkedin: www.linkedin.com/in/olivier-dasini Twitter : @freshdaz June 20, 2023

2 Copyright © 2023, Oracle and/or its affiliates. All rights
reserved. Me, Myself & I  MySQL Geek  Addicted to MySQL for 15+ years  Playing with databases for 20+ years  MySQL Writer, Blogger and Speaker  Also: DBA, Consultant, Architect, Trainer, ...  MySQL Cloud Principal Solutions Architect EMEA at Oracle  Stay up to date!  Blog: www.dasini.net/blog/en  Linkedin: www.linkedin.com/in/olivier-dasini/  Twitter: @freshdaz Olivier DASINI

Copyright © 2023, Oracle and/or its affiliates. All rights reserved.
Agenda 1. Security Overview 2. Regulations 3. MySQL Security 4. MySQL Enterprise Security 5. Tips & Tricks 3

4 Security is Job #1 4 “Keep the organization safe (cybersecurity/cyber resilience/GDPR compliance/data protection compliance) “ Was #1 – Security – in 2019 Still #1 – Security And on par for spending Increase with Cloud Global Partner Summit - Munich, 2019 Data is the Most Valuable Asset

5 Data Breaches – keep increasing 2021 a record year for data breaches Manufacturing & utilities 48 compromises and a total of 48,294,629 victims. 1,291 breaches in 2021 compared to 1,108 breaches in 2020 Healthcare sector 78 compromises and over 7 million victims. https://www.securitymagazine.com/articles/96667-the-top-data-breaches-of-2021

6 Attack Vectors and Targets for Databases Complexity grows, Risk Grows

7 Database Vulnerabilities  Poor Configurations – Set controls and change default setting  Over Privileged Accounts – Privilege Policies  Weak Access Control – Dedicated Administrative Accounts  Weak Authentication – Strong Password Enforcement  Weak Auditing – Compliance & Audit Policies  Lack of Encryption – Data, Backup, & Network Encryption  Proper Credential & Key Management – Use mysql_config_editor , Key Vaults  Unsecured Backups – Encrypted Backups  No Monitoring – Security Monitoring, Users, Objects  Poorly Coded Applications – Database Firewall

8 Database Malicious Actions  Information Disclosure: Obtain credit card and other personal information – Defense: Encryption – Data and Network, Tighter Access Controls  Denial of Service: Run resource intensive queries – Defense: Resource Usage Limits – Set various limits – Max Connections, Sessions, Timeouts, …  Elevation of Privilege: Retrieve and use administrator credentials – Defense: Stronger authentication, Access Controls, Auditing  Spoofing: Retrieve and use other credentials – Defense: Stronger account and password policies  Tampering: Change data in the database, Delete transaction records – Defense: Tighter Access Controls, Auditing, Monitoring, Backups

9 DBA Responsibilities  Ensure only users who should get access, can get access  Limit what users and applications can do  Limit from where users and applications can access data  Watch what is happening, and when it happened  Make sure to back things up securely  Ensure encryption keys are protected and managed  Minimize attack surface

Regulations

11 Regulatory Compliance  Regulations – GDPR, General Data Protection Regulation: Protection of Personal Data – PCI – DSS: Payment Card Data – HIPAA: Privacy of Health Data – Sarbanes Oxley, GLBA, The USA Patriot Act: – Financial Data, NPI "personally identifiable financial information" – FERPA – Student Data – Data Protection Act (UK): Protection of Personal Data  Requirements – Continuous Monitoring (Users, Schema, Backups, etc) – Data Protection (Encryption, Privilege Management, etc.) – Data Retention (Backups, User Activity, etc.) – Data Auditing (User activity, etc.)

12 EU General Data Protection Regulation (GDPR)  The E.U. General Data Protection Regulation (GDPR) was adopted on 27 April 2016. – Enforceable from 25 May 2018  GDPR is a European Union “EU”-wide framework – Protection of personal data of EU-based individuals – Restrictions to movement of that data  Fines for GDPR violations are – The greater of 20,000,000 Euros or 4% of annual revenue (R150, A83) https://ec.europa.eu/info/law/law-topic/data-protection_en

13 Appropriate Security Controls Data must be processed with controls that provide “Privacy by design, appropriate security & confidentiality“ Recitals of note - R74-78, R81, R83, R87, R90, A5, A24-25, A28, A32, A35)  Exact security controls are not specified in the GDPR – WHAT to do – Not HOW to do it

14 Regulations require these Security Steps  Assess  Locate Risks and Vulnerabilities, Ensure that necessary security controls are  Prevent  Using Cryptography, User Controls, Access Controls, etc  Detect  Still a possibility of a breach – so Audit, Monitor, Alert  Recover  Ensure service is not interrupted as a result of a security incident  Even through the outage of a primary database  Forensics – postmortem – fix vulnerability

MySQL Security

16 Security Improvements (summary)  AES 256 Encryption now the default  Password rotation policies  Password history (not reuse old ones)  Deployment: enable secure unattended install by default – Random password set on install – Remove anonymous accounts – Deployment without test account, schema, demo files  Easier instance initialization and setup: mysqld --initialize  New detection and support for systemd  MySQL Roles – Improving MySQL Access Controls  SSL – Enabled by default – Auto-detection of existing keys and certs – Auto generation of keys and certs when needed – New helper utility: mysql_ssl_rsa_setup – New --require_secure_transport option to prevent insecure communications – Added SSL support to binary log clients  Extended Proxy User Support – Added Built-in Authentication Plugins support for Proxy Users – Allows multiple users to share a single set of managed privileges  File, Tablespace & Log Encryption – AES 256 logs & tablespaces encryption at rest https://dev.mysql.com/doc/mysql-security-excerpt/8.0/en/

17 MySQL Pluggable Authentication  When a client connects to the MySQL server, the server uses the user name provided by the client and the client host to select the appropriate account row from the mysql.user system table  Pluggable authentication enables these important capabilities: – Choice of authentication methods • Pluggable authentication makes it easy for DBAs to choose and change the authentication method used for individual MySQL accounts – External authentication • Pluggable authentication makes it possible for clients to connect to the MySQL server with credentials appropriate for authentication methods that store credentials elsewhere than in the mysql.user system table • For example, plugins can be created to use external authentication methods such as PAM, Windows login IDs, LDAP, or Kerberos – Proxy users – If a user is permitted to connect, an authentication plugin can return to the server a user name different from the name of the connecting user, to indicate that the connecting user is a proxy for another user (the proxied user) – While the connection lasts, the proxy user is treated, for purposes of access control, as having the privileges of the proxied user. In effect, one user impersonates another https://dev.mysql.com/doc/refman/8.0/en/pluggable-authentication.html

18 Available Authentication Plugins 1/2  MySQL 8.0 provides these authentication plugins: – A plugin that performs native authentication; that is, authentication based on the password hashing method in use from before the introduction of pluggable authentication in MySQL. The mysql_native_password plugin implements authentication based on this native password hashing method – Plugins that perform authentication using SHA-256 password hashing. This is stronger encryption than that available with native authentication. caching_sha2_password default in MySQL 8.0 – A client-side plugin that sends the password to the server without hashing or encryption. This plugin is used in conjunction with server-side plugins that require access to the password exactly as provided by the client user – A plugin that performs external authentication using PAM (Pluggable Authentication Modules), enabling MySQL Server to use PAM to authenticate MySQL users – A plugin that performs external authentication on Windows (Active Directory), enabling MySQL Server to use native Windows services to authenticate client connections. Users who have logged in to Windows can connect from MySQL client programs to the server based on the information in their environment without specifying an additional password. This plugin supports proxy users as well https://dev.mysql.com/doc/refman/8.0/en/pluggable-authentication.html

19 Available Authentication Plugins 2/2  MySQL 8.0 provides these authentication plugins: – Plugins that perform authentication using LDAP (Lightweight Directory Access Protocol) to authenticate MySQL users by accessing directory services such as X.500. These plugins support proxy users as well – A plugin that performs authentication using Kerberos to authenticate MySQL users that correspond to Kerberos principals – A plugin that prevents all client connections to any account that uses it. Use cases for this plugin include proxied accounts that should never permit direct login but are accessed only through proxy accounts and accounts that must be able to execute stored programs and views with elevated privileges without exposing those privileges to ordinary users – A plugin that authenticates clients that connect from the local host through the Unix socket file – A plugin that authenticates users to MySQL Server using FIDO authentication • Enables authentication to MySQL Server using devices such as smart cards, security keys, and biometric readers – A test plugin that checks account credentials and logs success or failure to the server error log. This plugin is intended for testing and development purposes, and as an example of how to write an authentication plugin • If you are interested in writing your own authentication plugins https://dev.mysql.com/doc/refman/8.0/en/pluggable-authentication.html

20 Enable Socket Peer-Credential Authentication  The server-side auth_socket authentication plugin authenticates clients that connect to the MySQL server from the local host through the Unix socket file  auth_socket authentication is well suited to server administration user accounts for which access must be tightly restricted  The auth_socket plugin checks whether the socket user name matches the MySQL user name specified by the client program to the server – If the names do not match, the plugin also checks whether the socket user name matches the name specified in the authentication_string column of the mysql.user table row – If a match is found, the plugin permits the connection https://dev.mysql.com/doc/refman/8.0/en/socket-pluggable-authentication.html INSTALL PLUGIN auth_socket SONAME 'auth_socket.so'; CREATE USER daz@localhost IDENTIFIED WITH auth_socket; mysqlsh daz@localhost -S /tmp/mysql.sock --sql mysql -u daz -S /tmp/mysql.sock

21 Multifactor Authentication  MySQL 8.0.27+ includes support for multifactor authentication (MFA) – This capability includes forms of MFA that require up to three authentication values (1FA, 2FA, 3FA)  Multifactor authentication (MFA) is the use of multiple authentication values (or “factors”) during the authentication process  MFA provides greater security than one-factor authentication (1FA), which uses only one authentication method such as a password  MFA enables additional authentication methods, such as authentication using multiple passwords, or authentication using devices like smart cards, security keys, and biometric readers CREATE USER 'alice'@'localhost' IDENTIFIED WITH caching_sha2_password BY 'sha2_password' AND IDENTIFIED WITH authentication_ldap_sasl AS 'uid=u1_ldap,ou=People,dc=example,dc=com'; $ mysql --user=alice --password1 --password2 https://dev.mysql.com/doc/mysql-security-excerpt/8.0/en/multifactor-authentication.html

22 MySQL Password Policies Provides DBAs built-in password management  Random Password Generation – Generating random passwords for user accounts – https://dasini.net/blog/2020/04/15/mysql-security-random-password-generation/  Password Expiration Policy – Enables DBAs to require that users reset their password – https://dasini.net/blog/2020/04/21/mysql-security-password-expiration-policy/  Password Reuse Policy – Allows DBAs to determine the number of unique passwords a user must use before they can use an old password again – https://dasini.net/blog/2020/05/12/mysql-security-failed-login-tracking-and-temporary-account-locking/  Password Verification-Required Policy – Authorizes users to change their password only if they could provide the current password – https://dasini.net/blog/2020/05/05/mysql-security-password-verification-required-policy/  Failed-Login Tracking and Temporary Account Locking – Configures user accounts such that too many consecutive login failures cause temporary account locking – https://dasini.net/blog/2020/05/12/mysql-security-failed-login-tracking-and-temporary-account-locking/  Dual Password Support – Possibility to seamlessly perform credential changes without downtime – https://dasini.net/blog/2020/05/19/mysql-security-dual-password-support/

23 MySQL Encryption  SSL/TLS Encryption – Between MySQL clients and Server – Replication: Between Primary & Secondary  Data Encryption – AES Encrypt/Decrypt  MySQL Enterprise TDE – Transparent Data Encryption – Secure Key Management – Tablespaces & logs encryption  MySQL Enterprise Encryption – Asymmetric Encrypt/Decrypt – Generate Public Key and Private Keys – Derive Session Keys – Digital Signatures  MySQL Enterprise Backup – AES Encrypt/Decrypt

24 SSL / TLS  Encrypted connections – Between MySQL Client and Server – Replication: Between Primary & Secondary  MySQL enables encryption on a per-connection basis – Identity verification using the X509 standard  Specify the appropriate SSL certificate and key files  Will work with trusted CAs (Certificate Authorities)  Supports CRLs – Certificate Revocation Lists

25 SSL Related Improvements  Support latest TLS 1.3 – MySQL 8.0 with OpenSSL 1.1.1 +  SSL options dynamic – Now able to update the certificates without restarting the running server. – This work is based on a Facebook contribution – Thanks https://dev.mysql.com/doc/refman/8.0/en/encrypted-connections.html

26 FIPS Support  MySQL 8.0 supports FIPS (Federal Information Processing Standards) mode, if compiled using OpenSSL 1.0.2, and a FIPS-enabled OpenSSL library and FIPS Object Module are available at runtime  FIPS mode imposes conditions on cryptographic operations such as restrictions on acceptable encryption algorithms or requirements for longer key lengths  SSL Support FIPS mode operations – FIPS 140-2 to ensure a high degree of security, assurance and dependability – Needed for US Government Regulatory Compliance – https://dev.mysql.com/doc/refman/8.0/en/fips-mode.html https://dev.mysql.com/doc/refman/8.0/en/fips-mode.html

MySQL Enterprise Security

28 MySQL Security Overview Authentication Authorization Encryption Firewall MySQL Security Auditing Masking/De-Identification https://www.mysql.com/products/enterprise/

29 MySQL Enterprise Edition - Security  MySQL Enterprise Authentication – External Authentication Modules • Microsoft AD, Linux PAMs, LDAP, Kerberos • MySQL Enterprise Encryption – Public/Private Key Cryptography – Asymmetric Encryption – Digital Signatures, Data Validation – User Activity Auditing, Regulatory Compliance  MySQL Enterprise Firewall – Block SQL Injection Attacks – Intrusion Detection  MySQL Enterprise Audit – User Activity Auditing, Regulatory Compliance  MySQL Enterprise Thread Pool – Attack Hardening  MySQL Enterprise Monitor – Changes in Database Configurations, Users Permissions, Database Schema, Passwords  MySQL Enterprise Backup – Securing Backups, AES 256 encryption  MySQL Enterprise TDE – Data-at-Rest Encryption – Key Management/Security – KMIP, Hashicorp, OCI Vault  MySQL Enterprise Data Masking – Data Masking and Obfuscation – Formatted Data Randomization – Pseudonymization, Data Blacklists – Random Data generation

30 MySQL Enterprise Monitor  Enforce MySQL Security Best Practices – Identify vulnerabilities  Easily ensure all your MySQL assets are hardened and secure – User & Password Monitoring – Schema Change Monitoring  Manage MySQL Enterprise Firewall – Protect MySQL assets from SQL injection attacks and other common threats  Manage MySQL Enterprise Audit – Ensure regulatory compliance &Know what happened when things go wrong  Change monitoring and tracking – Configuration Management – Configuration Tuning Advice  Backup monitoring and policy enforcement  Centralized user management A DBA’s assistant : Notify problems and tuning opportunities

31 MySQL Enterprise Backup  Online, non-locking backup and recovery – Complete MySQL instance backup (data and config) – Full & Partial backup and restore  Direct Cloud storage backups (OCI, S3, etc.)  Incremental backups & Point-in-time recovery  Advanced compressed and Strong encryption: AES 256  Backup to tape (SBT)  Backup validation  Optimistic backups  Cross-Platform (Windows, Linux, Unix)  Certified with Oracle Secure Backup, NetBackup, Tivoli, ... Ensure availability of end-user data

32 MySQL Enterprise Transparent Data Encryption (TDE) Protects against Attacks on Database Files MySQL Database Encrypted Tablespace Files Undo/Redo logs Shared Tablespaces Binary & Relay logs Protected Key Hacker / Dishonest OS User Accesses Files Directly Information Access Blocked By Encryption Keyring plugin - used to retrieve keys from Key Stores over Standardized KMIP protocol https://dasini.net/blog/2018/04/10/mysql-security-mysql-enterprise-transparent-data-encryption

33 MySQL Enterprise Transparent Data Encryption (TDE)  Data at Rest Encryption – [System | General | Data Dictionary] Tablespaces, Undo/Redo & Binary/Relay logs, Storage, OS File system – Policy to enforce table encryption – Strong Encryption – AES 256  Transparent to applications and users – No application code, schema or data type changes  Transparent to DBAs – Keys are hidden from DBAs, no configuration changes  Requires Key Management – Protection, rotation, storage, recovery Protects against Attacks on Database Files https://dasini.net/blog/2018/04/10/mysql-security-mysql-enterprise-transparent-data-encryption

34 MySQL Enterprise Transparent Data Encryption (TDE)  KMIP – Key Management Interoperability Protocol (Oasis Standard) – Enables communication of cryptographic keys between a key management server and its clients – The plugin uses the KMIP 1.1 protocol to communicate securely as a client of a KMIP back end – Keyring material is generated exclusively by the back end  The plugin works with these KMIP-compatible products: – Oracle Key Vault (OKV) – Gemalto SafeNet KeySecure Appliance – Townsend Alliance Key Manager – Entrust KeyControl – https://dev.mysql.com/doc/refman/8.0/en/keyring-okv-plugin.html KMIP Compliant - Enables customers to meet regulatory requirements https://dasini.net/blog/2018/04/10/mysql-security-mysql-enterprise-transparent-data-encryption

35 MySQL Enterprise Transparent Data Encryption (TDE)  Oracle Cloud Infrastructure Vault – https://dev.mysql.com/doc/refman/8.0/en/keyring-oci-plugin.html  HashiCorp Vault – https://dev.mysql.com/doc/refman/8.0/en/keyring-hashicorp-plugin.html  Amazon Web Services Key Management Service (AWS KMS) – https://dev.mysql.com/doc/refman/8.0/en/keyring-aws-plugin.html  Additional Options – Key Ring File – Encrypted Key Ring File Product specific - Enables customers to meet regulatory requirements https://dasini.net/blog/2018/04/10/mysql-security-mysql-enterprise-transparent-data-encryption

36 At Rest Encryption Covers  InnoDB Tables and Tablespace – File Per Table Tablespace or General (Multi-Table) Tablespace  MySQL System Tablespace – Data Dictionary Tables  Binlog Encryption  MySQL Enteprise Audit Logs  MySQL Enterprise Backup Files  Note: DBAs can optionally force Table Encryption – i.e. Users can only create encrypted tables

37 Database Auditing  “Trust but verify" approach to security – Ensure users with strong privileges don’t misuse those privileges  Business Audit – Data Validity – Here’s proof my database data is accurate/correct – Prove no tampering to data has occurred  Forensic analysis – as a component of any defense-in-depth strategy – Proactive - Am being / Was hacked – Reactive – How were we hacked, what was changed, taken, etc. Maintaining an audit trail is an essential security best practice

38 MySQL Enterprise Audit Help implement stronger security controls & satisfy regulatory compliance 1. DBA Enables Audit Plugin • Defines Filters and Options • Who, What, Where, When, How Audit File (XML or JSON format) MySQL Enterprise Audit 2. User Connects from a Host • Authenticates • Runs Queries • Alters Tables, etc. 3. DBA Reviews Local Audit Events • MySQL Workbench EE • Or other JSON/XML viewer 4. IT Sec Archives to Audit Vault • Globally Assesses Audit Trail https://dasini.net/blog/2018/04/04/mysql-security-mysql-enterprise-audit

39 MySQL Enterprise Audit Help implement stronger security controls & satisfy regulatory compliance  Out-of-the-box logging of connections, logins, and query  Auditing for Security & Compliance – GDPR, PCI DSS, Sarbanes-Oxley, HIPAA, FIPS, ...  You see who has done what - connections, logins, and queries (works with firewall)  Secure SQL Access to audit events  Simple to fine grained policies for filtering, and log rotation  Dynamically enabled, disabled: no server restart  Send data to a remote server / audit data vault – Oracle Audit Vault, Splunk, etc. – Custom Settings • XML and JSON audit stream formatting options • Compression & Encryption • Remote Read Only SQL statement access Adds regulatory compliance to MySQL applications (GDPR, HIPAA, PCI DSS, etc.) Adds regulatory compliance to MySQL applications (GDPR, HIPAA, PCI DSS, etc.) https://dasini.net/blog/2018/04/04/mysql-security-mysql-enterprise-audit

40 MySQL Enterprise Audit Audit Filtering  Starting with MySQL Enterprise 5.7.13  Allows DBAs to “custom” design audit process – Use very fine grained rules • Reduce audit log file size • Reduce File System IO and Storage / Increases performance (less items logged) • Increases audit log post processing efficiency – less data to process for immediate answers • Defined using JSON – Coarse grained rules • When you need to watch everything • Obsolete. Recommended is to use new audit log filtering https://dasini.net/blog/2018/04/04/mysql-security-mysql-enterprise-audit

41 MySQL Enterprise Firewall Guard against cyber security threats  Block SQL Injection Attacks – Allow: SQL Statements that match Allowlist – Block: SQL statements that are not on Allowlist  Intrusion Detection System – Detect: SQL statements that are not on Allowlist • SQL Statements execute and alert administrators Select * from employee where id=22 Select * from employee where id=22 or 1=1 or 1=1 Block ✖ Allow ✔ Allow List Applications Detect & Alert Intrusion Detection Rule https://dasini.net/blog/2018/04/16/mysql-security-mysql-enterprise-firewall

42 MySQL Enterprise Firewall Guard against cyber security threats What happens when SQL is blocked in Protect Mode ?  The client application gets an ERROR mysql> SELECT * FROM customer WHERE customer_id = 1 OR TRUE; ERROR 1045 (28000): Statement was blocked by Firewall mysql> SHOW DATABASES; ERROR 1045 (28000): Statement was blocked by Firewall mysql> TRUNCATE TABLE mysql.user; ERROR 1045 (28000): Statement was blocked by Firewall  Reported to the Error Log  Increment Counters https://dasini.net/blog/2018/04/16/mysql-security-mysql-enterprise-firewall

43 MySQL Enterprise Firewall Guard against cyber security threats  Real Time Protection – Queries checked against Allow List and Firewall Rules  Blocks SQL Injection Attacks – Block Out of Policy Transactions  Intrusion Detection – Detect and Alert on Out of Policy Transactions  Automated Allow List – Learning mode for creation of approved list of SQL command patterns on a per user basis  Custom Rules – using Powerful JSON definitions  Transparent – No changes to application required MySQL Enterprise Firewall monitoring using MySQL Enterprise Monitor https://dasini.net/blog/2018/04/16/mysql-security-mysql-enterprise-firewall

44 MySQL Enterprise Firewall Guard against cyber security threats  Combined Firewall/Audit Rules – Create more general allow/deny firewall rules using JSON syntax – using abort=on Example - block execution of specific  SQL statements (insert, update, delete)  For a specific table (finances.bank_account) Test rules  By writing to audit log  If data as expected change to firewall – add “abort” https://dasini.net/blog/2018/04/16/mysql-security-mysql-enterprise-firewall

45 MySQL Enterprise Data Masking & De-Identification Help protect sensitive data from unauthorized uses https://dasini.net/blog/2019/03/19/mysql-security-mysql-enterprise-data-masking-and-de-identification

46 MySQL Enterprise Masking & De-Identification  Data Masking – String masking – Dictionary based replacement – Specific masking • SSN • Payment card : Strict/Relaxed Help protect sensitive data from unauthorized uses  Random Data Generator – Random number within a range – Email – Payment card (Luhn check compliant) – SSN – Dictionary based generation https://dasini.net/blog/2019/03/19/mysql-security-mysql-enterprise-data-masking-and-de-identification

47 MySQL Enterprise Authentication  Integrate with Centralized Authentication Infrastructure – Centralized Account Management – Password Policy Management – Groups & Roles  PAM (Pluggable Authentication Modules) – Enables a system to use a standard interface to access various kinds of authentication methods, such as Unix passwords or an LDAP directory  LDAP (Lightweight Directory Access Protocol) – Access native LDAP service for authentication. – Supports user name and password, SASL, and GSSAPI/Kerberos authentication methods to LDAP services  Windows – Use native Windows services to authenticate client connections – Users who have logged in to Windows can connect from MySQL client programs to the server based on the information in their environment without specifying an additional password  Kerberos (MySQL 8.0.26+) – Use Native Kerberos to authenticate MySQL users using there Kerberos Principals  Others: FIDO (MySQL 8.0.27+), … Ready to use external authentication modules to easily integrate existing security infrastructures Integrates MySQL with existing security infrastructures Integrates MySQL with existing security infrastructures https://dev.mysql.com/doc/refman/8.0/en/authentication-plugins.html

48 MySQL InnoDB Cluster “High Availability becomes a core first class feature of MySQL!” https://dasini.net/blog/2019/09/03/tutoriel-deployer-mysql-8-0-innodb-cluster-09-2019 Integrated, native, HA solution for your databases

49 MySQL InnoDB ClusterSet  High Availability (Failure within a Region) – RPO=0 – RTO=seconds (automatic failover)  Disaster Recovery (Region Failure) – RPO !=0 – RTO = mintues or more (manual failover) – No write performance impact  Features – Easy to use! – Familiar interface and usability – mysqlsh, CLONE,… – Add/remove nodes/clusters online – Router integration, no need to reconfigure application if the topology changes Disaster Recovery solution for InnoDB Cluster deployments One or more REPLICA MySQL InnoDB Clusters attached to a PRIMARY MySQL InnoDB Cluster

50 MySQL InnoDB ReplicaSet • Easy to use!  Asynchronous Replication Architecture – (manual) Switchover & Failover – (asynchronous) Read Scaleout – Simple Replication architecture  MySQL Shell Conﬁguring, Adding, Removing members  MySQL Router to route application traffic  InnoDB CLONE to automatically provision members, fully integrated in InnoDB A quick & easy way to get MySQL Replication & MySQL Router up and running

51

Tips & Tricks

53 Much can be done using SQL and … 1. DBA does not need to SSH/Login to the OS where mysql is running This is common. 2. All DBA actions must be audited MySQL Auditing can capture all statements performed by DBAs via SQL. 3. OS Admins don't need to be touching MySQL OS Auditing should show little past the initial installation Commands not exposed 4. DevOps Friendly – Service oriented 5. Great for repeatable assessment and fix automation

54 Multiple roots? SELECT user,host FROM mysql.user WHERE user='root' AND SELECT user,host FROM mysql.user WHERE user='root' AND host<>'localhost'; host<>'localhost'; Multiple root accounts ! Is the host name constrained or is it global – '%' Remove and ”global” host roots. Limit access if remote is necessary

55 Password Policies In Place? SELECT component_urn, 'PASSWORD Policy Component Installed?' SELECT component_urn, 'PASSWORD Policy Component Installed?' as Note, as Note, IF(count(component_urn) > 0, 'YES', 'NO') AS Answer IF(count(component_urn) > 0, 'YES', 'NO') AS Answer FROM mysql.component FROM mysql.component WHERE component_urn='file://component_validate_password' GROUP WHERE component_urn='file://component_validate_password' GROUP BY component_urn; BY component_urn; IS THE COMPONENT INSTALLED?

56 Password Policies SELECT VARIABLE_NAME, VARIABLE_VALUE SELECT VARIABLE_NAME, VARIABLE_VALUE FROM performance_schema.global_variables FROM performance_schema.global_variables WHERE VARIABLE_NAME LIKE 'valid%password%' WHERE VARIABLE_NAME LIKE 'valid%password%' OR VARIABLE_NAME='default_password_lifetime'; OR VARIABLE_NAME='default_password_lifetime';

57 If needed INSTALL COMPONENT 'file://component_validate_password'; Set Password Policies 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; Additionally maybe for password reset set persist password_require_current=YES Note some things can be set per account. ALTER USER 'jeffrey'@'localhost' PASSWORD HISTORY 5 PASSWORD REUSE INTERVAL 365 DAY; ALTER USER 'jeffrey'@'localhost' PASSWORD EXPIRE INTERVAL 90 DAY; Change my password policy

58 MySQL Connection Controls Are the Connection Controls Plugins in place? SELECT PLUGIN_NAME, PLUGIN_STATUS FROM INFORMATION_SCHEMA.PLUGINS WHERE SELECT PLUGIN_NAME, PLUGIN_STATUS FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_NAME LIKE 'connection%'; PLUGIN_NAME LIKE 'connection%'; Check Settings SELECT @@connection_control_failed_connections_threshold, SELECT @@connection_control_failed_connections_threshold, @@connection_control_min_connection_delay, @@connection_control_min_connection_delay, @@connection_control_max_connection_delay, @@connection_control_max_connection_delay, @@connection_control_failed_connections_threshold\G @@connection_control_failed_connections_threshold\G Dealing with Failed Login Attempts related to Brute Force Attacks *************************** 1. row *************************** *************************** 1. row *************************** @@connection_control_failed_connections_threshold: 3 @@connection_control_failed_connections_threshold: 3 @@connection_control_min_connection_delay: 1000 @@connection_control_min_connection_delay: 1000 @@connection_control_max_connection_delay: 2147483647 @@connection_control_max_connection_delay: 2147483647 @@connection_control_failed_connections_threshold: 3 @@connection_control_failed_connections_threshold: 3

59 Installing and Setting Connection Controls INSTALL PLUGIN CONNECTION_CONTROL SONAME 'connection_control.so'; INSTALL PLUGIN CONNECTION_CONTROL SONAME 'connection_control.so'; INSTALL PLUGIN CONNECTION_CONTROL_FAILED_LOGIN_ATTEMPTS SONAME INSTALL PLUGIN CONNECTION_CONTROL_FAILED_LOGIN_ATTEMPTS SONAME 'connection_control.so'; 'connection_control.so'; For example SET PERSIST connection_control_failed_connections_threshold = 4; SET PERSIST connection_control_failed_connections_threshold = 4; SET PERSIST connection_control_min_connection_delay = 1500; SET PERSIST connection_control_min_connection_delay = 1500; https://dev.mysql.com/doc/mysql-security-excerpt/8.0/en/connection-control-installation.html https://mysqlserverteam.com/the-connection_control-plugin-keeping-brute-force-attack-in-check/ Install and Set

60 Use your CA SELECT 'ALL SSL VARIABLES Listing' as NOTE, @@ssl_ca, @@ssl_capath, SELECT 'ALL SSL VARIABLES Listing' as NOTE, @@ssl_ca, @@ssl_capath, @@ssl_cert, @@ssl_cipher, @@ssl_cert, @@ssl_cipher, @@ssl_crl, @@ssl_crlpath, @@ssl_fips_mode,@@ssl_key; @@ssl_crl, @@ssl_crlpath, @@ssl_fips_mode,@@ssl_key; ALTER INSTANCE RELOAD TLS; ALTER INSTANCE RELOAD TLS; MySQL Installers create self signed keys Better if you generate and replace from your Certificate Authority Note: MySQL 8.0.16 now allows you to change SSL options without a restart. Prepares a new SSL context for the listening socket and then replacesthe old ones. Generate your new pem files – put them in place - then

61 SSL Required? SELECT VARIABLE_NAME, VARIABLE_VALUE, 'ONLY ALLOW SSL ' as Note, SELECT VARIABLE_NAME, VARIABLE_VALUE, 'ONLY ALLOW SSL ' as Note, IF(VARIABLE_VALUE = 'ON', 'PASS', 'FAIL') AS CHECK_VAL IF(VARIABLE_VALUE = 'ON', 'PASS', 'FAIL') AS CHECK_VAL FROM performance_schema.global_variables FROM performance_schema.global_variables WHERE VARIABLE_NAME IN ('require_secure_transport'); WHERE VARIABLE_NAME IN ('require_secure_transport'); FORCE encrypted connections globally set persist require_secure_transport=ON; set persist require_secure_transport=ON; Force it globally

62 Permitting import and export operations SELECT VARIABLE_NAME, VARIABLE_VALUE, 'Secure File Check' as Note, SELECT VARIABLE_NAME, VARIABLE_VALUE, 'Secure File Check' as Note, IF(length(VARIABLE_VALUE) > 0 and VARIABLE_VALUE!='NULL' , 'FAIL', 'PASS') IF(length(VARIABLE_VALUE) > 0 and VARIABLE_VALUE!='NULL' , 'FAIL', 'PASS') AS SecFileCheck AS SecFileCheck FROM performance_schema.global_variables FROM performance_schema.global_variables WHERE variable_name = 'secure_file_priv'; WHERE variable_name = 'secure_file_priv'; Turn off what you are using – reduce the attack surface

63 LOCAL Load Data INFILE Check local_infile SELECT IF(@@local_infile, 'ON', 'OFF') AS LOCAL_LOAD_DATA_ALLOWED; SELECT IF(@@local_infile, 'ON', 'OFF') AS LOCAL_LOAD_DATA_ALLOWED; By Default in 8.0 this is off SET PERSIST local_infile=OFF; SET PERSIST local_infile=OFF; Secure by default - OFF

64 Internal Users SELECT host, user, plugin, SELECT host, user, plugin, IF(plugin = IF(plugin = 'mysql_native_password', 'WEAK 'mysql_native_password', 'WEAK SHA1', 'STRONG SHA2') AS SHA1', 'STRONG SHA2') AS HASHTYPE HASHTYPE FROM mysql.user WHERE user not FROM mysql.user WHERE user not IN ('mysql.infoschema', IN ('mysql.infoschema', 'mysql.session') 'mysql.session') AND (plugin not like 'auth%' AND (plugin not like 'auth%' AND plugin <> 'mysql_no_login') AND plugin <> 'mysql_no_login') AND AND length(authentication_string) > length(authentication_string) > 0 ORDER BY plugin 0 ORDER BY plugin; Authenticated internally To Do's • Lock Accounts that are unknown – then drop once sure • Drop and create new user accounts with stricter host specification • Users with native typically are from MySQL 5.7 upgrade to 8.0 https://mysqlserverteam.com/mysql-8-0-4-new-default-authentication-plugin-caching_sha2_password

65 Internal Users SELECT ùser`.`Host`, ùser`.Ùser`, ùser`.`ssl_type`, SELECT ùser`.`Host`, ùser`.Ùser`, ùser`.`ssl_type`, CAST(ùser`.`x509_issuer` as CHAR) as Issuer, CAST(ùser`.`x509_issuer` as CHAR) as Issuer, CAST(ùser`.`x509_subject` as CHAR) as Subject CAST(ùser`.`x509_subject` as CHAR) as Subject FROM `mysql`.ùser` where (user not like 'mysql.%') AND ssl_type='X509'; FROM `mysql`.ùser` where (user not like 'mysql.%') AND ssl_type='X509'; REQUIRING X509 CERTIFICATE

66 External Authentication SELECT ùser`.`Host`, ùser`.Ùser`,ùser`.`plugin`, SELECT ùser`.`Host`, ùser`.Ùser`,ùser`.`plugin`, ùser`.àuthentication_string` from mysql.user where ùser`.àuthentication_string` from mysql.user where plugin like 'auth%'; plugin like 'auth%'; Many companies are going to external authentication – especially for internal users – DBAs and Developers Map and manage in LDAP, Actual User in Audit Trail Make sure users or mapped organizations should have MySQL Access. Globally manage – map to Enterprise, Use stronger Options LDAP, Windows AD SSPI, Kerberos, FIDO2 – Many Options

67 Multi-Factor Authentication Create with 2 CREATE USER 'alice'@'localhost' IDENTIFIED WITH caching_sha2_password CREATE USER 'alice'@'localhost' IDENTIFIED WITH caching_sha2_password BY ' BY 'sha2_password sha2_password' AND IDENTIFIED WITH authentication_ldap_sasl AS ' AND IDENTIFIED WITH authentication_ldap_sasl AS 'uid=u1_ldap,ou=People,dc=example,dc=com’ 'uid=u1_ldap,ou=People,dc=example,dc=com’; Can add a second or here a third factor later with ALTER ALTER USER 'alice'@'localhost' ADD 3 FACTOR IDENTIFIED WITH ALTER USER 'alice'@'localhost' ADD 3 FACTOR IDENTIFIED WITH authentication_fido; authentication_fido; “Assure that strong multi-factor authentication is pervasive to protect against common attacks against the credentials of consumers, merchants, and service providers” “The PCI DSS requires multi-factor authentication (MFA) mechanism for remote access to the Cardholder Data Environment (CDE).” Up to 3 – various regulations requiring MFA, 2FA, … - PCI DSS 8.3 for example https://www.pcisecuritystandards.org/documents/PCIDSS_QRGv3_1.pdf https://www.pcidssguide.com/pci-multi-factor-authentication-checklist/ https://dev.mysql.com/doc/refman/8.0/en/multifactor-authentication.html

68 User Rights For example if your company policy is MAX 210 SELECT VARIABLE_NAME, VARIABLE_VALUE, 'MUST be 210 or less' as Note, IF(VARIABLE_VALUE < 211, 'PASS', 'FAIL') FROM performance_schema.global_variables WHERE VARIABLE_NAME LIKE 'max_connections'; *************************** 1. row *************************** VARIABLE_NAME: max_connections VARIABLE_VALUE: 151 Note: MUST be 210 or less IF(VARIABLE_VALUE < 211, 'PASS', 'FAIL'): PASS If the result is FAIL – then FIX SET PERSIST max_connections = 210; Max Connections

69 MySQL Schema db tables_priv; columns_priv; procs_priv; roles; users Information Schema (VIEWS) user_privileges table_privileges schema_privileges column_privileges User Rights Granted Permissions

70 Permissions Reporting – Direct Grants WITH WITH tableprivs AS (SELECT user, host, 'mysql.tables_priv' as PRIV_SOURCE , DB as _db, tableprivs AS (SELECT user, host, 'mysql.tables_priv' as PRIV_SOURCE , DB as _db, Table_Name as _obj , ' ' as _col Table_Name as _obj , ' ' as _col FROM mysql.tables_priv where Table_name like '%' ), FROM mysql.tables_priv where Table_name like '%' ), colprivs AS (SELECT User, Host, 'mysql.columns_priv' as PRIV_SOURCE , DB as _db, colprivs AS (SELECT User, Host, 'mysql.columns_priv' as PRIV_SOURCE , DB as _db, table_name as _obj , column_name as _col table_name as _obj , column_name as _col FROM mysql.columns_priv WHERE Table_name like '%' ) 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 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 colprivs UNION SELECT user,host, PRIV_SOURCE, _db, _obj, _col FROM tableprivs) as tt group by user, SELECT user,host, PRIV_SOURCE, _db, _obj, _col FROM tableprivs) as tt group by user, host, PRIV_SOURCE, _db, _obj, _col; host, PRIV_SOURCE, _db, _obj, _col;

71 Review MySQL Plugins – Install if missing or uninstall if unused SELECT `PLUGINS`.`PLUGIN_NAME`,`PLUGINS`.`PLUGIN_VERSION`, SELECT `PLUGINS`.`PLUGIN_NAME`,`PLUGINS`.`PLUGIN_VERSION`, `PLUGINS`.`PLUGIN_STATUS`,`PLUGINS`.`PLUGIN_TYPE`, `PLUGINS`.`PLUGIN_STATUS`,`PLUGINS`.`PLUGIN_TYPE`, `PLUGINS`.`PLUGIN_TYPE_VERSION`,`PLUGINS`.`PLUGIN_LIBRARY`, `PLUGINS`.`PLUGIN_TYPE_VERSION`,`PLUGINS`.`PLUGIN_LIBRARY`, `PLUGINS`.`PLUGIN_LIBRARY_VERSION`,`PLUGINS`.`PLUGIN_DESCRIPTION`, `PLUGINS`.`PLUGIN_LIBRARY_VERSION`,`PLUGINS`.`PLUGIN_DESCRIPTION`, `PLUGINS`.`PLUGIN_LICENSE`,`PLUGINS`.`LOAD_OPTION` `PLUGINS`.`PLUGIN_LICENSE`,`PLUGINS`.`LOAD_OPTION` FROM `information_schema`.`PLUGINS` where plugin_library is Not null; FROM `information_schema`.`PLUGINS` where plugin_library is Not null;

72 Review User Ports SELECT VARIABLE_NAME, VARIABLE_VALUE, 'If the defined port is deemed SELECT VARIABLE_NAME, VARIABLE_VALUE, 'If the defined port is deemed prohibited, this is a FAIL.' as Note prohibited, this is a FAIL.' as Note FROM performance_schema.global_variables FROM performance_schema.global_variables WHERE VARIABLE_NAME in ('port', 'mysqlx_port', 'admin_port'); WHERE VARIABLE_NAME in ('port', 'mysqlx_port', 'admin_port'); MySQL Port Reference Tables https://dev.mysql.com/doc/mysql-port-reference/en/mysql-ports-reference-tables.html

73 Check on where your files are stored SELECT VARIABLE_NAME, VARIABLE_VALUE SELECT VARIABLE_NAME, VARIABLE_VALUE FROM FROM performance_schema.global_variables performance_schema.global_variables WHERE (VARIABLE_NAME LIKE '%dir' or WHERE (VARIABLE_NAME LIKE '%dir' or VARIABLE_NAME LIKE '%file') VARIABLE_NAME LIKE '%file') and (VARIABLE_NAME NOT LIKE '%core%' and (VARIABLE_NAME NOT LIKE '%core%' AND VARIABLE_NAME <> 'local_infile' AND VARIABLE_NAME <> 'local_infile' AND VARIABLE_NAME <> AND VARIABLE_NAME <> 'relay_log_info_file') order by 'relay_log_info_file') order by VARIABLE_NAME; VARIABLE_NAME;

74 Are your keys safe? Is keyring installed? Key manager? SELECT `PLUGIN_NAME`, `PLUGIN_STATUS`, `PLUGIN_TYPE`, `PLUGIN_LIBRARY`, SELECT `PLUGIN_NAME`, `PLUGIN_STATUS`, `PLUGIN_TYPE`, `PLUGIN_LIBRARY`, `PLUGIN_DESCRIPTION`, `LOAD_OPTION` `PLUGIN_DESCRIPTION`, `LOAD_OPTION` FROM `information_schema`.`PLUGINS` where PLUGIN_NAME LIKE 'keyring_file' and FROM `information_schema`.`PLUGINS` where PLUGIN_NAME LIKE 'keyring_file' and plugin_status='ACTIVE plugin_status='ACTIVE'; '; NOTE: keyring_file – is not for production. (Dev/QA only – its in a Plain text file) KMIP, Encrypted Keyring, OCI Vault, Hashicorp, AWS KMS, etc. should be used in production NOTE: Keyring installation is key manager specific. See https://dev.mysql.com/doc/refman/8.0/en/keyring.html

75 AT REST Encryption Checks InnoDB Tablespace Checks SELECT ÌNNODB_TABLESPACES`.`NAME`,ÌNNODB_TABLESPACES`.ÈNCRYPTION`, SELECT ÌNNODB_TABLESPACES`.`NAME`,ÌNNODB_TABLESPACES`.ÈNCRYPTION`, IF(ENCRYPTION = 'Y', 'PASS', 'FAIL') AS CHECK_VAL IF(ENCRYPTION = 'Y', 'PASS', 'FAIL') AS CHECK_VAL FROM ìnformation_schema`.ÌNNODB_TABLESPACES` WHERE ENCRYPTION='N FROM ìnformation_schema`.ÌNNODB_TABLESPACES` WHERE ENCRYPTION='N' ' ORDER BY ORDER BY CHECK_VAL; CHECK_VAL; Require InnoDB TDE (Are tables required to be encrypted?) SELECT VARIABLE_NAME, VARIABLE_VALUE, 'table_encryption_privilege_check - TABLE REQUIRE AT REST ENCRYPTION' as Note, IF(VARIABLE_VALUE = 'ON', 'PASS', 'FAIL') AS CHECK_VAL FROM performance_schema.global_variables where variable_name = 'table_encryption_privilege_check';

76 InnoDB REDO, UNDO, Binlog, Audit log Encrypted? SELECT VARIABLE_NAME, VARIABLE_VALUE, 'innodb_redo_log AT REST ENCRYPTION' AS Note, SELECT VARIABLE_NAME, VARIABLE_VALUE, 'innodb_redo_log AT REST ENCRYPTION' AS Note, IF(VARIABLE_VALUE = 'ON', 'PASS', 'FAIL') AS CHECK_VAL IF(VARIABLE_VALUE = 'ON', 'PASS', 'FAIL') AS CHECK_VAL FROM performance_schema.global_variables WHERE variable_name = 'innodb_redo_log_encrypt FROM performance_schema.global_variables WHERE variable_name = 'innodb_redo_log_encrypt'; '; -- -- SELECT VARIABLE_NAME, VARIABLE_VALUE, 'innodb_undo_log AT REST ENCRYPTION' AS Note, SELECT VARIABLE_NAME, VARIABLE_VALUE, 'innodb_undo_log AT REST ENCRYPTION' AS Note, IF(VARIABLE_VALUE = 'ON', 'PASS', 'FAIL') AS CHECK_VAL IF(VARIABLE_VALUE = 'ON', 'PASS', 'FAIL') AS CHECK_VAL FROM performance_schema.global_variables WHERE variable_name = 'innodb_undo_log_encrypt FROM performance_schema.global_variables WHERE variable_name = 'innodb_undo_log_encrypt'; '; -- -- SELECT VARIABLE_NAME, VARIABLE_VALUE, 'BINLOG - AT REST ENCRYPTION' AS Note, SELECT VARIABLE_NAME, VARIABLE_VALUE, 'BINLOG - AT REST ENCRYPTION' AS Note, IF(VARIABLE_VALUE = 'ON', 'PASS', 'FAIL') AS CHECK_VAL IF(VARIABLE_VALUE = 'ON', 'PASS', 'FAIL') AS CHECK_VAL FROM performance_schema.global_variables WHERE variable_name = 'binlog_encryption FROM performance_schema.global_variables WHERE variable_name = 'binlog_encryption'; '; -- -- SELECT VARIABLE_NAME, VARIABLE_VALUE, 'AUDIT LOG - AT REST ENCRYPTION' AS Note, SELECT VARIABLE_NAME, VARIABLE_VALUE, 'AUDIT LOG - AT REST ENCRYPTION' AS Note, IF(VARIABLE_VALUE = 'AES', 'PASS', 'FAIL') IF(VARIABLE_VALUE = 'AES', 'PASS', 'FAIL') AS CHECK_VAL AS CHECK_VAL FROM performance_schema.global_variables WHERE variable_name = 'audit_log_encryption'; FROM performance_schema.global_variables WHERE variable_name = 'audit_log_encryption';

77 Auditing Enabled? Is the audit plugin loaded SELECT `PLUGIN_NAME`, `PLUGIN_STATUS`, `PLUGIN_TYPE`, `PLUGIN_LIBRARY`, SELECT `PLUGIN_NAME`, `PLUGIN_STATUS`, `PLUGIN_TYPE`, `PLUGIN_LIBRARY`, `PLUGIN_DESCRIPTION`, `LOAD_OPTION` FROM `information_schema`.`PLUGINS` where PLUGIN_NAME `PLUGIN_DESCRIPTION`, `LOAD_OPTION` FROM `information_schema`.`PLUGINS` where PLUGIN_NAME LIKE 'audit_log' and plugin_status='ACTIVE LIKE 'audit_log' and plugin_status='ACTIVE'; '; If not loaded then run the installations script it will add the plugin and meta tables # shell> mysql -u root -p < audit_log_filter_linux_install.sql; # Edit the mysql config file my.cnf (or my.ini on windows) set --audit-log to ON, FORCE, or FORCE_PLUS_PERMANENT.

78 Audit Rules, Auditing Who? Rules in place (Log everything) SELECT àudit_log_filter`.`NAME`, àudit_log_filter`.`FILTER` FROM SELECT àudit_log_filter`.`NAME`, àudit_log_filter`.`FILTER` FROM `mysql`.àudit_log_filter`; `mysql`.àudit_log_filter`; Adding a rule. audit_log_filter_set_filter('log_all', '{ \"filter\": { \"log\": true } } audit_log_filter_set_filter('log_all', '{ \"filter\": { \"log\": true } }');\ ');\ NOTE: We have many rule templates. (20+) – which cover most needs. Simple rules may fill your disk or under audit. Rules let define selectivity. Applied to Who SELECT àudit_log_user`.ÙSER`, àudit_log_user`.`HOST`, SELECT àudit_log_user`.ÙSER`, àudit_log_user`.`HOST`, àudit_log_user`.`FILTERNAME` àudit_log_user`.`FILTERNAME` FROM `mysql`.àudit_log_user`; FROM `mysql`.àudit_log_user`;

79 SQL and More – MySQL Shell With MySQL Shell you can bring checks together in a script. For example

Copyright © 2023 Oracle and/or its affiliates. MySQL Secure Deployment Guide • https://dev.mysql.com/doc/mysql-secure-deployment-guide/8.0/en/ 60+ blogs to dive into specific topics and features • https://blogs.oracle.com/mysql/search.html?contentType=Blog-Post&default=security * • https://dev.mysql.com/blog-archive/?cat=Security Whitepapers • https://www.mysql.com/why-mysql/white-papers/#en-22-40 On Demand Webinars • https://www.mysql.com/news-and-events/on-demand-webinars/#en-20-40 Forums • https://forums.mysql.com/ Resources

Resources • How to optimize MySQL performance? ✔ https://dev.mysql.com/doc/refman/8.0/en/optimization.html • The InnoDB Storage Engine ✔ https://dev.mysql.com/doc/refman/8.0/en/innodb-storage-engine.html • MySQL Shell ✔ https://dev.mysql.com/doc/mysql-shell/8.0/en/ • MySQL HeatWave User Guide ✔ https://dev.mysql.com/doc/heatwave/en/ • lefred's blog: tribulations of a MySQL Evangelist ✔ https://lefred.be/ • DimitriK's (dim) Weblog ✔ http://dimitrik.free.fr/blog/ • dasini.net – Diary of a MySQL expert ✔ https://dasini.net/blog/en/ 81

reserved. Follow us on Social Media

reserved. Merci! Q&R Olivier Dasini MySQL Cloud Principal Solutions Architect EMEA [email protected] Blogs : www.dasini.net/blog/en : www.dasini.net/blog/fr Linkedin: www.linkedin.com/in/olivier-dasini Twitter : @freshdaz

84 MySQL Security Guidelines Recommendations from us https://dev.mysql.com/doc/refman/8.0/en/security-guidelines.html

86 FIPs Required SELECT VARIABLE_NAME, VARIABLE_VALUE, 'FIPS Mode' AS Note, IF(VARIABLE_VALUE = 'ON' OR VARIABLE_VALUE = 'STRICT', 'Yes', 'No') FROM performance_schema.global_variables where variable_name = 'ssl_fips_mode'; See if its on or not

87 Department of Defense (DoD) approved and published Security Technical Implementation Guide (STIG) • DISA STIG for MySQL 8.0 EE https://www.mysql.com/products/enterprise/stig.html https://public.cyber.mil/stigs/

88 Center For Internet Security Benchmark CIS Benchmark for MySQL 8.0 EE • https://www.cisecurity.org/benchmark/oracle_mysql/

89 Side Bar – In MySQL 8.0 DBAs can set system variables from SQL The value of SET PERSIST is written to mysqld-auto.cnf SET PERSIST ONLY – stores to mysqld-auto.cnf without setting the runtime value. Use for configuring read-only system variables that can be set only at server startup. A few system variables can't be set using this command See https://dev.mysql.com/doc/refman/8.0/en/nonpersistible-system-variables.html Need to be even more Secure – Install a MySQL Keyring then persist_sensitive_variables_in_plaintext=ON When set the server encrypts the values of any sensitive system variables Use SET PERSIST https://dev.mysql.com/doc/refman/8.0/en/persisted-system-variables.html#persisted-system-variables-sensitive

90 Side Bar – This file is in the datadir Less accessible than my.cnf Added security Epoch timestamped Track change times 1564600430679850 Mon, 26 Aug 2019 17:57:47 GMT mysqld-auto.cnf

MySQL Security - From Data Protection to Regulation Compliance

MySQL Security - From Data Protection to Regulation Compliance

More Decks by Olivier DASINI

Other Decks in Technology

Featured

Transcript