IN SQL DEVELOPER D1 Create the following three userdefined r

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 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 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.

Solution

D1> CREATE ROLE account_managers ;
GRANT SELECT, INSERT, DELETE ON OE.CUSTOMERS TO account_managers;
CREATE ROLE customer_service ;
GRANT INSERT, DELETE ON OE.CUSTOMERS TO customer_service;
CREATE ROLE sales_reps;
GRANT DELETE ON OE.CUSTOMERS TO sales_reps;

D2> CREATE USER user1 IDENTIFIED BY MyPassword;
GRANT account_managers TO user1

D3> Login in user1 using password MyPassword , or whatever you kept as password.
Then run the following commands :
SELECT * FROM SESSION_PRIVS;
It will show all the the priveledges granted to this user.

D4> GRANT sales_reps TO user1

D5>
select grantee, privilege, admin_option from sys.dba_sys_privs where admin_option = \'YES\' and grantee not in (‘SYS\',\'SYSTEM\');

D6>
select Grantee,\'Granted Through Role\' as Grant_Type, role, table_name
from role_tab_privs rtp, dba_role_privs drp
where rtp.role = drp.granted_role
and table_name = \'OE.CUSTOMERS\'
union
select Grantee,\'Direct Grant\' as Grant_type, null as role, table_name
from dba_tab_privs
where table_name = \'OE.CUSTOMERS\' ;

D7>
SELECT GRANTEE, TABLE_NAME, COLUMN_NAME, PRIVILEGE
FROM DBA_COL_PRIVS where \'OE.CUSTOMERS\' ;

D8>
CREATE PROFILE OE_PROFILE LIMIT
CONNECT_TIME 120
IDLE_TIME 3
CPU_PER_CALL 1
LOGICAL_READS_PER_CALL 1

;

D9> alter user user1 profile OE_PROFILE ;