IT360 Lab 12 Database Security
DUE: April 14, 2014, before 2359 (paper copy due BEFORE start of lab next day)
This lab is a collection of database security concepts. You will see how to give others access to your data, ensure that passwords are encrypted, and use session variables for secure access to your web site. Open a document and call it yourlastname_yourfirstname_Lab12.txt. You will place all your answers to the lab questions in this document.
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.
For example, GRANT INSERT ON mydatabase.AppUsers to mxxxxxx, will allow the user mxxxxxx to insert data into the AppUsers table in the database called mydatabase.
Now, let's take a closer look at the GRANT 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 permissions 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 databaseName.tableName(s). This line is omitted if we are granting database-level permissions.
The third line specifies the user that is being granted permissions.
For this part of the lab, you will need a partner. Please work with a colleague to accomplish and test the tasks below. Write all answers to yourlastname_yourfirstname_Lab12.txt
1. Connect to cardhu using MySQL Workbench and then try to select all songs from your partner’s songs12 table: SELECT * FROM myyyyyy.songs12; where yyyyyy is your partner’s alpha. What is the answer?
2. Ask your partner to grant you select privileges to his songs table. You should also write a GRANT statement to grant SELECT privileges on your songs table to your partner (use the GRANT syntax given above). Write your statement in yourlastname_yourfirstname_Lab12.txt
3. Try again the SELECT * FROM myyyyyy.songs12; where yyyyyy is your partner’s alpha. What is the answer this time? If you get the same answer as before, make sure your partner executed the Grant statement and try to logout and log back in to MySQL Workbench.
4. What happens if you try to INSERT a new song into your partner’s database by using the following statement: INSERT INTO myyyyyy.songs12(Title, Artist) VALUES('Your Favorite Song', 'Your Favorite Artist')? Why?
5. Now that you saw how you can grant privileges using the GRANT command, revoke the privileges that you granted to your partner: REVOKE SELECT ON mxxxxxx.songs FROM myyyyyy; where yyyyyy is your partner's alpha.
Part 2 Application Security: Protecting Passwords
I have provided a basic userPage.php and user.inc.php in the zip file downloaded from the course calendar. The userPage.php is a basic login page. We will add to that functionality next.
Test your add user button by inserting a few users. Check that users were indeed inserted by using the MySQL Workbench, SELECT * FROM users12;
$hashed = sha1("some string here");
returns the hashed version of the "some string here".
Insert a new user by invoking userPage.php from a browser and check your MySQL database and see that the passwords are now hashed.
8. Now modify the “validate” function in user.inc.php to check against this new sha1 value.
9. How does the hashing change the response to a user’s question: I forgot my password, can you tell me what it is? What should be the answer if hashing is used? Write your answer in yourlastname_yourfirstname_Lab12.txt
Part 3 Application Security: Session Variables
Remember the song website we started to create in class. With the new userPage.php in place, I would like to only allow valid users to access the songPage.php. The code currently in the userPage.php sets a session variable for the valid users and redirects them to songPage.php, so everything seems fine if users invoke userPage.php first. Only valid users should be able to see the list of songs, but try to access the songPage.php without logging in (without going to userPage.php first).
Part 4 Application Security: SQL Injections
b) What are the effects of your attack?
Extra credit: Real SQL Injections
Extra credit: Secure Sockets Layer: Many people use online banking. Go to the homepage for Navy Federal Credit Union at http://www.navyfcu.org. Note that the text boxes on the left are similar to the login screen that you used in this lab (userPage.php). Use any resource you would like and research how the server and client communicate during the session set up process for SSL/TLS (Chapter 18 in the “PHP and MySQL Web Development” book, your networks class, or discussion on SSL/TLS from IT350 might help).
Finally: 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 TLS for this course, but in the real world, you would also make sure to use TLS for appropriate secure transactions (ie, logins and passing of account information).
Turn in (BOTH electronic and paper submissions are required):
Electronic (due before 2359 on April 14, 2014):
Hard-copies (due before start of lab on April 15, 2014):