An Oracle relational database is used to record marks for projects undertaken by students on an MSc course.

INSTRUCTIONS TO CANDIDATES

ANSWER ALL QUESTIONS

Description, Guidelines and Marking Scheme

1. Aim of the Coursework Assignment

This coursework contributes 20% to the overall DKM assessment.

The aim of the Coursework Assignment is to help you learn more about and get experience of security in relational databases.

The assignment is further explained in Section 2. Section 3 explains the marking scheme. Section 4 gives submission instructions. Section 5 explains the penalties for late submissions. Section 6 explains how the College deals with plagiarism. Section 7 provides additional information on learning resources on plagiarism and study skills. Section 8 provides guidance on how to give appropriate references your answers.

 

2. Description of the assignment

An Oracle relational database is used to record marks for projects undertaken by students on an MSc course.

A student first submits a project proposal which is marked out of 100 by 2 or more examiners whose marks are combined by taking the average, rounded to the nearest whole mark, to give the mark for the proposal. Similarly, a student subsequently submits a project report which is also marked by 2 or more examiners whose marks are combined by taking the average, rounded to the nearest whole mark, to give the mark for the report. The overall project mark is calculated by combining the proposal mark (20%) and report mark (80%) and rounding to the nearest whole mark.

The database includes tables created as follows:

PROJ_EXAMINERS (EXAMINER)

PROJ_DETAILS (STUDENT, TITLE, SUPERVISOR) PROJ_PROPOSAL_EXAMINER_MARKS (STUDENT, EXAMINER, MARK, YEAR) PROJ_REPORT_EXAMINER_MARKS (STUDENT, EXAMINER, MARK, YEAR)

 

A row is stored in PROJ_EXAMINERS for each staff member who is permitted to examine projects, for example ('ADA').

 

A row is stored in PROJ_DETAILS for each project recording the student, project title and supervisor, for example ('MARY01', 'concurrency visualisation tool', 'ADA').

 

For each project, examiners are appointed consisting of the supervisor and one or more other examiners. Rows in PROJ_PROPOSAL_EXAMINER_MARKS and PROJ_REPORT_EXAMINER_MARKS record the marks awarded by examiners for the proposal and report respectively, together with the year the project is examined. Marks are initially NULL. So, for example, assuming that in 2019 for student MARY01 examiners JAMES and GRACE are appointed together with the supervisor ADA, the following rows would be inserted into both

 PROJ_PROPOSAL_EXAMINER_MARKS and PROJ_REPORT_EXAMINER_MARKS:

('MARY01', 'ADA', NULL, 2019)

('MARY01', 'JAMES', NULL, 2019)

('MARY01', 'GRACE', NULL, 2019)

 

The NULL values are updated with the examiners’ marks once they are known.

 

Values stored for STUDENT and EXAMINER are the Oracle usernames of students and examiners respectively.

 

Tables with example rows may be accessed as:

 

PROJ_EXAMINERS PROJ_DETAILS

PROJ_PROPOSAL_EXAMINER_MARKS PROJ_REPORT_EXAMINER_MARKS

 

Task 1: Security

 

An application developer has written an SQL statement to be used within a host language as described in the handout Host Language Support for SQL. The developer wishes to give students access to rows in PROJ_DETAILS so that they can check that their project details are correctly recorded, but not the project details of other students.

 

The application asks the student to enter their Oracle username which is stored in a variable username. The application then builds a string in a variable querystring for the SQL query to be executed by concatenating:

 

• SELECT TITLE, SUPERVISOR FROM PROJ_DETAILS WHERE STUDENT = '

• The name entered and stored in username

If, for example, the student enters MARY01 as their username, the application builds a querystring

SELECT TITLE, SUPERVISOR FROM PROJ_DETAILS WHERE STUDENT = 'MARY01'

which it then prepares with

 

EXEC SQL PREPARE QUERY_STMT FROM :querystring;

before processing with a cursor in the normal way to enable the application to handle the result rows.

 

A common form of attack against databases is by exploiting SQL injection techniques in applications. See: https://www.owasp.org/index.php/SQL_Injection

 

Explain in your own words (1500-2000 words in total):

 

(a) Explain what an SQL injection attack is and discuss the problems which this can cause.

