Lesson 8: Managing Users and Privileges - UniversalClass MySQL Course

the purpose of this lesson is to learn about user logins of passwords user roles user privileges including grant checking and revoking how to use different hosts and how to create and use a view in any computer system it is necessary to create usernames and passwords for each user by assigning a separate user login for each person the DBA can very easily audit activity that occurs in the system it is possible to see what actions each person perform users can be granted special permissions or privileges and we can grant individuals the ability to maintain the server or maintain process manage user accounts monitor security manage particular databases and etc so I could actually set up where I have a user that they're only able to reset passwords for other users I can I can create a user that they're allowed to make databases but they're not allowed to delete them I can make a particular permission for a user that they are allowed to create a database but they can't and put any data the problem is that assigning these privileges to each user can take a significant amount of time so what we can do is we can use what are called functional roles every user should have a unique user name with a strong password any strong password is considered to have at least 16 characters it does not have any repeating characters so you don't do something like 1 1 1 1 1 it does not use standard words in the dictionary and the passwords are going to be hashed and encrypted which actually MySQL is going to do for you one thing you need to be aware of with your passwords is if you use a password that has words in it so if I say something like my dog is brown that actually is a fairly long password but it is actually no more secure than a four character password because somebody can try to use what's called a dictionary attack and they will find out that it just has those four words in it now we can create user account so we can limit the user to a particular host databases or views so that way they can't try to access the MySQL stelae ssin on another server they can access databases they're not supposed to have access to they can't access views which we'll get into those more in this lesson I can also limit the number of connections enquiries that a user is able to make so if I only want user to be able to check for a telephone number once every so many milliseconds I can actually do that the idea with this is to prevent somebody from trying to get too much information out of a database and also to try to prevent something like a robot from getting in there and getting access to data we can assign users to administrative roles so I could I could create a user that's allowed to make other user accounts like a grade a user that's allowed to bring the server up and down and I also can assign what are called granular privileges so I have the ability to assign very specific permissions per user now to create a user account you're going to use this create user statement so we create the user whatever the user name is going to be and each user is identified by a password so what we need to do is we need to specify the username and the password once we have accounts we can rename them with a rename user command so we rename the user whatever their user name is to their new user name and we can also drop or delete user accounts so we just simply drop user and we drop them now what you need to be aware of is when we create a user we're actually creating an entry in the MySQL database tables and so what happens is there is a database called MySQL and there's a table called user and we're just simply creating a record for each user account we'll get to that wrong a couple of minutes so there is this one important account that you need be aware it's called root and root is the account that has the highest privilege on every MySQL server it is enabled by default it is the one that gets set up during the installation so we actually set the password during the installation procedure and what happens is usually when people install MySQL they typically set a very insecure root password if they set one at all because you have the option during the installation to leave the password blank so what will happen is when somebody's trying to hack into a MySQL server they will try to use the root account with no password or with common passwords knowing how to get into the system you have to know using them in a password I know that every MySQL installation out there has a root account I'm going to try to log in with that one because I have half the information that I need now the root account has your super user capabilities it can do whatever you want therefore you should only use it when it's absolutely necessary so when you are logging in even if you were the administrator you should not be logging in as root you should actually create a standard user account for yourself and only escalate your privileges when you absolutely have to so what you what you should do is you should create an administrative account for yourself and also a standard user account when you're in the system doing your standard queries and work ups or whatever else you're doing you should be using the standard user account not the root account or even your admin account and even if I set up an administrator I need to assign to them only the privileges that they actually need the privileges that you can have when you're working with data is you can select information so you can retrieve stuff on the table we can insert so we put data into the table we can update so we can modify data we can delete which means we would remove data from the table and we can execute now execute is a little bit different than what we play with we can store what are called stored procedures and a stored procedure is nothing more than saving an SQL script to run it later we will get into those more in another lesson but it just simply is a way for somebody to make a a script that can be run frequently and then we just simply execute it whenever we want to now we can also modify the database structure itself so some of our permissions we can create so we can make a new database a new table or new view we can alter the layout or the format of a table we can drop databases table to the views and we can also create indexes which we will discuss more in a future lesson some of the other things that we can do are we can create routines which a routine is simply a stored procedure and we can create it we can alter it we can create triggers and what a trigger is I could state that when somebody modifies the credit limit for an account then I want the system to do something so what I would do is I would create a trigger that is just simply monitoring a specific column in a in a database table and then when the trigger gets tripped then we cause some event to occur so maybe I want the system to send an email message to me if somebody tries to update a customer's credit we have different privilege levels we can use we have global which would mean all of the databases all the tables on all of our MySQL servers that are within our with our environment then we could also assign that you have rights to a particular database to a particular table and we can even go as granular as you only have rights to a particular column so if I have somebody in my in my environment and I only want them to be able to update how much money somebody owes to us or payments that they've made I can actually limit them to just that information so what I can do is I can specify that users are allowed to access the system physically sitting at the server or from remote machines so what I need to do is when I create an account I would actually specify if they have to be physically sitting at the box to be able to to do anything with it so the local host accounts those are our local ones they can only connect if they are physically sitting on the same machine as the MySQL server application remote accounts can connect from any other machine on the network now a user that has only local access would be listed like this we'd have user name and then at localhost but we could also specify user name at 127 dot 0 dot 0 dot 1 which this is our TCP loopback address which just simply means on the same computer now we do have the ability to create user accounts they can connect from anywhere and to do that we just simply specify a user name for them and we can also go so far is to specify that a user can only connect if they're coming in from a particular domain so if I want to make it where I work for example.com and I have users that are on example.com that they can only get to the system if they're coming from the example com domain now to grant privileges what we would do is actually use a grant a grant statement so what I would do is just simply grant and then what kind of privilege do we want for them to have on and then I have to specify of a database or every database to a particular user and we have to know their password to do this so typically we will grant privileges when we create the user accounts you know if we don't know the password there is a way to actually get the system to allow us to create this this grant without the password listed but typically we will end up resetting the user password if we need to all right so I'm going to create a user account that has no permissions and then we can create other accounts with Louisville privileges so if I state something like grant all what this would do is give the user every privilege that is available on and then this asterisk asterisk this is database dot table so if I do this I'm telling that this user has access to every database that we have dot every table that's in it so essentially this user would have every right on every database that we have in our system and notice I listed that it's username at localhost which would mean that this user has to physically be sitting at the box now if I wanted just the user to be able to access it then I would specify the actual just username without the yet localhost so I can also state that user has privilege to just an entire database and the way that I would do that is grant and then what can they do to the database so this person can select information they can insert they can update they can delete on this particular database and if you notice it's the database name dot asterisk so what this means is any table that is contained within this database name database they can do these permissions to so once I create privileges for users I want to view them to see what kind of privileges I've given everybody so what I can do is if I want to see what my privileges are I can log in and just simply show grants but if I want to see what is available for each user I could say show grants for and then list the actual user name and what it will do is show to me all of the privileges that the user has now one word of caution is that doesn't always work the way that we expect it to and remember I said that the user is nothing more than a record in a table so therefore I can quarry that table by selecting everything from the MySQL dot user table and I will see everything about the user accounts including their privileges if I have a user camp that I've given privileges to I can revoke those privileges and the way that I did is just the reverse of what I did at it I just simply state revoked privilege so whatever the privilege was so if I granted them all or if I granted them select or insert then I could revoke it on whatever the database is from that user account now I do also have the ability to do this for multiple users and to do that all I am going to do is simply state revoke the privilege on that particular database and table from all of the user accounts that I just simply state them and I put a comma between each one you so I am in MySQL workbench and I want to create a user account actually I'm going to create - I'm going to create a user and an admin account and the way that I do this is I just simply state create user and then list the name so I'm going to call it my underscore user and then this user is identified by something that is a password the password goes inside of a single quotes on apostrophe and so in this case I'm just simply going to make a password of password for the record this is a very insecure password do not ever use this in a production environment we are just simply doing this for testing purposes and I'm also going to create another statement here remember I can put multiple statements in at the same time it's ok but each statement has to end with a semicolon so I'm going to create user my admin and this person is identified by and the same password okay so when I run this you will notice that I have two output statements that says zero rows affected but what I actually just did was I created a brand new user account in my MySQL user table and I will show that to you in just a minute so I've created these two user accounts and what I want to do is I want to give the administrative account all permissions for classic models I want to give them all of the privileges so I'm going to say grant all on and then what is the database so I want them not necessarily to be a global admin so I don't want to give them asterisk asterisk because that would give them permissions to everything remember we want to set the permissions as low level as possible so if this person only needs access to the classic models database that's all I should give them permissions to so I'm going to set this user up so classic models dot asterisk and then I'm going to say to my admin ok now I told you before that I have to identify the user by password now because I have already created the user account with a password I don't have to identify them again right here I can just simply run the statement so I'm going to grant only specific privileges to our user account so this person is able to select insert delete and update on classic models Astrea so the entire classic vollis database to my user so what this does is this makes it where this user account cannot assign security permissions to other accounts for this database this user is only able to do database functions this user the administrator has all functions including being a little loud to administer other user accounts so when I run the script I will see that my grants were successful and so what happens now is it just simply created a record in a database table that will allow me to see what exactly the users have permissions to do so now I want to verify that this these permissions actually went through I'm going to open a new sequel tab and what I want to do is I want to see what kind of permissions I have so I'm going to show grants and this is for me I didn't specify user account so it's just simply whoever I'm logged in as and you'll see that I am logged in as root so therefore I have all privileges on every database that's out there to me as a local access and also via proxy which means that I can access this from anywhere I can do it from here or from another computer and notice I'm identified by a password and it's this really long password with Grant option now my password is actually MySQL all lowercase no spaces so where did this password come from all right this is what is called a salted and a hashed password so what happens is the system when I assign a password it will automatically add some characters to the password and then it will create a hash value which a hash value is just simply a manner of figuring out what all bits make up the actual password and putting it into a specific length field in this case the 41 character is long so what happens is that value is hashed and then on top of it it gets can crimp it so what will happen is what I actually login and I type in my password when I login it doesn't actually send my password to the system what it actually will do is it will figure out what my hash value should be it sends it to the MySQL server and then MySQL will figure out even the hash that I send matches the hash that they have in the database if the hash is matched then it will allow me to have access all right so I want to see what kind of grants I've given to my my admin account so for my admin and so now what I can see is that this user I've given grant usage which means that they have permission to use the databases on everything so every database in every table to my admin and then on top of usage I've also given them all privileges to the entire classic models database so usage just simply means I can log in grant all means I can do whatever I want to the database I can create user accounts I can delete the database I could change the schema I can add tables so whatever I want to do this user account can do all right so if I look at my user my user who is identified by this password hash is allowed the sign-in and is granted the ability to select insert update and delete anything in the classic models dot asterisk database tables so notice that this user account is my underscore user at and then there's a percentage sign what this would mean is that this user is allowed to log in from any host so this user is actually created as a remote user they can access the system either physically sitting here or from another machine on the network now like I said when I run this query I may not always get the exact output that I expect here so I can always go and check the actual table where this information is stored and this is stored in the MySQL user database table so what I can do is I can select everything from MySQL dot user and when I run this it will actually give to me a table that includes all of our user accounts where they're allowed to login so you notice I have a root user when they're logged in locally I have another root user who's logged in locally through the loopback address I have route that's through the loopback address for our IP version 6 and then I have a root account from anywhere and so even though it's the same user account the location of where that user logs in will dictate what kind of privileges they get I have another account here called no privileges it's just simply an account that was created that doesn't actually have any privileges and then I have my user and my admin now if you notice over here I I do not have any granular permissions set this user yet I will show you in a minute how to set these but I can go through this database through this table and I can actually see all of their permissions and then the other thing that I'll see is that I created them with what's called a MySQL native password you actually have some different types of passwords that are allowed there is the plain text which you don't ever want to use and then the other one that you can use is a sha-256 which means it's a 256 bit encrypted password so in this case this one is salted and then hashed and then I have the ability to encrypt the password if I want so this table just simply shows me all the information about all the user accounts that I have on the system it shows to me their password hash and all of the permissions that the users have so I granted these users some permissions and I don't want them to have those permissions so my my user count I accidentally granted that person the ability to do whatever they need to for updates and selects for our classic models database and I don't want them to have that permission anymore so what I'm going to do is I'm going to remove it okay so instead of granting I'm going to revoke it and I'm not going to do it to the user I'm gonna revoke it from the user so it's just a little bit of wording but the difference between granting versus revoking I grant it to the user and I revoke it from the user so now when i revoke I need to know what permissions they already have and I can do my show grants for my user and it would show to me what they have for permissions and I just simply have to revoke those opposite permissions so if I revoke this it's now revoked and so if I show grants for my user and I run this you'll notice that they only have grant usage which means they're allowed to login but that's it so if this user count tries to log into the database all that they're going to see is the is actually just a login and they'll be able to see the sysadmin table because everybody has to be able to see that that's our scheme and default so that dictates what they're allowed to see therefore that user has to have access to it but as far as the rest of my databases when this user logs in they won't see any of those other databases at all so I created an account called my admin and that's not actually a good name for I want to rename that account and to do that I'm just simply going to rename user and then what is the old username so I'm going to say my admin and I'm going to rename it to some new name now what you'll see in some system is especially people that have used Microsoft sequel server is you will see that there's an essay account for system administrator in some systems you'll see administrator and then in other systems you'll see Mickey Mouse just because somebody didn't want there to be somebody by the name of administrator sitting out there so what I'm going to do is I'm just simply going to create this as an account and I'm gonna call it Bob okay so Bob could be a regular user it could be an administrator and now what this does is makes it run somebody logs in and tries to see a list of users there they're not going to know who the administrator account is I could call it whatever I want but in the case of this example I'm just simply going to call it Bob and then we'll execute this query and now we have Bob so now if I come back over to my query to show all of my users and i refresh this what you'll see here is my admin is now Bob now I still have a my user account because I didn't delete the user I just simply didn't grant them permission to access any of the database tables okay so what I want to do is this my user that user account should only have access to whatever information I really want them to have access to I don't want them to have access to everything in the database so what will happen in a production environment is we create these things called views and a view is nothing more than a query that I save that will create something that looks like a table acts like a table I can perform queries on it like a table but it's not a real table it is something that just strictly exists in memory so I want to create a view for the my user account now in our table over here we have a customers table and I hired this user the person called my user and this user is just simply supposed to be able to see customer names and their customer phone number and so what I want to do is I want to make it where that person can only see that information so what I'm going to do is create a brand new view for them so I'm going to create a view and I'm going to call it customer underscore phone and then I'm going to create it as some sort of query and my query is just simply going to be our select statement like we normally have done so select and then customer name comma phone from customers so what I'm doing is I'm doing a simple select statement so I want to select the customers name in the phone from the customers table but instead of running it where out puts it on the screen I want it to actually create a view now what this will do for me is over here in our classic models database we have tables and we have views and I actually have one out here already so I'm going to drop it that way we can actually do those but what we're going to do is we're going to create a brand new view that will function like a table okay so I'm going to run this query it just created it so if i refresh over here then I will see this brand new view and if I were to do something like select everything from classic models dot customer underscore phone I will see something that looks like a table that just simply has the customer name and a phone now in reality this table that I see doesn't really exist it is pulling data on the fly from our actual production customers table over here so the the purpose of the view is to only pull the information that we really need at the time that we run it so I can query this just like a database table okay so this is actually a four of security also because what I'm going to do is I'm going to create views for different job functions so our sales people will have a list of all of our customer names their address telephone number who they're supposed to contact they may have things like the credit limit what the current balance is so I would make a view for our salespeople and so for our receptionists we would just simply make a view that shows this information so he or she can call the company and maybe let them know when their appointments are coming up so what I want to do is I want to lock it down where my user is only able to see information in this particular view you right now my user is only allowed to login my user can't do anything else I want my user to be able to simply select information from the customer underscore phone view and so the way that I'm going to do this is I'm going to grant the Select privilege so that's all this user can do is just simply select which means they can only view information they can update they can make changes or anything like that okay so where do I want them to be able to do that I want it to be on classic models dot customer phone so what I am doing is I'm limiting this person so that they can only view information from the classic models database customer phone view that's all they're allowed to use okay so Who am I doing this to I am doing it to my user run the query and then so now what I've done is I've granted this user the ability to look at customer phone so if I show grants I want to show the grants for my user so now you'll see that they're allowed to log in and they're allowed to select information from the classic models dot customer phone view now it doesn't know necessarily if it's a view or a table when I grant it but when I when I look at the database I can see that I have tables and I have use on my views called customer underscore phone one word of note your view names can not be the same as any tables that exist so I can't create an offices view because the system would get confused so the tables and the views have to be different names they have to be unique so what I have done is I created a new connection that will connect to the local server notice it's still the local host here and what I did is they just simply made this where we're going to log in as my user notice our other existing connections using route this one's going to use my user so I want to connect and I'm going to issue a command for showing the databases when I show the databases we have information schema so we can see information about the database schemas and then we have classic models I don't see any other database tables that are out there so I'm going to use classic models okay so now I'm using classic valves and I want to show tables so notice the only table that I have is customer underscore phone and so as far as the system is concerned customer underscore phone is a table sorry as far as I'm concerned it's a table it will function just like a table and I can select everything from customer underscore phone so I get the information but if I want to do something like updating their telephone number I will actually get an error because I don't have the permission to do that they only assign myself the right to select so I want to try to change the telephone number for signal gift stores so what I'm going to do is open a new tab and I want to try to update the customer underscore phone call table I want to set the phone column equal to one two three four five six where the customer name is equal to signal store yes okay so I'm just simply trying to update the phone number for this particular customer and when I run this I will get an error and the error just simply says command denied to this particular user I don't have permission to update I only have permission to select therefore I cannot make changes in the database all right so let's say my admin loses their password I'm sorry my user loses their password I'm going to exit out of my user connection and I'm back over in the root connection and what I want to do is I want to reset the password for my user okay so the the way that the command works is it's set password for and then whatever the user account so in this case is going to be my user and I'm going to set that equal to a password and the password goes inside of parentheses and also inside of single quotes so I'm going to call it my new password and that's what I'm setting their new password to now if I wanted to do this for myself I would just simply delete the for my user because I could just simply say set password equals and then password and whatever my new password should be so if I want this to be for a particular user I have to specify their name here so when I execute this you will see that the password was reset and now what that actually just did is it did reset their password but it created a new hash for that particular user account so now this user leaves they get fired they decide to quit now I need to get rid of their user account I want to drop them so I'm going to drop user and then it's my user and so by doing this now they're completely gone notice there's a difference between revoking their privileges and dropping them if I revoke the privileges the user account is still out there they can still log in they just can't do anything if I drop them they are flat-out gone so if I look at my table I had my user and if i refresh this now you'll see that I no longer have my user so if I get rid of this user account called no privileges get rid of the user account and I come over here and refresh now they're gone same thing with Bob I want to get rid of Bob execute this and refresh and now Bob is gone and I'm back to just simply having root accounts the last thing I want to show to you is if I am signing in from another system I have the ability to sign in from different host and the way that you would do that is through your new connections when you specify the host name in our existing examples we've used the loopback address but if I want to connect to another server let's say it's a MySQL server at the example.com domain then this is how I would actually list it and if we're using a different port number of enlist that if I have a different username I listed here if I had a different password I could store it and what they call the vault and I can also specify default schema so let's say I log in as a root and I have access to everybody I could tell it that I only want my classic models to be the one that I default to I want it to be the one that as soon as I log in that it will use that database I can give it a name and then we also have the ability to do things like choosing different connection types so I could use a local socket or a pipe so if I'm connecting through let's say a Windows computer name or if I'm connecting to rememory connection I could do SSH so if I want to connect over secure connection and they also have MySQL fabric management node which is a MySQL function but the ones will typically use is going to be tcp/ip we do have the ability to use SSL for encryption if it's available is the default like I said it's a no or I could require it I could also require a certificate authority and I could make it where I have to have a certificate from a specific computer before I'm allowed to use SSL so I actually have the ability to connect using more secure connections you I also have the ability to compress my query so I could actually do things like connect to another system and my commands would be compressed automatically so the other one is I could do clear text authentication so if I want to I could send my authentication across as clear text or I could use the old authentication protocol we don't typically don't want to change anything in these usually we just simply change the information in here you in this lesson you learned about user logins and passwords user roles user privileges including grants checking and revoking privileges how to use different hosts and how to create

Channel: Universal Class

Views: 13,046

Rating: 4.8967743 out of 5

Keywords: universal class, unviersal class, universalclass, universalclass.com, online course, online class, tutorial, training, how to, how to guide, MySQL Course, SQL Course

Id: i_RgtRfCkKU

Length: 37min 47sec (2267 seconds)

Published: Tue Jun 21 2016