My Oracle Support Banner

Error When ORDS Tries To Connect To DB - ORA-01031: Insufficient Privileges Error (Doc ID 2735744.1)

Last updated on NOVEMBER 11, 2022

Applies to:

Oracle REST Data Services - Version 18.3 to 19.4
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Information in this document applies to any platform.

Symptoms

NOTE: The examples provided are for demonstration purposes only and represents fictitious data. Any similarity to actual data, web sites and / or persons, living or dead, is purely coincidental and not intended in any manner.



After configuring this instance the request for https://<IP>:<PORT>/ords/<SCHEMA_ALIAS>/<MODULE>/report_detail?report_id=1 fails with the following:
ORA-00604: error occurred at recursive SQL level 1
ORA-01031: insufficient privileges

The query with the REST service runs without error in SQL Workshop and returns one row.

Another instance has the same configuration / uses the same REST service without error.

ORDS 19.4.6 is configured in standalone mode on the same server as the OCI - Database.
**https://<IP>:<PORT>/ords/<SCHEMA_ALIAS>/hr/employees/ runs without error and the appropriate JSON response is returned.
** sqloutputprivileges.png - shows no privileges have been defined for the URL pattern that matches the REST service: /lacls/co/report_detail?report_id=1

- APEX / ORDS validation was executed.
  java -jar ords.war validate
  The issue still occurs.

The failing SQL request is:

Caused by: Error : 604, Position : 0, Sql = select p.name, cursor(select r.name from user_ords_roles r, user_ords_privilege_roles pr where pr.privilege_id = p.id and r.id = pr.role_id) roles from user_ords_privileges p where p.name = :1 , OriginalSql = select p.name, cursor(select r.name from user_ords_roles r, user_ords_privilege_roles pr where pr.privilege_id = p.id and r.id = pr.role_id) roles from user_ords_privileges p where p.name = ?, Error Msg = ORA-00604: error occurred at recursive SQL level 1
ORA-01031: insufficient privileges

at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:513)
... 105 more


Cause

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!


In this Document
Symptoms
Cause
Solution


My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.