MySQL Security - From Data Protection to Regulation Compliance with Enterprise Edition

MySQL Security From Data Protection to Regulation Compliance with Enterprise
Edition Lyon 18/04/2024 Olivier Dasini Cloud Solutions Architect @ Oracle MySQL [email protected] Blogs : www.dasini.net/blog/en : www.dasini.net/blog/fr Linkedin : www.linkedin.com/in/olivier-dasini Slides : https://speakerdeck.com/freshdaz

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

Agenda 1. Security Overview 2. Regulations 3. MySQL built-in Security 4. MySQL Enterprise Security + Bonus: Tips & Tricks 3

Security Overview

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

6 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

7 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

8 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

10 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.)

11 Regulations require these Security Steps  Assess  Locate Risks and Vulnerabilities, Ensure necessary security controls  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 built-in Security

13 Built-in Security (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/

14 MySQL Password Policies  Random Password Generation – Generating random passwords for user accounts – http://dasini.net/blog/2020/04/15/mysql-security-random-password-generation/  Password Expiration Policy – Enables DBAs to require that users reset their password – http://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 – http://dasini.net/blog/2020/04/28/mysql-security-password-reuse-policy/  Password Verification-Required Policy – Authorizes users to change their password only if they could provide the current password – http://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 – http://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 – http://dasini.net/blog/2020/05/19/mysql-security-dual-password-support/ https://dev.mysql.com/doc/refman/8.0/en/pluggable-authentication.html Provides DBAs built-in password management

15 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

16 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

17 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

18 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

19 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

MySQL Enterprise Security

21 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

22 MySQL Enterprise Security Tools  Assess  MySQL Enterprise Monitor  Prevent  MySQL Enterprise Authentication  MySQL Enterprise Firewall  MySQL Enterprise TDE  MySQL Enterprise Encryption  MySQL Enterprise Data Masking  Detect  MySQL Enterprise Audit  Recover  MySQL Enterprise HA  MySQL Enterprise Backup https://www.mysql.com/products/enterprise/

Assess 23

24 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

Prevent 25

26 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

27 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

28 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

29 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

30 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

31 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

32 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

33 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

34 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

35 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

Detect 36

37 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

38 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

Recover 39

40 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 MySQL built-in High Availability solution: • Easy to use! • RPO=0 • RTO=seconds (automatic failover)

41 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

42 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

43 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

In short A picture is worth a thousand words

45

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/ 47

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

Follow us on Social Media “Data is the Oxygen of
Business” 49 Copyright © 2024, Oracle and/or its affiliates

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

Bonus: 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 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?' as Note, IF(count(component_urn) > 0, 'YES', 'NO') AS Answer FROM mysql.component WHERE component_urn='file://component_validate_password' GROUP BY component_urn; IS THE COMPONENT INSTALLED?

56 Password Policies SELECT VARIABLE_NAME, VARIABLE_VALUE FROM performance_schema.global_variables WHERE VARIABLE_NAME LIKE 'valid%password%' 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 PLUGIN_NAME LIKE 'connection%'; Check Settings SELECT @@connection_control_failed_connections_threshold, @@connection_control_min_connection_delay, @@connection_control_max_connection_delay, @@connection_control_failed_connections_threshold\G Dealing with Failed Login Attempts related to Brute Force Attacks *************************** 1. row *************************** @@connection_control_failed_connections_threshold: 3 @@connection_control_min_connection_delay: 1000 @@connection_control_max_connection_delay: 2147483647 @@connection_control_failed_connections_threshold: 3

59 Installing and Setting Connection Controls INSTALL PLUGIN CONNECTION_CONTROL SONAME 'connection_control.so'; INSTALL PLUGIN CONNECTION_CONTROL_FAILED_LOGIN_ATTEMPTS SONAME 'connection_control.so'; For example SET PERSIST connection_control_failed_connections_threshold = 4; 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, @@ssl_cert, @@ssl_cipher, @@ssl_crl, @@ssl_crlpath, @@ssl_fips_mode,@@ssl_key; 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, IF(VARIABLE_VALUE = 'ON', 'PASS', 'FAIL') AS CHECK_VAL FROM performance_schema.global_variables WHERE VARIABLE_NAME IN ('require_secure_transport'); FORCE encrypted connections globally 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, IF(length(VARIABLE_VALUE) > 0 and VARIABLE_VALUE!='NULL' , 'FAIL', 'PASS') AS SecFileCheck FROM performance_schema.global_variables 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; By Default in 8.0 this is off SET PERSIST local_infile=OFF; Secure by default - OFF

64 Internal Users SELECT host, user, plugin, IF(plugin = 'mysql_native_password', 'WEAK SHA1', 'STRONG SHA2') AS HASHTYPE FROM mysql.user WHERE user not IN ('mysql.infoschema', 'mysql.session') AND (plugin not like 'auth%' AND plugin <> 'mysql_no_login') AND length(authentication_string) > 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`, CAST(ùser`.`x509_issuer` as CHAR) as Issuer, CAST(ùser`.`x509_subject` as CHAR) as Subject 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`, ùser`.àuthentication_string` from mysql.user where 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 BY 'sha2_password' AND IDENTIFIED WITH authentication_ldap_sasl AS '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 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 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;

71 Review MySQL Plugins – Install if missing or uninstall if unused SELECT `PLUGINS`.`PLUGIN_NAME`,`PLUGINS`.`PLUGIN_VERSION`, `PLUGINS`.`PLUGIN_STATUS`,`PLUGINS`.`PLUGIN_TYPE`, `PLUGINS`.`PLUGIN_TYPE_VERSION`,`PLUGINS`.`PLUGIN_LIBRARY`, `PLUGINS`.`PLUGIN_LIBRARY_VERSION`,`PLUGINS`.`PLUGIN_DESCRIPTION`, `PLUGINS`.`PLUGIN_LICENSE`,`PLUGINS`.`LOAD_OPTION` 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 prohibited, this is a FAIL.' as Note FROM performance_schema.global_variables 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 FROM performance_schema.global_variables WHERE (VARIABLE_NAME LIKE '%dir' or VARIABLE_NAME LIKE '%file') and (VARIABLE_NAME NOT LIKE '%core%' AND VARIABLE_NAME <> 'local_infile' AND VARIABLE_NAME <> 'relay_log_info_file') order by VARIABLE_NAME;

74 Are your keys safe? Is keyring installed? Key manager? SELECT `PLUGIN_NAME`, `PLUGIN_STATUS`, `PLUGIN_TYPE`, `PLUGIN_LIBRARY`, `PLUGIN_DESCRIPTION`, `LOAD_OPTION` FROM `information_schema`.`PLUGINS` where PLUGIN_NAME LIKE 'keyring_file' and 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`, IF(ENCRYPTION = 'Y', 'PASS', 'FAIL') AS CHECK_VAL FROM ìnformation_schema`.ÌNNODB_TABLESPACES` WHERE ENCRYPTION='N' ORDER BY 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, IF(VARIABLE_VALUE = 'ON', 'PASS', 'FAIL') AS CHECK_VAL 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, IF(VARIABLE_VALUE = 'ON', 'PASS', 'FAIL') AS CHECK_VAL FROM performance_schema.global_variables WHERE variable_name = 'innodb_undo_log_encrypt'; -- SELECT VARIABLE_NAME, VARIABLE_VALUE, 'BINLOG - AT REST ENCRYPTION' AS Note, IF(VARIABLE_VALUE = 'ON', 'PASS', 'FAIL') AS CHECK_VAL FROM performance_schema.global_variables WHERE variable_name = 'binlog_encryption'; -- SELECT VARIABLE_NAME, VARIABLE_VALUE, 'AUDIT LOG - AT REST ENCRYPTION' AS Note, IF(VARIABLE_VALUE = 'AES', 'PASS', 'FAIL') AS CHECK_VAL 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`, `PLUGIN_DESCRIPTION`, `LOAD_OPTION` FROM `information_schema`.`PLUGINS` where PLUGIN_NAME 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 `mysql`.àudit_log_filter`; Adding a rule. 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`, àudit_log_user`.`FILTERNAME` FROM `mysql`.àudit_log_user`;

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

80 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

81 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/

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

83 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

84 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 Regula...

MySQL Security - From Data Protection to Regulation Compliance with Enterprise Edition

More Decks by Olivier DASINI

Other Decks in Technology

Featured

Transcript