28
© 2015, MariaDB Corp. MariaDB Roadshow Berlin 2015 MariaDB 10.1: Datenbankverschlüsselung und andere Sicherheitsvorteile Ralf Gebhardt, Principal Sales Engineer
© 2015, MariaDB Corp.
MariaDBRoadshowBerlin2015
MariaDB10.1:DatenbankverschlüsselungundandereSicherheitsvorteile
RalfGebhardt,PrincipalSalesEngineer
©MariaDBCorporaBonAb.
Agenda
• MariaDB10.1NewFeatures• ForHighAvailability• ForScalability• ForSecurity
• MariaDB10.1SecurityFeatureSet
07.12.15 2
©MariaDBCorporaBonAb.
MariaDB10.1Released
• FirstGAversion10.1.8releasedinOctober• BasedonMariaDB10.0• IncludescontribuBonsfromcommunitymemberslikeFacebook
07.12.15 3
©MariaDBCorporaBonAb.
MariaDB10.1Themes
High Availability
Scalability Security
07.12.15 4
©MariaDBCorporaBonAb.07.12.15 5
High Availability
©MariaDBCorporaBonAb.
GaleraClusterintegrated
• FullintegraBonofGaleraClusterintoMariaDB10.1—itwon’tbeaseparatedownload
• EnableGaleraClusterwhenyouneedit
07.12.15 6
©MariaDBCorporaBonAb.
GaleraCusterintegrated
• PerdefaultMariaDB10.1workslikeavanillaMariaDBServer• ForGaleraClusteritisrequiredto:
• 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
07.12.15 7
©MariaDBCorporaBonAb.07.12.15 8
Scalability
©MariaDBCorporaBonAb.
ParallelSlaveReplicaBon(10.0)
• MulB-sourcereplicaBonfromdifferentmasters(domains)executedinparallel
• Queriesthatareruninparallelonthemasterareruninparallelontheslave(basedongroupcommit)
• TransacBonsmodifyingthesametablecanbeupdatedinparallelontheslave!
• SupportsbothstatementbasedandrowbasedreplicaBon.
07.12.15 9
©MariaDBCorporaBonAb.
OpBmisBcParallelReplicaBon
• NewreplicatonmodeinMariaDB10.1• AnyINSERT,UPDATEorDELETEcanbeappliedinparallelontheslave
• Notneccessarilymeansthatitwascommitedinparallelonthemaster
• Needsa10.1master• NeedsatransacBonalengineforrollbackincaseofaconflict
07.12.15 10
©MariaDBCorporaBonAb.
OpBmisBcParallelReplicaBon
• Enabledbyslave-parallel-mode=optimistic
• Temporarilydisablebyvariable@@skip_parallel_replication
• ServeropBmisBcallyassumesthatfewconflictswilloccur
• rollbackandretryforconflicBngtransacBons
07.12.15 11
©MariaDBCorporaBonAb.
PerformanceImprovements
• EspeciallyforHigh-EndServers• Highprocessingpower• Morecores
• Benchmark10.1onLinuxOnlyPOWER8• „1millionSQLqueriespersecond:GAMariaDB10.1onPOWER8“
• heps://blog.mariadb.org/10-1-mio-qps/
07.12.15 12
©MariaDBCorporaBonAb.
InnoDBDefragmentaBon
• Deletedrecordscancreategapsonpages• DefragmentaBonbasedonanimplementaBonfromFacebookandKakaoCorp
• ButnonewSQLliteralsneededandchangestotheserverneeded
• OPTIMIZETABLEisused• innodb_defragment=1
07.12.15 13
©MariaDBCorporaBonAb.
MySQLCompaBbilityFeature
• MariaDB10.1canbeaslavetoMySQL5.6• AlsowhenGTIDsareused
• FeaturewasrequestedfromtheCommunity• TotestMariaDBinaMySQLdeployment• FormigraBngtoMariaDB
07.12.15 14
©MariaDBCorporaBonAb.07.12.15 15
Security
©MariaDBCorporaBonAb.
SecurityFeaturesinMariaDB10.1
• DataatRestEncrypBon• PasswordValidaBonPlugin• PAMAuthenBcaBonPlugin• AuditPlugin• SSLConnecBons• EncrypBonfuncBons
07.12.15 16
©MariaDBCorporaBonAb.
DataatRestEncrypBon
• NewwithMariaDB10.1• OriginatesfromGoogleencrypBonpatch• TablespaceandtableencrypBon• Basedon
• EncypBonkey• Keyid• KeyrotaBon• Keyversion
07.12.15 17
©MariaDBCorporaBonAb.
DataatRestEncypBon
• EncrypBonfor• XtraDB/InnoDBtablespaces• XtraDB/InnoDBlogfiles• Binarylogs• Ariatables• Temporaryfiles
• NoEncrypBonfor• Metadata• Memory• Config-Files
07.12.15 18
©MariaDBCorporaBonAb.
DataatRestEncrypBon
• LastinternalbenchmarksonencrypBonoverhead
• XtraDB/InnoDBencrypBon• <1%(ro)• ≈8-14%(rw)
• TemporaryfilesencrypBon• ≈7-10%(filesort)• BinarylogencrypBon:<4%
07.12.15 19
©MariaDBCorporaBonAb.
DeletedDataEncrypBon
• Scrubbing• Backgroundthreadsperiodicallyscantablespacesandlogsandoverwritealldatathatshouldbedeleted.
• Moreinfo:• heps://mariadb.com/kb/en/mariadb/xtradb-innodb-data-scrubbing/
07.12.15 20
©MariaDBCorporaBonAb.
PasswordValidaBonPlugins
• PasswordvalidaBonpluginAPI• simple_password_checkplugin
• Canenforceaminimumpasswordlengthandguaranteethatapasswordcontainsatleastaspecifiednumberofupperandlowercaseleeers,digits,andpunctuaBoncharacters
• cracklib_password_checkplugin• Awidelyusedlibrary• Stopusersfromchoosingeasytoguesspasswords.ItincludeschecksfornotallowingpasswordsbasedontheusernameoradicBonarywordetc.
07.12.15 21
©MariaDBCorporaBonAb.
PAMAuthenBcaBonPlugin
• AuthenBcaBonusing/etc/shadow• AuthenBcaBonusingLDAP,SSHpassphrases,passwordexpiraBon,usernamemapping,loggingeveryloginaeempt,etc…
• INSTALL PLUGIN pam SONAME 'auth_pam.so'; • CREATE USER foo@host IDENTIFIED via pam; • REMEMBERtoconfigurePAM(/etc/pam.dor/etc/pam.conf)
07.12.15 22
©MariaDBCorporaBonAb.
MariaDBAuditPlugin
• AudiBngdatabaseaccessto• File(commadelimitedformat)• Syslog
• ModifiedPluginAPIinMariaDB• AuditPlugincompaBblewithMySQLServer
• OnlyMariaDBallowstomonitortablelevelevents
07.12.15 23
©MariaDBCorporaBonAb.
MariaDBAuditPlugin
07.12.15 24
CONNECTION
QUERY
CONNECT
DDL
DISCONNECT
FAILED CONNECT
DML+TCL
OBJECT DATABASE
TABLES
TIMESTAMP HOST USER
SESSION
DCL
©MariaDBCorporaBonAb.
MariaDBAuditPlugin
• Passwordfilteringincluded
07.12.15 25
20150117 23:40:56,MYSQL5530,root,localhost,1,1,QUERY,,'CREATE USER "test1"@"localhost" IDENTIFIED BY *****',0!20150117 23:40:56,MYSQL5530,root,localhost,1,1,QUERY,,'CREATE USER "test4"@"localhost" IDENTIFIED BY PASSWORD *****',0!20150117 23:40:56,MYSQL5530,root,localhost,1,1,QUERY,,'INSERT INTO t_pwdtest VALUES (1,PASSWORD("mypwd"))',0!20150117 23:40:56,MYSQL5530,root,localhost,1,1,QUERY,,'UPDATE t_pwdtest SET mypwd = PASSWORD("mynewpwd")',0!20150117 23:40:56,MYSQL5530,root,localhost,1,1,QUERY,,'INSERT INTO t_pwdtest VALUES (2,OLD_PASSWORD("mypwd2"))',0!20150117 23:40:56,MYSQL5530,root,localhost,1,1,QUERY,,'UPDATE t_pwdtest SET mypwd = OLD_PASSWORD("mynewpwd2")',0!20150117 23:40:56,MYSQL5530,root,localhost,1,1,QUERY,,'GRANT ALL ON *.* TO "test5"@"localhost" IDENTIFIED BY *****',0!
©MariaDBCorporaBonAb.
SSLConnecBons
• EncryBonbetweenclientandserver• Disabledbydefault
• TLSv1.2protocol• SSLalsoavailableforreplicaBon• VariablesneededtouseSSL
• ssl-ca=ca.pem• ssl-cert=server-cert.pem• ssl-key=server-key.pem
07.12.15 26
©MariaDBCorporaBonAb.
EncrypBonFuncBons
• EncryBonfuncBonsareusedpercolumn• AvailableencrypBons
• AES(AdvancedEncrypBonStandard)algorithm• DES(DataEncrypBonStandard)algorithm
• RequiresSSLtobeconfigured• StringencrypBonviaDECODE/ENCODE
07.12.15 27
©MariaDBCorporaBonAb.07.12.15 28
ThankYou
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."
