Upgrade to Pro — share decks privately, control downloads, hide ads and more …

MySQL Security - From Data Protection to Regulation Compliance

MySQL Security - From Data Protection to Regulation Compliance

In the era of data breaches, security issues can bring lots of headaches to any organization. Besides fines from authorities, bad reputation can put many customers away.
Being able to protect important data from external threats, as well as control the increasing number of people who have internal access to the database systems means minimizing risks and ensuring that monitoring and prevention is in place.

Furthermore, to protect sensitive data and privacy of personal information, governments and industry organizations have developed multiple privacy regulations and data protection laws (GDPR, PCI DSS, HIPAA).

Discover how the latest MySQL Enterprise Edition includes the most comprehensive set of advanced features, management tools, and technical support to achieve the highest levels of MySQL security.

Olivier DASINI

October 03, 2023
Tweet

More Decks by Olivier DASINI

Other Decks in Technology

Transcript

  1. 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

    View full-size slide

  2. 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

    View full-size slide

  3. 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

    View full-size slide

  4. Copyright © 2023, Oracle and/or its affiliates. All rights reserved.
    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

    View full-size slide

  5. Copyright © 2023, Oracle and/or its affiliates. All rights reserved.
    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

    View full-size slide

  6. Copyright © 2023, Oracle and/or its affiliates. All rights reserved.
    6
    Attack Vectors and Targets for Databases
    Complexity grows, Risk Grows

    View full-size slide

  7. Copyright © 2023, Oracle and/or its affiliates. All rights reserved.
    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

    View full-size slide

  8. Copyright © 2023, Oracle and/or its affiliates. All rights reserved.
    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

    View full-size slide

  9. Copyright © 2023, Oracle and/or its affiliates. All rights reserved.
    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

    View full-size slide

  10. Copyright © 2023, Oracle and/or its affiliates. All rights reserved.
    Regulations

    View full-size slide

  11. Copyright © 2023, Oracle and/or its affiliates. All rights reserved.
    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.)

    View full-size slide

  12. Copyright © 2023, Oracle and/or its affiliates. All rights reserved.
    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

    View full-size slide

  13. Copyright © 2023, Oracle and/or its affiliates. All rights reserved.
    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

    View full-size slide

  14. Copyright © 2023, Oracle and/or its affiliates. All rights reserved.
    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

    View full-size slide

  15. Copyright © 2023, Oracle and/or its affiliates. All rights reserved.
    MySQL Security

    View full-size slide

  16. Copyright © 2023, Oracle and/or its affiliates. All rights reserved.
    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/

    View full-size slide

  17. Copyright © 2023, Oracle and/or its affiliates. All rights reserved.
    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

    View full-size slide

  18. Copyright © 2023, Oracle and/or its affiliates. All rights reserved.
    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

    View full-size slide

  19. Copyright © 2023, Oracle and/or its affiliates. All rights reserved.
    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

    View full-size slide

  20. Copyright © 2023, Oracle and/or its affiliates. All rights reserved.
    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

    View full-size slide

  21. Copyright © 2023, Oracle and/or its affiliates. All rights reserved.
    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

    View full-size slide

  22. Copyright © 2023, Oracle and/or its affiliates. All rights reserved.
    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/

    View full-size slide

  23. Copyright © 2023, Oracle and/or its affiliates. All rights reserved.
    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

    View full-size slide

  24. Copyright © 2023, Oracle and/or its affiliates. All rights reserved.
    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

    View full-size slide

  25. Copyright © 2023, Oracle and/or its affiliates. All rights reserved.
    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

    View full-size slide

  26. Copyright © 2023, Oracle and/or its affiliates. All rights reserved.
    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

    View full-size slide

  27. Copyright © 2023, Oracle and/or its affiliates. All rights reserved.
    MySQL Enterprise Security

    View full-size slide

  28. Copyright © 2023, Oracle and/or its affiliates. All rights reserved.
    28
    MySQL Security Overview
    Authentication
    Authorization
    Encryption
    Firewall
    MySQL Security
    Auditing
    Masking/De-Identification
    https://www.mysql.com/products/enterprise/

    View full-size slide

  29. Copyright © 2023, Oracle and/or its affiliates. All rights reserved.
    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

    View full-size slide

  30. Copyright © 2023, Oracle and/or its affiliates. All rights reserved.
    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

    View full-size slide

  31. Copyright © 2023, Oracle and/or its affiliates. All rights reserved.
    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

    View full-size slide

  32. Copyright © 2023, Oracle and/or its affiliates. All rights reserved.
    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

    View full-size slide

  33. Copyright © 2023, Oracle and/or its affiliates. All rights reserved.
    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

    View full-size slide

  34. Copyright © 2023, Oracle and/or its affiliates. All rights reserved.
    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

    View full-size slide

  35. Copyright © 2023, Oracle and/or its affiliates. All rights reserved.
    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

    View full-size slide

  36. Copyright © 2023, Oracle and/or its affiliates. All rights reserved.
    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

    View full-size slide

  37. Copyright © 2023, Oracle and/or its affiliates. All rights reserved.
    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

    View full-size slide

  38. Copyright © 2023, Oracle and/or its affiliates. All rights reserved.
    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

    View full-size slide

  39. Copyright © 2023, Oracle and/or its affiliates. All rights reserved.
    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

    View full-size slide

  40. Copyright © 2023, Oracle and/or its affiliates. All rights reserved.
    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

    View full-size slide

  41. Copyright © 2023, Oracle and/or its affiliates. All rights reserved.
    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

    View full-size slide

  42. Copyright © 2023, Oracle and/or its affiliates. All rights reserved.
    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

    View full-size slide

  43. Copyright © 2023, Oracle and/or its affiliates. All rights reserved.
    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

    View full-size slide

  44. Copyright © 2023, Oracle and/or its affiliates. All rights reserved.
    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

    View full-size slide

  45. Copyright © 2023, Oracle and/or its affiliates. All rights reserved.
    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

    View full-size slide

  46. Copyright © 2023, Oracle and/or its affiliates. All rights reserved.
    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

    View full-size slide

  47. Copyright © 2023, Oracle and/or its affiliates. All rights reserved.
    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

    View full-size slide

  48. Copyright © 2023, Oracle and/or its affiliates. All rights reserved.
    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

    View full-size slide

  49. Copyright © 2023, Oracle and/or its affiliates. All rights reserved.
    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

    View full-size slide

  50. Copyright © 2023, Oracle and/or its affiliates. All rights reserved.
    50
    MySQL InnoDB ReplicaSet
    • Easy to use!
     Asynchronous Replication Architecture
    – (manual) Switchover & Failover
    – (asynchronous) Read Scaleout
    – Simple Replication architecture
     MySQL Shell Configuring, 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

    View full-size slide

  51. Copyright © 2023, Oracle and/or its affiliates. All rights reserved.
    51

    View full-size slide

  52. Copyright © 2023, Oracle and/or its affiliates. All rights reserved.
    Tips & Tricks

    View full-size slide

  53. Copyright © 2023, Oracle and/or its affiliates. All rights reserved.
    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

    View full-size slide

  54. Copyright © 2023, Oracle and/or its affiliates. All rights reserved.
    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

    View full-size slide

  55. Copyright © 2023, Oracle and/or its affiliates. All rights reserved.
    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?

    View full-size slide

  56. Copyright © 2023, Oracle and/or its affiliates. All rights reserved.
    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';

    View full-size slide

  57. Copyright © 2023, Oracle and/or its affiliates. All rights reserved.
    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='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

    View full-size slide

  58. Copyright © 2023, Oracle and/or its affiliates. All rights reserved.
    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

    View full-size slide

  59. Copyright © 2023, Oracle and/or its affiliates. All rights reserved.
    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

    View full-size slide

  60. Copyright © 2023, Oracle and/or its affiliates. All rights reserved.
    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

    View full-size slide

  61. Copyright © 2023, Oracle and/or its affiliates. All rights reserved.
    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

    View full-size slide

  62. Copyright © 2023, Oracle and/or its affiliates. All rights reserved.
    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

    View full-size slide

  63. Copyright © 2023, Oracle and/or its affiliates. All rights reserved.
    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

    View full-size slide

  64. Copyright © 2023, Oracle and/or its affiliates. All rights reserved.
    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

    View full-size slide

  65. Copyright © 2023, Oracle and/or its affiliates. All rights reserved.
    65
    Internal Users
    SELECT `user`.`Host`, `user`.`User`, `user`.`ssl_type`,
    SELECT `user`.`Host`, `user`.`User`, `user`.`ssl_type`,
    CAST(`user`.`x509_issuer` as CHAR) as Issuer,
    CAST(`user`.`x509_issuer` as CHAR) as Issuer,
    CAST(`user`.`x509_subject` as CHAR) as Subject
    CAST(`user`.`x509_subject` as CHAR) as Subject
    FROM `mysql`.`user` where (user not like 'mysql.%') AND ssl_type='X509';
    FROM `mysql`.`user` where (user not like 'mysql.%') AND ssl_type='X509';
    REQUIRING X509 CERTIFICATE

    View full-size slide

  66. Copyright © 2023, Oracle and/or its affiliates. All rights reserved.
    66
    External Authentication
    SELECT `user`.`Host`, `user`.`User`,`user`.`plugin`,
    SELECT `user`.`Host`, `user`.`User`,`user`.`plugin`,
    `user`.`authentication_string` from mysql.user where
    `user`.`authentication_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

    View full-size slide

  67. Copyright © 2023, Oracle and/or its affiliates. All rights reserved.
    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

    View full-size slide

  68. Copyright © 2023, Oracle and/or its affiliates. All rights reserved.
    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

    View full-size slide

  69. Copyright © 2023, Oracle and/or its affiliates. All rights reserved.
    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

    View full-size slide

  70. Copyright © 2023, Oracle and/or its affiliates. All rights reserved.
    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;

    View full-size slide

  71. Copyright © 2023, Oracle and/or its affiliates. All rights reserved.
    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;

    View full-size slide

  72. Copyright © 2023, Oracle and/or its affiliates. All rights reserved.
    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

    View full-size slide

  73. Copyright © 2023, Oracle and/or its affiliates. All rights reserved.
    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;

    View full-size slide

  74. Copyright © 2023, Oracle and/or its affiliates. All rights reserved.
    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

    View full-size slide

  75. Copyright © 2023, Oracle and/or its affiliates. All rights reserved.
    75
    AT REST Encryption Checks
    InnoDB Tablespace Checks
    SELECT `INNODB_TABLESPACES`.`NAME`,`INNODB_TABLESPACES`.`ENCRYPTION`,
    SELECT `INNODB_TABLESPACES`.`NAME`,`INNODB_TABLESPACES`.`ENCRYPTION`,
    IF(ENCRYPTION = 'Y', 'PASS', 'FAIL') AS CHECK_VAL
    IF(ENCRYPTION = 'Y', 'PASS', 'FAIL') AS CHECK_VAL
    FROM `information_schema`.`INNODB_TABLESPACES` WHERE ENCRYPTION='N
    FROM `information_schema`.`INNODB_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';

    View full-size slide

  76. Copyright © 2023, Oracle and/or its affiliates. All rights reserved.
    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';

    View full-size slide

  77. Copyright © 2023, Oracle and/or its affiliates. All rights reserved.
    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.

    View full-size slide

  78. Copyright © 2023, Oracle and/or its affiliates. All rights reserved.
    78
    Audit Rules, Auditing Who?
    Rules in place (Log everything)
    SELECT `audit_log_filter`.`NAME`, `audit_log_filter`.`FILTER` FROM
    SELECT `audit_log_filter`.`NAME`, `audit_log_filter`.`FILTER` FROM
    `mysql`.`audit_log_filter`;
    `mysql`.`audit_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 `audit_log_user`.`USER`, `audit_log_user`.`HOST`,
    SELECT `audit_log_user`.`USER`, `audit_log_user`.`HOST`,
    `audit_log_user`.`FILTERNAME`
    `audit_log_user`.`FILTERNAME`
    FROM `mysql`.`audit_log_user`;
    FROM `mysql`.`audit_log_user`;

    View full-size slide

  79. Copyright © 2023, Oracle and/or its affiliates. All rights reserved.
    79
    SQL and More – MySQL Shell
    With MySQL Shell you can bring checks together in a script.
    For example

    View full-size slide

  80. Copyright © 2023, Oracle and/or its affiliates. All rights reserved. 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

    View full-size slide

  81. Copyright © 2023, Oracle and/or its affiliates. All rights reserved.
    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

    View full-size slide

  82. 82 Copyright © 2023, Oracle and/or its affiliates. All rights reserved.
    Follow us on Social Media

    View full-size slide

  83. 83 Copyright © 2023, Oracle and/or its affiliates. All rights 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

    View full-size slide

  84. Copyright © 2023, Oracle and/or its affiliates. All rights reserved.
    84
    MySQL Security Guidelines
    Recommendations from us
    https://dev.mysql.com/doc/refman/8.0/en/security-guidelines.html

    View full-size slide

  85. Copyright © 2023, Oracle and/or its affiliates. All rights reserved.

    View full-size slide

  86. Copyright © 2023, Oracle and/or its affiliates. All rights reserved.
    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

    View full-size slide

  87. Copyright © 2023, Oracle and/or its affiliates. All rights reserved.
    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/

    View full-size slide

  88. Copyright © 2023, Oracle and/or its affiliates. All rights reserved.
    88
    Center For Internet Security Benchmark
    CIS Benchmark for MySQL 8.0 EE
    • https://www.cisecurity.org/benchmark/oracle_mysql/

    View full-size slide

  89. Copyright © 2023, Oracle and/or its affiliates. All rights reserved.
    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

    View full-size slide

  90. Copyright © 2023, Oracle and/or its affiliates. All rights reserved.
    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

    View full-size slide