MySQL/Percona Server/MariaDB Server security features overview Colin - - PowerPoint PPT Presentation

▶

Aug 23, 2022 41 likes •682 views

MySQL/Percona Server/MariaDB Server security features overview Colin Charles, Chief Evangelist, Percona Inc. colin.charles@percona.com / byte@bytebot.net http://bytebot.net/blog/ | @bytebot on Twitter Percona Live Santa Clara 2018, Santa Clara,

SLIDE 1

MySQL/Percona Server/MariaDB Server security features overview

Colin Charles, Chief Evangelist, Percona Inc. colin.charles@percona.com / byte@bytebot.net http://bytebot.net/blog/ | @bytebot on Twitter Percona Live Santa Clara 2018, Santa Clara, California, USA 25 April 2018

SLIDE 2

#PerconaLive

whoami

Chief Evangelist, Percona Inc
Focusing on the MySQL ecosystem (MySQL, Percona Server, MariaDB

Server), as well as the MongoDB ecosystem (Percona Server for MongoDB) + 100% open source tools from Percona like Percona Monitoring & Management, Percona xtrabackup, Percona Toolkit, etc.

Founding team of MariaDB Server (2009-2016), previously at Monty

Program Ab, merged with SkySQL Ab, now MariaDB Corporation

Formerly MySQL AB (exit: Sun Microsystems)
Past lives include The Fedora Project (FESCO), OpenOffice.org
MySQL Community Contributor of the Year Award winner 2014

SLIDE 3

#PerconaLive

License

Creative Commons BY-NC-SA 4.0
https://creativecommons.org/licenses/by-nc-sa/4.0/legalcode

SLIDE 4

#PerconaLive

MySQL Variants

MySQL Community Edition
Won’t cover: 3.23, 4.0, 4.1, 5.0, 5.1
Will focus on: 5.5, 5.6, 5.7, and the newly released 8.0
MySQL Enterprise Edition
Percona Server for MySQL
5.5, 5.6, 5.7
MariaDB Server
Won’t cover: 5.1, 5.2, 5.3
5.5, 10.0, 10.1, 10.2, with 10.3 as an alpha
What we won’t cover: MySQL Cluster (NDBCLUSTER), Galera Cluster, Group Replication/

InnoDB Cluster, X Protocol/mysqlsh (33060)

SLIDE 5

#PerconaLive

Structured Query Language (SQL)

ISO/IEC 9075 (reviewed every 5 years), SQL-86, SQL-89, SQL-92, SQL:1999,

SQL:2003, SQL:2006, SQL:2008, SQL:2011, SQL:2016

select @@global.sql_mode;
ANSI - come close to the SQL standard
STRICT_TRANS_TABLES - If a value could not be inserted as given into a

transactional table, abort the statement.

TRADITIONAL - “give an error instead of a warning” when inserting an incorrect

value into a column.

https://dev.mysql.com/doc/refman/5.7/en/sql-mode.html
Deprecated - MariaDB Server has NO_AUTO_CREATE_USER but MySQL 5.7 has

this in standard sql_mode

SLIDE 6

#PerconaLive

MySQL security by version

GRANT (3.23)
REVOKE (3.23)
SET PASSWORD (3.23)
SHOW GRANTS (3.23)
DROP USER (4.1)
SHOW PRIVILEGES (4.1)
CREATE USER (5.0)
RENAME USER (5.0)
ALTER USER (5.6)
SHOW CREATE USER (5.7)
CREATE ROLE (8.0)
DROP ROLE (8.0)
SET ROLE (8.0)
SET DEFAULT ROLE (8.0)
N/B: ROLES came to MariaDB Server in 10.0, and the DEFAULT ROLE came in 10.1

SLIDE 7

#PerconaLive

mysql.user table

host
user
password (removed in 5.7; still present in MariaDB)
plugin (5.5)
authentication_string (5.5)
password_expired (5.6)
account_locked (5.7)
Create_role_priv (8.0)
Drop_role_priv (8.0)

Comparing mysql.user between MariaDB Server 10.2 and MySQL 5.7

SLIDE 8

#PerconaLive

Key security features by version

5.1 - McAfee Audit plugin
5.5 - pluggable authentication (MariaDB 5.2 backport), proxy users, changes in mysql.user table, client password

warning; Enterprise provided Audit and PAM authentication (present again in Percona Server for MySQL and MariaDB Server)

5.6 - encrypted client credentials (mysql_config_editor), sha256_password, password expiry,

VALIDATE_PASSWORD_STRENGTH(), --random-passwords (optional random on install), mysql.user password_expired column; Enterprise Firewall

5.7 - grep for root password on installation, password expiry every ‘n’ days, user accounts can be locked/unlocked,

mysql_ssl_rsa_setup, mysql.user.password removed, super_read_only, at rest tablespace encryption

