MariaDB security features and best practices Robert Bindar - - PowerPoint PPT Presentation

▶
mariadb security features and best practices
SMART_READER_LITE
LIVE PREVIEW

MariaDB security features and best practices Robert Bindar - - PowerPoint PPT Presentation

Aug 29, 2023 175 likes •610 views

MariaDB security features and best practices Robert Bindar Software Developer @MariaDB Foundation Percona Live Austin, 28-30 May 2019 Motivation - Users Potential public shaming through data breaches Massive loss of business

slide-1
SLIDE 1

MariaDB security features and best practices

Robert Bindar Software Developer @MariaDB Foundation

Percona Live Austin, 28-30 May 2019

slide-2
SLIDE 2
  • Potential public shaming through data breaches
  • Massive loss of business
  • Angry Clients
  • Expensive lawsuits and fines
  • And it’s getting worse as more people start realizing the impact of data

security

  • Bonus points for being compliant with data protection regulations

2 MariaDB Security Features and Best Practices https://mariadb.org

Motivation - Users

slide-3
SLIDE 3
  • One of the most popular db servers
  • Part of critical infrastructure worldwide
  • Its main purpose is to manage data
  • Very important for our users

3 MariaDB Security Features and Best Practices https://mariadb.org

Motivation - MariaDB

slide-4
SLIDE 4

Potential threats and defense mechanisms

4 MariaDB Security Features and Best Practices https://mariadb.org

slide-5
SLIDE 5

Direct DB threats

  • Man in the middle attacks
  • Spoofing
  • Memory corruption exploits

Threat Prevention

5

  • Limit/block outside TCP connections

to MariaDB

  • Secure your DNS infrastructure
  • MariaDB should accept connections
  • nly from the application host
  • Use bind_address
  • Use TLS/SSL
  • Keep your OS updated
  • Keep your MariaDB Server updated

MariaDB Security Features and Best Practices https://mariadb.org

slide-6
SLIDE 6

Application threats

Threat

  • DOS attacks
  • Data leaks/corruption
  • SQL injection

Prevention

  • Your MariaDB server should ideally

run on a dedicated machine

  • Avoid running the application on the

DB machine

  • Keep the DB machine as clean as

possible

  • Have a strong permissions system
  • Application code security practices

6 MariaDB Security Features and Best Practices https://mariadb.org

slide-7
SLIDE 7

Limiting Human Errors

Threat

  • Genuine human mistakes
  • Bad intentions

Prevention

  • Limit sudo access on the MariaDB

server machine

  • Limit ssh access
  • Avoid running mysqld as root
  • Use specific hostnames instead of

wildcards

  • Use secure_file_priv
  • Robust defaults

7 MariaDB Security Features and Best Practices https://mariadb.org

slide-8
SLIDE 8

Secure Installations with mysql_secure_installation

8 MariaDB Security Features and Best Practices https://mariadb.org

slide-9
SLIDE 9

It won’t provide bullet-proof security for your deployment. This script just presents a basic set of recommended settings to get started.

9

mysql_secure_installation

MariaDB Security Features and Best Practices https://mariadb.org

slide-10
SLIDE 10
  • Set/Change root accounts passwords
  • Delete root accounts accessible from outside
  • Remove anonymous user accounts
  • Remove test database
  • FLUSH PRIVILEGES on the house!

10

mysql_secure_installation

MariaDB Security Features and Best Practices https://mariadb.org

slide-11
SLIDE 11

Data Encryption

11 MariaDB Security Features and Best Practices https://mariadb.org

slide-12
SLIDE 12

#define

In-Transit Data:

Data transmitted between clients and the MariaDB server, between server instances in replication or data transmitted within the Galera cluster. Defaults to unencrypted!

At-Rest Data:

Some of the data residing in persistent storage: tables, tablespaces, binary logs. Supported with InnoDB and XtraDB, partially with Aria.

12 MariaDB Security Features and Best Practices https://mariadb.org

slide-13
SLIDE 13
  • MariaDB uses TLS
  • static linking with yaSSL - server + client
  • dynamic linking with OpenSSL - server + client
  • dynamic linking with GnuTLS or Schannel - client
  • have_ssl will tell you if TLS is supported/enabled

