Assignment 3
Microsoft Word - Assignment 3 - Understanding and applying data security.docx Diploma of Information Technology Session: Spring 2020 UOW College Australia DPIT115 Data Management and Security Assignment 3 Published on 8 September 2020 Scope This assignment is related to discretionary access control in relational database systems, verification of a complex consistency constraint, implementation of a simple auditing system, and database backup and recovery techniques. You will also complete an incident report on an incident reported in the media and discuss the incident with your tutorial class. Please read the information listed below very carefully. This assignment contributes to 15% of the total evaluation in a subject DPIT115. The outcomes of the assignment work are due by Tuesday 22 September 2020, 11.55 pm. This assignment consists of 5 tasks, and specification of each task starts from a new page. A submission procedure is explained at the end of the specification. A submission of compressed files (zipped, gzipped, rared, tared, 7-zipped, lhzed, … etc) is not allowed. The compressed files will not be evaluated. A submission marked by Moodle as Late is treated as a late submission no matter how many seconds it is late. All files left on Moodle in a state "Draft(not submitted)" 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 in Assignment 3 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. If it is suspected that you have received assistance from another person to complete the tasks, the matter will be investigated as an alleged breach of the UOW College Academic Integrity and Student Conduct Policy, in accordance with the Procedure for Managing Alleged Student Misconduct. As part of this investigation, you may be required to undergo an oral examination to verify your understanding of the assessment content. Configuring the Virtual Server Connect to Moodle and download the Sample database(Sample_database.zip) on Moodle. Extract the files dbcreate.sql, dbdrop.sql, dbload.sql, dbcount.sql, and dbschema.bmp. 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 number of rows in each database table. 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. 1. When connected, select a database csit115 with a command use csit115. 2. To create the relational tables of a sample database, process SQL script dbcreate.sql. 3. To load data into the relational tables created in the previous step process SQL script dbload.sql. 4. To list the names of relational tables created, use a command show tables. 5. To list a structure of a relational table use a command describe . 6. To list the total number of rows in each relational table process a script dbcount.sql. 7. Use a pdf viewer to open a file dbschema.pdf with a conceptual schema of the sample database. No report is expected to be submitted from the implementation of the actions listed above. Tasks Task 1 (3 marks) Your task is to implement and to process SQL script solution1.sql that creates a new database, creates the new user accounts, creates the new roles, grants roles and privileges to the new user accounts, sets resource limits and locks the accounts. Insert into a file solution1.sql implementation of the steps listed below. 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. The steps to be implemented are the following. (1) Create a database with the same name as a prefix of your University email account. For example, if your University email account is
[email protected] then a name of a database should be xyz007. (0.1 mark) (2) Create two new user accounts. The names of user accounts and the 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 to load data into the relational tables later on used in this laboratory class. All relational tables must be located in a database created in step (1). A listing of SQL statements processed by the scripts must NOT be included in a report from processing of a script solution1.sql. It means that before processing of the script you must process notee statement to turn the spooling off and after processing of the scripts you must process a statement tee solution1.rpt to turn the spooling on into a report file. (0.2 mark) (4) Next, create two new roles: driver and admin and grant to a role admin the read privileges on the entire database. The privileges must be granted such that any owner of a role admin cannot grant the same privileges to another role or user. (0.2 mark) (5) Next, grant to a role driver a read privilege on a relational table DRIVER located in the database. A privilege must be granted such that any owner of a role driver can grant the same privilege to another role or user. (0.2 mark) (6) Next, grant to a role driver the read and write privileges on the relational table TRIPLEG located in the database. The privileges must be granted such that any owner of a role driver cannot grant the same privilege to another role or user. (0.2 mark) (7) Next, grant to a role driver a read privilege on the columns FNAME, INITIALS, LNAME in a relational table EMPLOYEE. A privilege must be granted such that any owner of a role driver cannot grant the same privilege to another role or user. (0.3 mark) (8) Next, grant to a role admin an insert privilege on a relational table EMPLOYEE. A privilege must be granted such that any owner of a role admin can propagate the same privileges to another role or user (0.2 mark) (9) Next, grant to a role admin a privilege to create relational tables located in the database. The privileges must be granted such that any owner of a role admin cannot grant the same privileges to another role or user. (0.2 mark) (10) Next, grant to a role admin a privilege to create relational views located in the database. The privileges must be granted such that any owner of a role admin cannot grant the same privileges to another role or user. (0.2 mark) (12) Next, grant to a role admin a read privilege on information about the trips completed in 2018. A hint is on create a relational view and grant a read privilege on the view. A privilege must be granted such that any owner of a role admin cannot grant the same privilege to another role or user. (0.5 mark)