Security and Data Manipulation Triggers - To-Rent Estate Agent

Creating a Trigger to Record Auditing Data

Security and Data Manipulation Triggers

Following the database for the To-Rent estate agent created in previous term, their renting service has grown rapidly and there is a high demand for their services. So, they are even planning to expand their business to different geographical regions in the UK. However, the management of the To-Rent suspects that there are fraudulent activities taking place within the renting services, with fake visits being recorded and fake rented property assigned to them. They suspect the following activities are taking place:

· Staffs are creating tables to hide property and customer details through some activities made;

· Some rented property records are being deleted from the property table;

These activities need to be identified and stopped, and the person(s) responsible are needed to be identified. In your role as the Database Administrator you have been given the task of auditing properties and recording the activities for every Create, Insert and Delete operation carried out. You are to create a Trigger to automatically record the auditing data.

(a) Create a Trigger that responds to CREATE_Table, ALTER_Table and DROP_Table commands on ToRent estate agent database. The Trigger should store the following data:

· Database Name

· Table Name

· Event Type

· Login Name

· SQL Command

· Audit Date & Time

(b) Create a table named PRGDatabaseAudit to store the above details in question (a)

(c) Create a table named PRGPropertyAudit to store the following data items:

· AuditID;

· PropertyInformation (it should contain ID of Customer, visit detail and rented property inserted, deleted or updated, user responsible for the transaction and the date of transaction).

(d) Create a Trigger that responds to Insert commands on visits. The Trigger should store the following data:

· ID of Property inserted;

· Date of insert;

· User responsible for the insert;

(e) Create a Trigger that responds to Delete commands on Sales. The Trigger should store the following data:

· ID of Property deleted;

· Date of Delete;

· User responsible for the delete;

(f) Create a Trigger that responds to Update commands on Sales. The Trigger should store the following data:

· Details of @Old and @New data;

· Date of update;

· User responsible for the update;

The management of the To-Rent estate agent require a report showing mitigating approaches that you have put in place and how this functionalities work. You must present your security approach and outline why you have chosen them.

In the brief, you are required to submit a report detailing the problem and solutions provided. The report should be approximately 2000 words (excluding title page,table of contents, references and appendices). In the report, document the strategies that can be employed by To-Rent estate agent to prevent further fraudulent activities.

a) Include in the report a screenshot of sales and audit data table and how they can be used to detect any DDL and DML transaction occurring

b) Identify the advantages to be gained from your proposed solution and use appropriate examples from your solution (in Task 1) to support your discussion. Show queries that can be ran using the database.

a. Discuss the term Transaction and Concurrency as used in the DBMS.

b. Explain the ACID properties of Transaction and why they are necessary? Which of the properties are responsible for user?

c. Discuss the three anomalies such as Dirty Read, Unrepeatable Read and Lost Update caused by interleaved schedule with suitable example for each anomaly.

d. Explain the two-phase locking and demonstrate how two-phase locking would help to avoid problem caused by Dirty Read using the Dirty Read schedule example mentioned in the above question (c).

Submission Details

You are required to complete the tasks outlined in the 'Task Description' section and to submit:

1. An electronic version of your Assignment Project via Turnitin.

2. Upload the zipped file for Task 1 containing your .sql files work to Moodle.