13

Encryption Libraries in MariaDB

MariaDB Security Features and Best Practices https://mariadb.org

MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE 'version_ssl_library';

+---------------------+----------------------------+ | Variable_name | Value | +---------------------+----------------------------+ | version_ssl_library | OpenSSL 1.1.0g 2 Nov 2017 | +---------------------+----------------------------+

slide-14
SLIDE 14

14

Server <-> Clients data encryption with TLS

MariaDB Security Features and Best Practices https://mariadb.org

  • Defaults to not encrypted
  • have_ssl == YES means TLS is enabled
  • FLUSH SSL reloads TLS context from 10.4
  • Two-way TLS is required if REQUIRE X509, REQUIRE SUBJECT, REQUIRE

ISSUER are used for an account

  • TLS can be required for specific accounts from untrusted hosts

[mariadb] ssl_cert = /etc/my.cnf.d/certificates/server-cert.pem ssl_key = /etc/my.cnf.d/certificates/server-key.pem ssl_ca = /etc/my.cnf.d/certificates/ca.pem

slide-15
SLIDE 15

15

Secure Connections in Replication

MariaDB Security Features and Best Practices https://mariadb.org

  • Defaults to not encrypted
  • Enable TLS on all server instances
  • Stop slaves and execute CHANGE MASTER
  • Two-way TLS can also be enabled with CHANGE MASTER

MariaDB [(none)]> CHANGE MASTER TO MASTER_SSL_CA = '/path/to/ca/ca.pem', MASTER_SSL_VERIFY_SERVER_CERT=1;

slide-16
SLIDE 16

16

Encryption for Galera Cluster

MariaDB Security Features and Best Practices https://mariadb.org

  • Defaults to not encrypted
  • Enable TLS on each server node
  • Add wsrep ssl options to cnf along the server ssl options
  • Traffic is encrypted within the cluster and with external client connections
  • Backup utilities also support encryption

[mariadb] … cert,key,ca wsrep_provider_options="socket.ssl_cert=/path/server-cert.pem; socket.ssl_key=/path/server-key.pem; socket.ssl_ca=/path/ca.pem"

slide-17
SLIDE 17

17

At-Rest Data Encryption

MariaDB Security Features and Best Practices https://mariadb.org

  • Encrypting some of the data on disk
  • Overhead is estimated at 3-5%
  • Supported fully with InnoDB and XtraDB SEs
  • Aria support for ROW_FORMAT=PAGE tables
  • You need to install an encryption management plugin
  • Only helpful if the attacker is not an authorized MariaDB user
slide-18
SLIDE 18

Account Management best practices

18 MariaDB Security Features and Best Practices https://mariadb.org

slide-19
SLIDE 19

Password Validation Plugins

  • .so shipped with MariaDB - easy install
  • Minimum length
  • Mixed case
  • Alphanumeric checks
  • Special chars
  • Can be used with PAM as of 10.4

Simple Password Check Cracklib Password Check

19

  • Not shipped by default with MariaDB
  • Checks password against a dictionary
  • Uses the CrackLib db
  • Can be used with PAM as of 10.4

MariaDB Security Features and Best Practices https://mariadb.org

slide-20
SLIDE 20
  • Use OS credentials when connecting to MariaDB
  • Enabled by default in 10.4.3
  • https://mariadb.org/authentication-in-mariadb-10-4/

20

Unix Socket Authentication

MariaDB [(none)]> CREATE USER username@hostname IDENTIFIED VIA unix_socket; Query OK, 0 rows affected (0.00 sec)

MariaDB Security Features and Best Practices https://mariadb.org

slide-21
SLIDE 21
  • Mark an account as locked and deny any subsequent connection

requests for that account

  • Minimum privilege package = no client connection at all
  • Integrated solution for refusing client connections

21

Account Locking

MariaDB Security Features and Best Practices https://mariadb.org

slide-22
SLIDE 22
  • Creates a user account that is locked

22

Account Locking

