Skip to Main Content

ORDS, SODA & JSON in the Database

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

ORDS 24.4 Privilegies needed to execute pl/sql procedures with ORDS option --gateway-mode direct and using --gateway-user

ElenaLoFeb 6 2025

I'm using ORDS only for mod_plsql. We config --gateway-mode direct and use --gateway-user. With ORDS 23.4 our gateway-user had GRANT EXECUTE ANY PROCEDURE and it works ok. But, with version 24.4 it doesn't work. The error is:

HTTP Status Code: 404

No se ha podido acceder al procedimiento package.procedure. Puede que no se haya declarado, que el usuario que ejecuta esta solicitud no tenga permiso de ejecución en el proceso o que una función especificada por la propiedad de configuración security.requestValidationFunction impida el acceso. Compruebe la ortografía del procedimiento y si se ha otorgado el privilegio de ejecución al usuario que realiza la llamada, así como la función configurada security.requestValidationFunction. Si se utiliza la lista de procedimientos permitidos de gateway PL/SQL, compruebe que el procedimiento está permitido en ords_admin.add_plsql_gateway_procedure.

I not use ords_admin.add_plsql_gateway_procedure.
Are there differences with version 23.4 and 24.4 about privilegies needed? We use the same config.

I install ORDS with command:
ords --config "C:\Program Files\ords\conf" install --db-pool poolname --admin-user sys --proxy-user --db-hostname servername --db-port 1521 --db-servicename sn --gateway-user username --gateway-mode direct --log-folder "C:\Program Files\ords\logs"

Also, I change the name poolname to default in the configuration for path access.

I'm using Tomcat 9.0 and the pool.xml is:

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE properties SYSTEM "http://java.sun.com/dtd/properties.dtd">
<properties>
<entry key="db.connectionType">basic</entry>
<entry key="db.hostname">servername</entry>
<entry key="db.port">1521</entry>
<entry key="db.servicename">sn</entry>
<entry key="db.username">username</entry>
<entry key="plsql.gateway.mode">direct</entry>
<entry key="debug.debugger">true</entry>
<entry key="debug.printDebugToScreen">true</entry>
<entry key="error.keepErrorMessages">true</entry>
<entry key="error.maxEntries">50</entry>
<entry key="owa.docTable">schema.table</entry>
<entry key="security.requestAuthenticationFunction">package.procedure</entry>
<entry key="jdbc.DriverType">thin</entry>
<entry key="jdbc.InactivityTimeout">1800</entry>
<entry key="jdbc.InitialLimit">10</entry>
<entry key="jdbc.MaxConnectionReuseCount">10000</entry>
<entry key="jdbc.MaxLimit">100</entry>
<entry key="jdbc.MaxStatementsLimit">50</entry>
<entry key="jdbc.MinLimit">10</entry>
<entry key="jdbc.statementTimeout">900</entry>
</properties>

I appreciate any help. Thanks

Comments

thatJeffSmith-Oracle Feb 13 2025

Your ENTRA users will get authenticated via JSON Web Tokens, and their Entra roles will determine which ORDS REST APIs they can hit.

When they hit an endpoint, it'll execute code in the database as the database user that owns the schema where the REST API is defined, not as Entra defiend end user. In fact, the Entra users won't have accounts in the database (they could, but wont' need to).

The :current_user field as far as ords is concerned would be the corresponding oauth2 client or JWT issued for the authorizied session.

Your prehook should be able to alter the session to set the context that would put your RLS/VPD security policy in play.

1 - 1

Post Details

Added on Feb 6 2025
1 comment
89 views