Overview: In this lab, you will use your AWS Oracle RDS account to set-up several users given them access to Profile, Tablespace, Permission and Roles settings that you will also create in this lab.
Note: Be sure you have read the materials in week 5 and week 6 prior to completing this lab.
Important: This assignment requires you to use your AWS account.
Recommend getting started early as this will take you longer than you think.
Assignment: Total 100 points
Scenario:
In your newly assigned Oracle DBA position, the security audit team wants you to be involved in a new project to make sure Users are created with Least Privileges, use separate table spaces for users and applications, user passwords expire on first login, quotas should be used for tablespaces as opposed to unlimited tablespaces, temporary tablespaces should be used to help speed performance, no default user passwords exist in the database, Roles are used for privileges as opposed to being assigned directly to the user, and the password complexity should meet or exceed some requirements for DoD STIGs.
In additional the following business guidelines are provided to help in your design:
Number of Database Users:
10 Total
2 with DBA privileges
4 with Privileges to Select, Update, Insert and Delete from the students, employees and finances tables
2 with Privileges to Select from the students, employees and finances tables
2 with Privileges to select, Update, Insert and Delete from the products, sales and payroll application tables.
It is estimated no more 500K tablespace is needed for each user.
Big tablespaces are not required.
Note: You will need to create the tables to test your permissions and roles.
Deliverables:
A word (or PDF) document describing in detail how and why you came to your design decisions based on the requirements from your security audit team. This document should be well-written, using APA style guides and references and include screen captures of you successfully running all of the scripts. The word document should include a test plan and the results of running that test plan to verify all security components are functioning as expected. (Hint: Use data dictionary views, and log in as users to test access and permissions Insert, Update, Select and Delete functionality as required)
A complete SQL script that runs perfectly from start to finish that creates all tablespaces, profiles, roles, users and tables.
Grading Components and Rubric:
Design and Justification of Design (15 points)
Creation and use of Temporary Tablespaces (10 points)
Creation and use of Profiles (10 points)
Creation and use of Roles (10 points)
Creation and use of Users (15 points)
Creation and use of Tables (10 points)
Includes detailed Test plan and results (10 points)
Includes complete SQL script (10 points)
Document is well-organized, well-written and formatted in APA style (10 points)
Did not use AWS account (-100 points)
2
