Maria db 10.1 datenbankverschlüsselung und andere sicherheitsvorteile-v3

© 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 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 10.1 Themes

Security High Availability

Scalability

20.11.15 4


High Availability


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


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


Scalability


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


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


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


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


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


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


Security


Security

Encryption

Auditing Authentication

Validation

20.11.15 16


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


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


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


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


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


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


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



20.11.15 25

CONNECTION

QUERY

CONNECT

DDL

DISCONNECT

FAILED CONNECT

DML+TCL

OBJECT DATABASE

TABLES

TIMESTAMP HOST USER

SESSION

DCL



• 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


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


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


Thank You

mariadb.com

[email protected]

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

Software

Maria db 10.1 datenbankverschlüsselung und andere sicherheitsvorteile-v3