【云贝教育】Oracle 19c OCP 082题库解析(10)

考试科目：1Z0-082
考试题量：90
通过分数：60%
考试时间：150min
本文为云贝教育郭一军（微信：guoyJoe）原创，请尊重知识产权，转发请注明出处，不接受任何抄袭、演绎和未经注明出处的转载。

10. You want to apply the principle of Least Privilege in all your live databases.
One of your requirements is to revoke unnecessary privileges from all users who have them using  Privilege Analysis.
Which two are types of analyses that can be done using the DBMS PRIVILEGE CAPTURE  package?
A.analysis of privileges that a user has on other schema's objects
B.analysis of privileges that a user has on their own schema objects
C.analysis of privileges granted indirectly to a role that are then used by a user who has been  granted that role
D.analysis of privileges granted directly to a role that are then used by a user who has been  granted that role
E.analysis of all privileges used by the sys user

参考答案：AC

解析：
DBMS PRIVILEGE CAPTURE不能分析SYS，也不能分析自己schema的，BDE排除,所以选AC，具体测试详情见下面的脚本
权限分析脚本

role :teacher
owner:teacher1
table:teacher_name

role :student
owner:student1
table:student_name

--1.创建用户
create user student1
identified by "apps"
default tablespace apps_data_tablespace
temporary tablespace apps_tmp_tablespace;
create user teacher1
identified by "apps"
default tablespace apps_data_tablespace
temporary tablespace apps_tmp_tablespace;
--2.授权


--3.创建表
create table  student1.student_table_test(id number);
create table  teacher1.teacher_table_test(id number);
--4.创建角色并授权
create role student_role;
grant create session to student_role;
--create session 等价于role connect
create role teacher_role;
grant student_role to teacher_role;
grant student_role to student1;
grant teacher_role to teacher1;

--5分析关系
teacher访问student表对应AC
teacher访问teacher表对应BD

--6创建分析策略

BEGIN
DBMS_PRIVILEGE_CAPTURE.CREATE_CAPTURE(
name         => 'analysis_role_teacher',
description  => '分析teacher角色',
type         => DBMS_PRIVILEGE_CAPTURE.G_ROLE,
roles        => role_name_list('TEACHER_ROLE')
);
END;
/
--7.开始权限分析
BEGIN
dbms_privilege_capture.enable_capture(NAME =>'analysis_role_teacher');
END;
/
--8关闭权限分析
BEGIN
dbms_privilege_capture.DISABLE_CAPTURE(NAME =>'analysis_role_teacher');
END;
/
--9分析填充视图
BEGIN
dbms_privilege_capture.GENERATE_RESULT(NAME =>'analysis_role_teacher');
END;
/
select * from dba_Role_privs where granted_role='TEACHER_ROLE';
select * from DBA_TAB_PRIVS WHERE GRANTEE ='TEACHER_ROLE';
select * from dba_sys_provs where  GRANTEE ='TEACHER_ROLE';
select user_name from DBA_UNUSED_SYSPRIVS ;
select * from DBA_USED_SYSPRIVS ;
--10删除策略函数
BEGIN
dbms_privilege_capture.DROP_CAPTURE (NAME =>'analysis_role_teacher');
END;
/