Validate User Connections and Data Access Control

4.8 Validate User Connections and Data Access Control

In this final section, you'll validate the security configuration. You'll log in as different users (Marvin and Emma), confirm the signed-in identity, and then query the employees table to verify that role-based access is applied as intended.

Note:

If your database server requires egress through a network proxy to validate Microsoft Entra ID tokens, set the HTTP proxy and restart the database listener.
```
export http_proxy=http://<your-proxy-host>:<port>/
export https_proxy=http://<your-proxy-host>:<port>/
lsnrctl stop
lsnrctl start
```
To make the query output easier to read in SQL*Plus, set page and column widths before you run the queries. For example, you can use the following settings:
```
SET LINES 200 PAGES 100
COL employee_id  FOR 9999
COL first_name   FOR a10
COL last_name    FOR a10
COL email        FOR a10
COL manager      FOR a10
COL ssn          FOR a15
COL salary       FOR 99999
COL phone        FOR a10
```

Verify manager access (Marvin).
1. Open a new terminal window.
2. Initiate a connection using the slash / syntax and your TNS alias.
```
sqlplus /@db_entraid
```
3. When your browser opens the Microsoft sign-in page, sign in as Marvin.
4. After the browser displays Authentication Complete, close the window and return to your terminal.
  You're now logged in to the database.
5. Run the following query to confirm the database session is using Marvin's identity.
```
SELECT ORA_END_USER_CONTEXT.username FROM dual;
```
  You see the following output.
```
USERNAME
--------------------------------------------------------------------------------
"marvin@<your-entraID-domain>"
```
6. Check Marvin's access to the employees table.
```
SELECT * FROM hr.employees;
```
  You see Marvin's record and all his direct reports (Emma and Taylor), but the SSN column for others is NULL.
```
EMPLOYEE_ID  FIRST_NAME  LAST_NAME  EMAIL                         MANAGER                        SSN          SALARY  PHONE
-----------  ----------  ---------  ----------------------------  -----------------------------  -----------  ------  --------
200          Marvin      Anderson   marvin@<your-entraID-domain>  victoria@<your-entraID-domain>  457-55-5462  12030   555-0200
400          Emma        Baker      emma@<your-entraID-domain>    marvin@<your-entraID-domain>                 8200   555-0400
500          Taylor      Mills      taylor@<your-entraID-domain>  marvin@<your-entraID-domain>                 9000   555-0500
```
Verify employee access (Emma).
1. Exit the current session and initiate a new connection.
```
exit
sqlplus /@db_entraid
```
2. When your browser opens the Microsoft sign-in page, sign in as Emma.
3. After the browser displays Authentication Complete, close the window and return to your terminal.
  You're now logged in to the database.
4. Run the following query to confirm the database session is using Emma's identity.
```
SELECT ORA_END_USER_CONTEXT.username FROM dual;
```
  You see the following output.
```
USERNAME
--------------------------------------------------------------------------------
"emma@<your-entraID-domain>"
```
5. Check Emma's access to the employees table.
```
SELECT * FROM hr.employees;
```
  You only see Emma's record.
```
EMPLOYEE_ID  FIRST_NAME  LAST_NAME  EMAIL                       MANAGER                       SSN          SALARY  PHONE
-----------  ----------  ---------  --------------------------  ----------------------------  -----------  ------  --------
400          Emma        Baker      emma@<your-entraID-domain>  marvin@<your-entraID-domain>  733-02-9821   8200   555-0400
```