Oracle R&D Challenge

Hi,

I am trying to configure Embedded PL/SQL Gateway (EPG) in Oracle Database 19c Standard Edition 2.

My requirement:

• Create a DAD HR_DAD
• The DAD points to schema HR
• Another database user EPG_DEV should authenticate using Basic Authentication
• After authentication, the PL/SQL procedure should execute under the HR schema

Current configuration:

Database version:

Oracle Database 19c Standard Edition 2 Release 19.0.0.0.0 - Production

DAD configuration:

DAD Name: HR_DAD
database-username: HR
authentication-mode: Basic

The procedure exists and is valid:

SELECT object_name, object_type, status
FROM user_objects
WHERE object_name = 'EPG_GET_SALARY_RANGE';

-- Output:
EPG_GET_SALARY_RANGE  PROCEDURE  VALID

The HR user is open:

SELECT username, account_status
FROM dba_users
WHERE username='HR';

-- Output:
HR OPEN

Authorization was configured using:

BEGIN
    DBMS_EPG.AUTHORIZE_DAD(
        'HR_DAD',
        'EPG_DEV'
    );
END;
/

Testing:

URL:

GET http://localhost:8080/HR_DAD/epg_get_salary_range

When using HR credentials:

Username: HR
Password: HR

the request works.

When using EPG_DEV credentials:

Username: EPG_DEV
Password: <correct password>

the request fails:

HTTP/1.1 401 Unauthorized

WWW-Authenticate: Basic realm="HR_DAD"

The database login for EPG_DEV works successfully, so the password is correct.

The HTTP request is reaching XDB and Basic authentication challenge is returned:

GET /HR_DAD/epg_get_salary_range HTTP/1.1
Authorization: Basic <EPG_DEV credentials>

HTTP/1.1 401 Unauthorized
WWW-Authenticate: Basic realm="HR_DAD"

I also checked:

SELECT DBMS_EPG.GET_DAD_ATTRIBUTE(
       'HR_DAD',
       'authentication-mode')
FROM dual;

Result:

Basic

and:

SELECT DBMS_EPG.GET_DAD_ATTRIBUTE(
       'HR_DAD',
       'database-username')
FROM dual;

Result:

HR

An interesting observation:

• When HR is authorized using DBMS_EPG.AUTHORIZE_DAD, behavior changes.
• Removing HR authorization causes Basic authentication with HR to work.
• EPG_DEV authentication still fails.

I also noticed that:

SELECT DBMS_EPG.GET_DAD_ATTRIBUTE(
       'HR_DAD',
       'authorization')
FROM dual;

returns NULL.

Attempting to set it:

BEGIN
    DBMS_EPG.SET_DAD_ATTRIBUTE(
        'HR_DAD',
        'authorization',
        'Basic'
    );
END;
/

fails with:

ORA-24232: unknown Embedded PL/SQL Gateway attribute authorization

Questions:

1. Is it supported in Oracle 19c to have one user (EPG_DEV) authenticate against a DAD that executes using another schema (HR)?
2. Is there any additional XDB/EPG configuration required besides DBMS_EPG.AUTHORIZE_DAD?
3. How can I verify which users are currently authorized for a DAD?
4. Is there any known issue with Basic authentication and AUTHORIZE_DAD in Oracle 19c?

Any guidance would be appreciated.

Thanks.