Learn how to create the MySQL database and grant privileges for a user to access all tables on the database.
- In this movie, we will create the MySQL database that our project is going to use to store its data. There are three MySQL commands we'll be using. The first is to create the database using CREATE DATABASE and then the name of the database. I'm going to call it chain_gang. Second, we want to make sure that we have a user that has the ability to access that database. Now, by default, our root MySQL user has access to all of the databases, but it's a much better security practice for us to create a new user that our application can use, instead of using that root user.
That root user's just simply too powerful, and can do too much damage to our database. So, we're going to create a new user called webuser and give it privileges. So I do that with GRANT ALL PRIVILEGES ON, and then I have the database name, followed by a period and an asterisk. That's a shorthand for all tables on the chain_gang database, and I'm going to assign those to the webuser at localhost. Using localhost ensures that connections can only be made from the local computer, not from some remote IP address somewhere else.
And then, IDENTIFIED BY and the password you want to use. I'm using secretpassword. I hope that you'll choose something much better because that's not a very good password. And then, finally, once we've done that, we can actually choose to use our newly created database by typing USE and then the database name, chain_gang. Let's try these three out. So, from our command line, I'm going to go into MySQL. I'm going to do that using my root user to begin with. I'm not going to use my web app user to do this; I want my root user that has all of the powers and privileges to create new databases.
So I'll log in with that password, and once I'm inside, then I can CREATE DATABASE chain_gang. Okay, it says it created the database. We can actually confirm that with SHOW DATABASES. We'll see a few of the basic databases that MySQL needs, but we'll most importantly see that database there, chain_gang, so we know it was created. So now, let's grant privileges on it. So we'll do that with GRANT ALL PRIVILEGES, make sure you spell that right, ON chain_gang.*, because we want all tables.
And then you can go to a new line here, or you can just keep typing, it doesn't matter. Space, webuser@localhost, and then another space, go ahead and go to a new line here, IDENTIFIED BY and then secretpassword will be mine, but hopefully you'll choose something better. And then the semicolon at the end lets it know that the command is done. Now we have privileges for our webuser, which is great, now we'll be able to log in as the webuser from our PHP code.
And then, last of all, let's type USE chain_gang. And that will actually switch to the database, so now we can issue all of our commands directly to that database from the command line.
Note: This is an intermediate-level training course that assumes you have existing knowledge of PHP. To refresh your skills, check out PHP Essential Training and PHP: Object-Oriented Programming.
- Creating a project database and tables
- Connecting to the database
- Database queries with OOP
- The active record design pattern
- Defining a database-driven class
- Performing common database tasks with OOP
- Creating inheritable and reusable code
- Object-oriented user authentication
- Object-oriented pagination