PHP – Creating Effective User Acces: Database Design
Okay, when we look at creating effective user access, the first thing we have to look at is the database design. As I mentioned in the overview post of this series, we will not look in-depth at how to connect to a MySQL database, the code will be shown, but not explained. However, we do need to explain a decent table layout, as well as define what tables are needed in our example here.
One thing I should mention is some specifics that I will be basing the rest of this series off of, in others here is the list of requirements I am going to meet. While the list is not extensive or exhustive, it provides a good base to which you should be able to extend to fit your requirements list.
- The site needs to log users in and out.
- The site needs to keep users logged in for a minimum of 15 minutes.
- The site needs to keep users logged longer if they desire to choose “Remember Me”.
- The site needs to authenticate on all pages.
- The site needs to logout users by session timeout, cookie timeout and user forced logout.
- The site should log all login attemps, whether successful or not for security purposes.
- The site should be able to provide the user with their last login date and time.
- The site should operate over a secure connection.
- The site should lock an account after three unsuccessful login attempts, if the account login name is correct and the password is wrong, and it should stop accepting submissions if the user name is not found within three attemps and log the error.
On to the MySQL database definition…..
We will want to create two basic tables in our database for this project. The first table will contain the user information while the second table will contain the logging information.
On the following table definitions, the convention is:
columnName, columnType, columnNull/NotNull, columnDefault
For the user table we will have the following definition:
ID, int(11), Not Null, auto_number - Primary Key
user, varchar(100), Not Null
auth, varchar(50), Not Null
isLocked, tinyint(1), Not Null
isDisabled, tinyint(1), Not Null
For the accessLog table we will have the following definition:
ID, int(11), Not Null, auto_number - Primary Key
userID, varchar (100), Not Null
hostname, varchar(150), Null
ipaddress, varchar (50), Null
timestamp, timestamp, Not Null, CURRENT_TIMESTAMP
It should be noted here that the auth field is the password field, and that it will be encrypted for security purposes, thus the length is set to 50 to accomodate the lenght of the password after encryption has been applied. The isLocked field will allow the automatic locking of any valid account that has three unsuccesfull login attempts on it, while the isDisabled account will allow a system admin or webmaster to manually and temporarily disable accounts, which is useful if you run a paid-for-service or membership service.