MariaDB [(none)]> CREATE USER user@localhost ACCOUNT LOCK; Query OK, 0 rows affected (0.00 sec)

MariaDB Security Features and Best Practices https://mariadb.org

slide-23
SLIDE 23
  • SHOW CREATE USER displays the locking status of an account

23

Account Locking

MariaDB [(none)]> SHOW CREATE USER user@localhost; +---------------------------------------------+ | CREATE USER for user@localhost | +---------------------------------------------+ | CREATE USER 'user'@'localhost' ACCOUNT LOCK | +---------------------------------------------+ 1 row in set (0.000 sec)

MariaDB Security Features and Best Practices https://mariadb.org

slide-24
SLIDE 24
  • Altering an existing account to lock/unlock

24

Account Locking

MariaDB [(none)]> ALTER USER user@localhost ACCOUNT UNLOCK; Query OK, 0 rows affected (0.00 sec) MariaDB [(none)]> ALTER USER user@localhost ACCOUNT LOCK; Query OK, 0 rows affected (0.00 sec)

MariaDB Security Features and Best Practices https://mariadb.org

slide-25
SLIDE 25
  • Attempting a connection using a locked account returns

ER_ACCOUNT_HAS_BEEN_LOCKED

25

Account Locking

bindar@computer:~/MariaDB/server$ ./client/mysql -uuser ERROR 4151 (HY000): Access denied, this account is locked

MariaDB Security Features and Best Practices https://mariadb.org

slide-26
SLIDE 26

Whether an account is locked or not is checked during the authentication phase (including COM_CHANGE_USER). Locking an account does not affect existing connections.

26

Account Locking

MariaDB Security Features and Best Practices https://mariadb.org

slide-27
SLIDE 27
  • A new connection with an expired password is either denied or only

allowed to execute SET PASSWORD

  • Supports expiring passwords with immediate effect, per-account

automatic expiration as well as global policies for automatic expiration

  • Compliance with latest security standards
  • Fully compatible with MySQL 5.7 datadirs

27

Expiration of User Passwords

MariaDB Security Features and Best Practices https://mariadb.org

slide-28
SLIDE 28
  • Creates a new account and expire the password with immediate effect

28

Password Expiration

MariaDB [(none)]> CREATE USER user@localhost PASSWORD EXPIRE; Query OK, 0 rows affected (0.00 sec)

MariaDB Security Features and Best Practices https://mariadb.org

slide-29
SLIDE 29

Password Expiration

Disconnect Mode:

In this mode, any new connections for accounts with expired passwords are refused.

Sandbox Mode:

A new connection for an account with the password expired is only allowed to execute SET PASSWORD to change the account password, attempts to execute any other statements are rejected.

29 MariaDB Security Features and Best Practices https://mariadb.org

slide-30
SLIDE 30
  • disconnect_on_expired_password system var (default OFF) controls how

clients unaware of the sandbox mode are treated

  • But --connect-expired-password arg passed to the client takes precedence

and the server knows to put the connection in sandbox mode

  • Also interactive client connections are always put in sandbox mode
  • In the MariaDB C Connector,

MYSQL_OPT_CAN_HANDLE_EXPIRED_PASSWORDS can be passed to mysql_options to achieve a similar behavior

30

Password Expiration

MariaDB Security Features and Best Practices https://mariadb.org

slide-31
SLIDE 31
  • The client is still able to connect to the server, but only the SET PASSWORD

statement is allowed for changing the account password

  • Executing any other statement returns ER_MUST_CHANGE_PASSWORD

31

Password Expiration

$ mysql -u user

Welcome to the MariaDB monitor.

MariaDB [(none)]> SELECT CURRENT_USER; ERROR 1820 (HY000): You must SET PASSWORD before executing this statement MariaDB [(none)]> SET PASSWORD= PASSWORD(‘abc’);

Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> SELECT CURRENT_USER;

+-----------------+ | CURRENT_USER | +-----------------+ | user1@localhost | +-----------------+ 1 row in set (0.00 sec)

MariaDB Security Features and Best Practices https://mariadb.org