8.0 - roles + mysql.user changes
MariaDB 10.0 - roles, userstats
MariaDB 10.1 - default roles, at rest table/tablespace encryption, simple_password_check, cracklib_password_check,

AWS Key Management plugin

MariaDB 10.2 - user limits, ed25519 auth
Percona Server for MySQL 5.5 - extended SHOW GRANTS, utility user, userstats
Percona Server for MySQL 5.6 - super_read_only
Percona Server for MySQL 5.7 - Vault plugin

SLIDE 9

#PerconaLive

Installation Default Passwords

'root' user
Pre 5.7 no password
5.7 expired random password
Anonymous users
Removed in 5.7

SLIDE 10

#PerconaLive

How are passwords stored in MySQL? (5.5)

mysql55 >SELECT /* 5.5 */ host, user, password, plugin, authentication_string FROM mysql.user;  +-----------+------+----------+--------+-----------------------+  | host | user | password | plugin | authentication_string |  +-----------+------+----------+--------+-----------------------+  | localhost | root | | | |  | mysql55 | root | | | |  | 127.0.0.1 | root | | | |  | ::1 | root | | | |  | localhost | | | | NULL |  | mysql55 | | | | NULL |  +-----------+------+----------+--------+-----------------------+  6 rows in set (0.00 sec) 

SLIDE 11

#PerconaLive

How are passwords stored in MySQL? (5.6)

mysql56 >SELECT /* 5.6 */ host, user, password, plugin, authentication_string, password_expired FROM mysql.user;  +-----------+------+----------+-----------------------+-----------------------+------------------+  | host | user | password | plugin | authentication_string | password_expired |  +-----------+------+----------+-----------------------+-----------------------+------------------+  | localhost | root | | mysql_native_password | | N |  | mysql56 | root | | mysql_native_password | | N |  | 127.0.0.1 | root | | mysql_native_password | | N |  | ::1 | root | | mysql_native_password | | N |  | localhost | | | mysql_native_password | NULL | N |  | mysql56 | | | mysql_native_password | NULL | N |  +-----------+------+----------+-----------------------+-----------------------+------------------+  6 rows in set (0.00 sec) 

SLIDE 12

#PerconaLive

How are passwords stored in MySQL? (5.7)

mysql57 >SELECT /* 5.7 */ host, user, plugin, authentication_string, password_expired, password_last_changed, password_lifetime, account_locked FROM mysql.user;  +-----------+---------------+-----------------------+------------------------------------------- +------------------+-----------------------+-------------------+----------------+  | host | user | plugin | authentication_string | password_expired | password_last_changed | password_lifetime | account_locked |  +-----------+---------------+-----------------------+------------------------------------------- +------------------+-----------------------+-------------------+----------------+  | localhost | root | mysql_native_password | *E89C1DBB80A00976B61D19025C3081E4B190D8BE | N | 2017-09-03 18:45:43 | NULL | N |  | localhost | mysql.session | mysql_native_password | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | N | 2017-09-03 18:42:33 | NULL | Y |  | localhost | mysql.sys | mysql_native_password | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | N | 2017-09-03 18:42:33 | NULL | Y |  +-----------+---------------+-----------------------+------------------------------------------- +------------------+-----------------------+-------------------+----------------+  3 rows in set (0.01 sec) 

SLIDE 13

#PerconaLive

How are passwords stored in MySQL? (8.0)

mysql> SELECT /* 8.0 */ host, user, plugin, authentication_string, password_expired, password_last_changed, password_lifetime, account_locked FROM mysql.user; +-----------+------------------+-----------------------+------------------------------------------------------------------------ +------------------+-----------------------+-------------------+----------------+ | host | user | plugin | authentication_string | password_expired | password_last_changed | password_lifetime | account_locked | +-----------+------------------+-----------------------+------------------------------------------------------------------------ +------------------+-----------------------+-------------------+----------------+ | localhost | mysql.infoschema | mysql_native_password | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | N | 2018-04-25 13:04:15 | NULL | Y | | localhost | mysql.session | mysql_native_password | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | N | 2018-04-25 13:04:15 | NULL | Y | | localhost | mysql.sys | mysql_native_password | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | N | 2018-04-25 13:04:15 | NULL | Y | | localhost | root | caching_sha2_password | $A$005$hqy-OG+.:|qsypaH/HS.i19CInGfOtklCz3kyo4cZxqCFy2bEHcogi6/ | N | 2018-04-25 13:04:19 | NULL | N | +-----------+------------------+-----------------------+------------------------------------------------------------------------ +------------------+-----------------------+-------------------+----------------+ 4 rows in set (0.00 sec)

SLIDE 14

#PerconaLive

Minimum password policy (5.7+)

mysql> alter user root identified by 'percona'; ERROR 1819 (HY000): Your password does not satisfy the current policy requirements mysql> select @@validate_password_policy; +----------------------------+ | @@validate_password_policy | +----------------------------+ | LOW | +----------------------------+ 1 row in set (0.00 sec)

