Configure Data Access Control

5.7 Configure Data Access Control

In this section, you configure data access control using Oracle Deep Data Security (Deep Sec) capabilities.

You'll perform the following tasks:

Configure the database to accept external identities from Microsoft Entra ID.
Create sample employee records data.
Set up the connection pool user account and application identity for the Spring Boot application.
Create data roles, which map to Entra ID application roles.
Define data grants that limit Emma to her own employee record, and enable privilege elevation for the salary-summary endpoint.

Connect to the database as a named user with the DBA role.
Switch to the target pluggable database (for example, ORCLPDB). Run the following command.
```
ALTER SESSION SET CONTAINER = <your-target-PDB>;
```
Enable Microsoft Entra ID as the identity provider.
Configure the database to validate tokens from your Entra ID tenant. Use the values from Create Application Registrations in Microsoft Entra ID to replace the placeholders.
```
ALTER SYSTEM SET IDENTITY_PROVIDER_TYPE = AZURE_AD SCOPE = BOTH;

ALTER SYSTEM SET IDENTITY_PROVIDER_CONFIG = '{
  "application_id_uri": "[DB_APP_ID_URI]",
  "tenant_id": "[TENANT_ID]",
  "app_id": "[DB_APP_ID]"
}' SCOPE = BOTH;
```

Create sample data.

Create a sample HR schema with an employees table. Replace <your-entraID-domain> in the SQL statements with your actual Microsoft Entra ID domain (for example, supremo.onmicrosoft.com).

Note:

If you completed the previous quick start (Configure Oracle Deep Data Security for Direct Logon with End Users in IAM) and the hr.employees table already exists, you can reuse it.

Create the hr user and the employees table.

Note:

The CREATE USER statement below uses the USERS tablespace. If this tablespace does not exist in your database, create it.

CREATE TABLESPACE users
  DATAFILE '<data_file_path>' SIZE 100M
  AUTOEXTEND ON;

Replace <data_file_path> with the full path for the data file.

-- Create the hr user
CREATE USER hr NO AUTHENTICATION
  DEFAULT TABLESPACE users
  QUOTA UNLIMITED ON users;

-- Create the employees table
CREATE TABLE hr.employees (
    employee_id   NUMBER PRIMARY KEY,
    first_name    VARCHAR2(50),
    last_name     VARCHAR2(50),
    email         VARCHAR2(128),
    manager       VARCHAR2(128),
    ssn           VARCHAR2(20),
    salary        NUMBER(10,2),
    phone         VARCHAR2(20)
);

Populate the employees table with the following sample data.

INSERT INTO hr.employees VALUES (100, 'Victoria', 'Williams',
  'victoria@<your-entraID-domain>', NULL,
  '219-09-9999', 13000, '555-0100');
 
INSERT INTO hr.employees VALUES (200, 'Marvin', 'Anderson',
  'marvin@<your-entraID-domain>', 'victoria@<your-entraID-domain>',
  '457-55-5462', 12030, '555-0200');
 
INSERT INTO hr.employees VALUES (300, 'Chris', 'Evans',
  'chris@<your-entraID-domain>', 'victoria@<your-entraID-domain>',
  '321-12-4567', 6900, '555-0300');
 
INSERT INTO hr.employees VALUES (400, 'Emma', 'Baker',
  'emma@<your-entraID-domain>', 'marvin@<your-entraID-domain>',
  '733-02-9821', 8200, '555-0400');
 
INSERT INTO hr.employees VALUES (500, 'Taylor', 'Mills',
  'taylor@<your-entraID-domain>', 'marvin@<your-entraID-domain>',
  '558-76-1243', 9000, '555-0500');
 
COMMIT;

Create the connection pool user account and assign privileges.
Create a database user account for the Spring Boot application’s connection pool. The application uses this account to maintain its connection pool. You must grant this user account the privileges to establish a database session and transmit the end-user security context payload. This user authenticates to the database using the database-access token.
```
CREATE USER hr_app_user IDENTIFIED GLOBALLY AS
    'AZURE_CLIENT_ID=[EMP_RECORDS_APP_ID]';

GRANT CREATE SESSION TO hr_app_user;
GRANT CREATE END USER SECURITY CONTEXT TO hr_app_user;
```
Create an application identity.
Create an identity for the Spring Boot application in the database that maps to its client ID in Entra ID. This identity is used to authorize the application for privilege elevation.
```
CREATE OR REPLACE APPLICATION IDENTITY hr_app
  MAPPED TO 'AZURE_CLIENT_ID=[EMP_RECORDS_APP_ID]';
```
Configure data roles.
1. Define a data role that maps to Emma's application role defined in Microsoft Entra ID.
```
CREATE OR REPLACE DATA ROLE employee_role MAPPED TO 'AZURE_ROLE=EMPLOYEE';
```
2. Next, create a data role as disabled for privilege elevation. This data role is never active by default; it is only enabled temporarily by the application at runtime. Grant it to the application identity so that only the Spring Boot application can activate it.
```
CREATE OR REPLACE DATA ROLE compensation_analyst DISABLED;
GRANT DATA ROLE compensation_analyst TO hr_app;
```
Create data grants.
Define two data grants: one that limits employees to their own record, and one that enables the salary-summary endpoint through privilege elevation.
1. Create the EMPLOYEES_OWN_RECORD data grant.
  This data grant ensures that users assigned the EMPLOYEE_ROLE data role can access only those rows where the value in the email column matches their login user name (UPN).
```
CREATE OR REPLACE DATA GRANT hr.employees_own_record AS
  SELECT
  ON hr.employees
  WHERE email = ORA_END_USER_CONTEXT.username
  TO employee_role;
```
2. Create the EMPLOYEES_SALARY_SUMMARY data grant.
  This data grant gives the COMPENSATION_ANALYST data role read access to the salary column across all employees. Because the data role is disabled by default and granted exclusively to the application identity, it can only be activated when the application explicitly elevates privileges at runtime.
```
CREATE OR REPLACE DATA GRANT hr.employees_salary_summary AS
  SELECT (salary)
  ON hr.employees
  WHERE 1 = 1
  TO compensation_analyst;
```