You execute the following piece of code with appropriate privileges:
User SCOTT has been granted the CREATE SESSION privilege and the MGR role.
Which two statements are true when a session logged in as SCOTT queries the SAL column in
the view and the table?
A.
Data is redacted for the EMP.SAL column only if the SCOTT session does not have the MGR
role set.
B.
Data is redacted for EMP.SAL column only if the SCOTT session has the MGR role set.
C.
Data is never redacted for the EMP_V.SAL column.
D.
Data is redacted for the EMP_V.SAL column only if the SCOTT session has the MGR role set.
E.
Data is redacted for the EMP_V.SAL column only if the SCOTT session does not have the
MGR role set.
Explanation:
Note:
* DBMS_REDACT.FULL completely redacts the column data.
* DBMS_REDACT.NONE applies no redaction on the column data. Use this function for
development testing purposes. LOB columns are not supported.
* The DBMS_REDACT package provides an interface to Oracle Data Redaction, which enables
you to mask (redact) data that is returned from queries issued by low-privileged users or an
application.
* If you create a view chain (that is, a view based on another view), then the Data Redaction policy
also applies throughout this view chain. The policies remain in effect all of the way up through this
view chain, but if another policy is created for one of these views, then for the columns affected in
the subsequent views, this new policy takes precedence.
A,C.
See case:
[oracle@virt12c ~]$ sqlplus /nolog
SQL*Plus: Release 12.1.0.2.0 Production on Thu Aug 21 18:23:40 2014
Copyright (c) 1982, 2014, Oracle. All rights reserved.
SQL> SET LINESIZE 1000
CONNECT SYS/qwerty123@virt12c:1521/spherepdb1 AS SYSDBA
CREATE USER DOLGUSHINDEV
DEFAULT TABLESPACE USERS
SQL> TEMPORARY TABLESPACE TEMP
PROFILE DEFAULT
IDENTIFIED BY kolobok123
QUOTA UNLIMITED ON USERS
/
CREATE TABLE DOLGUSHINDEV.DEPARTMENT
(DEPARTMENT_ID NUMBER,
DEPARTMENT_NAME VARCHAR2(100),
LOCATION_ID NUMBER)
TABLESPACE USERS
/
INSERT INTO DOLGUSHINDEV.DEPARTMENT(DEPARTMENT_ID,DEPARTMENT_NAME,LOCATION_ID) VALUES (1,’DEP1′,1)
/
INSERT INTO DOLGUSHINDEV.DEPARTMENT(DEPARTMENT_ID,DEPARTMENT_NAME,LOCATION_ID) VALUES (2,’DEP2′,1)
INSERT INTO DOLGUSHINDEV.DEPARTMENT(DEPARTMENT_ID,DEPARTMENT_NAME,LOCATION_ID) VALUES (3,’DEP3′,1)
/
INSERT INTO DOLGUSHINDEV.DEPARTMENT(DEPARTMENT_ID,DEPARTMENT_NAME,LOCATION_ID) VALUES (4,’DEP4′,1)
/
COMMIT
/
CREATE VIEW DOLGUSHINDEV.DEPT_VIEW AS SELECT * FROM DOLGUSHINDEV.DEPARTMENT
/
GRANT CREATE SESSION TO DOLGUSHINDEV
/
CREATE ROLE MGR
/
ALTER USER DOLGUSHINDEV DEFAULT ROLE NONE
/
GRANT MGR TO DOLGUSHINDEV
/
GRANT CREATE SESSION TO DOLGUSHINDEV
/
BEGIN
DBMS_REDACT.ADD_POLICY(
OBJECT_SCHEMA => ‘DOLGUSHINDEV’,
OBJECT_NAME => ‘DEPARTMENT’,
COLUMN_NAME => ‘DEPARTMENT_ID’,
POLICY_NAME => ‘REDACT_DEPARTMENT_ID’,
FUNCTION_TYPE => DBMS_REDACT.FULL,
EXPRESSION => ‘SYS_CONTEXT(”SYS_SESSION_ROLES”,”MGR”) = ”FALSE”’);
END;
/
BEGIN
DBMS_REDACT.ADD_POLICY(
OBJECT_SCHEMA => ‘DOLGUSHINDEV’,
OBJECT_NAME => ‘DEPT_VIEW’,
COLUMN_NAME => ‘DEPARTMENT_ID’,
POLICY_NAME => ‘EXAMPLE_FOR_DEPARTMENT_ID’,
FUNCTION_TYPE => DBMS_REDACT.NONE,
EXPRESSION => ‘SYS_CONTEXT(”SYS_SESSION_ROLES”,”MGR”) = ”FALSE”’);
END;
/
CONNECT DOLGUSHINDEV/kolobok123@virt12c:1521/spherepdb1
SELECT DEPARTMENT_ID,DEPARTMENT_NAME FROM DEPARTMENT
/
SELECT DEPARTMENT_ID,DEPARTMENT_NAME FROM DEPT_VIEW
/
SET ROLE MGR
/
SELECT DEPARTMENT_ID,DEPARTMENT_NAME FROM DEPARTMENT
/
SELECT DEPARTMENT_ID,DEPARTMENT_NAME FROM DEPT_VIEW
/
SET ROLE NONE
/
SELECT DEPARTMENT_ID,DEPARTMENT_NAME FROM DEPARTMENT
/
SELECT DEPARTMENT_ID,DEPARTMENT_NAME FROM DEPT_VIEW
/Connected.
SQL> 2 3 4 5 6 7
User created.
SQL> SQL> 2 3 4 5 6
Table created.
SQL> SQL> 2
1 row created.
SQL> 2 SQL> 2
1 row created.
SQL> 2
1 row created.
SQL> 2
Commit complete.
SQL> SQL> 2
View created.
SQL> SQL> SQL> 2
Grant succeeded.
SQL> 2
Role created.
SQL> 2
User altered.
SQL> 2
Grant succeeded.
SQL> 2
Grant succeeded.
SQL> SQL> 2 3 4 5 6 7 8 9 10
PL/SQL procedure successfully completed.
SQL> SQL> 2 3 4 5 6 7 8 9 10
PL/SQL procedure successfully completed.
SQL> SQL> Connected.
SQL> SQL> 2
DEPARTMENT_ID DEPARTMENT_NAME
————- —————————————————————————————————-
0 DEP1
0 DEP3
0 DEP4
SQL> 2
DEPARTMENT_ID DEPARTMENT_NAME
————- —————————————————————————————————-
1 DEP1
3 DEP3
4 DEP4
SQL> 2
Role set.
SQL> 2
DEPARTMENT_ID DEPARTMENT_NAME
————- —————————————————————————————————-
1 DEP1
3 DEP3
4 DEP4
SQL> 2
DEPARTMENT_ID DEPARTMENT_NAME
————- —————————————————————————————————-
1 DEP1
3 DEP3
4 DEP4
SQL> 2
Role set.
SQL> 2
DEPARTMENT_ID DEPARTMENT_NAME
————- —————————————————————————————————-
0 DEP1
0 DEP3
0 DEP4
SQL> 2
DEPARTMENT_ID DEPARTMENT_NAME
————- —————————————————————————————————-
1 DEP1
3 DEP3
4 DEP4
SQL>
AC
AC
AC
A,C
AC