Learning Outcomes
1. Manage user access and user profiles within a complex DBMS
2. Conduct database audits
3. Develop backup and recovery procedures
Read the given scenario and perform all tasks. You have to answer all questions with respect to Database Security concepts.
HomeForOrphans is a charity organization that organizes different events for the benefit of homeless children. An event will always have a sponsor and will be linked by different participants from different company team. An event will be hosted by one of the coordinators of HomeForOrphans. Currently, the HomeForOrphans records in all the events, sponsors, participants, coordinators, and company team manually, where all the staffs have access (adding new record, crossing-out incorrect details, updating incomplete details, etc). HomeForOrphans encountered some problems with the current manual system like coordinators were assigned to two different events at the same time; some participants details were missing or some participants were listed twice for some event that affects the availability of participant slot (the organization followed that the maximum number of participants in any event should be at most 100); some events were duplicated; unable to give a report on time; and in some occasion, the cost of the event is more than the allocated budget by the sponsor but as per the operation requirement of the organization, the cost of an event should not be more than 60% of the given budget. Due to these problems, the management of HomeForOrphans decided to change the current system to a database application. You have been hired as the DBA to analyze the data requirements, security and define back-up method to use. During data gathering, you have identified that HomeForOrphans has one CEO, one manager, six coordinators who are also responsible for creating different events and looking for sponsors. After several discussions with the management and staff of HomeForOrphans, you have designed the below given ER model.
TASK 1:
Submit on Moodle a Work Proposal for this assignment by the end of 9th Week (10th Dec 2016, before 11:55 PM) which must include:
- Understanding on deliverables - a brief description of deliverables/output for task 2 to task 4.
- General overview of proposed plan - initial understanding of solution to task 2 to task 4.
- Time line for completion of each task.
Note: The Work Proposal should be typed in MS Word format only.
As a DBA, you have to perform the following tasks:
Task 2: Manage the user access on the different objects by giving proper privileges to the users. Give justifications on why you have granted the privileges to that user/s. (30 marks) Note: You have to perform this task in the lab and provide the screenshots.
Task 3: Write audit commands and at least one audit trigger to monitor and record the different operations done by the users created in Task 2.
Note: You have to perform this task in the lab and provide the screenshots.
Task 4: Compare and contrast at least two types of database backup. Then suggest and justify which type of database backup would be more suitable to the organization.
TASK #5:
Presentation and Viva voce is a must. You are required to present and upload the presentation in Moodle. The presentation should contain 10 to 12 slides regarding the overall work done towards to this assignment and should get completed within 10 to 12 minutes; and 3 minutes should be given for clarifications. No marks will be awarded for Task 1, 2, 3 and 4 if the student does not attend viva.