 Anyway, so we are one minute early. Can we start? I think there is nobody else coming Let's do it. Good morning, everybody I would start with a question. Who has tried the MySQL 8? Good boys For the ones who don't know me. My name is Giuseppe. I've been working with MySQL a long time. I currently work with VMware I'm doing things different from database, but I do Database on my own just because I like it and Fred asked me to remind you that there is a first them hashtag and MySQL dev room hashtag that you should use actively. So roles What are they? It's something that is available in MySQL since version 8.0 and something that is created like a user and granted like a privilege and There is the tricky part the part that Triggered me to To prepare this presentation is that you need to activate them in order to use and there are a couple of Problems or tricks, but we'll see so before roles Meaning in MySQL up to 5.7. What did you have to do to authorize somebody to use? Database you create a user and then you grant grant grant to the to that user Then we have another user which similar access so you create the user and grant again a lot of things Create the user grant grant grant until you make a mistake So it's a lot of repetitive work and there is a lot of chances to to make mistakes So the advantage of roles should be easy to understand First of all is faster user administration you define a role you assign many times and So you have less Possibilities of making mistakes and then you have a centralized grants handling this is As a consequence gives you also the possibility of having better grants statistics So let's start with something practical. Let's start with a bad example because you know when you try something new Usually you end up with something that doesn't work so we have Creating we have created a role we call it powerful Then to this role we grant everything on every object in the database Then we create a user We make it we give it a password and then we grant the role to that user So far everything is according to the manual now, let's see if it works So we We connect with the new user and we try use test and it says access denied and We show grants And we see that the powerful the role that should contain the the privileges is there However It doesn't work so What has happened? We are going to find out soon. Stay tuned Let let me explain how the role usage works in my SQL 5a and 80 You created a role first of all Then you grant privileges to the role. We have seen that create the user Grant role to the user and the missile piece was set the default role You have to do something Extra in order for that user to be able to use that role Let's see in practical We create a role and Notice that there is no identified clause when we create a role We grant something to that role create the user and Then grant the role that we have created to that user Notice the difference when you grant a role is different from when you grant a regular privilege There is no on close in the grant statement And then you set the default role So you should say alter user default role and the name of the role Or you can use a set default role Name of the role and name of the user you can do that in several ways Unfortunately, so a few points that you need to remember first the my skill dev room hashtag because Fred likes that You can grant many roles to one user and this is by design You may want to have several roles that have one portion of the privileges that you want to Create for a given work and then assign several roles to to a you to a user The roles Don't have a specific table. There is a the roles are saved in the user table so Basically a role is a user without a login and As a consequence of the previous Point is that you can actually grant a User as if it were a role to another user No, I was this is the next one The point that I was making before it was that When you grant the role is not your job is not finished you have also to activate the The role you have to set the role as default and what I was saying before it was that You can grant a user to a user so You don't need to to have a role specifically defined you can say just Grant user shlomi to user Fred and It will work. It will be a confusion between the two, but it works just like a role now the most important part is that you not you need to Set the role active. So you need to read the manual and the manual gives you a bit of headache So set the role Means for this session Use this role as default set default role Means forever not only for this session use This assign this role to that particular user and Set role default Means something completely different meaning if this user has a default role and was using something different right now Give it my give it back the default role So if this is confusing for you, you are right. I Hope that the my SQL team will do something better with this So Pelling roles from users there's something that I would like to as an administrator. I would like to know Can I List just the roles, but there is no feature for that So the thing that you should do is to adopt naming convention for example Start of the user the roles with the R underscore something and only the roles will start that way I know that there is a feature Request for this, but I'm not holding my breath about that Things that we do find instead. There are a few tables related to roles There is a role edges and table That tells you which roles have been assigned to which users and then there is a default roles That says which roles are default For the users a funny thing is that in default roles You can put mostly anything even if the role does not exist This is a bug for me is a philosophical question about According to the developer who responded to my bug. So basically the default role may not exist and I hope that somebody will change their mind in the my SQL team and decide that The default roles should be something that exists. Otherwise We go back to the problems that we had before the roles So the roles in action and don't forget about of the hashtag Let's suppose that we wanted to deal with the Lord of the Rings World using roles So I have a role that is Observer another that is tester another that is developer and DBA and This all these roles will go To act on a database called the Lord of the Rings so what do we do we Assigned the select to observer Select insert update and delay delete to tester all to the developer and all on everything to the DBAs so What do we do then we assign the? The roles to the users and we have users that of course belong to this To this database and our bill both or some people Mary Boromir Gimli Aragon Legolas Gandalfa Galadriel and gollum and if you know the story, you know that The DBA go to Gandalf and Galadriel because they cannot go to anybody else and The developers are Frodo Aragon and Legolas And and the testers are some Bilbo and Gimli so you see we have a several roles assigned to Different users and notice that I have set the default role for the right user so they will work immediately How do they look if we look at the user table? You see that Basically, they look that like user. The only thing is that they don't have a password because we Created the role using the create role instead of create user And that's it So you can see that this the roles start with R underscore is just my convention Not something that you are forced to And that's it So you see this if you use the roles with a bit of organization you should be able to to find them Now using the The roles you can also use some different ways For example, you can say the ones who have the password expired and account locked they are roles But you know you can have also regular users with the same With the same characteristics If you are adopting a naming convention you can just select the users that Start with Prefix that you wanted to use You can see the role age That gives you which roles are assigned to which users and the default roles and you see the assignment of defaults and Then you can use specific queries to see who Which users have a particular role For example, the DBAs are Galadriel Gandalf The developers are Aragon Frodo and Legolas And then you can use those tables just you know with regular SQL To see how many DBAs you have how many developers how many observers and testers Similar query So when you have a user with a default role you have a show grants and You see that The grants Here come explicitly so you see that it has a select insert update and delete and When you say select current role you get the name of the role if you don't have a Default role when you do the grants you don't get the privileges you get only the name of the role so when you do a User without a default role You try to use it and you get access denied just because The role is not active use you do a set role and And You can enter the database and do whatever you need else you need to do I think to remember is that the set role is not permanent Set role is something that happens in The session is a question there The question is what the current role shows if you have many roles Assigned as default it shows the list of roles that were assigned Set role set select the current role and you see the name of the role then You'll see that You can enter the database where you should enter then if you do a connect meaning that you restart the the connection The set role expires and then the connection is denied So let's go back to the bad example that we had at the beginning and We were in this condition So we have The powerful role that is assigned But is not active yet so the thing that we need to do is Set role powerful and Then show grants will show all the privileges that come to with that with that role and finally You can enter the database one thing that is a piece of good news in My SQL 802 There is a possibility of having roles active by default So there is one option that it says activate all roles on login and If you activate that option every Every user that has a role and When at the moment it connects it will be It will have the role active. So you don't need to do set role at all Of course, you can do manually if you want to change the deck default role, but the default role will be active And there is a strange thing that is called mandatory roles mandatory roles means Set this role mandatory for every user and I don't know it could be useful or it could not so with mandatory roles, let's see we Create a schema we select We grant Something to this role and Then we say this role now a mandatory for everybody. So everybody can see the Lord of the Rings tables So I create a user Identified by M. Some box and I don't give it anything So when I connect with this user I get an error why because the role is not active But if I say also set the global activate all roles on login equals one and Then I try to connect again then this dummy, which didn't have any Privileges at all it gets the mandatory role and with this mandatory role is able to use the the Lord of the ring database One thing that the one strange thing that is a consequence of these mandatory roles is that if you already have the privileges Provided by that mandatory roles. It doesn't matter that the mandatory role comes to you anyway for example the user route here has also the grants for the Lord of the rings database even though root can access already everything Last thing in my skill 803 there is the possibility of saying Putting the default role in the create user. So the create user is something that comes before Assigned roles so you can say create the user blah blah and default role is Foo but You need also to assign that role So I don't know I think there is some work to do on this Because yes, it's good that I can assign a default role. It would have been even better if I could also say Also, I want this role to be Not only the default but really to exist for that user Which you know if you don't if you do things This way you say set default role During the create table create user you end up with a user that has a default role and no role It's complicated. Yes, it is so end of time I Can answer a few questions and don't forget the hashtags no Open a bug What happened when you delete a role that was assigned? The Next time that the user connects it will not be allowed to to use that role if So if you delete a user the default role remains Unfortunately, so it's something that should have disappeared, but it doesn't So the question is do you need to say to Specify set the role every time no if you have a set default role then that role will come Every time you connect. It's a SQL command. So you need to specify Which with ever whichever client you want to use? Yes, you can assign several roles as default Last question is very I didn't hear the last part. Yes, it is