© MariaDB Corpora,on Ab. 20.11.15 1
MariaDB Roadshow Bern 2015
MariaDB 10.1: Datenbankverschlüsselung und andere Sicherheitsvorteile
Ralf Gebhardt
© MariaDB Corpora,on Ab.
Agenda
• MariaDB 10.1 New Features • For High Availability • For Scalability • For Security
• MariaDB 10.1 Security Feature Set
20.11.15 2
© MariaDB Corpora,on Ab.
MariaDB 10.1 Released
• First GA version 10.1.8 released this week • Based on MariaDB 10.0 • Includes contribu,ons from community members like Facebook
20.11.15 3
© MariaDB Corpora,on Ab.
MariaDB 10.1 Themes
Security High Availability
Scalability
20.11.15 4
© MariaDB Corpora,on Ab. 20.11.15 5
High Availability
© MariaDB Corpora,on Ab.
Galera Cluster integrated
• Full integra,on of Galera Cluster into MariaDB 10.1 — it is not a separate download
• Enable Galera Cluster when you need it
20.11.15 6
© MariaDB Corpora,on Ab.
Galera Custer integrated
• Per default MariaDB 10.1 works like a vanilla MariaDB Server • In order to use Galera Cluster you need to set the following:
• wrep_on = ON • wsrep_provider • wsrep_cluster_address • binlog_format=ROW • default_storage_engine=InnoDB • innodb_autoinc_lock_mode=2 • innodb_doublewrite=1 • query_cache_size=0
20.11.15 7
© MariaDB Corpora,on Ab. 20.11.15 8
Scalability
© MariaDB Corpora,on Ab.
Parallel Slave Replica,on (10.0)
• Mul,-‐source replica,on from different masters (domains) executed in parallel
• Queries that are run in parallel on the master are run in parallel on the slave (based on group commit)
• Transac,ons modifying the same table can be updated in parallel on the slave!
• Supports both statement based and row based replica,on.
20.11.15 9
© MariaDB Corpora,on Ab.
Op,mis,c Parallel Replica,on
• New replicaton mode in MariaDB 10.1 • Any INSERT, UPDATE or DELETE can be applied in parallel on the slave
• Even if it wasn't commibed in parallell on the master • Needs a 10.1 master • Needs a transac,onal engine for rollback in case of a conflict
20.11.15 10
© MariaDB Corpora,on Ab.
Op,mis,c Parallel Replica,on
• Enabled by slave-parallel-mode=optimistic
• Temporarily disable by variable @@skip_parallel_replication
• Server op,mis,cally assumes that few conflicts will occur
• roll back and retry for conflic,ng transac,ons
20.11.15 11
© MariaDB Corpora,on Ab.
Performance Improvements
• Especially for High-‐End Servers • High processing power • More cores
• Benchmark 10.1 on Linux Only POWER8 • „1 million SQL queries per second: GA MariaDB 10.1 on POWER8“
• hbps://blog.mariadb.org/10-‐1-‐mio-‐qps/
20.11.15 12
© MariaDB Corpora,on Ab.
InnoDB Defragmenta,on
• Deleted records can create gaps on pages • Defragmenta,on based on an implementa,on from Facebook and Kakao Corp
• Neither new SQL literals nor server changes needed
• OPTIMIZE TABLE is used • innodb_defragment=1
20.11.15 13
© MariaDB Corpora,on Ab.
MySQL Compa,bility Feature
• MariaDB 10.1 can be a slave to MySQL 5.6 • Also when GTIDs are used
• Feature was requested by the Community • To test MariaDB in a MySQL deployment • For migra,ng to MariaDB
20.11.15 14
© MariaDB Corpora,on Ab. 20.11.15 15
Security
© MariaDB Corpora,on Ab.
Security
Encryption
Auditing Authentication
Validation
20.11.15 16
© MariaDB Corpora,on Ab.
Security Features in MariaDB 10.1
Encryption
Auditing Authentication
Validation
20.11.15 17
• Data at Rest Encryp,on • Password Valida,on Plugin • PAM Authen,ca,on Plugin • Audit Plugin • SSL Connec,ons • Encryp,on func,ons
© MariaDB Corpora,on Ab.
Data at Rest Encryp,on
• New with MariaDB 10.1 • Originates from Google encryp,on patch • Table space and table encryp,on • Based on
• Encyp,on key • Key id • Key rota,on • Key version
20.11.15 18
© MariaDB Corpora,on Ab.
Data at Rest Encyp,on
• Encryp,on for • XtraDB/InnoDB tablespaces • XtraDB/InnoDB log files • Binary logs • Aria tables • Temporary files
• No Encryp,on for • Metadata • Memory • Config-‐Files
20.11.15 19
© MariaDB Corpora,on Ab.
Data at Rest Encryp,on
• Last internal benchmarks on encryp,on overhead
• XtraDB/InnoDB encryp,on • <1% (ro) • ≈8-‐14% (rw)
• Temporary files encryp,on • ≈7-‐10% (filesort) • Binary log encryp,on: <4%
20.11.15 20
© MariaDB Corpora,on Ab.
Deleted Data Encryp,on
• Scrubbing • Background threads periodically scan tablespaces and logs and overwrite all data that should be deleted.
• More info: • hbps://mariadb.com/kb/en/mariadb/xtradb-‐innodb-‐data-‐scrubbing/
20.11.15 21
© MariaDB Corpora,on Ab.
Password Valida,on Plugins
• Password valida,on plugin API • simple_password_check plugin
• Can enforce a minimum password length and guarantee that a password contains at least a specified number of upper and lowercase lebers, digits, and punctua,on characters
• cracklib_password_check plugin • A widely used library • Stop users from choosing easy to guess passwords. It includes checks for not allowing passwords based on the username or a dic,onary word etc.
20.11.15 22
© MariaDB Corpora,on Ab.
PAM Authen,ca,on Plugin
• Authen,ca,on using /etc/shadow • Authen,ca,on using LDAP, SSH pass phrases, password expira,on, username mapping, logging every login abempt, etc…
• INSTALL PLUGIN pam SONAME 'auth_pam.so'; • CREATE USER foo@host IDENTIFIED via pam; • REMEMBER to configure PAM (/etc/pam.d or /etc/pam.conf)
20.11.15 23
© MariaDB Corpora,on Ab.
MariaDB Audit Plugin
• Audi,ng database access to • File (comma delimited format) • Syslog
• Modified Plugin API in MariaDB • Audit Plugin compa,ble with MySQL Server
• Only MariaDB allows to monitor table level events
20.11.15 24
© MariaDB Corpora,on Ab.
MariaDB Audit Plugin
20.11.15 25
CONNECTION
QUERY
CONNECT
DDL
DISCONNECT
FAILED CONNECT
DML+TCL
OBJECT DATABASE
TABLES
TIMESTAMP HOST USER
SESSION
DCL
© MariaDB Corpora,on Ab.
MariaDB Audit Plugin
• Password filtering included
20.11.15 26
20150117 23:40:56,MYSQL5530,root,localhost,1,1,QUERY,,'CREATE USER "test1"@"localhost" IDENTIFIED BY *****',020150117 23:40:56,MYSQL5530,root,localhost,1,1,QUERY,,'CREATE USER "test4"@"localhost" IDENTIFIED BY PASSWORD *****',020150117 23:40:56,MYSQL5530,root,localhost,1,1,QUERY,,'INSERT INTO t_pwdtest VALUES (1,PASSWORD("mypwd"))',020150117 23:40:56,MYSQL5530,root,localhost,1,1,QUERY,,'UPDATE t_pwdtest SET mypwd = PASSWORD("mynewpwd")',020150117 23:40:56,MYSQL5530,root,localhost,1,1,QUERY,,'INSERT INTO t_pwdtest VALUES (2,OLD_PASSWORD("mypwd2"))',020150117 23:40:56,MYSQL5530,root,localhost,1,1,QUERY,,'UPDATE t_pwdtest SET mypwd = OLD_PASSWORD("mynewpwd2")',020150117 23:40:56,MYSQL5530,root,localhost,1,1,QUERY,,'GRANT ALL ON *.* TO "test5"@"localhost" IDENTIFIED BY *****',0
© MariaDB Corpora,on Ab.
SSL Connec,ons
• Encry,on between client and server • Disabled by default
• TLSv1.2 protocol • SSL also available for replica,on • Variables needed to use SSL
• ssl-‐ca=ca.pem • ssl-‐cert=server-‐cert.pem • ssl-‐key=server-‐key.pem
20.11.15 27
© MariaDB Corpora,on Ab.
Encryp,on Func,ons
• Encry,on func,ons are used per column • Available encryp,ons
• AES (Advanced Encryp,on Standard) algorithm • DES (Data Encryp,on Standard) algorithm
• Requires SSL to be configured • String encryp,on via DECODE / ENCODE
20.11.15 28
© MariaDB Corpora,on Ab. 20.11.15 29
Thank You
mariadb.com
"MySQL is a registered trademark of Oracle and/or its affiliates. Other names may be trademarks of their respective owners. MariaDB is not affiliated with MySQL."