Validate Data Access Control

3.6 Validate Data Access Control

Log in as each end user and query the hr.employees table to verify that the data grants enforce the expected access control.

Verify manager access (Marvin).

sqlplus '"manderson"'/<password>@//<host>:<port>/<PDB-service-name>

Confirm that the session is using Marvin's identity.
```
SELECT ORA_END_USER_CONTEXT.username FROM dual;
```
You see the following output.
```
USERNAME
----------
"manderson"
```

Query the employees table.

SELECT * FROM hr.employees;

Marvin holds both the employee_role and manager_role data roles. As an employee, he can view his own record (including his SSN). As a manager, he can also view his direct reports (Emma and Taylor), but their SSN values are returned as NULL.

EMPLOYEE_ID  FIRST_NAME  LAST_NAME  EMAIL       MANAGER     SSN          SALARY  PHONE
-----------  ----------  ---------  ----------  ----------  -----------  ------  --------
200          Marvin      Anderson   manderson   vwilliams   457-55-5462  12030   555-0200
400          Emma        Baker      ebaker      manderson                 8200   555-0400
500          Taylor      Mills      tmills      manderson                 9000   555-0500

Verify employee access (Emma).

sqlplus '"ebaker"'/<password>@//<host>:<port>/<PDB-service-name>

Confirm that the session is using Emma's identity.
```
SELECT ORA_END_USER_CONTEXT.username FROM dual;
```
You see the following output.
```
USERNAME
----------
"ebaker"
```

Query the employees table.

SELECT * FROM hr.employees;

Emma holds only the employee_role data role. She can view only her own record.

EMPLOYEE_ID  FIRST_NAME  LAST_NAME  EMAIL   MANAGER    SSN          SALARY  PHONE
-----------  ----------  ---------  ------  ---------  -----------  ------  --------
400          Emma        Baker      ebaker  manderson  733-02-9821   8200   555-0400