Configuring Deep Data Security with APEX

Oracle Deep Data Security (Deep Sec) is a centralized and declarative data authorization model in the database. Security administrators have full, centralized visibility into who can access rows and columns. APEX applications can be configured to use Deep Sec.

About Deep Data Security

Oracle Deep Data Security (Deep Sec) is a database-enforced data authorization framework. It enables application developers and security architects to define and enforce application-level security requirements directly at the database layer.

Oracle Deep Data Security, integrated with Oracle AI Database, provides a database-native, declarative access control framework. By securing data at its source, it helps you adopt AI while prioritizing data security, privacy, and compliance.

For more information, see Understand Oracle Deep Data Security in Oracle Deep Data Security Guide.

Prerequisites for Using Deep Data Security

Prior to setting up Deep Sec on an APEX application, you must complete the prerequisites by configuring the Identity Provider, configuring the database, and completing the APEX requirements.

Deep Data Security requires Oracle AI Database release 23.26.3 or later.

Identity Provider Requirements

To complete the Identity Provider requirements:

Configure the database application in IAM.

For details, see Register the Database in OCI IAM in Oracle Deep Data Security Guide.
Configure the specific APEX application in IAM.

For details, see Register the Application in OCI IAM in Oracle Deep Data Security Guide.
Configure users and groups in IAM.
1. To configure groups, see Configure Custom Claims for Group Information in OCI IAM in Oracle Deep Data Security Guide.
2. To add users to groups, see Create Users and Assign Groups in OCI IAM in Oracle Deep Data Security Guide.
3. To validate the OCI IAM configuration, see Validate the OCI IAM Configuration in Oracle Deep Data Security Guide.

Deep Sec Database Configuration Requirements

To configure the database:

Configure IAM as the Identity Provider for the database.

For details, see Configure the Database for IAM Integration in Oracle Deep Data Security Guide.
Grant privileges to create an end user context.

For details, see Configure End-User Contexts and Attributes in Oracle Deep Data Security Guide.
Create App Identity, Data Roles, and Data Grants.
1. To create the App Identity, see Configure Application Identities in Oracle Deep Data Security Guide.
2. To configure Data Roles, see Configure Data Roles in Oracle Deep Data Security Guide.
3. To configure Data Grants, see Configure Data Grants in Oracle Deep Data Security Guide.

APEX Requirements

The APEX application must use Social Sign In and OCI IAM or Microsoft Entra as the Identity Provider.

Configuring Deep Data Security in APEX

Configure the database application in IAM:
1. Name: Name for the database application. For example, Deep Sec Database App
2. Application URL: URL to APEX on the current database. For example, <https://example.com:8008/ords>
Configure the APEX database:
1. Set up the database to use Deep Data Security with IAM:
```
ALTER SYSTEM SET IDENTITY_PROVIDER_TYPE = OCI_IAM SCOPE=BOTH;
```
2. Configure IAM details:
```
ALTER SYSTEM SET IDENTITY_PROVIDER_OAUTH_CONFIG = '{
"app_id": "72...................................2b",
"domain_url": "https://idcs-87.........................33.identity.oraclecloud.com:443"
}' SCOPE=BOTH;
```
  Tip: Use the Domain URL from your IAM Domain configuration and the Application ID (not the OAuth Client ID) of the Database application registration. For the domain_url attribute, add the :433 TCP/IP port information. If the port information is missing, Deep Sec will not work.

Grant privileges to create an end user context:

grant create end user security context to apex_public_router;
grant create end user security context to apex_public_user;

Create an application identity where <sample_app> is the name of your app identity:

CREATE APPLICATION IDENTITY <sample_app>
MAPPED TO 'IAM_OAUTH_CLIENT_ID=19................................c4';

Create data roles, and map them to the names of groups created on IAM. For example, if the IAM groups are sales_role and hr_role:

CREATE DATA ROLE sales_role MAPPED TO 'IAM_OAUTH_GROUP=sales_role';

CREATE DATA ROLE hr_role    MAPPED TO 'IAM_OAUTH_GROUP=hr_role';

Create Data Grants to allow table access to specific data roles. For example:

CREATE OR REPLACE DATA GRANT {schema}.{name}
    AS SELECT
    ON {schema}.emp
    WHERE deptno = 20
    TO sales_role;

Create an empty APEX application.

For more information, see Using the Simple Create Application Wizard.
Navigate to Page Designer.
Create a new classic report with the following SQL Query:
```
select ora_end_user_context as ctx from dual
```
Showing the results of ora_end_user_context is an ideal test case because the JSON does not populate until the Deep Data Security End User Context is correctly configured.
Select Shared Components, Security Attributes. The Edit Security Attributes page opens.
Select Configure Deep Data Security to launch the Deep Data Security wizard.
Complete the attributes.
1. OAuth Client ID - The Client ID for the APEX App registered on IAM.
2. OAuth Client Secret - The Client Secret for the APEX App registered on IAM.
3. Identity Provider Type - Select OCI IAM.
4. Identity Domain URL - The Domain URL used to configure the database.
5. Database Resource Scope - The scope from the Database App on IAM.
6. Application Resource Scope - The scope from the APEX App on IAM.
7. End User Token Scopes - Any additional needed scopes for Social Sign In.
8. Request Refresh Tokens on Login - If you enabled Refresh Tokens when registering the app on IAM, check this.
Select Apply Changes.

Deep Data Security is configured. You can now log into the application using one of the usernames you created on IAM.

If you are logging in for the first time, you must provide consent to allow APEX to access the profile and groups scopes.

When the configuration is complete, you can see the JSON content of the End User Security Context. You can now continue building APEX components - each SQL now runs with Deep Data Security enabled, and requires a Data Role in place to access table data.