Storing user data

Where are we?

You’ve seen the workflow we want in DogToys. You’ve seen how sessions work.

We’ll need some way to remember user names and passwords, and the permissions that each user has. Let’s adding user data to the DogToys database.

This lesson’s goals

Learn:

  • Create a users table in the database. It will have user names, passwords, and permission flags.
  • Good passwords have lowercase letters, uppercase letters, digits, and special characters. They don’t correspond to a dictionary word.

The users table

Let’s add a table called users to the DogToys database. It will have the data we need for authentication and permissions. Here are the fields:

users table

Figure 1. users table

user_id is the primary key, an unsigned integer. auto_increment means that MySQL will automatically supply values when new records are added to the users table.

user_name is, er, the user name. It can be up to 20 characters long.

password is the user’s password. Like user_name, it can be up to 20 characters long.

We’re storing the password in clear text. You wouldn’t do that in a secure application.

permission_add is a one-character field that is either y or n. If it’s y, the user has permission to add products.

permission_edit is a one-character field as well. If it’s y, the user can change data about existing products.

permission_delete controls whether users are allowed to delete product records.

Sample data

Here’s some data. I broke the image into pieces so it would fit on this page.

Users data

Figure 2. Users data

You can see that Kieran and Louise are allowed to do anything to the data. But Renata can only edit data. She can’t add products or delete them.

Look at the passwords. Renata’s password is terrible. If a hacker learned Renata’s user name, one of the first things s/he would try is using the same thing for the password.

Loiuse’s password isn’t very good either. The characters are all the same.

CC’s password is bad as well. It’s a single word. It’s vulnerable to “dictionary” attacks, where a program tries to log in using English words as passwords. Eventually, it would hit “sheltie.” The word is in dictionary.com.

Kieran’s password is the only good one. It uses four different character types:

  • Lowercase letters
  • Uppercase letters
  • Digits
  • Special characters

It’s not vulnerable to dictionary attacks, and is hard to guess.

Maintaining user data

We need to be able to:

  • Add new user records
  • Let users change their passwords
  • Delete records when people leave the company
  • Change permissions when people change jobs

In a real Web application, we’d write Web pages for these tasks. We’d assign someone as an administrator. We’d add a permissions field that would let an administrator access those the pages that change user data.

To keep things simple, we don’t do that in this chapter. It’s not core.

You can change the user data with phpMyAdmin.

Exercise: DogJokes users table

Add a users table to the DogJokes database you created in the previous chapter. Use the same fields I used for the DogToys users table. Add some records.

Summary

  • Create a users table in the database. It will have user names, passwords, and permission flags.
  • Good passwords have lowercase letters, uppercase letters, digits, and special characters. They don’t correspond to a dictionary word.

What now?

Now let’s see how you use the table in the log in process.


How to...

Lessons

User login

Log in problems? Try here


Dogs