https://dev.mysql.com/doc/refman/5.7/en/validate-password-options- variables.html#sysvar_validate_password_policy

SLIDE 15

#PerconaLive

VALIDATE_PASSWORD_STRENGTH()

mysql> select validate_password_strength('percona'); +---------------------------------------+ | validate_password_strength('percona') | +---------------------------------------+ | 25 | +---------------------------------------+ 1 row in set (0.00 sec) mysql> select validate_password_strength('Percona.123'); +-------------------------------------------+ | validate_password_strength('Percona.123') | +-------------------------------------------+ | 100 | +-------------------------------------------+ 1 row in set (0.00 sec)

SLIDE 16

#PerconaLive

VALIDATE_PASSWORD_STRENGTH()

https://dev.mysql.com/doc/refman/5.7/en/encryption-functions.html#function_validate-password-strength

SLIDE 17

#PerconaLive

mysql_native_password format (deprecated in 5.7)

mysql56 > SELECT PASSWORD('test123') AS pwd;  +-------------------------------------------+  | pwd |  +-------------------------------------------+  | *676243218923905CF94CB52A3C9D3EB30CE8E20D |  +-------------------------------------------+  1 row in set (0.00 sec)    mysql57 > SELECT PASSWORD('test123') AS pwd;  +-------------------------------------------+  | pwd |  +-------------------------------------------+  | *676243218923905CF94CB52A3C9D3EB30CE8E20D |  +-------------------------------------------+  1 row in set, 1 warning (0.00 sec)  https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_old_passwords https://dev.mysql.com/doc/refman/5.7/en/password-hashing.html

SLIDE 18

#PerconaLive

Security Hardening 101

Set a password for ‘root’
Remove anonymous users
SELECT host, user, password FROM mysql.user WHERE user=‘’;
SELECT host, user, authentication_string FROM mysql.user WHERE

user=‘’;

Remove the ‘test’ database
All the above is taken care of with mysql_secure_installation
8.0 is awesome in the sense that the test database is not there by default,

you can turn on the VALIDATE_PASSWORD_PLUGIN(), and so on

SLIDE 19

#PerconaLive

Why are anonymous users bad?

mysql55 >USE test; mysql55 >CREATE TABLE t1(i1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY, v1 VARCHAR(100) NOT NULL); mysql55 >INSERT INTO t1(i1, v1) VALUES (1, REPEAT('abcde',20)); mysql55 >INSERT INTO t1(i1, v1) SELECT NULL, a.v1 FROM t1 a, t1 b, t1 c; Query OK, 1 row affected (0.00 sec) mysql55 >INSERT INTO t1(i1, v1) SELECT NULL, a.v1 FROM t1 a, t1 b, t1 c; Query OK, 8 rows affected (0.00 sec) mysql55 >INSERT INTO t1(i1, v1) SELECT NULL, a.v1 FROM t1 a, t1 b, t1 c; Query OK, 1000 rows affected (0.02 sec) # Does it return in your VM, or fill your host disk?

Be Destructive

SLIDE 20

#PerconaLive

MySQL 5.6 improvements

Password expiry - ALTER USER 'foo'@'localhost' PASSWORD EXPIRE;
Password validation plugin - VALIDATE_PASSWORD_STRENGTH()
mysql_config_editor - store authentication credentials in an encrypted login path file

named .mylogin.cnf

○ http://dev.mysql.com/doc/refman/5.6/en/mysql-config-editor.html

Random ‘root’ password on install

○ mysql_install_db —random-passwords ○ cat $HOME/.mysql_secret

SLIDE 21

#PerconaLive

PASSWORD EXPIRE

$ export MYSQL_PS1="\u@\h [\d]> " $ mysql -uroot -p root@localhost [(none)]> CREATE USER demo IDENTIFIED BY 'passw0rd1'; $ mysql -udemo -p #passw0rd1 $ mysql -uroot root@localhost [(none)]> ALTER USER demo PASSWORD EXPIRE; $ mysql -udemo -p #passw0rd1 demo@localhost [(none)]> # No issue connecting demo@localhost [(none)]> USE test; ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement. # can reset password to current value demo@localhost [(none)]> ALTER USER demo IDENTIFIED BY 'passw0rd1';

SLIDE 22

#PerconaLive

MySQL 5.7 improvements

Improved password expiry — automatic password expiration available, so set

default_password_lifetime in my.cnf

You can also require password to be changed every n-days

○ ALTER USER foo@localhost PASSWORD EXPIRE INTERVAL n DAY;

PASSWORD EXPIRE DEFAULT | NEVER options
There is also account locking/unlocking now

○ ALTER USER foo@host ACCOUNT LOCK | UNLOCK; https://dev.mysql.com/doc/refman/5.7/en/alter-user.html

SLIDE 23

#PerconaLive

MariaDB Server passwords