(12 marks)

 

(b) Explain, giving two examples specific to the schema described, how the application outlined could lead to an SQL injection attack.

(8 marks)

 

(c) The application is clearly vulnerable in any case to a user guessing or knowing another student’s username. If user passwords were also stored in the database, and the user entered a password as well as a username with the querystring built to test for both in the WHERE clause, would SQL injection attacks be prevented? Explain your answer and give an example specific to the schema described. (8 marks)

(d) How can such attacks be guarded against by more careful use of prepared statements when building applications which use SQL from a host language? Illustrate your answer with the changes you would make to the SQL statements of the application outlined above.

(16 marks)

Task 2: Views

 

Views are an additional mechanism which can help support secure access to table data when used with appropriate privileges. The following views are proposed to control the data visible to different Oracle users when logged on to Oracle.

 

Give the SQL statements for the creation of views in Oracle to support these requirements.

 

Note that in Oracle you may reference the function USER in an SQL statement, for example in a SELECT or WHERE clause. It returns the username of the person executing the SQL statement. Also, a function ROUND(n,1) returns n rounded to one decimal place.

 

(a) One view is required with the same columns as PROJ_DETAILS which gives access to all rows to staff permitted to examine projects, but gives students access to only the row for their own project. (16 marks)

 

(b) Two views are required summarising the project proposal marks and project report marks obtained by combining the individual examiners’ marks:

 

PROJ_PROPOSAL_MARKS (YEAR, STUDENT, MARK) PROJ_REPORT_MARKS (YEAR, STUDENT, MARK)

For example, a row in PROJ_PROPOSAL_MARKS (2019, 'NEIL01' 62)and a row in PROJ_REPORT_MARKS(2019, 'NEIL01' 57)record that NEIL01 had his project examined in 2019 with proposal mark 62 and report mark 57.

 

Only projects which have all proposal marks recorded should be included in PROJ_PROPOSAL_MARKS while only projects which have all report marks recorded should be included in PROJ_REPORT_MARKS.

 

Rows in each view should be accessible by staff permitted to examine projects but a student should only have access to rows for their own project. (24 marks)

 

(c) One view is required summarising the overall project marks in each year

PROJ_OVERALL_MARKS (YEAR, STUDENT, PROPOSAL_MARK,

REPORT_MARK, PROJECT_MARK)

which summarises the marks for the proposal, report and overall project mark each year. So, for NEIL01, a row for his project result in 2019 should be recorded:

(2019, 'NEIL01' 62, 57, 58)

Rows in the view should be accessible by staff permitted to examine projects but a student should only have access to the row for their own project. (16 marks)

3. Marking Scheme

Marks will be allocated as shown in the description of the task. The total possible mark is 100.

For Task 1, marks will be awarded for correctness, clarity, originality and depth of understanding demonstrated in the answer.

For Task 2, full marks will be given for fully correct solutions. Otherwise marks will be awarded for partially correct aspects of the solution.

Attachments:

Related Questions

. Introgramming & Unix Fall 2018, CRN 44882, Oakland University Homework Assignment 6 - Using Arrays and Functions in C

DescriptionIn this final assignment, the students will demonstrate their ability to apply two ma

. The standard path finding involves finding the (shortest) path from an origin to a destination, typically on a map. This is an

Path finding involves finding a path from A to B. Typically we want the path to have certain properties,such as being the shortest or to avoid going t

. Develop a program to emulate a purchase transaction at a retail store. This program will have two classes, a LineItem class and a Transaction class. The LineItem class will represent an individual

Develop a program to emulate a purchase transaction at a retail store. Thisprogram will have two classes, a LineItem class and a Transaction class. Th

. SeaPort Project series For this set of projects for the course, we wish to simulate some of the aspects of a number of Sea Ports. Here are the classes and their instance variables we wish to define:

1 Project 1 Introduction - the SeaPort Project series For this set of projects for the course, we wish to simulate some of the aspects of a number of

. Project 2 Introduction - the SeaPort Project series For this set of projects for the course, we wish to simulate some of the aspects of a number of Sea Ports. Here are the classes and their instance variables we wish to define:

1 Project 2 Introduction - the SeaPort Project series For this set of projects for the course, we wish to simulate some of the aspects of a number of