References

Requirement 1: User Management Interface

The goal of Requirement 1 is to build a User Management Interface (as a GUI) for managing user accounts in MySQL. Your GUI should allow granting and/or revoking privileges to existing and/or new users for various "resources" in MySQL. We care the following types of resources:

• Databases;
• Tables;
• Columns;
• Stored procedures.

Here is a sample JDBC program that connects to MySQL, gets all the database names, and acquires information about different tables and columns within "moviedb" database. (don't forget to add the mysql JDBC jar file to your classpath at the time of compiling. You'll also need to change the root password in the DriverManager.getConnection() line to before compiling).

The set of privileges that are allowed to be granted/revoked on resources using your GUI is at your discretion. However, there are some implicit restrictions. For instance, privileges such as select, insert, update make sense at the database, table as well as column level, but cannot be associated with stored procedures. Similarly, a user can be granted execute privilege on a stored procedure, but not on a database, table or column. You should keep such restrictions in mind when designing your GUI and deciding how you want the user of your GUI (we'll call him the DBA) to be able grant/revoke privileges.

You will need to demonstrate that privileges were correctly added/revoked by using your GUI. To do so, you'll need to issue SQL queries at the command prompt in a separate window that are relevant to the changes you made through your GUI. For example, if using the GUI, the DBA grants a user by the name joe select privilege on the "stars" table in the moviedb database, you should be able to log in through a terminal using joe's credentials and successfully execute a query such as select * from moviedb.stars Similarly, if your GUI shows that there are certain privileges that joe does not have, the corresponding sql queries should fail.

The exact look and feel of the User Management Interface is up to you. As a guideline, you can refer Fig 6.8, which is part of one of the chapters of the MySQL commercial workbench reference While the figure shows 3 tabs with "roles" , "objects" and "privileges", you can have something like "users" instead of "roles" and "resources" instead of "objects". Again, this is only one of several possible ways of designing your interface, and you should feel free to implement an original idea that you find intuitive and user-friendly.

Here are a few more suggestions that should help you.

• Thoroughly read the MySQL documentation on privileges and user account management.
• You might consider having a "main" window in which you can easily visualize information on current users, resources and privilges, and an auxiliary window from which the program accepts information about new users that you want to add (ex: username and password, host ?) To be consistent with good design principles, if you do things this way, you should try and "refresh" the "main" window with the info that your program accepts from the auxiliary window.
• Have appropriate error handling. For example, if the DBA tries to add a user that already exists, that should be an error and handled in a way that is appropriate to your system and communicated back to the DBA in a manner appropriate for your user interface.
• Try to display lists of things (privileges, resources, users) so that as many as feasible fit neatly on one screen.
• Try to take advantage of the heirarchical nature of resources in designing your interface (multiple databases in the DBMS, multiple tables in every database, multiple columns per table, etc). This will facilitate the selection of resource(s) on which a particular user is being granted/revoked certain privilege(s),

Some Technical Details

The default superuser in MySQL 5.1 is "root". The "root" account is created at the time of installation. The password for this account should be the password you provided at the time of installation of MySQL. For unfettered access to adding and/or revoking different privileges, you can assume that the DBA is logged in as root.

All privilege information is maintained in the MySQL grant tables (Explained in detail in the reference section on MySQL privilege management). In particular, the 4 main grant tables in MySQL are:

• user
• Db
• tables_priv
• columns_priv

Do not directly change the information in these grant tables using your back-end program. Instead, call upon GRANT, REVOKE, ALTER, CREATE and DROP as appropriate; these commands will update the grant tables as necessary.

As FabFlix inventory is added to the FabFlix database, customer browsing and searching times may increase to unacceptable levels. So, here we create a large test database, reflective of how big the FabFlix database is likely to become, and determine if indexing certain database tables is warranted.

• Generate a test database of at least 200,000 movie records and at least 20,000 star records. Titles and names can be randomly generated. (Hint: Write a short JDBC program that generates a list of titles and a list of stars, then write combinations of these repeatedly to a file such that 200,000 movie and 20,000 star records are written in a Comma Separated Value format, ready to be read by the LOAD DATA IN-FILE command in MySQL.

• Using the FabFlix back-end program as modified through Project 3, conduct at least 10 searches by movie title, at least 10 by star name, and at least 10 by both movie title and star name. (Searching is done in the manner described in Project 2.) Make sure the searches lead to different parts of the tables, in particular its start, middle, and end. Determine the average time it takes to complete each of these three kinds of searches. You may do this as follows: add code to your FabFlix system so that the computer measures and reports the time. For instance, you can use System.currentTimeMillis(), which reports the current time with an accuracy of 10 milliseconds. Call the method before you start some operation, call it again right after that operation completes, and subtract  to get the time the operation takes. You may even find the MySQL commands EXPLAIN and ANALYZE TABLE useful.

• Now create various indexes into the database tables. Your goal is to have these indices make the searches significantly faster. Which tables you index, what you index on, and how many indices you have--that's all up to you. Think carefully about what to try. Redo the same searches as before with (various combinations of) indices in effect and again determine the average search times.

• Write a short (one page or so) summary report that explains what indices you tried and the results you obtained when using them and when using no indices. Then give your reasoned opinion as to what indices should be used in the final, production system, and why. Remember there are trade-offs; for instance, indices take more disk space, and it takes longer to add and delete records when indices are in use. Your report is to be stored in a file that is readable by Word or Firefox.

  Desired, but not required for full credit (which means that if you do it you can earn credit that can offset points lost for other reasons) are tests with multiple users searching the database simultaneously. This can be done  by modifying your back-end system to repeatedly issue a SQL command and using multiple such back-end systems simultaneously. You can also do it by writing a multi-threaded Java program where each thread queries the database.

---

Deliverables

Turn in one ZIP file, labeled with your team name, that contains your updated employee access system and your tuning report; make sure the file's extension is ZIP.
Turn in the ZIP file via EEE.
Your ZIP file is due no later than May 20th, 11:45 pm

Plan how you want to demonstrate the capabilities of your GUI. Try and cover different types of functionalities and carefully choose the sql queries that demonstrate that the corresponding functionality works. What order you do things in is up to you. Additionally, you might also want to show us that the changes were indeed made to the necessary grant tables in the mysql database. Remember that for database specific privileges assigned to a user, you'll have entries in the Db table as well as the the user table. For example, if you assign a SELECT and INSERT privilege on all tables in the moviedb database to joe, then you'll have an entry for joe in the user table as well as an entry in the Db table, tying joe's privileges to the moviedb database.

We will also ask you to briefly explain the results of your tuning tests and your recommendations regarding indexing of the database.

Written Chen Li as "Project 4" for ICS185 Fall 2005.
Revised by Norman Jacobson for the Spring 2005 offering of ICS185, May 2005.
Revised by Norman Jacobson with assistance from Michael Sirivianos, for the Winter 2006 offering of ICS185, to include FabFlix user groups, and to reflect the use of postgres 8.1, including the use of "roles," February 2006. Revised by Chaitanya Desai and Chen Li in Spring'08.