In this video, learn how user accounts work in MySQL. Discover how to create and manage user accounts and how MySQL stores passwords.
- [Instructor] When you installed MySQL you created a root user account. The purpose of the root account is to facilitate the installation and setup of the server. You should rarely if ever, use the root account beyond that purpose. That said, because it's essential to setting up the server, the root user may not be deleted. A user credential in MySQL is a combination of a user name and a host. So, let's create an admin account, this is the equivalent of the root user, but it's fully under your control. So I'm going to log in right now as the root user and I'm going to select Users and Privileges from the Administration pane and add account. Now this starts out calling it New User, I'm going to change that to admin, and I'm going to select the caching sh2 password type. This is the type of a password that's used for modern MySQL. You'll notice the Limits to Hosts Matching; this limits where you can log in as this user and we want it to be the local host so I'm going to type that in here. You'll notice that what's in here by default is a wildcard, the percent sign, which means any host. These wildcards and percent sign and the underscore are just like the wildcards used in SQL for the like clause. Now I'm going to type in here localhost and I'll put in a password, and I'll type that password again and I'm not going to press apply yet, I'm going to come over here to Administrative Roles. You'll notice that nothing is checked. These are all the global privileges that this user has and we want to give it all those privileges, rather than checking all of these at once, we're going to use one of these roles and here we have the DBA role, which grants all of the rights. And so there's all of the rights granted. And then under account limits, we don't need any account limits for this, and under Schema Privileges it's worth noting that MySQL uses the word schema interchangeably with the word database, and we'll cover that in some detail later on but for right now just know that we're not limiting this to any particular databases, it's just going to be able to do everything everywhere, just like the root user only it's under our control. So we're calling it admin and you can only log in from local host and I'm going to press apply and now the account is added. So I can close this and I can close our local instance here and I can now add a connection. I press the little plus here on the home screen. I'm going to call this admin and Hostname 127.0.0.1 is the local host, so that's fine. The user name is going to be admin and there's no default schema or anything like that. Use SSL. We just leave all of the rest of these things as they are, it's going to be a standard TCPIP connection, and I'll press okay, and now we have admin and it's a localhost 3306, just like the local instance for root and when I click on that it'll ask me for the password, I'm going to click Save password in keychain. Only do that if you know that your computer is secure. I know that this one is secure and I'm going to type in the password and press okay and now we're logged in as the admin user that we just created. So MySQL user credentials include both the user name and the host name, and in this case it's admin@localhost and this allows you to limit certain users to certain hosts. We'll look closer at this in the rest of this chapter.
- Installing MySQL on macOS, Windows, and Linux
- Installing MySQL Workbench
- Importing SQL data into MySQL
- Creating user accounts
- Managing roles
- Implementing storage engines
- Creating a new database