IN SQL DEVELOPER
D1. Create the following three user-defined roles that are shown in the table below and assign them the specified permissions for the OE.CUSTOMERS table.
|
Role
|
Select
|
Insert
|
Update
|
Delete
|
|
account_managers
|
|
|
X
|
|
|
customer_service
|
X
|
|
X
|
|
|
sales_reps
|
X
|
X
|
X
|
|
D2. Create user user1. Assign role account_manger to user1
D3. Use Oracle new connection to login as user1 and test permissions.
List all steps you performed to test permissions add screenshot with results
D4. Assign role sales_reps to user1
D5. As a part of an auditing process your company is conducting, you are required to report all users who have been granted system privileges with ADMIN option.
Write a query that will display users and their privileges. (Hint: use data dictionary SYS_PRIVS)
Include SQL scripts, number of rows and screenshot of the results
D6. Write SQL statement that lists all roles AND users (without repetition) who have access to theOE.CUSTOMERS table.
D7. How can you check that whether ACCOUNT_MANAGERS have permission to update only one column in CUSTOMERS table?
D8. Create an Oracle profile OE_PROFILE to limit database resources using the following criteria
a. Logon time-2 hours
b. Idle time-3 minutes
c. CPU time required per call-1 second
d. 1 block per query
D9. Assign profile OE_PROFILE to user1.