In SQL Developer ONLY Deliverables Include SQL scripts and s

In SQL Developer ONLY!

Deliverables Include SQL scripts and screenshot of the results:

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.

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 the OE.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 time2 hours b. Idle time3 minutes c. CPU time required per call1 second d. 1 block per query

D9. Assign profile OE_PROFILE to user1.

Solution

D1. create role account_managers [IDENTIFIED BY pwd];

create role customer_service   [IDENTIFIED BY pwd];

create role sales_reps [IDENTIFIED BY pwd];

Assigning permissions to the roles created :

GRANT UPDATE OE.CUSTOMERS TO account_managers;

GRANT SELECT,UPDATE ON OE.CUSTOMERS TO customer_service;

GRANT SELECT,INSERT,UPDATE ON OE.CUSTOMERS TO sales_reps;

D2. CREATE USER user1 IDENTIFIED BY pwd;

GRANT account_manger to user1;

D3. After granting role \"account_mangers\" to user1 user we connect to oracle database using sqlplus by

   entering our user name and password .

   Then we get \"sqlplus\" prompt where we try to execute the select, insert and update and delete statements. We will see that only update statement is giving some output but others are not as user1 is not assigned any of the other privileges other than update.

D4. GRANT sales_resp TO user1;

D5. select * from DBA_SYS_PRIVS; // query to get list of all users and privileges having admin option.

   where DBA_SYS_PRIVS table has three columns namely :

   GRANTEE : name of the role or user that was assigned the privilege

  PRIVILEGE is the privilege that is assigned

  ADMIN_OPTION indicates if the granted privilege also includes the ADMIN option

D6. Answer not known

D7. Answer not known

LOGON TIME 2,

IDLE TIME 3,

CPU TIME PER CALL 1,

D9. ALTER USER user1 PROFILE OE_PROFILE;