 I mean I will just briefly cover the unknown security in 8.0 so yeah it's a it covers almost everything not just the connection security to it. All right so I am Harin Barodharia very good afternoon to you all and thank you for making here on time and staying awake after a nice meal probably. So yeah I am Harin I work for server general team of MySQL primarily focusing on security side of security feature side I have been working with MySQL for the last five years now and yeah. So today's my talk is going to be about enhanced security features that we introduced in 8.0. As you all must be aware by now 8.0 is in RC phase at the moment we released 8.0.4 and we are planning to reach 8.0.11 so yeah let's see safe harbor statement this just for the information purpose only not for your business decisions okay. So to quickly go over the features that we introduced in 8.0 as far as security is concerned we added role support this was a long pending feature in MySQL asked by many of our community members we added dynamic privileges to help extend plugins to use the privilege system. We made our password policy enhancement and added the reuse restrictions we have been doing this since 5.6 and in 8.0 we now complete the story we also added a new authentication plugin called caching SHA-2 password that's for the impure security of the server 5.7 we started introducing features for encryption at rest for disk base encryption that is transparent we are now extending it to cover redo and unblock encryption as well and we also made the user management DDLs atomic to ease out the pin of handling the mixed cases of error versus failures alright. So I'll start by roles I'll take one feature at a time or till the time permit okay. So just some introduction what are roles roles are essentially containers for privileges you can group together related privileges create a role grant that role to uses to make your database administration easy. For all practical purposes they are just like any atomic or any basic privileges like select or insert or update or delete you can grant it to a user you can even grant it to other roles as well to you know kind of create a hierarchy of roles. So why roles I mean why can't you directly grant privileges to users of course you can grant privileges to users so you have a user you want that user to perform bunch of tasks and you grant all those privileges to user and that user will be able to do all those tasks perfectly valid example it works. The problem begins when number of user increases so with each each added user if you want to perform those users to you know if you want those users to perform same set of tasks you'll have to repeatedly grant all the privileges again. So if you have five three privileges you have 15 grants in total now add to the scenario that you want to add one more privileges to this kitty or remove one privileges from the kitty so you have to repeat the task for each of the user or else you will have a scenario where some of the user can perform certain extended task where others can't. So enter roles what you do is create a role that serves as a container grant all the privileges to the role and then just grant that role to all users if you want to add a privilege you just need to add one grant because all the grants that are granted to a role will propagate to all the users automatically if you think that there are certain extra privileges given to the role well remove them and all the removal will directly affect all the users who are granted that role sorry so it you know it makes the administration part of privilege easy it keeps your design clean all right so how do we implement roles in massquale first of all if the roles share their namespaces with user what essentially means is if you have a user with a name foo you can't have the role with the same name because both of them are stored in the same table the clash will prevent you from creating a role we store the grant information which says which role is granted to which user or which other role and how it is granted I'll come to this part later and we also store role activation information that enables a role by default for any of the user so let's see you can use create role to create a role which is essentially a sugar coated way of create user it essentially creates a locked user account basically drop role in the role is gone but creating and dropping a role alone doesn't make a role role what makes a role role is the grant or revoke so when you grant a role to a user that's when a role becomes a role or an author to be more specific an authorization ID becomes a role and we also allow something called with admin option when you are granting roles to other users because it allows creation of lesser admins you can choose certain users who are responsible for managing the role itself the grant of the role itself so you can delegate the duty by with admin option any user that has this that has been granted the role with admin option they can further grant the role to other users and you know lessen the load of the core root administrator probably roles yes now once you grant the role the user will start inheriting properties but in order to use the role the user has to set the role first so to set a role we have a set role syntax so user connects does a set role foo and it starts inheriting all the properties from the role foo after that it can perform all the tasks that foo enables for example if foo enables select from all the tables after doing set role foo the user will be able to do select from all the tables you can set all roles or set none of the roles those are syntax variations available and one more important feature is activation of the role by default so you can mark certain role as default when they are marked as default immediately upon login the roles are activated so you no need to say set role and then start using it just log in and start using we have introduced something called mandatory roles now it's it's like grant to public you want certain privileges available to all your database users so that you don't repetitively have to create grants for those items so what you do is you create a role you set as a mandatory role it's a system variable actually so you set the system variable to a list of roles that you want to mark as mandatory roles and that's it so any user that logs in gets those mandatory role by default even if you have not explicitly say that grant role public to user it it takes place automatically all you have to do is just set role and then start using it and well we have introduced a cool way to look at the role hierarchy you can take a look at which all roles are granted to which all users whether they are granted with admin option or without administration option it's a graph ML output you can use dian graph ML to render it and create visual you know visual representation of your privilege system all right so enough about roles let's move to the next one that is dynamic privileges so let me give some background we have something called super privilege I'm sure for the show of hand who doesn't know about super privilege all right so yeah you know about super so as on 5.7 the latest 5.7 that we have there are 12 different tasks that super can perform and as my colleague Joro would put it it's twice as many as the Alice had to do so yeah it's there are 12 tasks mostly unrelated for example if you have super you can manage binary log and at the same time you can manage encryption two of them have nothing in common but well you are if you if you have super you can do both so you know it has become kind of a jack-of-all-trade there is no further granularity which is allows you to grant only a specific set of privileges and not the other if you grant super you get everything adding of a new privileges difficult it requires changes at code level at our system table level it propagates very deep so what happened is as we introduced new plugins and in more often that not plugins would require certain required to do certain privilege action and to do that privilege action you need to rely on certain privilege what will you do if you have it you know you find it difficult to introduce a new privilege you really basically piggyback on the existing one and that made super you know a bit more fatter so now there are plugins like audit firewall more recently group replication which are leveraging on super to perform privilege check so this was kind of becoming a problem and we decided to yeah you know it kind of become like this so we decided to resolve this issue we did two things we basically broke super into more granular chunks so now you can say that if you want to grant somebody an ability to administrate binary logs you can precisely do that and nothing else if you want somebody else to do maintain your global system variables you can grant privileges which will be which will grant them ability to change the variables and nothing else so we broke super into 13 chunks for server management part and further four chunks for plugin related task so this is something called dynamic privileges and why because it makes it easy to add new privilege and this part is for plugin developers more because as we add new plugin the plugin developer can register unregister a new privilege with server let's say if I'm developing a plugin called foo I can go and say hey if you install this plugin I want server to recognize foo admin as the new privilege and so we'll say sure and then grant rework is supported so we'll be able to check that privilege against you know whether the user has foo admin or not and say yes or no to the plugin so so we did that and then we used it in our audit firewall and group replication plugins so now they are instead of well they are still relying on super because it's still available but deprecated but they also allow you to perform those tasks by introducing new dynamic privileges for example audit introduces audit user firewall introduces firewall user and firewall admin so on and so forth so yeah so in future any new plugin will that's we that we introduce or any of the plugin developer interviews can make use of this functionality alright okay so password reuse policy I would begin by this with 5.6 we started working on the ways to improve our password policies so we started with password strength validation that essentially allows you to make sure that your password is not weak probably it contains uppercase later low casulators it doesn't contain commonly known insecure password like ABCD 1 2 3 4 5 6 or something like that you can manage all these things using the plugin called really dead password another step that we took partially in 5.6 and more in 5.7 is password expiry that allows an admin to explicitly expire a user's password and thereby forcing that user to change the password on the next login or will schedule a password for expiry that after n days or let's say 10 days or 15 days the password will expire or 3 months password will expire something like that so this too we did in 5.6 5.7 combined the last piece that I was missing was password reuse policy why I can change password but I can reuse the same password and server will not complain right I mean as long as the password is changed service satisfied so what we did is to introduce reuse policy on two things one is based on the history you can now say that a user cannot reuse last 10 passwords you have to have a new password we have a new configurable system variable that allows you to configure that value for all the users or you can use create alter user extensions which allows you to specify a specific value for certain user so if there are sensitive account and if you want to increase the restriction that instead of 10 my sensitive account won't be able to use 20 last password you can do that using this another restriction that we added is restrict the use based on time so you can say that you cannot you can reuse a password as long as you have not used it for the past six months or I mean it it takes in in terms of number of days so you can configure it at any interval so both this restriction supply when a user is changing a password if somebody is and yeah the reuse based on time has similar configuration there is a system variable to configure it by default for all the users and on top of it you have password reuse policy related extension to configure it for individual users or sensitive accounts so with this we have all the three parts we have password validation plugin we have a mechanism to expire password and now we have a mechanism to prevent the reuse of the password so that sort of completes and gives an administrator control over how passwords are used in the system all right in 8.0 and this is we introduce a caching SHA-2 password plugin it's a new authentication plugin it uses SHA-256 hash which is much more secure than the SHA-1 hash which right now we are using for MySQL native password it uses salted SHA-256 hash that we hash it for 5000 times when we store the transformation in the MySQL.user table there are two modes in which this plugin operates there is an uncached mode which requires an actual password to be sent to server and it's restricted to use the secure connection or there is a challenge response mechanism which can work on the plain text connection as well so let me just give a brief detail so okay images in situation where a user wants to connect to the server I will skip the initial step where first user will say hi I want to connect server will say hey these are my capabilities and here is a challenge for you so once the server sends challenge the the client creates a response using the password that it knows and send it across to the server now server knows that server keeps an in-memory cache where it maintains hash of the password per account and it says hey I don't have an entry for you so let's switch to uncached mode and in uncached mode server says that I want your password and that password should be sent over secure channel so client sends password over secure channel server compares it against the salted SHA-256 hash which is basically stored in my skill dot user table and if everything goes as plant server will say yes it's success you can come in and do your job at the same time server will update the cache so that in the next phase when the same user try to log in again you just have to send the scramble so next time server will again send a new challenge a new response will be created sent to the server but this time around the server will have the cached password in the memory available and it can simply say yes or no based on the challenge response verification so yeah as you see cache mode is faster it basically as long as there are no changes in your authentication data's the cache will persist and as long as cache persist once you have created a cache entry for a user next time on word you pay less price for authentication so it's faster of course we invalidated the cache in events of sensitive change like password change or you have renamed the user or drop the user or well you did a flush privilege by means you're saying that okay get rid of everything that you have in cache and just read everything from the table so in those instances the cache is flushed that means that the next attempt that the user made they have to go through the uncached version of the protocol this is new default for maskal server so if you are using 5.7 and upgrading to 8.0 any new user that you create without specifying the plugin information simply saying create user who identified by haha and that user will start using this plug in so yeah make a note of this if you are planning to upgrade all right so enough about oh yes okay so these are the other enhancement that we made in 8.0 we introduced two system variables that controls the encryption of redo and undo log we in 5.7 we added support for user table encryption so we now extend it and this is transparent user doesn't need to do anything the keys are generated automatically the keys are stored in key ring all you need to do is configure server to use a plug in that's key ring plug in and that's about it the server will manage everything else on its own we also made the user management DDL satomic till 5.7 it's possible that when you are when you have a user management DDL which consists more than one user it is possible that some of them will get failure and other will succeed but in 5.8.0 that's not the case so all the partial changes are gone it's either everything is done or nothing is done so it makes replication life easy because you don't have to carry error all the way through the slave and then expect the same error on the slave etc and last but not the least we made certain changes related to how we link against SSL library now we dynamically link against open SSL and all our community distribution now have linkage against open SSL against to via SSL so yeah this is the summary thank you for your time any questions right thank you it's moved to you know DB well it's required because I wanted you know as I said we made it atomic and you know DB is a transactional DB that we have we required that property 5.7 yeah so in 5.7 we introduce key ring service and a bunch of key ring plugins we have community addition which is a file based key ring essentially and we have commercial additions which can use a proper back-end key ring back ends like HSMs and stuff K might be compliant you can use the key ring server has variables which make sure that key ring is fired up even before you know DB because you know DB leverages on key ring in the recovery mode so all these things are in place you have to configure server to start with the key ring after that when you create a table you say that create table so and so encryption equal to yes and that's it I mean nothing else is required you don't need to provide any key you don't need to provide anything everything is just main you know done by the key ring plugin that is introduced so right before inserting data into the table it will be encrypted and return to the disk once we read the data from the disk at the time of select or any of the query we decrypted and presented to user so as long as you have the privilege over the table you will be able to see the data but your own disk data is always encrypted not that we found I mean not very significant no from our study it's not that because it's only encrypted and decrypted when you are reading or writing pages to and from the disk rest of the time it's available to the user in an unencrypted form as long as it is user has the permission of course the configuration of key ring is you know separate for each of the node in replication if you have a user table for which you are using encryption then yes that you have to replicate the same change on the slave as well I mean you need to make sure that slave also support encryption because create you create table encryption equal to yes goes as it is on this other node so need encryption support otherwise replication will fail for redo log undo log they are driven through system variables so okay so redo log undo log it's driven through system variables and system variables are not been locked so for that I mean to my understanding it would not I mean you have to configure it individually on slave for redo and I can check it out and confirm another thing that since you ask about replication is the keys used by tables for encryption be it redo log undo log or the user table on one node I mean in two different node will be different so the keys are never shared the keys are never sent across the network the keys are local to a node so you set up one instance with one set of keys another instant uses a completely different set of keys for itself it's a yes yes synchronous application because well asynchronous would be sorry asymmetric would be very expensive so for actual disk encryption we are using a yes 128 the output of that function is there is something called graph ML library it outputs in it its own format and then there are rendering tools you just feed the the file to it and then they will construct the graph it's like a JSON format all right thank you very much thank you