Error When ORDS Tries To Connect To DB - ORA-01031: Insufficient Privileges Error
(Doc ID 2735744.1)
Last updated on JULY 22, 2024
Applies to:
Oracle REST Data Services - Version 18.3 to 19.4Oracle Cloud Infrastructure - Database Service - Version N/A and later
Information in this document applies to any platform.
Symptoms
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 |