Configure Data Access Control

4.7 Configure Data Access Control

In this section, you'll 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.
Create data roles, which map to Entra ID roles.
Create sample HR application data.
Define data grants to restrict access to Marvin and Emma based on their roles.

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 direct logon for Microsoft Entra ID users. Use the values from Register the Database Resource 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;

Configure data roles and permissions.
Instead of creating a database user for each employee (Marvin or Emma), define data roles that map to the application roles defined in Microsoft Entra ID. You can then assign access privileges to these data roles as required.

Additionally, create a standard database role with privileges to establish a database session and grant this role to each data role.
```
-- Create data roles mapped to Microsoft Entra ID roles
CREATE DATA ROLE employee_role MAPPED TO 'AZURE_ROLE=EMPLOYEE';
CREATE DATA ROLE manager_role MAPPED TO 'AZURE_ROLE=MANAGER';

-- Create a standard database role for connection privileges
CREATE ROLE db_role;
GRANT CREATE SESSION TO db_role;

-- Grant the connection privileges to the data roles
GRANT db_role TO employee_role;
GRANT db_role TO manager_role;
```

Create sample data.

To test the data access control capabilities of Deep Sec, create a sample HR application schema and populate it with an employees table.

To create a sample HR schema, begin by creating the hr user. Next, create the employees table. If you already have an hr.employees table, drop it before proceeding.
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 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.

Replace <your-entraID-domain> in the SQL statements with your actual Microsoft Entra ID domain (for example, supremo.onmicrosoft.com).

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;

After you load the sample data, the hr.employees table contains the following records:

EMPLOYEE_ID  FIRST_NAME  LAST_NAME  EMAIL                            MANAGER                          SSN          SALARY  PHONE
-----------  ----------  ---------  ------------------------------   ------------------------------   -----------  ------  --------
100          Victoria    Williams   victoria@<your-entraID-domain>                                    219-09-9999  13000   555-0100
200          Marvin      Anderson   marvin@<your-entraID-domain>     victoria@<your-entraID-domain>   457-55-5462  12030   555-0200
300          Chris       Evans      chris@<your-entraID-domain>      victoria@<your-entraID-domain>   321-12-4567  6900    555-0300
400          Emma        Baker      emma@<your-entraID-domain>       marvin@<your-entraID-domain>     733-02-9821  8200    555-0400
500          Taylor      Mills      taylor@<your-entraID-domain>     marvin@<your-entraID-domain>     558-76-1243  9000    555-0500

Implement data access control.
You'll use data grants to define table-level access rules and column masks. In data grant statements, you'll use a SQL function, ORA_END_USER_CONTEXT, to retrieve session-context parameters for the signed-in user as a JSON data type. For example, ORA_END_USER_CONTEXT.username returns the user’s name (UPN) from the current session. Create the following two data grants in the hr schema.
1. Create the employees_own_record data grant for the employee data role (employee_role).
  This data grant ensures that users assigned the employee_role can access only those rows where the value in the email column matches their login user name.
```
-- Grant 1: Allow employees to see only their own record
CREATE DATA GRANT hr.employees_own_record
  AS SELECT
  ON hr.employees
  WHERE email = ORA_END_USER_CONTEXT.username
  TO employee_role;
```
2. Next, create the manager_direct_reports data grant for the manager data role (manager_role).
  
  This data grant ensures that users assigned the manager_role (for example, Marvin) can access all those rows where the value in the manager column matches their login user name.
  
  Additionally, in the data grant, you'll use the clause ALL COLUMNS EXCEPT ssn. This ensures that, although Marvin can view the rows for his direct reports, the social security number (SSN) column returns NULL for those rows, preventing access to this sensitive information.
```
-- Grant 2: Allow managers to see their direct reports (with SSN excluded)
CREATE DATA GRANT hr.manager_direct_reports
  AS SELECT (ALL COLUMNS EXCEPT ssn)
  ON hr.employees
  WHERE manager = ORA_END_USER_CONTEXT.username
  TO manager_role;
```