mysql 8.0: security - percona · ¡ensure service is not interrupted as a result of a security...

Copyright©2017, Oracleand/oritsaffiliates.Allrightsreserved.|

MySQL8.0:Security

Georgi“Joro”Kodinov,SeniorSoftwareDevelopmentManager

MikeFrank,ProductManagementDirector


SafeHarborStatementThefollowingisintendedtooutlineourgeneralproductdirection.Itisintendedforinformationpurposesonly,andmaynotbeincorporatedintoanycontract.Itisnotacommitmenttodeliveranymaterial,code,orfunctionality,andshouldnotberelieduponinmakingpurchasingdecisions.Thedevelopment,release,andtimingofanyfeaturesorfunctionalitydescribedforOracle’sproductsremainsatthesolediscretionofOracle.


Agenda• MySQLSecurityLandscape• New!inMySQL8.0– SQLRoles– AtomicACLStatements– DynamicGlobalPrivileges

• Architectureforimprovedsecurity– KeyringAPI

3


ComplexitygrowsRiskGrows

4


RegulatoryComplianceRequirementsGrow• Regulations– PCI– DSS:PaymentCardData– HIPAA:PrivacyofHealthData– SarbanesOxley,GLBA,TheUSAPatriotAct:

FinancialData,NPI"personallyidentifiablefinancialinformation"– FERPA– StudentData– EUGeneralDataProtectionDirective:ProtectionofPersonalData(GDPR)– DataProtectionAct(UK):ProtectionofPersonalData

• Requirements– ContinuousMonitoring(Users,Schema,Backups,etc.)– DataProtection(Encryption,PrivilegeManagement,etc.)– DataRetention(Backups,UserActivity,etc.)– DataAuditing(Useractivity,etc.)

5


PrinciplesofSecuringDatabases¡Assess¡ LocateRisksandVulnerabilities,Ensurethatnecessarysecuritycontrolsareinplace

¡Prevent¡ UsingCryptography,UserControls,AccessControls,etc

¡Detect¡ Stillapossibilityofabreach– soAudit,Monitor,Alert

¡Recover¡ Ensureserviceisnotinterruptedasaresultofasecurityincident¡ Eventhroughtheoutageofaprimarydatabase¡ Forensics– postmortem– fixvulnerability

6

Copyright©2017, Oracleand/oritsaffiliates.Allrightsreserved.| 7

MySQLSecurityOverviewAuthentication

Authorization

Encryption

FirewallMySQLSecurity

Auditing

Monitoring

Availability

Copyright©2017, Oracleand/oritsaffiliates.Allrightsreserved.| 8

NowontotheNewStuffin8.0


New!MySQLRolls

wecouldn’tresist

9


New!MySQLRolesImprovingMySQLAccessControls• Introducedinthe8.0.0DMR• Easiertomanageuserandapplicationsrights• Asstandardscompliantaspracticallypossible• Multipledefaultroles• CanexporttherolegraphinGraphML

10

FeatureRequestfromDBAs

Directly

IndirectlySetRole(s)

DefaultRole(s)SetofACLS

SetofACLS


SQLRolesImplementationDetails- 1• Aroleisbasicallyauseraccountwithlogindisabled.• Amemorybasedhashofflattenedprivilegesetsforeachactiverole• 2newtables:mysql.role_edges andmysql.default_roles• 2newSQLfunctions:CURRENT_ROLE() andROLE_GRAPHML()• 3 newglobalprivileges:CREATEROLE,DROPROLE andROLE_ADMIN• Extensionsto:ALTERUSER,GRANT/REVOKE,SET[DEFAULT]ROLEandSHOWGRANTS

11


SQLRolesImplementationDetails- 2• Rolescanhaveanoptionalhostpart(notcurrentlyused)• Pre-rolesACLcodeisusedwhenthere’snoactiverole(s)• Userscanbeassignedseveralroles• Userscanhavezeroormoredefaultroles• ActiveRolescanbechanged– fromvariousassignedroles– Forexamplejustescalateorchangeprivilegesfromwithinanapplicationforcertainoperations

12


RoleExamples

13


New!AtomicACLStatements• LongstandingMySQLissue!– ForReplication,HA,Backups,etc.

