Skip to Main Content
Forums

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.

ORA -1031 in ORDS 20.4.3 on a protected resource

Varad AcharyaApr 5 2021

Hello
After I secure a REST service based on a pattern in the URL for the REST service, invoking the service results in an HTTP 500 error. The same service works correctly when it is no longer protected.
Any help is appreciated.
Varad

Error returned to caller
{
"code": "InternalServerError",
"message": "Internal Server Error",
"type": "tag:oracle.com,2020:error/InternalServerError",
"instance": "tag:oracle.com,2020:ecid/w53kXRgeTfF-x12TisfEFQ"
}

ORDS log has the following information
Msg = ORA-00604: error occurred at recursive SQL level 1
ORA-01031: insufficient privileges
select
p.name,
p.label,
p.description,
pm.pattern,
cursor(select r.name from user_ords_privilege_roles pr,
user_ords_roles r
where pr.privilege_id = p.id
and pr.role_id = r.id) roles
from user_ords_privileges p,
user_ords_privilege_mappings pm
where instr(?,trim(trailing '*' from pm.pattern)) = 1 and pm.privilege_id = p.id
order by pm.pattern desc
This post has been answered by thatJeffSmith-Oracle on May 10 2023
Jump to Answer

Comments

Varad Acharya

Update .
I could make the error go away by granting Read on the ORDS_METADATA.SEC__ * tables to the schema that owns the REST services.
Also , another peculiar observation. The REST service were previously created under APEX and then migrated to ORDS. Creating REST services under a REST enabled schema does not encounter the 'insuffiecient privileges' error !
Varad

user-j6snm

Parece que es muy común que esto suceda , a mi también me ha ocurrido , y no se que hacer

thatJeffSmith-Oracle
Answer

This sounds A LOT like this -

Known Issues

Oracle Database may report 'ORA-01031: insufficient privileges' for queries that involve CURSOR expressions

In a number of cases, ORDS executes queries which include a CURSOR expression. An issue has been found executing these queries for some schemas in Oracle 18c and Oracle 19c databases. Depending on the roles and privileges involved, the issue does not occur for all schemas, which can give the impression that the issue is intermittent. Refer to RDBMS bug 29049673 for more information. The issue is manifest as follows in the exception returned:

ORA-00604: error occurred at recursive SQL level 1 ORA-01031: insufficient privileges

This is a problem with view-merging of nested cursors introduced in Oracle 18.1 database. Please use the following as a workaround:

alter system set "_fix_control"='20648883:OFF';

Marked as Answer by thatJeffSmith-Oracle · May 10 2023
1 person found this helpful
1 - 3

Post Details

Added on Apr 5 2021
3 comments
1,041 views