A.2 Scripts for Direct Logon with End Users in IAM
Run these scripts to automate the configuration described in Configure Oracle Deep Data Security for Direct Logon with End Users in IAM.
These scripts perform database configuration only. End-user validation occurs interactively when a user signs in to Microsoft Entra ID from a SQL*Plus client, as described in the chapter.
Topics:
A.2.1 How to Run the Scripts
Run these scripts as SYS from a shell on the database
host, in the following order.
02_entra_demo.sql: Sets up the demo environment. The script reads these parameters in order from either command-line arguments or interactive prompts:syspasswd:SYSpassword for the target PDB.dbapasswd: Password for theDEEPSEC_DBAadministrative user that the script creates.target_pdb: Service name of the target PDB.TENANT_ID: Directory (tenant) ID of your Microsoft Entra ID tenant.DB_APP_ID: Application (client) ID of theOracleDB_Resourceapplication registration in Entra ID. See Register the Database Resource.DB_APP_ID_URI: Application ID URI of theOracleDB_Resourceapplication registration in Entra ID.ENTRA_DOMAIN: Your Microsoft Entra ID domain (for example,supremo.onmicrosoft.com). Used to construct sample employee email addresses.
02_entra_cleanup.sql: Removes all objects the demo script created. Run this to reset the environment. The script reads these parameters in order from either command-line arguments or interactive prompts:syspasswd:SYSpassword for the target PDB.target_pdb: Service name of the target PDB.
A.2.2 Scripts
Review the source for both scripts used in the scenario.
| File | Purpose |
|---|---|
02_entra_demo.sql |
Sets up the demo environment. |
02_entra_cleanup.sql |
Removes all objects created by the demo script. |
A.2.2.1 02_entra_demo.sql
The source for the 02_entra_demo.sql
script.
SET ECHO ON
SET FEEDBACK 1
SET NUMWIDTH 10
SET LINESIZE 200
SET TRIMSPOOL ON
SET TAB OFF
SET PAGESIZE 100
define syspasswd=&1
define dbapasswd=&2
define target_pdb=&3
define TENANT_ID=&4
define DB_APP_ID=&5
define DB_APP_ID_URI=&6
define ENTRA_DOMAIN=&7
spool 02_entra_demo.log
----------------------------------------------------------------------
-- HR Security Demo - Direct Logon with IAM Users
----------------------------------------------------------------------
-- This script creates the demo environment:
-- * 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
----------------------------------------------------------------------
-- 1. Connect as SYS to the target PDB
----------------------------------------------------------------------
connect sys/&syspasswd@&target_pdb as sysdba
----------------------------------------------------------------------
-- 2. Create the administration user
----------------------------------------------------------------------
-- This local user is used to manage the demo environment.
create user DEEPSEC_DBA identified by &dbapasswd;
grant DBA to DEEPSEC_DBA;
----------------------------------------------------------------------
-- 3. Connect as the administration user
----------------------------------------------------------------------
connect DEEPSEC_DBA/&dbapasswd@&target_pdb
----------------------------------------------------------------------
-- 4. Configure the identity provider
----------------------------------------------------------------------
-- Enable Microsoft Entra ID as the identity provider for this PDB.
-- SCOPE = BOTH applies the setting to both the CDB and the PDB.
-- The database will use these values to validate incoming Entra tokens.
ALTER SYSTEM SET IDENTITY_PROVIDER_TYPE = AZURE_AD SCOPE = BOTH;
-- Supply the Entra tenant details so the database can validate tokens.
-- application_id_uri: the Application ID URI of the EmployeeRecordsDB
-- Entra app registration [DB_APP_ID_URI]
-- tenant_id: the Directory (tenant) ID of your Entra tenant [TENANT_ID]
-- app_id: the Application (client) ID of EmployeeRecordsDB [DB_APP_ID]
ALTER SYSTEM SET IDENTITY_PROVIDER_CONFIG = '{
"application_id_uri": "&DB_APP_ID_URI",
"tenant_id": "&TENANT_ID",
"app_id": "&DB_APP_ID"
}' SCOPE = BOTH;
----------------------------------------------------------------------
-- 5. Create data roles
----------------------------------------------------------------------
-- Employees can see only their own row.
CREATE DATA ROLE employee_role MAPPED TO 'AZURE_ROLE=EMPLOYEE';
-- Managers can see their direct reports (with SSN masked).
CREATE DATA ROLE manager_role MAPPED TO 'AZURE_ROLE=MANAGER';
----------------------------------------------------------------------
-- 6. Create the session role
----------------------------------------------------------------------
-- A standard database role that carries the CREATE SESSION privilege.
-- Granting this role to a data role allows end users who hold that
-- data role to establish a database session.
CREATE ROLE db_role;
GRANT CREATE SESSION TO db_role;
----------------------------------------------------------------------
-- 7. Grant create session privilege to data roles
----------------------------------------------------------------------
GRANT db_role TO employee_role;
GRANT db_role TO manager_role;
----------------------------------------------------------------------
-- 8. Create tablespace and HR schema
----------------------------------------------------------------------
-- Create the tablespace used by the HR demo schema.
-- Oracle Managed Files (OMF) controls the datafile location.
CREATE TABLESPACE users DATAFILE SIZE 100M AUTOEXTEND ON;
-- Create the HR schema that owns the employee table.
-- NO AUTHENTICATION means this schema cannot be logged into directly;
-- it is accessed only through object grants.
CREATE USER hr NO AUTHENTICATION
DEFAULT TABLESPACE users
QUOTA UNLIMITED ON users;
----------------------------------------------------------------------
-- 9. Create the employee table
----------------------------------------------------------------------
-- Create the data model
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)
);
----------------------------------------------------------------------
-- 10. Load sample data
----------------------------------------------------------------------
-- Email and manager values use UPN format (firstname@ENTRA_DOMAIN)
-- The Entra-issued JWT and passes as ORA_END_USER_CONTEXT.username.
INSERT INTO hr.employees VALUES (100,'Victoria','Williams','victoria@&ENTRA_DOMAIN',NULL,'219-09-9999',13000,'555-0100');
INSERT INTO hr.employees VALUES (200,'Marvin','Anderson','marvin@&ENTRA_DOMAIN','victoria@&ENTRA_DOMAIN','457-55-5462',12030,'555-0200');
INSERT INTO hr.employees VALUES (300,'Chris','Evans','chris@&ENTRA_DOMAIN','victoria@&ENTRA_DOMAIN','321-12-4567',6900,'555-0300');
INSERT INTO hr.employees VALUES (400,'Emma','Baker','emma@&ENTRA_DOMAIN','marvin@&ENTRA_DOMAIN','733-02-9821',8200,'555-0400');
INSERT INTO hr.employees VALUES (500,'Taylor','Mills','taylor@&ENTRA_DOMAIN','marvin@&ENTRA_DOMAIN','558-76-1243',9000,'555-0500');
COMMIT;
----------------------------------------------------------------------
-- 11. Create data grants
----------------------------------------------------------------------
-- Employee access rule:
-- A user can see only the row where the email matches their identity.
CREATE OR REPLACE DATA GRANT hr.employees_own_record
AS SELECT
ON hr.employees
WHERE email = ORA_END_USER_CONTEXT.username
TO employee_role;
-- Manager access rule:
-- A manager can see rows where the manager column matches their identity.
-- The ALL COLUMNS EXCEPT clause masks SSN for those rows.
CREATE OR REPLACE 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;
spool off
A.2.2.2 02_entra_cleanup.sql
The source for the 02_entra_cleanup.sql
script.
SET ECHO ON
SET FEEDBACK 1
SET NUMWIDTH 10
SET LINESIZE 200
SET TRIMSPOOL ON
SET TAB OFF
SET PAGESIZE 100
define syspasswd=&1
define target_pdb=&2
spool 02_entra_cleanup.log
-- This script removes all objects created by the demo so the
-- environment can be reused cleanly.
----------------------------------------------------------------------
-- 1. Connect as SYS
----------------------------------------------------------------------
connect sys/&syspasswd@&target_pdb as sysdba
----------------------------------------------------------------------
-- 2. Remove the HR schema
----------------------------------------------------------------------
DROP USER hr CASCADE;
----------------------------------------------------------------------
-- 3. Remove data roles
----------------------------------------------------------------------
DROP DATA ROLE employee_role;
DROP DATA ROLE manager_role;
----------------------------------------------------------------------
-- 4. Remove the database role
----------------------------------------------------------------------
DROP ROLE db_role;
----------------------------------------------------------------------
-- 5. Remove the administration user and tablespace
----------------------------------------------------------------------
DROP USER DEEPSEC_DBA CASCADE;
DROP TABLESPACE users INCLUDING CONTENTS AND DATAFILES;
spool off
A.2.3 Log Files
Review the output generated by running each script.
| File | Purpose |
|---|---|
02_entra_demo.log |
Output generated by running
02_entra_demo.sql.
|
02_entra_cleanup.log |
Output generated by running
02_entra_cleanup.sql.
|
A.2.3.1 02_entra_demo.log
The output generated by running
02_entra_demo.sql.
SQL>
SQL> ----------------------------------------------------------------------
SQL> -- HR Security Demo - Direct Logon with IAM Users
SQL> ----------------------------------------------------------------------
SQL> -- This script creates the demo environment:
SQL> -- * Configure the database to accept external identities
SQL> -- * Create data roles, which map to Entra ID roles
SQL> -- * Create sample HR application data
SQL> -- * Define data grants to restrict access to Marvin and Emma based on their roles
SQL>
SQL> ----------------------------------------------------------------------
SQL> -- 1. Connect as SYS to the target PDB
SQL> ----------------------------------------------------------------------
SQL>
SQL> connect sys/&syspasswd@&target_pdb as sysdba
Connected.
SQL>
SQL> ----------------------------------------------------------------------
SQL> -- 2. Create the administration user
SQL> ----------------------------------------------------------------------
SQL>
SQL> -- This local user is used to manage the demo environment.
SQL> create user DEEPSEC_DBA identified by &dbapasswd;
old 1: create user DEEPSEC_DBA identified by &dbapasswd
new 1: create user DEEPSEC_DBA identified by dba_pwd
User created.
SQL> grant DBA to DEEPSEC_DBA;
Grant succeeded.
SQL>
SQL> ----------------------------------------------------------------------
SQL> -- 3. Connect as the administration user
SQL> ----------------------------------------------------------------------
SQL>
SQL> connect DEEPSEC_DBA/&dbapasswd@&target_pdb
Connected.
SQL>
SQL> ----------------------------------------------------------------------
SQL> -- 4. Configure the identity provider
SQL> ----------------------------------------------------------------------
SQL>
SQL> -- Enable Microsoft Entra ID as the identity provider for this PDB.
SQL> -- SCOPE = BOTH applies the setting to both the CDB and the PDB.
SQL> -- The database will use these values to validate incoming Entra tokens.
SQL> ALTER SYSTEM SET IDENTITY_PROVIDER_TYPE = AZURE_AD SCOPE = BOTH;
System altered.
SQL>
SQL> -- Supply the Entra tenant details so the database can validate tokens.
SQL> -- application_id_uri: the Application ID URI of the EmployeeRecordsDB
SQL> -- Entra app registration [DB_APP_ID_URI]
SQL> -- tenant_id: the Directory (tenant) ID of your Entra tenant [TENANT_ID]
SQL> -- app_id: the Application (client) ID of EmployeeRecordsDB [DB_APP_ID]
SQL> ALTER SYSTEM SET IDENTITY_PROVIDER_CONFIG = '{
2 "application_id_uri": "&DB_APP_ID_URI",
3 "tenant_id": "&TENANT_ID",
4 "app_id": "&DB_APP_ID"
5 }' SCOPE = BOTH;
old 2: "application_id_uri": "&DB_APP_ID_URI",
new 2: "application_id_uri": "<DB_APP_ID_URI>",
old 3: "tenant_id": "&TENANT_ID",
new 3: "tenant_id": "<TENANT_ID>",
old 4: "app_id": "&DB_APP_ID"
new 4: "app_id": "<DB_APP_ID>"
System altered.
SQL>
SQL> ----------------------------------------------------------------------
SQL> -- 5. Create data roles
SQL> ----------------------------------------------------------------------
SQL>
SQL> -- Employees can see only their own row.
SQL> CREATE DATA ROLE employee_role MAPPED TO 'AZURE_ROLE=EMPLOYEE';
Data role created.
SQL>
SQL> -- Managers can see their direct reports (with SSN masked).
SQL> CREATE DATA ROLE manager_role MAPPED TO 'AZURE_ROLE=MANAGER';
Data role created.
SQL>
SQL> ----------------------------------------------------------------------
SQL> -- 6. Create the session role
SQL> ----------------------------------------------------------------------
SQL>
SQL> -- A standard database role that carries the CREATE SESSION privilege.
SQL> -- Granting this role to a data role allows end users who hold that
SQL> -- data role to establish a database session.
SQL> CREATE ROLE db_role;
Role created.
SQL> GRANT CREATE SESSION TO db_role;
Grant succeeded.
SQL>
SQL> ----------------------------------------------------------------------
SQL> -- 7. Grant create session privilege to data roles
SQL> ----------------------------------------------------------------------
SQL>
SQL> GRANT db_role TO employee_role;
Grant succeeded.
SQL> GRANT db_role TO manager_role;
Grant succeeded.
SQL>
SQL> ----------------------------------------------------------------------
SQL> -- 8. Create tablespace and HR schema
SQL> ----------------------------------------------------------------------
SQL>
SQL> -- Create the tablespace used by the HR demo schema.
SQL> -- Oracle Managed Files (OMF) controls the datafile location.
SQL> CREATE TABLESPACE users DATAFILE SIZE 100M AUTOEXTEND ON;
Tablespace created.
SQL>
SQL> -- Create the HR schema that owns the employee table.
SQL> -- NO AUTHENTICATION means this schema cannot be logged into directly;
SQL> -- it is accessed only through object grants.
SQL> CREATE USER hr NO AUTHENTICATION
2 DEFAULT TABLESPACE users
3 QUOTA UNLIMITED ON users;
User created.
SQL>
SQL> ----------------------------------------------------------------------
SQL> -- 9. Create the employee table
SQL> ----------------------------------------------------------------------
SQL>
SQL> -- Create the data model
SQL> CREATE TABLE hr.employees (
2 employee_id NUMBER PRIMARY KEY,
3 first_name VARCHAR2(50),
4 last_name VARCHAR2(50),
5 email VARCHAR2(128),
6 manager VARCHAR2(128),
7 ssn VARCHAR2(20),
8 salary NUMBER(10,2),
9 phone VARCHAR2(20)
10 );
Table created.
SQL>
SQL> ----------------------------------------------------------------------
SQL> -- 10. Load sample data
SQL> ----------------------------------------------------------------------
SQL>
SQL> -- Email and manager values use UPN format (firstname@ENTRA_DOMAIN)
SQL> -- The Entra-issued JWT and passes as ORA_END_USER_CONTEXT.username.
SQL> INSERT INTO hr.employees VALUES (100,'Victoria','Williams','victoria@&ENTRA_DOMAIN',NULL,'219-09-9999',13000,'555-0100');
old 1: INSERT INTO hr.employees VALUES (100,'Victoria','Williams','victoria@&ENTRA_DOMAIN',NULL,'219-09-9999',13000,'555-0100')
new 1: INSERT INTO hr.employees VALUES (100,'Victoria','Williams','victoria@<ENTRA_DOMAIN>',NULL,'219-09-9999',13000,'555-0100')
1 row created.
SQL> INSERT INTO hr.employees VALUES (200,'Marvin','Anderson','marvin@&ENTRA_DOMAIN','victoria@&ENTRA_DOMAIN','457-55-5462',12030,'555-0200');
old 1: INSERT INTO hr.employees VALUES (200,'Marvin','Anderson','marvin@&ENTRA_DOMAIN','victoria@&ENTRA_DOMAIN','457-55-5462',12030,'555-0200')
new 1: INSERT INTO hr.employees VALUES (200,'Marvin','Anderson','marvin@<ENTRA_DOMAIN>','victoria@<ENTRA_DOMAIN>','457-55-5462',12030,'555-0200')
1 row created.
SQL> INSERT INTO hr.employees VALUES (300,'Chris','Evans','chris@&ENTRA_DOMAIN','victoria@&ENTRA_DOMAIN','321-12-4567',6900,'555-0300');
old 1: INSERT INTO hr.employees VALUES (300,'Chris','Evans','chris@&ENTRA_DOMAIN','victoria@&ENTRA_DOMAIN','321-12-4567',6900,'555-0300')
new 1: INSERT INTO hr.employees VALUES (300,'Chris','Evans','chris@<ENTRA_DOMAIN>','victoria@<ENTRA_DOMAIN>','321-12-4567',6900,'555-0300')
1 row created.
SQL> INSERT INTO hr.employees VALUES (400,'Emma','Baker','emma@&ENTRA_DOMAIN','marvin@&ENTRA_DOMAIN','733-02-9821',8200,'555-0400');
old 1: INSERT INTO hr.employees VALUES (400,'Emma','Baker','emma@&ENTRA_DOMAIN','marvin@&ENTRA_DOMAIN','733-02-9821',8200,'555-0400')
new 1: INSERT INTO hr.employees VALUES (400,'Emma','Baker','emma@<ENTRA_DOMAIN>','marvin@<ENTRA_DOMAIN>','733-02-9821',8200,'555-0400')
1 row created.
SQL> INSERT INTO hr.employees VALUES (500,'Taylor','Mills','taylor@&ENTRA_DOMAIN','marvin@&ENTRA_DOMAIN','558-76-1243',9000,'555-0500');
old 1: INSERT INTO hr.employees VALUES (500,'Taylor','Mills','taylor@&ENTRA_DOMAIN','marvin@&ENTRA_DOMAIN','558-76-1243',9000,'555-0500')
new 1: INSERT INTO hr.employees VALUES (500,'Taylor','Mills','taylor@<ENTRA_DOMAIN>','marvin@<ENTRA_DOMAIN>','558-76-1243',9000,'555-0500')
1 row created.
SQL> COMMIT;
Commit complete.
SQL>
SQL> ----------------------------------------------------------------------
SQL> -- 11. Create data grants
SQL> ----------------------------------------------------------------------
SQL>
SQL> -- Employee access rule:
SQL> -- A user can see only the row where the email matches their identity.
SQL> CREATE OR REPLACE DATA GRANT hr.employees_own_record
2 AS SELECT
3 ON hr.employees
4 WHERE email = ORA_END_USER_CONTEXT.username
5 TO employee_role;
Data grant created.
SQL>
SQL> -- Manager access rule:
SQL> -- A manager can see rows where the manager column matches their identity.
SQL> -- The ALL COLUMNS EXCEPT clause masks SSN for those rows.
SQL> CREATE OR REPLACE DATA GRANT hr.manager_direct_reports
2 AS SELECT (ALL COLUMNS EXCEPT ssn)
3 ON hr.employees
4 WHERE manager = ORA_END_USER_CONTEXT.username
5 TO manager_role;
Data grant created.
SQL>
SQL> spool off
A.2.3.2 02_entra_cleanup.log
The output generated by running
02_entra_cleanup.sql.
SQL>
SQL> -- This script removes all objects created by the demo so the
SQL> -- environment can be reused cleanly.
SQL>
SQL> ----------------------------------------------------------------------
SQL> -- 1. Connect as SYS
SQL> ----------------------------------------------------------------------
SQL>
SQL> connect sys/&syspasswd@&target_pdb as sysdba
Connected.
SQL>
SQL> ----------------------------------------------------------------------
SQL> -- 2. Remove the HR schema
SQL> ----------------------------------------------------------------------
SQL>
SQL> DROP USER hr CASCADE;
User dropped.
SQL>
SQL> ----------------------------------------------------------------------
SQL> -- 3. Remove data roles
SQL> ----------------------------------------------------------------------
SQL>
SQL> DROP DATA ROLE employee_role;
Data role dropped.
SQL> DROP DATA ROLE manager_role;
Data role dropped.
SQL>
SQL> ----------------------------------------------------------------------
SQL> -- 4. Remove the database role
SQL> ----------------------------------------------------------------------
SQL>
SQL> DROP ROLE db_role;
Role dropped.
SQL>
SQL> ----------------------------------------------------------------------
SQL> -- 5. Remove the administration user and tablespace
SQL> ----------------------------------------------------------------------
SQL>
SQL> DROP USER DEEPSEC_DBA CASCADE;
User dropped.
SQL> DROP TABLESPACE users INCLUDING CONTENTS AND DATAFILES;
Tablespace dropped.
SQL>
SQL> spool off