• Possiblenow- ACLtablesresidein8.0InnoDBDataDictionary• Notjustatableoperation:memorycachesneedupdatetoo• Appliestostatementsperformingmultiplelogicaloperations,e.g.– CREATEUSERu1,u2– GRANTSELECTON*.*TOu1,u2

• UsesacustomMDLlocktoblockACLrelatedactivity–Whilealtering theACLcachesandtables

14



New!DynamicPrivilegesProvidesfinergrainedadministrativelevelaccesscontrols• Toooftensuperisrequiredfortaskswhenlessprivilegeisreallyneeded– Supportconceptof“leastprivilege”

• Neededtoallowaddingadministrativeaccesscontrols– Nowcancomewithnewcomponents– Examples• Replication• HA• Backup

• Giveusyourideas

15


WhyDynamicGlobalPrivileges?• Howtoaddanewglobalprivilege(the5.7version)– Addacolumninmysql.user– Extendtheparser– AmendACLcachecode:reading,caching,writing,upgrade,…– Addchecksforthenewprivilege

• Notpossiblefromaplugin!• Abuseofexistingprivileges(SUPER)!• TheSUPER-potentSUPER!

16



HowDoDynamicPrivilegesWork?• Providesnewcomponentservice– Canadd,removeandcheckglobalprivileges

• OnlyGRANTsarepersisted– Storedinmysql.global_grants

• Usesthefamiliar– GRANT<dynamic_acl>ON*.*TO… syntax

17


DynamicPrivilegesatWork• SUPERprivilegesplitintoasetofdynamicprivileges,e.g.– SYSTEM_VARIABLES_ADMIN– ROLE_ADMIN– CONNECTION_ADMIN,etc.

• Eachplugincannowregisterandusetheirownuniqueprivileges• AllexistingMySQLpluginscurrentlyusingSUPERareupdatedtoaddspecificprivileges,e.g.– FIREWALL_ADMIN– AUDIT_ADMIN– VERSION_TOKEN_ADMIN

18



Wouldloveyourfeedbackandideas

Pleasereviewthedynamicprivilegesdefinedbythe8.0.1DMR

Whatwouldyouliketosee?

19


New!InnoDBRedoandUndoEncryption• AES256encryption• Encryptedwhenredo/undologdataiswrittentodisk• Decryptionoccurswhenredo/undologdataisreadfromdisk• Onceredo/undologdataisreadintomemory,itisinunencryptedform.• Twotieredencryption– likeInnodbtablepaceencryption– Fastkeyrotation,highperformance

• Easytouse– Enabledusing innodb_redo_log_encrypt andinnodb_undo_log_encrypt

20



TheKeyringAPI:TheBigPicture

21

TheMySQLServerPlugins(Consumers) Keys

KeyringPlugin(backend)

KeyStorage

Keys

KeyringPluginService

KeyringPluginAPI

KeysKeyRingAPI EachKey

HasaName/ACL


WhatistheKeyringAPI?• Auniforminfrastructureforhandlingkeys• Usablebyboththeserverandplugins• AvailableinMySQL5.7andupasapluginAPIandapluginservice• Fullyextensible• CanbeinitializedbeforeInnoDBatstartup• Minimumefforttoaddnewbackendsandconsumers

22


Keyringplugins:TheInventory

23

• CurrentConsumers– InnoDBtablespaceencryption– SQLuserdefinedfunctions(UDF)plugin

• CurrentBackends– Flatfilebackend– KMIPcompliantclients• OracleKeyVault• GemaltoSafenetKeySecure• ProbablymoreiftheysupportKMIPstandards– giveitatry.


Questions?

24


SafeHarborStatementTheprecedingisintendedtooutlineourgeneralproductdirection.Itisintendedforinformationpurposesonly,andmaynotbeincorporatedintoanycontract.Itisnotacommitmenttodeliveranymaterial,code,orfunctionality,andshouldnotberelieduponinmakingpurchasingdecisions.Thedevelopment,release,andtimingofanyfeaturesorfunctionalitydescribedforOracle’sproductsremainsatthesolediscretionofOracle.

25

mysql 8.0: security - percona · ¡ensure service is not interrupted as a result of a security...

Documents