Goal
This will be a short lab. We are trying to get everyone logged into the lab and using at least two different SQL-based database systems. That’s all.
Using the cheat sheet
Start by opening
Stephen Scott’s
(My)SQL Cheat Sheet and finding the commands for
creating a tables, inserting rows into a table, and printing all rows
from a table.
These three commands are CREATE TABLE,
INSERT INTO, and SELECT.
Using the terminal
We’ll be using the command line today. Open up a terminal session and get ready to type.
Using SQLite
Take a look at the web page for the
command line shell for
SQLite and create a database called lab01
while connected to your home directory.
By the way, the command line shell is called
sqlite3 ,
Insert some interesting values in your database.
Exit sqlite3 and list your directory. You should see a file named lab01 .
Now restart sqlite3 on the same database and list a few values. Your tables should contain the same values.
sqlite3 stores everything in a file. This is not a client-server system.
Using mySQL
A database and account has been created for you on the department’s MySQL server mysql.cs.unca.edu . Your account name is the same as your username. Attach the capital letters “DB” to the end of your username to get the name of your database.
Connect to your MySQL database with the following command:
mysql -u username -h mysql.cs.unca.edu -p usernameDB
Your password was generated using the same algorithm used to generate your initial workstation password. You can look at the UNCA CSCI Username and Password page, if you can’t remember the algorithm.
In many ways, SQL is SQL, but the MySQL Wikibook does have a cheat sheet that lists some of the specialized commands of MySQL.
At this point, go ahead and create a table with values, similar to
what you did with SQLite3.
Also, use SQL’s select to list some values.
MySQL Workbench
Of course, everyone should use the command line, but there is a graphical interface to MySQL called MySQL Workbench that can be downloaded for Linux, Mac OS X and Windows operating systems.
You should be able to start it from the command line by running the program mysql-workbench. You should also be able to find it in the applications menu.
If you have a personal computer with you, you might also want to try installing it now.
In any case, use MySQL Workbench to connect to your MySQL database and make some changes.
By the way, most Linux distributions continue to use the pre-Oracle GPL version of the workbench.
Protecting the data
It’s back to the command line with SQLite for a minute. You’ll need a partner for this part of the lab. However, you and your partner will continue to both use your own terminal sessions.
Figure out the filename of the your SQLite3 database
and share it with your partner.
It’s probably something like
/home/username/lab01 .
Use the sqlite3 program
to connect to your partner’s database.
You should be able to query (SELECT)
but unable to modify (INSERT).
Now exit sqlite3 and add a little protection to your database. Since SQLite database protection depends solely on your operating system’s file protection mechanism, you’ll need to use the Unix chmod command to change the file permission settings. Look at University of Washington’s tutorial on file permissions to see how to do this.
Set the permissions of your database file so that only you can read the database. (By the way, you can prove you hackitude by using octal numbers to do this.) Have your partner verify that your database is now unreadable.
Next set the permissions of the database file so that everyone can read and write your database. Now have your partner modify your database.
Of course, universally writable files are a bad idea, so undo that last change.
In order to meet the Security-Enhanced Linux requirements of the NSA, most operating systems provide some support for access control lists. If you want to set up your database so that only you and your partner can use it, take a look at Red Hat’s documentation about access control lists. It is not easy to use.
What about MySQL?
MySQL has a more sophisticated system for protecting database access.
We’ll save that for a later lab.
Also, your database was not create with the
GRANT OPTION,
so you can’t grant access to others anyway.
(See the
documentation about the GRANT statement.)