which three situations will data not be redacted?

A, B – TRUE
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

D – TRUE
SYS_CONTEX test roles MGR and in this case is TRUE then TRUE = FALSE then redaction is off

http://www.morganslibrary.org/reference/sys_context.html

ABD

ABC

Options C and D are bit confusing..

But you should go with ABC as final answer.

ACF

here are my tests :

sqlplus / as sysdba

SQL> create user c##scott identified by oracle;

User created.

SQL> create role c##mgr;

Role created.

SQL> grant c##mgr,connect,resource,unlimited tablespace to c##scott;

Grant succeeded.

SQL> create table c##scott.emp(empname varchar(7),sal number);

Table created.

SQL> insert into c##scott.emp values(‘Peter’,1000);

1 row created.

SQL> insert into c##scott.emp values(‘David’,1500);

1 row created.

SQL> commit;

Commit complete.

SQL> begin
dbms_redact.add_policy(
object_schema => ‘C##SCOTT’,
object_name => ‘EMP’,
policy_name => ‘C##SCOTT_EMP’,
column_name => ‘SAL’,
expression => ‘SYS_CONTEXT(”SYS_SESSION_ROLES”,”C##MGR”) = ”FALSE”’);
end;
/
PL/SQL procedure successfully completed.

SQL> connect / as sysdba
Connected.
SQL> select user,e.* from c##scott.emp e;

USER EMPNAME SAL
—————————— ——- ———-
SYS Peter 1000
SYS David 1500

SQL> set role none;

Role set.

SQL> select user,e.* from c##scott.emp e;

USER EMPNAME SAL
—————————— ——- ———-
SYS Peter 1000
SYS David 1500
— A is correct

SQL> conn c##scott
Connected.
SQL> select user,e.* from emp e;

USER EMPNAME SAL
—————————— ——- ———-
C##SCOTT Peter 1000
C##SCOTT David 1500

SQL> set role none;

Role set.

SQL> select user,e.* from emp e;

USER EMPNAME SAL
—————————— ——- ———-
C##SCOTT Peter 0
C##SCOTT David 0

— C is correct, D is wrong, E is wrong

SQL> connect system
Connected.
SQL> select user,e.* from c##scott.emp e;

USER EMPNAME SAL
—————————— ——- ———-
SYSTEM Peter 1000
SYSTEM David 1500

SQL> set role none;

Role set.

SQL> select user,e.* from c##scott.emp e;

USER EMPNAME SAL
—————————— ——- ———-
SYSTEM Peter 0
SYSTEM David 0

— B is wrong, F is correct

AB is true.

Data is redacted only, if the EXPRESSION ‘SYS_CONTEXT (”SYS_SESSION_ROLES”,”MGR”) = ”FALSE”’ evaluates to true:
If you grant scott the mgr role and you do a
select SYS_CONTEXT (‘SYS_SESSION_ROLES’,’MGR’) from dual;
it returns “TRUE”.
As TRUE is not the wanted output (which is “FALSE”) the whole expression is FALSE. So data is NOT redacted. D is the third right answer.
If the MGR role is not granted to Scott, the SYS_CONTEXT select returns FALSE, which is the required return value and then data is redacted.

By the way – who is able to SET a role that was not granted before? So C is definitely wrong… (ora-01924)

=> ABD

ABD

D because the MGR role is granted to Scott and AB because sys and system have the EXEMPT REDACTION POLICY system privilege

How Oracle Data Redaction Affects the SYS, SYSTEM, and Default Schemas
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.

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”’

look very carefully, there are no tricks here but ‘smart use’ of sys_context
i.e. if MGR role is granted, data is redacted

when MGR is granted,
expression => sys_context(sys_session_roles,MGR) = false
=> true = false
=> invalid
=> policy does not take effect, no data redaction

when MGR is not granted,
expression => sys_context(sys_session_roles,MGR) = false
=> false = false
=> valid
=> policy take effect, data is redacted

ABD

reference https://docs.oracle.com/cloud/latest/db121/ASOAG/redaction_config.htm#ASOAG737

A. SYS sessions, regardless of the roles that are set in the session
=> SYS is always exempted. Even ‘set role none’ does not disable redaction

B. SYSTEM sessions, regardless of the roles that are set in the session
=> If you ‘set role none’ SAL will get redacted!

C. SCOTT sessions, only if the MGR role is set in the session
=> by default all roles are set when connect session. Noneed explicit set role.

D. SCOTT sessions, only if the MGR role is granted to SCOTT
=> true

E. SCOTT sessions, because he is the owner of the table
=> false

F. SYSTEM session, only if the MGR role is set in the session
=> false. By default SYSTEM have DBA role which have EXP_FULL_DATABASE role which is granted EXEMPT REDACTION POLICY system privilege

Another shitty question. My choice ACD