Password validation plugin

○ https://mariadb.com/kb/en/mariadb/development/mariadb-internals-documentation/password-validation/

simple_password_check password validation plugin

○ can enforce a minimum password length and guarantee that a password contains at least a specified number of uppercase and lowercase letters, digits, and punctuation characters.

cracklib_password_check password validation plugin

○ Allows passwords that are strong enough to pass CrackLib test. This is the same test that pam_cracklib.so does ○ SET PASSWORD FOR 'bob'@'%.loc.gov' = PASSWORD('abc');

SLIDE 24

#PerconaLive

Authentication in MySQL/MariaDB

auth_socket - Authenticates against the Unix socket file, using so_peercred
sha256_password - default-authentication-plugin=sha256_password, passwords never exposed as

cleartext when connecting; SSL or RSA auth ○ caching_sha256_password

ed25519 - Elliptic Curve Digital Signature Algorithm, same as OpenSSH
Kerberos/GSSAPI/SSPI - User principals: <username>@<KERBEROS REALM>
Active Directory (Enterprise only)
mysql_no_login ( MySQL 5.7 ) - prevents all client connections to an account that uses it

SLIDE 25

#PerconaLive

SLIDE 26

#PerconaLive

Secure Communications

SSL for replication
SSL for client connections
SSL for admin connections
Encryption on the wire

https://dev.mysql.com/doc/refman/5.6/en/secure-connections.html https://dev.mysql.com/doc/refman/5.7/en/secure-connections.html

SLIDE 27

#PerconaLive

Default client connection traffic (5.6)

mysql56$ sudo ngrep -d eth1 -wi -P ' ' -W single -l port 3306 interface: eth1 (192.168.42.0/255.255.255.0) filter: ( port 3306 ) and ((ip || ip6) || (vlan && (ip || ip6))) host$ mysql -uexternal -p -h192.168.42.16 host> select 'unencrypted'; # T 192.168.42.1:47634 -> 192.168.42.16:3306 [AP] select 'unencrypted' # T 192.168.42.16:3306 -> 192.168.42.1:47634 [AP] ! def unencrypted ! ! unencrypted   https://wiki.christophchamp.com/index.php?title=Ngrep http://infoheap.com/ngrep-quick-start-guide/

SLIDE 28

#PerconaLive

Default client connection traffic (5.7)

