Create the database table for storing information about the administrators who are authorized to access the password-protected staff area.
- [Instructor] In the last movie, you'll recall that the first step in user authentication is to add a person's credentials, or their username and password to the database. To do that, we first need a database table that can store those credentials. The OSQL command we need to do this is Create Table. Then we provide the table name and then in parenthesis, the column definitions. You can see here I have a primary key of id. Then I have columns for first name, last name, email, username, and hashed password.
All of those are going to be of type VARCHAR and have a link of 255 characters, because they're just going to store strings in them. Now first name, last name, and email are really just conveniences so I really know who this person is in real life, and how I can reach them. The most important part of the credentials are the last two, their username and their password. Notice that I don't call it just password though. I call it hashed, underscore, password. Hashed is another name for an encrypted string, so this an encrypted password. And I named it that to remind me of that fact, that it's always going to be encrypted.
It's not a plain text password, it's an encrypted password. You may also remember that in part one of this course we talked about indexes and how indexes can allow you to look up information quickly based on a certain column. And there's always an index on the primary key. And we talked about how to add indexes on a foreign key. It's also a good idea here to have an index on username. If you think about it, we're going to be looking up admins by their username whenever someone logs in. We're not going to be looking for them by their id, we're going to be saying "Hey admins table, "do you have a user with a username "that matches this log in?" So in order to enable that process to go as quickly as possible, it's a good idea to add an index on that column.
This will just allow us to look up things by the username very quickly. Let's try adding the Admins table to our database. The first step is to log into mysql from the command line. You can see I've got here mysql- u and I'm using my webuser, that's the user that I created that has access to the globe bank database, globe_bank. And I'm using a -p option because I want to type in a password. So I'll type return, it'll ask for my password. Then once I type that in, I'll be inside MySQL. And I can say SHOW TABLES, and you can see that I have just my subjects and my pages table.
So now we need that Admins Table. In the exercise files, you'll find that definition that we just created, or you can go back and pause and copy it down. I'm just going to copy this first part and I'll paste that in. And that created my table. And then let's do the second one which is to add that index, ALTER TABLE admins, and we'll paste that one in, and it says OK. So now let's do SHOW TABLES, and you'll see I have my admins table. If I say SHOW FIELDS FROM admins, you'll see it comes up and it shows me the different columns that it created.
Now that I have an admins table, I just need a way for staff users, people who already have access to the staff area, to be able to add new users, to authorize them. So we'll tackle that next.
- Creating a content management system
- Building dynamic content navigation
- Using context and function options for conditional behavior
- Hiding content from the public view
- Insecure direct object reference
- Allowing HTML in dynamic content
- Previewing content in the public context
- Working with cookies and sessions
- Storing status messages in the session
- Secure user authentication (login/logout)
- Regulating page access
- Nesting related resources
- Managing an ordered list automatically