Assignment Question Lab 3 Creating Profiles, Users and Roles

Overview: 

In this lab, you will demonstrate you are comfortable creating users, profiles and roles.

You must connect to the provided AWS Oracle RDS environment and complete these task to earn credit for this lab.

Lab Requirements

1. Create a unique Profile based on the following requirements:
   1. Password complexity should meet requirements for Ora12 Verify function.
   2. User may have up to 3 concurrent sessions.
   3. User may have up to 4 consecutive failed attempts to log in before the account is locked.
   4. User may wait up till 120 days before their password must be changed.
   5. User account will be locked for 1 hours after the specified number of consecutive failed login attempts.
   6. Default values for other Profile parameters is acceptable
   7. You should name the Profile PFirstnameLastname where Lastname and Firstname are your First and Lastname
2. Verify your Profile was successfully created by Creating and executing a SQL statement querying the appropriate Data Dictionary objects.
3. Create 2 users assign them to the Permanent Tablespace of Users with a Quota of 30M. Assign the new users the Profile you established in Step 1 of this lab. Be sure to expire their passwords upon creation. Name the users as follows:
   1. U1FirstnameLastname
   2. U2FirstnameLastname

Where Firstname and Lastname are your first and lastname.

4. Create a role allowing users assigned to be able to connect to the database and create tables. Name this R1FirstnameLastname where Firstname and Lastname are your first and lastname.
5. Create two tables in your (root/admin) schema. Name them User1Data and User2Data. The tables should contain a primary key and 3 additional columns of your choice. Insert 3 records into each table.
6. Provide privileges for U1FirstnameLastname and U2FirstnameLastname to be able to connect to the database and create tables. Be sure to use security best practices when assigning these privileges. In addition, provide one user the privileges to select from User1Data and Insert into User1Data. Provide the other user the privileges to select from User1Data and User2Data.
7. Prepare and execute a detailed test plan to verify the two users have all the privileges they need but no additional privileges. Be sure to test by logging in as those users, changing their passwords, connecting, creating table and then using the assigned privileges in the User1Data and User2Data tables and performing and documenting other tests as required.

Deliverables (100 points total)

Two separate files should be provided with your name, date, course information (e.g. SDEV 350 6380) and professor name 

o One SQL file that contains all SQL statements used. Comments must be included in the

SQL file briefly describing groups of SQL statements. (50 points) o Well-written, detailed test plan and execution results showing the privileges were appropriate and within specification for each user. {Hint: Be sure to include and show denied actions in your test plan as well.) Screen captures should be provided with figure numbers and labels verifying the execution within the AWS Oracle account. The descriptions should be written using full paragraphs with detailed explanations of each of the test performed along with the results. (50 points)