mysql57$ sudo ngrep -d eth1 -wi -P ' ' -W single -l port 3306  interface: eth1 (192.168.42.0/255.255.255.0)  filter: ( port 3306 ) and ((ip || ip6) || (vlan && (ip || ip6)))    host$ mysql -uexternal -p -h192.168.42.17    host> select 'encrypted';    T 192.168.42.1:36781 -> 192.168.42.17:3306 [AP] @ F l d iVr H b ^ s t Z ( 2d " ? | )  #  T 192.168.42.17:3306 -> 192.168.42.1:36781 [AP] p% s` 3u5!%P] v= r # x E a y '! )Z 8 Js z. \t (r H@ 0 2 5k\ < M @)E& b q|q@ h 

SLIDE 29

#PerconaLive

5.7 non-SSL client connection traffic

host$ > mysql -uexternal -p -h192.168.42.17 --ssl=0 host >select '-ssl=0 unencrypted'; T 192.168.42.1:36785 -> 192.168.42.17:3306 [AP] select '-ssl=0 unencrypted' # T 192.168.42.17:3306 -> 192.168.42.1:36785 [AP] ' def -ssl=0 unencrypted ! 3 - ssl=0 unencrypted

SLIDE 30

#PerconaLive

SSL system variables

SLIDE 31

#PerconaLive

Secure Communications

[mysqld]  ssl-ca=ca.pem  ssl-cert=server-cert.pem  ssl-key=server-key.pem

SLIDE 32

#PerconaLive

SSL Protocols and Ciphers

SLIDE 33

#PerconaLive

SSL Client Connections

https://dev.mysql.com/doc/connector-python/en/connector-python-connectargs.html

import mysql.connector  from mysql.connector.constants import ClientFlag    config = {  'user': 'ssluser',  'password': 'asecret',  'host': '127.0.0.1',  'client_flags': [ClientFlag.SSL],  'ssl_ca': '/opt/mysql/ssl/ca.pem',  'ssl_cert': '/opt/mysql/ssl/client-cert.pem',  'ssl_key': '/opt/mysql/ssl/client-key.pem',  }

https://dev.mysql.com/doc/connectors/en/connector-net-tutorials- ssl.html

SLIDE 34

#PerconaLive

Secure Connections

mysql_ssl_rsa_setup in MySQL 5.7
This program creates the SSL certificate and key files and RSA

key-pair files required to support secure connections using SSL and secure password exchange using RSA over unencrypted connections, if those files are missing.

use the openssl command

SLIDE 35

#PerconaLive

Cloud SSL

Rackspace - MySQL has GRANT modifier, REQUIRE SSL
GRANT SELECT, INSERT, UPDATE, DELETE ON mydatabase.* TO 'user'@'%' REQUIRE SSL;
Amazon - ssl-verify-server-cert so SSL connection verifies the DB instance endpoint against the

endpoint in the SSL certificate

mysql \ 

h myinstance.123456789012.us-east-1.rds.amazonaws.com \ 
-ssl-ca=rds-ca-2015-root.pem \ 
-ssl-verify-server-cert
Google - max 10 certs/instance

mysql --ssl-ca=server-ca.pem --ssl-cert=client-cert.pem --ssl-key=client-key.pem \ 

-host=[INSTANCE_IP] --user=root --password

SLIDE 36

#PerconaLive

SSL notes

MySQL clients shipped pre-5.7.3 consider the --ssl option just as advise.
It silently falls back to unencrypted connections if the server doesn’t accept encrypted

connections!

You have to configure the instance so that only SSL connections can connect to it.
Also… Client-side --ssl option is deprecated as of MySQL 5.7.11 and is removed in MySQL

8.0. For client programs, use --ssl-mode instead. So --ssl-mode=REQUIRED is preferred

This matters - your “mysql” client could be from MariaDB Server which is different!
SSL libraries for MySQL Community and Enterprise are different; also worth noting some are

linked statically, some are dynamic

SLIDE 37

#PerconaLive

Stronger passwords (sha256)

mysql> CREATE USER bernie@localhost IDENTIFIED WITH sha256_password; mysql> select host,user,password,plugin,authentication_string,password_expired from mysql.user where user='bernie'\G *************************** 1. row *************************** host: localhost user: bernie password: plugin: sha256_password authentication_string: password_expired: N mysql> SET old_passwords=2; mysql> SET PASSWORD FOR bernie@localhost = PASSWORD('<clear-text-password>''); mysql> select host,user,password,plugin,authentication_string,password_expired from mysql.user where user='bernie'\G *************************** 1. row *************************** host: localhost user: bernie password: plugin: sha256_password authentication_string: $5$ln^h{z\d{JSj}FB$9TF1X0S.Ts4lBSOdsx7p86F4OxRL8ataFok6hyPgew/ password_expired: N

SLIDE 38

#PerconaLive

PAM

Percona PAM authentication plugin (5.6+)
Open Source, runs community also
MySQL Enterprise PAM authentication (5.5+)
Commercial license
MariaDB PAM Authentication (5.2+)

https://www.percona.com/doc/percona-pam-for-mysql/index.html  https://dev.mysql.com/doc/refman/5.5/en/pam-pluggable-authentication.html  https://mariadb.com/kb/en/the-mariadb-library/authentication-plugin-pam/

SLIDE 39

#PerconaLive

Configuring PAM (Demo only)

echo "auth required pam_warn.so auth required pam_unix.so audit account required pam_unix.so audit" | sudo tee /etc/pam.d/mysqld # DO NOT DO THIS sudo groupadd shadow sudo usermod -G shadow mysql grep shadow /etc/group sudo chgrp shadow /etc/shadow sudo chmod 440 /etc/shadow sudo systemctl restart mysqld.service

DO NOT DO THIS

SLIDE 40

#PerconaLive

Installing PAM Plugin

percona56 >SELECT plugin_name FROM information_schema.plugins WHERE plugin_type='AUTHENTICATION'; +-----------------------+ | mysql_native_password | | mysql_old_password | | sha256_password | +-----------------------+ percona56 >INSTALL PLUGIN auth_pam SONAME 'auth_pam.so'; percona56 >SELECT plugin_name FROM information_schema.plugins WHERE plugin_type='AUTHENTICATION'; +-----------------------+ | plugin_name | +-----------------------+ | mysql_native_password | | mysql_old_password | | sha256_password | | auth_pam | +-----------------------+ 4 rows in set (0.00 sec)

SLIDE 41

#PerconaLive

Demonstrating PAM

percona56 >CREATE USER external@localhost IDENTIFIED WITH auth_pam; $ mysql -uexternal Password: ERROR 1045 (28000): Access denied for user 'external'@'localhost' (using password: YES) $ sudo useradd external $ sudo passwd external $ mysql -uexternal -p -Ne "SELECT USER()" +--------------------+ | external@localhost | +--------------------+ mysql > SELECT /* Use 5.6 */ host, user, password, plugin, authentication_string, password_expired FROM mysql.user WHERE user='external'\G *************************** 1. row *************************** host: localhost user: external password: plugin: auth_pam authentication_string: password_expired: N 1 row in set (0.00 sec)

SLIDE 42

#PerconaLive

MariaDB Authentication Plugins

edd25519 Authentication (10.1)

Elliptic Curve Digital Signature Algorithm

GSSAPI Authentication (10.1)

usually synonymous with Kerberos (*NIX)

Named Pipe (10.1)

Windows

Unix Socket (10.1)

https://mariadb.com/kb/en/the-mariadb-library/password-authentication-and-encryption-plugins/

SLIDE 43

#PerconaLive

ed25519 Authentication (MariaDB 10.1+) 

maria101 > INSTALL SONAME 'auth_ed25519';  maria101 > CREATE FUNCTION ed25519_password RETURNS STRING SONAME "auth_ed25519.so";    maria101 > SELECT plugin_name FROM information_schema.plugins WHERE plugin_type='AUTHENTICATION';  +-----------------------+  | mysql_native_password |  | mysql_old_password |  | ed25519 |  +-----------------------+    maria101 > SELECT ed25519_password("<clear-text-passwd>") AS pwd;  +---------------------------------------------+  | pwd |  +---------------------------------------------+  | CobnFnV6aei4yy55u90XPFUeBRMBSPtZazzJq8kLHNE |  +---------------------------------------------+    maria101 > CREATE USER demo@localhost IDENTIFIED VIA ed25519 USING 'CobnFnV6aei4yy55u90XPFUeBRMBSPtZazzJq8kLHNE'; 

SLIDE 44

#PerconaLive

Unix Socket Authentication (MariaDB 10.1) 

maria101 > INSTALL PLUGIN unix_socket SONAME 'auth_socket'; maria101 > SELECT plugin_name FROM information_schema.plugins WHERE plugin_type='AUTHENTICATION'; +-----------------------+ | mysql_native_password | | mysql_old_password | | ed25519 | | unix_socket | +-----------------------+ maria101 > CREATE USER vagrant IDENTIFIED VIA unix_socket; $ whoami vagrant $ mysql -e "SELECT USER(),CURRENT_USER()" +-------------------+----------------+ | USER() | CURRENT_USER() | +-------------------+----------------+ | vagrant@localhost | @localhost | +-------------------+----------------+ root@maria101 > CREATE USER notvagrant IDENTIFIED VIA unix_socket; $ mysql -unotvagrant ERROR 1698 (28000): Access denied for user 'notvagrant'@'localhost'

SLIDE 45

#PerconaLive

Auditing Plugin Options

McAfee (5.1+)
Percona (5.5)
MariaDB (10.0)
MySQL Enterprise (5.5)
Installation
Configuration
Log format

SLIDE 46

#PerconaLive

Installing McAfee Plugin (on Percona 5.6)

https://github.com/mcafee/mysql-audit/wiki/Installation

wget https://dl.bintray.com/mcafee/mysql-audit-plugin/:audit-plugin-mysql-5.6-1.1.5-742-linux-x86_64.zip unzip audit-plugin-mysql-5.6-1.1.5-742-linux-x86_64.zip cd audit-plugin-mysql-5.6-1.1.5-742/ sudo cp lib/libaudit_plugin.so /usr/lib64/mysql/plugin/ sudo chmod +w /usr/lib64/mysql/plugin/libaudit_plugin.so wget https://raw.github.com/mcafee/mysql-audit/master/offset-extract/offset-extract.sh chmod +x offset-extract.sh sudo yum install -y gdb ./offset-extract.sh `which mysqld` sudo vi /etc/my.cnf plugin-load=AUDIT=libaudit_plugin.so audit_offsets=6992, 7040, 4000, 4520, 72, 2704, 96, 0, 32, 104, 136, 7128, 4392, 2800, 2808, 2812, 536, 0, 0, 6360, 6384, 6368, 13048, 548, 516 sudo service mysqld restart

SLIDE 47

#PerconaLive

Installing Audit plugin (Enterprise 5.7) 

enterprise57 > SHOW GLOBAL VARIABLES LIKE 'audit%';  Empty set (0.01 sec)    enterprise57 $ mysql -uroot -p < /usr/share/mysql/audit_log_filter_linux_install.sql  Result  OK    enterprise57 > SELECT PLUGIN_NAME, PLUGIN_STATUS   FROM INFORMATION_SCHEMA.PLUGINS   WHERE PLUGIN_NAME LIKE 'audit%';  +-------------+---------------+  | PLUGIN_NAME | PLUGIN_STATUS |  +-------------+---------------+  | audit_log | ACTIVE |  +-------------+---------------+  1 row in set (0.00 sec) 

https://dev.mysql.com/doc/refman/5.5/en/audit-log.html

SLIDE 48

#PerconaLive

Installing Percona Audit Plugin 

percona56 $ mysql -uroot    percona56 >INSTALL PLUGIN audit_log SONAME 'audit_log.so';    percona56 >SELECT PLUGIN_NAME, PLUGIN_STATUS FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_NAME LIKE 'audit%';  +-------------+---------------+  | PLUGIN_NAME | PLUGIN_STATUS |  +-------------+---------------+  | audit_log | ACTIVE |  +-------------+---------------+   

https://www.percona.com/doc/percona-server/5.5/management/audit_log_plugin.html https://www.percona.com/blog/2014/05/16/introduction-to-the-percona-mysql-audit-log-plugin/

SLIDE 49

#PerconaLive

Installing MariaDB Audit Plugin

maria101 > INSTALL PLUGIN server_audit SONAME 'server_audit.so';  maria101 > SELECT PLUGIN_NAME, PLUGIN_STATUS FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_TYPE='AUDIT';  +--------------+---------------+  | PLUGIN_NAME | PLUGIN_STATUS |  +--------------+---------------+  | SERVER_AUDIT | ACTIVE |  +--------------+---------------+ maria101 > SET GLOBAL server_audit_events = 'CONNECT,QUERY,TABLE'; maria101 > SET GLOBAL server_audit_logging=ON; 

https://mariadb.com/kb/en/mariadb/about-the-mariadb-audit-plugin/

SLIDE 50

#PerconaLive

Audit Log Formats

OLD (XML)
NEW (XML)
JSON
CSV

https://dev.mysql.com/doc/refman/5.5/en/audit-log-file.html

SLIDE 51

#PerconaLive

OLD/NEW Format

$ sudo tail /var/lib/mysql/audit.log <AUDIT_RECORD NAME="Connect" RECORD="9_2017-09-04T21:40:19" TIMESTAMP="2017-09-04T21:45:34 UTC" CONNECTION_ID="13" STATUS="1045" USER="root" PRIV_USER="root" OS_LOGIN="" PROXY_USER="" HOST="localhost" IP="" DB="" /> $ sudo tail /var/lib/mysql/audit.log <AUDIT_RECORD> <NAME>Quit</NAME> <RECORD>1358_2017-09-25T12:59:46</RECORD> <TIMESTAMP>2017-09-25T12:59:49 UTC</ TIMESTAMP> <CONNECTION_ID>1</CONNECTION_ID> <STATUS>0</STATUS> <USER>root</USER> <PRIV_USER>root</PRIV_USER> <OS_LOGIN></OS_LOGIN> <PROXY_USER></PROXY_USER> <HOST>localhost</HOST> <IP></IP> <DB></DB> </AUDIT_RECORD>

SLIDE 52

#PerconaLive

JSON/CSV Format

sudo tail /var/lib/mysql/audit.log {"audit_record":{"name":"Ping","record":"3417_2017-09-25T13:37:27","timestamp":"2017-09-25T13:37:29 UTC","command_class":"error","connection_id":"1","status":0,"sqltext":"","user":"root[root] @ localhost []","host":"localhost","os_user":"","ip":"","db":""}} {"audit_record":{"name":"Quit","record":"3418_2017-09-25T13:37:27","timestamp":"2017-09-25T13:37:29 UTC","connection_id":"1","status": 0,"user":"root","priv_user":"root","os_login":"","proxy_user":"","host":"localhost","ip":"","db":""}} sudo tail /var/lib/mysql/audit.log "Connect","4915_2017-09-25T13:39:34","2017-09-25T13:39:38 UTC","1", 0,"root","root","","","localhost","","" "Ping","4916_2017-09-25T13:39:34","2017-09-25T13:39:38 UTC","error","1",0,"","root[root] @ localhost []","localhost","","",""

SLIDE 53

#PerconaLive

MariaDB Audit Log Format

$ sudo cat /var/lib/mysql/server_audit.log  20170912 02:02:15,maria101,root,localhost,3,11,QUERY,,'SET GLOBAL server_audit_logging=ON',0  20170912 02:02:39,maria101,root,localhost,3,0,DISCONNECT,,,0  20170912 02:02:40,maria101,root,localhost,4,0,CONNECT,,,0  20170912 02:02:40,maria101,root,localhost,4,13,QUERY,,'select @@version_comment limit 1',0  20170912 02:02:40,maria101,root,localhost,4,14,QUERY,,'select USER()',0  20170912 02:02:42,maria101,root,localhost,4,0,DISCONNECT,,,0  20170912 02:02:45,maria101,root,localhost,5,0,FAILED_CONNECT,,,1045  20170912 02:02:45,maria101,root,localhost,5,0,DISCONNECT,,,0  https://mariadb.com/kb/en/library/mariadb-audit-plugin-log-format/

SLIDE 54

#PerconaLive

Auditing Implementation

MariaDB Server
User filtering as an additional feature via audit API extensions
Query cache enabled? No table records
Percona
Multiple output formats: OLD, NEW, JSON, CSV
Filter by user, SQL command type, database,
Auditing can be expensive, so asynchronous/performance/semisynchronous/synchronous modes for logging - e.g.

log using memory buffers, drop messages if buffers are full, or log directly to file, flush and sync at events, etc.

McAfee Audit plugin
Uses offsets
MySQL Enterprise Audit Plugin (utility: mysqlauditgrep)

SLIDE 55

#PerconaLive

Secure Storage

Encryption of data at rest
Data (table vs tablespace)
Binary Logs
Other Logs
Key management

SLIDE 56

#PerconaLive

Encryption in MariaDB Server

Encryption: tablespace OR table level encryption with support for rolling

keys using the AES algorithm (only with a keyserver)

table encryption — PAGE_ENCRYPTION=1
tablespace encryption — encrypts everything including log files
file_key_management_filename, file_key_management_filekey,

file_key_management_encryption_algorithm

Documented — https://mariadb.com/kb/en/mariadb/data-at-rest-

encryption/

Tablespace/logs scrubbing: background process that regularly scans

through the tables and upgrades the encryption keys

--encrypt-tmp-files & --encrypt-binlog

SLIDE 57

#PerconaLive

Encryption in MariaDB Server II

[mysqld] plugin-load-add=file_key_management.so file-key-management file-key-management-filename = /home/mdb/keys.enc innodb-encrypt-tables innodb-encrypt-log innodb-encryption-threads=4 aria-encrypt-tables=1 # PAGE row format encrypt-tmp-disk-tables=1 # this is for Aria CREATE TABLE customer ( customer_id bigint not null primary key, customer_name varchar(80), customer_creditcard varchar(20)) ENGINE=InnoDB page_encryption=1 page_encryption_key=1;

SLIDE 58

#PerconaLive

Encryption in MariaDB Server III

Use the preset! - /etc/my.cnf.d/enable_encryption.preset
A plugin for Amazon Key Management Server (KMS)
mysqlbinlog has no way to read (i.e. decrypt) an encrypted binlog
This does not work with MariaDB Galera Cluster yet (gcache is not

encrypted yet), and also Percona XtraBackup needs additional work (i.e. if you encrypt the redo log)

Recommended backup method: MariaDB Backup (fork of Percona XtraBackup)

SLIDE 59

#PerconaLive

Encryption in MySQL

MySQL 5.7.11 introduces InnoDB tablespace encryption
early-plugin-load=keyring_file.so in my.cnf
Must use innodb_file_per_table
Convert via ALTER TABLE table ENCRYPTION=‘Y’
Data is not encrypted in the redo/undo/binary logs
Has external key management (Oracle Key Vault)

SLIDE 60

#PerconaLive

Key management in MySQL

MySQL 5.7.11 and higher includes a keyring plugin, keyring_file, that stores keyring

data in a file local to the server host.

MySQL 5.7.12 and higher includes keyring_okv, a KMIP 1.1 plugin for use with KMIP-

compatible back end keyring storage products such as Oracle Key Vault and Gemalto SafeNet KeySecure Appliance. (Enterprise)

MySQL 5.7.19 and higher includes keyring_aws, a plugin that communicates with the

Amazon Web Services Key Management Service for key generation and uses a local file for key storage. (Enterprise)

MySQL 5.7.13 and higher includes an SQL interface for keyring key management,

implemented as a set of user-defined functions (UDFs).

Percona Server for MySQL 5.7.20-18 - Vault - https://www.percona.com/doc/percona-

server/LATEST/management/data_at_rest_encryption.html

SLIDE 61

#PerconaLive

Encryption conclusions

InnoDB Tablespace Encryption: MySQL & Percona Server 5.7
MySQL Enterprise 5.7: Transparent Data Encryption

▪ MySQL Enterprise TDE uses a two-tier encryption key architecture, consisting of a master encryption key and tablespace keys, which provides easy key management and rotation.

Google patches for InnoDB/XtraDB/Aria (tmptable) tablespace encryption: MariaDB Server 10.1+
Percona Server for MySQL keyring_vault is good - https://www.percona.com/doc/percona-server/LATEST/

management/data_at_rest_encryption.html#keyring-vault-plugin

Eperi patch for InnoDB/XtraDB table encryption: MariaDB Server 10.1+
AWS KMS: MariaDB Server 10.2+ (in 10.1 you have to compile it)
Linux Unified Key Setup (LUKS)
Column encryption doesn’t exist today
Other solutions include Penta Security’s MyDiamo
Cloud? Amazon has its own encryption + KMS + IAM. Google uses AES-256, with symmetric keys. Rackspace

has a MariaDB Server encryption option.

SLIDE 62

#PerconaLive

SQL Standard Roles

Bundles users together, with similar privileges - follows the SQL standard
MariaDB Server 10.0 (10.1 adds that each user can have a DEFAULT ROLE)
MySQL 8.0

CREATE ROLE audit_bean_counters; GRANT SELECT ON accounts.* to audit_bean_counters; GRANT audit_bean_counters to ceo; https://mariadb.com/kb/en/mariadb/roles_overview/ https://dev.mysql.com/doc/refman/8.0/en/roles.html

SLIDE 63

#PerconaLive

Thank You Sponsors!!

SLIDE 64