Identify the correct sequence of steps.

B

B – 1, 3, 6, 2, 5, 4

B

B

1. Create a policy to capture the privilege used by a user for privilege analysis.
exec SYS.DBMS_PRIVILEGE_CAPTURE.CREATE_CAPTURE ( name => ‘All_privs’, description=> ‘All privs used’, type => dbms_privilege_capture.g_database);

3. Start analyzing the data captured by the policy.

exec SYS.DBMS_PRIVILEGE_CAPTURE.ENABLE_CAPTURE (name =>’All_privs’);

6. Stop analyzing the data.

exec SYS.DBMS_PRIVILEGE_CAPTURE.DISABLE_CAPTURE (name=> ‘All_privs’);

2. Generate a report with the data captured for a specified privilege capture.

exec SYS.DBMS_PRIVILEGE_CAPTURE.GENERATE_RESULT (name =>’All_privs’)

SQL> SQL> COL capture FORMAT A14
COL username FORMAT A10
COL object_owner FORMAT A12
COL object_name FORMAT A12
COL obj_priv FORMAT A12SQL> SQL> SQL> SQL>
SQL>
SQL> SELECT capture, username, object_owner, object_name,
obj_priv
FROM dba_used_objprivs
WHERE username IN (‘JIM’, ‘TOM’, ‘U1’, ‘U2’)
AND object_name NOT IN
(‘DBMS_APPLICATION_INFO’, ‘PRODUCT_PRIVS’, ‘DUAL’)
ORDER BY username; 2 3 4 5 6 7

CAPTURE USERNAME OBJECT_OWNER OBJECT_NAME OBJ_PRIV
————– ———- ———— ———— ————
All_privs JIM HR EMPLOYEES DELETE
All_privs TOM SH SALES SELECT
All_privs U1 U2 T1 SELECT
ORA$DEPENDENCY U1 SYS DBMS_OUTPUT EXECUTE
All_privs U1 SYS DBMS_OUTPUT EXECUTE
ORA$DEPENDENCY U1 U2 T1 SELECT

6 rows selected.

SQL> SELECT username, sys_priv FROM dba_used_sysprivs
WHERE username IN (‘JIM’, ‘TOM’, ‘U1’, ‘U2’); 2

USERNAME SYS_PRIV
———- —————————————-
TOM CREATE SESSION
U1 CREATE SESSION
U2 UNLIMITED TABLESPACE
U1 CREATE PROCEDURE
JIM CREATE SESSION

SQL> COL path FORMAT A32
SQL> SELECT username, obj_priv, object_name, path
FROM dba_used_objprivs_path
WHERE username IN (‘TOM’,’JIM’, ‘U1’, ‘U2’)
AND object_name NOT IN
(‘DBMS_APPLICATION_INFO’, ‘PRODUCT_PRIVS’, ‘DUAL’)
ORDER BY username, object_name; 2 3 4 5 6

USERNAME OBJ_PRIV OBJECT_NAME PATH
———- ———— ———— ——————————–
JIM DELETE EMPLOYEES GRANT_PATH(‘JIM’, ‘HR_MGR’)
TOM SELECT SALES GRANT_PATH(‘TOM’, ‘SALES_CLERK’)
U1 EXECUTE DBMS_OUTPUT GRANT_PATH(‘PUBLIC’)
U1 EXECUTE DBMS_OUTPUT GRANT_PATH(‘PUBLIC’)
U1 SELECT T1 GRANT_PATH(‘U1’)
U1 SELECT T1 GRANT_PATH(‘U1′)

6 rows selected.

SQL> COL username FORMAT A8
COL sys_priv FORMAT A8
COL obj_priv FORMAT A8SQL> SQL>
SQL> SELECT username, sys_priv, obj_priv, object_name, path
FROM dba_unused_privs
WHERE username=’JIM’; 2 3

USERNAME SYS_PRIV OBJ_PRIV OBJECT_NAME PATH
——– ——– ——– ———— ——————————–
JIM INSERT EMPLOYEES GRANT_PATH(‘JIM’, ‘HR_MGR’)
JIM SELECT EMPLOYEES GRANT_PATH(‘JIM’, ‘HR_MGR’)
JIM UPDATE EMPLOYEES GRANT_PATH(‘JIM’, ‘HR_MGR’)

5. Compare the used and unused privileges’ lists.

SQL> create role HR_MGR_JUNIOR;

Role created.

SQL>
SQL>
SQL> GRANT select, update, delete ON hr.employees TO hr_mgr_junior;

Grant succeeded.

SQL> grant HR_MGR_JUNIOR to JIM;

Grant succeeded.

SQL> revoke HR_MGR from JIM;

Revoke succeeded.

SQL> COL name FORMAT A14
COL type FORMAT A8
COL enabled FORMAT A2
COL roles FORMAT A26
COL context FORMAT A26SQL> SQL> SQL> SQL>
SQL> SELECT name, type, enabled, roles, context
FROM dba_priv_captures; 2

NAME TYPE EN ROLES CONTEXT
————– ——– — ————————– ————————–
ORA$DEPENDENCY DATABASE N
All_privs DATABASE N

SQL> exec SYS.DBMS_PRIVILEGE_CAPTURE.DROP_CAPTURE (name=> ‘All_privs’);

PL/SQL procedure successfully completed.

SQL> SELECT username, sys_priv, obj_priv, object_name, path
FROM dba_unused_privs
WHERE username IN (‘JIM’, ‘TOM’, ‘U1’, ‘U2′,’KATE’) ; 2 3

no rows selected

4. Revoke the unused privileges.

SQL> revoke HR_MGR from JIM;

Revoke succeeded.