9.2 Configure the Database for Local End-User Authentication

Complete the required configuration in your database to implement Oracle Deep Data Security (Deep Sec) when your end users are managed locally rather than through an IAM system. Your specific configuration steps vary depending on whether your local end users connect through an application or establish direct database sessions with password authentication.

• Set Up Local Authentication for Application-Mediated Connections
• Set Up Local Authentication for Direct Logon

9.2.1 Set Up Local Authentication for Application-Mediated Connections

If your application maintains its own user store (for example, a user registry in a separate database, LDAP directory, or an application-managed identity system) and those users connect to the database through the application, use the database configuration detailed here to implement Deep Sec, without requiring the users to have IAM accounts.

In this scenario, the local end user is identified by a user name and a security context lookup key supplied by the application.

Even though the end users are managed locally, the application must still be registered in your IAM system and must obtain a database-access token to authorize its own connection to the database. A TLS-secured connection, a connection pool user account, and the identity provider configuration in the database are therefore required. For application registration in IAM, see Register the Application in Microsoft Entra ID and Register the Application in OCI IAM.

1. Configure the database for application sessions.
   Complete the TLS, connection pool user account, and identity provider setup described in Set Up IAM Integration for Application-Mediated Connections. The configuration for this scenario is identical.
2. Create end users in the database.
   Provision end users with user names that match the users in the application’s user store. Because these users authenticate to the database through the application’s trust rather than directly, create the end-user accounts without a password.

   CREATE END USER emma;

   For the complete syntax and options, see Configure Local End Users.
3. Create data roles that are managed locally in the database.
   Create the required data roles to enable within your end-user security contexts. See Configure Data Roles.

   CREATE DATA ROLE employee_role;

4. Grant the data roles to end users.
   Explicitly grant appropriate data roles to your end-user accounts. See Grant and Revoke Data Roles.

   GRANT DATA ROLE employee_role TO emma;

5. Perform optional additional configuration.
   The following tasks are not mandatory to establish a basic end-user security context, but may be required depending on your application’s authorization model.
   1. Create end-user context definitions.
      If your application logic or data grants rely on custom end-user context attributes, create the corresponding END USER CONTEXT schema objects before deployment. See Configure End-User Contexts and Attributes.
   2. Create the application identity.
      Set up a database identity for your application that matches its IAM client ID. See Configure Application Identities.

9.2.2 Set Up Local Authentication for Direct Logon

For environments where local end users establish direct database sessions through a SQL client using credential-based authentication, perform the following configuration tasks to implement Deep Sec.

1. Create end users in the database.
   For this scenario, create end-user accounts with password authentication enabled.

   CREATE END USER emma IDENTIFIED BY <password>;

   For the complete syntax and options, see Configure Local End Users.
2. Create data roles that are managed locally in the database.
   Create the required data roles to enable within your end-user security contexts. See Configure Data Roles.

   CREATE DATA ROLE employee_role;

3. Create and grant a generic database role.
   Create a generic database role and grant it the CREATE SESSION privilege. Then grant this generic role to your data role used for direct logon. See Grant Database Role to Data Role.

   CREATE ROLE db_role;
   GRANT CREATE SESSION TO db_role;
   
   GRANT db_role TO employee_role;
   

4. Grant the data roles to end users.
   Explicitly grant appropriate data roles to your end-user accounts. See Grant and Revoke Data Roles.

   GRANT DATA ROLE employee_role TO emma;