A redaction policy was added to the SAL column of the SCOTT.EMP table:
All users have their default set of system privileges.
For which three situations will data not be redacted?
A.
SYS sessions, regardless of the roles that are set in the session
B.
SYSTEM sessions, regardless of the roles that are set in the session
C.
SCOTT sessions, only if the MGR role is set in the session
D.
SCOTT sessions, only if the MGR role is granted to SCOTT
E.
SCOTT sessions, because he is the owner of the table
F.
SYSTEM session, only if the MGR role is set in the session
Explanation:
* SYS_CONTEXT
This is a twist on the SYS_CONTEXT function as it does not use USERENV. With this usage
SYS_CONTEXT queries the list of the user’s current default roles and returns TRUE if the role is
granted.
Example:
SYS_CONTEXT(‘SYS_SESSION_ROLES’, ‘SUPERVISOR’)
conn scott/tiger@pdborcl
SELECT sys_context(‘SYS_SESSION_ROLES’, ‘RESOURCE’)
FROM dual;
SYS_CONTEXT(‘SYS_SESSION_ROLES’,’SUPERVISOR’)
———————————————FALSE
conn sys@pdborcl as sysdba
GRANT resource TO scott;
conn scott/tiger@pdborcl
SELECT sys_context(‘SYS_SESSION_ROLES’, ‘RESOURCE’)
FROM dual;
SYS_CONTEXT(‘SYS_SESSION_ROLES’,’SUPERVISOR’)
———————————————TRUE
B is correct. F is wrong.
Both users SYS and SYSTEM automatically have the EXEMPT REDACTION POLICY system privilege. (SYSTEM has the EXP_FULL_DATABASE role, which includes the EXEMPT REDACTION POLICY system privilege.) This means that the SYS and SYSTEM users can always bypass any existing Oracle Data Redaction policies, and will always be able to view data from tables (or views) that have Data Redaction policies defined on them.
http://docs.oracle.com/database/121/ASOAG/redaction.htm#ASOAG360
https://docs.oracle.com/database/121/ASOAG/redaction_guidelines.htm#ASOAG360
I agree
https://docs.oracle.com/cd/E11882_01/network.112/e40393/redaction_config.htm#ASOAG722
The EXEMPT REDACTION POLICY system privilege is included in the DBA role, but this privilege must be granted explicitly to users because it is not included in the WITH ADMIN OPTION for DBA role grants. Users who were granted the DBA role are exempt from redaction policies because the DBA role contains the EXP_FULL_DATABASE role, which is granted the EXEMPT REDACTION POLICY system privilege.
So A and B are true
A B D
Have you tested out this question on exam?
5.5.3 Applying the Redaction Policy Based on Database Role
To apply a Data Redaction policy based on database roles, you can use the SYS_SESSION_ROLES namespace in the SYS_CONTEXT function, which contains attributes for each role. The value of the attribute is TRUE if the specified role is enabled for the querying application user; the value is FALSE if the role is not enabled.
For example, suppose you wanted only supervisors to be allowed to see the actual data. Example 5-2 shows how to use the DBMS_REDACT.ADD_POLICY expression parameter to set the policy to show the actual data to any application user who has the supervisor role enabled, but redact the data for all of the other application users.
Example 5-2 Applying a Data Redaction Policy by Database Role
expression => ‘SYS_CONTEXT(”SYS_SESSION_ROLES”,”SUPERVISOR”) = ”FALSE”’
ACF
A,B,C.
A,B : SYS and SYSTEM by default have the EXEMPT REDACTION POLICY system privilege. (SYS and SYSTEM has the DBA role which includes EXP_FULL_DATABASE role, which includes the EXEMPT REDACTION POLICY system privilege.)
SELECT * FROM dba_sys_privs WHERE privilege=’EXEMPT REDACTION POLICY’;
SELECT * FROM dba_role_privs WHERE granted_role IN (‘EXP_FULL_DATABASE’,’DBA’);
C : if the MGR role is set (granted+enabled) in the SCOTT session, then SYS_CONTEXT(‘SYS_SESSION_ROLES,’MGR’)=TRUE, then policy expression accept FALSE and does not redact data this session.