slide-32
SLIDE 32
  • Trying to connect using an expired password account returns

ER_MUST_CHANGE_PASSWORD_LOGIN

32

Password Expiration

$ mysql -u user ERROR 1862 (HY000): Your password has expired. To log in you must change it using a client that supports expired passwords

MariaDB Security Features and Best Practices https://mariadb.org

slide-33
SLIDE 33
  • default_password_lifetime (default 0) controls the global automatic

password expiration policy

  • Can be set at runtime using SET GLOBAL, specified in the config file or as

server arg (--default-password-lifetime=90)

  • default_password_lifetime=0 means passwords never expire
  • default_password_lifetime=90 means passwords expire every 90 days
  • But per-account expiration policies override the global policies

33

Password Expiration

MariaDB Security Features and Best Practices https://mariadb.org

slide-34
SLIDE 34
  • The password of this account will never expire regardless of what global

policies say

34

Password Expiration

MariaDB [(none)]> ALTER USER user@localhost PASSWORD EXPIRE NEVER;

Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> SHOW CREATE USER user@localhost;

+-------------------------------------------------------+ | CREATE USER for user@localhost | +-------------------------------------------------------+ | CREATE USER 'user'@'localhost' PASSWORD EXPIRE NEVER | +-------------------------------------------------------+ 1 row in set (0.00 sec)

MariaDB Security Features and Best Practices https://mariadb.org

slide-35
SLIDE 35
  • default_password_lifetime is overriden and for this account the password

will expire every 30 days

35

Password Expiration

MariaDB [(none)]> ALTER USER user@localhost PASSWORD EXPIRE INTERVAL 30 DAY;

Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> SHOW CREATE USER user@localhost;

+-----------------------------------------------------------------+ | CREATE USER for user@localhost | +-----------------------------------------------------------------+ | CREATE USER 'user'@'localhost' PASSWORD EXPIRE INTERVAL 30 DAY | +-----------------------------------------------------------------+ 1 row in set (0.00 sec)

MariaDB Security Features and Best Practices https://mariadb.org

slide-36
SLIDE 36
  • By specifying DEFAULT as per-account policy, the value in the

default_password_lifetime sys var will be used.

36

Password Expiration

MariaDB [(none)]> ALTER USER user@localhost PASSWORD EXPIRE DEFAULT;

Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> SHOW CREATE USER user@localhost;

+---------------------------------+ | CREATE USER for user@localhost | +---------------------------------+ | CREATE USER 'user'@'localhost' | +---------------------------------+ 1 row in set (0.00 sec)

MariaDB Security Features and Best Practices https://mariadb.org

slide-37
SLIDE 37
  • Or pass --max-password-errors=N to mysqld
  • Denies further connections if a password was wrong the previous N times
  • FLUSH PRIVILEGES resets the counter

37

  • -max-password-errors

MariaDB [(none)]> SET GLOBAL max_password_errors=3; Query OK, 0 rows affected (0.00 sec)

MariaDB Security Features and Best Practices https://mariadb.org

slide-38
SLIDE 38
  • Separation of privileges per group of users
  • It’s possible to set a default role per user
  • Only one active role at a time
  • A role can be assigned to another role
  • Managing privileges for groups becomes easier
  • It’s easy to inspect roles info through I_S

38

Role-based Access Control

MariaDB Security Features and Best Practices https://mariadb.org

slide-39
SLIDE 39

Monitoring Server Activity

39 MariaDB Security Features and Best Practices https://mariadb.org

slide-40
SLIDE 40
  • Log server’s activity for each client session
  • Username Host for each connection
  • Executed queries
  • Accessed tables
  • Updates to server variables
  • Shipped with MariaDB
  • Compliance with audit regulations

40

MariaDB Audit Plugin

MariaDB Security Features and Best Practices https://mariadb.org

slide-41
SLIDE 41

41

Sponsors

MariaDB Security Features and Best Practices https://mariadb.org

slide-42
SLIDE 42

Thank You!

Contact details: robert@mariadb.org About: mariadb.org/about/staff/robert-bindar/

42 MariaDB Security Features and Best Practices https://mariadb.org