IT360 Lab 8 Database Security


DUE: April 14, 2009, BEFORE start of lab


This lab is a collection of database security concepts.  You will see how to ensure that passwords are protected by the Secure Sockets Layer protocol, give others secure access to your data, and use session variables for secure access to your web site.  Open a document and call it yourlastname_Lab8.txt.  You will place all your answers to the lab questions in this document. 


Preliminaries: Download the file from the course calendar (Lab8) and unzip it on your W drive.  You are going to upload two tables to your database: users(User_ID, Password, Type) and songs(Song_ID, Artist, Title). If you have another version of these tables that you would like to save, you can create a backup of your database by using MySQL AdministratoràBackupàNew Projectàchoose name for project, select your schema to backup, select the tables you want to backup, and “execute backup now”. After you create the backup, you can drop the tables from your database, and re-create them later (after you are done with this lab).  If you don’t already have these two tables, run the script in it360.sql to load them (MySQL Query Browser àfile àopen scriptàExecute). 


Part 1 Database Security: Granting Privileges


As a database user, you currently have a username (mxxxxxx), password, and you have the rights (permissions) to a particular database (mxxxxxx) where you can create tables, insert into them, etc. Assume now that you are a database administrator, and you are the one giving permissions to users to use the database. For security reasons, you should restrict access to your database as much as possible, and only give permissions that are really needed in order to accomplish the task. We'll accomplish this through the use of the SQL GRANT statement.

GRANT <permissions>
[ON <table(s)>]
TO <user>


Now, let's take a look at this statement line-by-line. The first line, GRANT <permissions>, allows us to specify the specific permissions we are granting. These can be either table-level permissions (such as SELECT, INSERT, UPDATE and DELETE) or database permissions (such as CREATE TABLE, DROP TABLE, ALTER DATABASE, GRANT, etc). More than one permission can be granted in a single GRANT statement, but table-level permissions and database-level permissions may not be combined in a single statement.

The second line, ON <table>, is used to specify the affected table for table-level permissions. Use the format database.table(s). This line is omitted if we are granting database-level permissions. The third line specifies the user that is being granted permissions.

Try it out by Granting SELECT on your songs table to a partner.

1.      What does the GRANT statement look like?


2.      When they log into MySQL after this statement is invoked, what has changed?



3.      What happens when they run a SELECT statement on the table?


4.      What happens when they try to INSERT a new song into the database? 


Part 2 Application Security: Protecting Passwords


Secure Sockets Layer: Many of us have begun to use online banking.  Go to the homepage for Navy Federal Credit Union at  Note that the text boxes on the left are similar to the login screen that you created in a previous lab and project.  Click on the “Security” link.  Use any resource you would like and research how the server and client communicate during the session set up process for SSL (Chapter 17 in the textbook might help). 


  1. Consider your knowledge of networking and Internet programming and describe what happens when you enter your login information in these text boxes and press the “Sign on” key. 


  1. What would be the address in the browser’s address bar to invoke your product_page.php in Lab5 using a secure connection (SSL)?


Note: The rest of this lab is a recap of some security issues we discussed before. You still need to complete this part of the lab.


SHA Hashing:  Modify user_page.php downloaded from the course calendar to add an “add user” button. When clicked, the “add_user” function in user.php is invoked.



Add a line in add_user function in user.php to encrypt the password received by using the php function sha1( ), which returns the calculated sha1 hash of $str 


pstring sha1 ( string $str )


Now modify the “validate” function in user.php to check against this new sha1 value.  Check your MySQL database and see that the passwords are no longer saved in the clear. 


7.      How does this change the response to a user’s question: I forgot my password, can you tell me what it is?”



Part 3 Application Security: Session Variables

Your song website allows users to log on to vote for their favorite song.  Only valid users should be able to see the list of songs, but try to access the song_page.php without logging in. 


  1. What happened?  Why?  Although you may not think that this is a big deal, what if it was a list of your accounts and account numbers instead of a bunch of songs? 


Correct the file song_page.php to ensure that only valid users (ie. those who have logged in properly and have had the appropriate session variable set) will be able to see the page content.


Note: Make sure you incorporate these “best practices” into your project.  This means adding the appropriate test cases for SQL injects, checking session variables before page displays and saving all passwords as hashed values.  We will not be using SSL for this course, but in the real world, you would also make sure to use SSL for appropriate secure transactions (ie, logins and passing of account information).


Turn in (due before start of lab on April 14, 2009):


  1. Upload yourlastname_Lab8.txt with all your answers, and your version of the files user_page.php, user.php, and song_page.php to the Lab 8 assignment on the Blackboard.



  1. The completed assignment coversheet. Your comments will help us improve the course.
  2. A hard copy of your yourlastname_Lab8.txt containing the answers for each exercise, and hard copies for user_page.php, user.php, and song_page.php.