School of Computing and Information Technology Session: Spring 2022
University of Wollongong Lecturer: Tianbing Xia
CSIT115 Data Management and Security
Laboratory 6
Published on 6 October 2022
Scope
This laboratory includes the tasks related to discretionary access control.
The outcomes of the laboratory work are due by Saturday 15 October 2022, 10.00 pm (sharp).
Please read very carefully information listed below.
This laboratory contributes to 3% of the total evaluation in a subject CSIT115.
A submission procedure is explained at the end of specification.
This laboratory work consists of 1 task.
It is recommended to solve the problems before attending the laboratory classes in order to efficiently use supervised laboratory time.
A submission marked by Moodle as Late is treated as a late submission no matter how many seconds it is late.
A policy regarding late submissions is included in the subject outline.
A submission of compressed files (zipped, gzipped, rared, tared, 7-zipped, lhzed, … etc) is not allowed. The compressed files will not be evaluated.
An implementation that does not compile due to one or more syntactical errors scores no marks and implementation that has the processing errors scores no marks.
It is expected that all tasks included within Laboratory 6 will be solved individually without any cooperation with the other students. If you have any doubts, questions, etc. please consult your lecturer or tutor during lab classes or office hours. Plagiarism will result in a FAIL grade being recorded for the assessment task.
Prologue
Connect to Moodle and download the files dbcreate.sql, dbdrop.sql, dbload.sql, dbcount.sql, and dbschema.bmp from Sample database on Moodle.
SQL script dbcreate.sql can be used to create the relational tables of a sample database. SQL script dbdrop.sql can be used to drop the tables of a sample database. SQL script dbload.sql can be used to load data into a sample database. SQL script dbcount.sql can be used to display the total number of rows in each table included in a sample database. Finally, a file dbschema.bmp contains a conceptual schema of a sample database.
Connect to MySQL database server either through command line interface mysql or graphical user interface MySQL Workbench.
When connected, select a database csit115 with a command use csit115.
To create the relational tables of a sample database, process SQL script dbcreate.sql.
To load data into the relational tables created in the previous step process SQL script dbload.sql.
To list the names of relational tables created, use a command show tables.
To list a structure of a relational table table-name use a command describe
table-name .
To list the total number of rows in each relational table process a script dbcount.sql.
Use an image viewer to open a file dbschema.bmp with a conceptual schema of the sample database.
No report is expected from the implementation of the actions listed above.
Tasks
Task 1 (3 marks)
Download a template file solution1.sql and insert the implementations of the following Discretionary Access Control (DAC) that creates a new database, creates the new users, creates the new roles, grants roles and privileges to the new user accounts, and sets resource limits. Note that a user csit115 does not have the privileges required to process these steps. You must connect as a user root with a password csit115.
Your implementation must directly follow a comment with a specification of a subtask.
(1) Create a database with the same name as a prefix of your University email account. For example, if your University email account is abc001@uow.edu.au then a name of a database should be abc001.
(0.1 mark)
(2) Create two new user accounts. The names of user accounts and passwords are up to you.
(0.1 mark)
(3) While connected as a user root, process the scripts dbcreate.sql and dbload.sql to create and load data into the relational tables in the database created in step (1). A listing of SQL statements of the scripts dbcreate.sql and dbload.sql processed by the scripts MUST NOT be included in a report.
It means that before processing the scripts you must process the statement notee;
after processing the command
use your-database-name;
This command turns the spooling off.
Then process the scripts dbcreate.sql and dbload.sql. Finally, you must process a statement tee solution1.rpt; to turn the spooling on again into a report file.
(0.2 mark)
(4) Create two new roles: emp and admin.
(0.1 mark)
(5) Grant to a role admin the read privileges on the entire database created in step (1). The privileges must be granted such that any owner of a role admin cannot grant the same privileges to another role or user.
(0.1 mark)
(6) Grant to a role emp read privilege on the relational table PROJECT in the database created in step (1). The privilege must be granted such that any owner of a role emp can grant the same privileges to another role or user.
(0.2 mark)
(7) Grant to a role emp the read and write privileges on the relational table DP in the database created in step (1). The privileges must be granted such that any owner of a role emp cannot grant the same privileges to another role or user.
(0.2 mark)
(8) Grant to a role emp the read privileges on the columns name, hireDate, dName, city in a relational table EMPLOYEE in the database created in step (1). The privileges must be granted such that any owner of a role emp can grant the same privileges to another role or user.
(0.2 mark)
(9) Grant to a role admin the write privilege on a relational table PROJECT in the database create in step (1). The privilege must be granted such that any owner of a role admin cannot propagate the same privileges to another role or user.
(0.2 mark)
(10) Grant to a role admin a privilege to create relational tables in the database created in step (1). The privileges must be granted such that any owner of a role admin cannot grant the same privileges to another role or user.
(0.1 mark)
(11) Grant to a role admin a privilege to create relational views located in the database created in step (1). The privileges must be granted such that any owner of a role admin cannot grant the same privileges to another role or user.
(0.1 mark)
(12) Grant to a role emp the read privileges on information about the department name and total number of projects for each department. The privileges must be granted such that any owner of a role emp can grant the same privileges to another role or user.
Hint: Create a relational view and grant the read privileges on the view.
(0.5 mark)
(13) Grant a role emp to one of the users created in step (2), and then grant a role admin to another user created in step (2).
(0.2 mark)
(14) Set a resource limit on the maximum total number of queries per hour and the total number times of connections per hour to both users created in step (2). The values of the maximum total number of queries per hour and the total number times of connections per hour are up to you.
(0.4 mark)
(15) Drop the users, the roles and the database that created in steps (1), (2) and (4).
(0.3 mark)
You can find a lot of information about discretionary access control in a presentation 20 Discretionary Access Control, 21 User Management, and in Cookbook, How to manage discretionary access control? Recipe 9.1, 9.2 and 9.3.
To implement and to test SQL script file solution1.sql, you can either use graphical user interface MySQL Workbench or command line interface mysql.
Note that some commends (tee, notee and source script_file) cannot be processed on MySQL Workbench. You can open the script files and click the lighting button to process script files.
It is recommended to refresh the database if you need to reprocess the solutions. You can drop the database, the users and the roles created in steps (1), (2) and (4). In such way, your script always operates on the original of the database systems.
To create a report from the processing of a script file solution1.sql, open a Terminal window and start the command line interface mysql in the following way:
mysql -u root -p -v -c
Next, process SQL script solution1.sql and save a report in a file solution1.rpt. Note that when started with the options -v and -c the command line interface includes both listing of SQL statements that processed and the comments included in the original version of the file solution1.sql.
Note: Make sure the script files dbcreate.sql and dbload.sql are copied to the same directory with the file solution1.sql.
We have already practiced saving a report from the processing of SQL script in Laboratories 1 and 3. You can also find more information about creating reports from the processing of SQL scripts in Cookbook, Recipe 3.1 How to use “mysql? Command based interface to MySQL database server? Step 4 How to save the results of SQL processing in a file?”
A report that contains no listing of processed SQL statements scores no marks, and report that contains errors of any kind also scores no marks!
Deliverables
A file solution1.rpt with a report from the processing of SQL script file solution1.sql. The report must be created with the command line interface mysql, and the report MUST NOT include any errors, and the report MUST LIST ALL SQL statements processed with the results, and ALL comments included in the original (downloaded) version of the file solution1. Marks will be deducted for the missing comments. Submission of a file with a different name and/or different extension and/or different type scores no marks.

Submission
Note, that you have only one submission. So, make it absolutely sure that you submit correct files with the correct contents. No other submission is possible!
Submit a file solution1.rpt to Moodle in the following way:
(1) Access Moodle at http://moodle.uowplatform.edu.au/
(2) To login use a Login link located in the right upper corner the Web page or in the middle of the bottom of the Web page
(3) When logged select a site CSIT115 (S222) Data Management and Security
(4) Scroll down to a section Submissions
(5) Click on a link In this place you can submit the outcomes of Laboratory 6
(6) Click on a button Add Submission
(7) Move a file solution1.rpt into an area You can drag and drop files here to add them. You can also use a link Add…
(8) Click on the checkbox with a text attached: By checking this box, I confirm that this submission is my own work, … in order to confirm the authorship of your submission
(9) Click on a button Save changes
End of specification