User Losing Access To ORDS Schema Objects (Doc ID 2906667.1)

Last updated on SEPTEMBER 18, 2023

Applies to:

Symptoms

User is losing access to Oracle REST Data Services (ORDS) 21.2 schema objects.

When using Apex and ORDS with Oracle database, the application works fine when the database starts. However, eventually the web server returns the following error.

Application starts working again if the database is restarted.

ERROR
-----------------------

2022-10-18T13:08:51.217Z INFO <....> POST <hostname> /ords/api/sso/v1/get_session 403 The user can not access the ORDS Metadata database objects. Please consult the section in the ORDS documentation titled 'Troubleshooting ORDS Metadata database objects access'.
ORDSMetadataNotFoundException [statusCode=403, logLevel=INFO, reasons=[The user can not access the ORDS Metadata database objects. Please consult the section in the ORDS documentation titled 'Troubleshooting ORDS Metadata database objects access'.]]
  at oracle.dbtools.rest.resource.privileges.jdbc.JDBCPrivilegeMappings.mappings(JDBCPrivilegeMappings.java:209)
  at oracle.dbtools.rest.resource.privileges.jdbc.JDBCPrivilegeMappings.authorizationConstraint(JDBCPrivilegeMappings.java:80)
  at oracle.dbtools.rest.resource.privileges.cache.PrivilegeCache.authorizationConstraint(PrivilegeCache.java:43)
  at oracle.dbtools.rest.resource.privileges.cache.CachedPrivilegeMappings.authorizationConstraint(CachedPrivilegeMappings.java:76)
  at oracle.dbtools.http.auth.RequestAuthorizationProvider.authorizationConstraint(RequestAuthorizationProvider.java:84)
  at oracle.dbtools.http.auth.AuthorizationConstraintsDispatchHook.getConstraint(AuthorizationConstraintsDispatchHook.java:64)
  at oracle.dbtools.http.auth.AuthorizationConstraintsDispatchHook.before(AuthorizationConstraintsDispatchHook.java:42)
  at oracle.dbtools.http.dispatch.hooks.DispatchHookChain.before(DispatchHookChain.java:34)
  at oracle.dbtools.http.dispatch.hooks.DispatchHooks.before(DispatchHooks.java:49)
  at oracle.dbtools.http.entrypoint.Dispatcher.dispatch(Dispatcher.java:122)
  at oracle.dbtools.http.entrypoint.EntryPoint$FilteredServlet.service(EntryPoint.java:170)
  at oracle.dbtools.http.filters.FilterChainImpl.doFilter(FilterChainImpl.java:73)
  at oracle.dbtools.http.forwarding.QueryFilteringRewrite.doFilter(QueryFilteringRewrite.java:90)
  at oracle.dbtools.http.filters.HttpFilter.doFilter(HttpFilter.java:47)
  at oracle.dbtools.http.filters.FilterChainImpl.doFilter(FilterChainImpl.java:64)
  at oracle.dbtools.http.forwarding.ForwardingFilter.doFilter(ForwardingFilter.java:68)
  at oracle.dbtools.http.filters.HttpFilter.doFilter(HttpFilter.java:47)
  at oracle.dbtools.http.filters.FilterChainImpl.doFilter(FilterChainImpl.java:64)
  at oracle.dbtools.http.cors.CORSPreflightFilter.doFilter(CORSPreflightFilter.java:68)
  at oracle.dbtools.http.filters.HttpFilter.doFilter(HttpFilter.java:47)
  at oracle.dbtools.http.filters.FilterChainImpl.doFilter(FilterChainImpl.java:64)
  at oracle.dbtools.http.cookies.auth.CookieSessionCSRFFilter.doFilter(CookieSessionCSRFFilter.java:71)
  at oracle.dbtools.http.filters.HttpFilter.doFilter(HttpFilter.java:47)
  at oracle.dbtools.http.filters.FilterChainImpl.doFilter(FilterChainImpl.java:64)
  at oracle.dbtools.http.auth.AuthenticationFilter.authenticate(AuthenticationFilter.java:91)
  at oracle.dbtools.http.auth.AuthenticationFilter.doFilter(AuthenticationFilter.java:64)
  at oracle.dbtools.http.filters.HttpFilter.doFilter(HttpFilter.java:47)
  at oracle.dbtools.http.filters.FilterChainImpl.doFilter(FilterChainImpl.java:64)
  at oracle.dbtools.url.mapping.RequestMapperImpl.doFilter(RequestMapperImpl.java:158)
  at oracle.dbtools.url.mapping.URLMappingBase.doFilter(URLMappingBase.java:86)
  at oracle.dbtools.url.mapping.db.DatabaseTenantMapping.dispatchSelf(DatabaseTenantMapping.java:193)
  at oracle.dbtools.url.mapping.db.DatabaseTenantMappingBase.doFilter(DatabaseTenantMappingBase.java:50)
  at oracle.dbtools.url.mapping.defaultpool.LegacyDatabaseTenantMapping.doFilter(LegacyDatabaseTenantMapping.java:46)
  at oracle.dbtools.url.mapping.tenant.TenantMappingDispatcher.dispatch(TenantMappingDispatcher.java:52)
  at oracle.dbtools.url.mapping.db.DatabaseTenantMappingBase.dispatchChild(DatabaseTenantMappingBase.java:151)
  at oracle.dbtools.url.mapping.db.DatabaseTenantMappingBase.doFilter(DatabaseTenantMappingBase.java:48)
  at oracle.dbtools.url.mapping.defaultpool.LegacyDatabaseTenantMapping.doFilter(LegacyDatabaseTenantMapping.java:46)
  at oracle.dbtools.url.mapping.tenant.TenantMappingDispatcher.dispatch(TenantMappingDispatcher.java:52)
  at oracle.dbtools.url.mapping.tenant.TenantMappingFilter.doFilter(TenantMappingFilter.java:75)
  at oracle.dbtools.http.filters.HttpFilter.doFilter(HttpFilter.java:47)
  at oracle.dbtools.http.filters.FilterChainImpl.doFilter(FilterChainImpl.java:64)
  at oracle.dbtools.http.forwarding.ForwardingFailedFilter.doFilter(ForwardingFailedFilter.java:41)
  at oracle.dbtools.http.filters.HttpFilter.doFilter(HttpFilter.java:47)
  at oracle.dbtools.http.filters.FilterChainImpl.doFilter(FilterChainImpl.java:64)
  at oracle.dbtools.http.auth.external.ExternalSessionFilter.doFilter(ExternalSessionFilter.java:59)
  at oracle.dbtools.http.filters.HttpFilter.doFilter(HttpFilter.java:47)
  at oracle.dbtools.http.filters.FilterChainImpl.doFilter(FilterChainImpl.java:64)
  at oracle.dbtools.rt.authentication.apex.ApexSessionQueryRewriteFilter.doFilter(ApexSessionQueryRewriteFilter.java:58)
  at oracle.dbtools.http.filters.HttpFilter.doFilter(HttpFilter.java:47)
  at oracle.dbtools.http.filters.FilterChainImpl.doFilter(FilterChainImpl.java:64)
  at oracle.dbtools.http.cors.CORSResponseFilter.doFilter(CORSResponseFilter.java:90)
  at oracle.dbtools.http.filters.HttpResponseFilter.doFilter(HttpResponseFilter.java:45)
  at oracle.dbtools.http.filters.FilterChainImpl.doFilter(FilterChainImpl.java:64)
  at oracle.dbtools.http.filters.AbsoluteLocationFilter.doFilter(AbsoluteLocationFilter.java:65)
  at oracle.dbtools.http.filters.HttpResponseFilter.doFilter(HttpResponseFilter.java:45)
  at oracle.dbtools.http.filters.FilterChainImpl.doFilter(FilterChainImpl.java:64)
  at oracle.dbtools.http.auth.external.ExternalAccessValidationFilter.doFilter(ExternalAccessValidationFilter.java:59)
  at oracle.dbtools.http.filters.HttpFilter.doFilter(HttpFilter.java:47)
  at oracle.dbtools.http.filters.FilterChainImpl.doFilter(FilterChainImpl.java:64)
  at oracle.dbtools.http.errors.ErrorPageFilter.doFilter(ErrorPageFilter.java:87)
  at oracle.dbtools.http.filters.HttpFilter.doFilter(HttpFilter.java:47)
  at oracle.dbtools.http.filters.FilterChainImpl.doFilter(FilterChainImpl.java:64)
  at oracle.dbtools.http.secure.ForceHttpsFilter.doFilter(ForceHttpsFilter.java:74)
  at oracle.dbtools.http.filters.HttpFilter.doFilter(HttpFilter.java:47)
  at oracle.dbtools.http.filters.FilterChainImpl.doFilter(FilterChainImpl.java:64)
  at oracle.dbtools.http.auth.ForceAuthFilter.doFilter(ForceAuthFilter.java:44)
  at oracle.dbtools.http.filters.HttpFilter.doFilter(HttpFilter.java:47)
  at oracle.dbtools.http.filters.FilterChainImpl.doFilter(FilterChainImpl.java:64)
  at oracle.dbtools.http.filters.Filters.filter(Filters.java:67)
  at oracle.dbtools.http.entrypoint.EntryPoint.service(EntryPoint.java:70)
  at oracle.dbtools.http.entrypoint.EntryPointServlet.service(EntryPointServlet.java:125)
  at oracle.dbtools.entrypoint.WebApplicationRequestEntryPoint.service(WebApplicationRequestEntryPoint.java:50)
  at jakarta.servlet.http.HttpServlet.service(HttpServlet.java:770)
  at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:223)
  at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:158)
  at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:53)
  at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:185)
  at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:158)
  at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:202)
  at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:97)
  at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:543)
  at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:127)
  at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:92)
  at org.apache.catalina.valves.AbstractAccessLogValve.invoke(AbstractAccessLogValve.java:690)
  at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:78)
  at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:353)
  at org.apache.coyote.ajp.AjpProcessor.service(AjpProcessor.java:439)
  at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:65)
  at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:870)
  at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1696)
  at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:49)
  at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
  at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
  at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
  at java.lang.Thread.run(Thread.java:748)
Caused by: java.sql.SQLException: ORA-00604: error occurred at recursive SQL level 1
ORA-00942: table or view does not exist
  at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:628)
  at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:562)
  at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1145)
  at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:726)
  at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:291)
  at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:492)
  at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:148)
  at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:928)
  at oracle.jdbc.driver.OracleStatement.prepareDefineBufferAndExecute(OracleStatement.java:1158)
  at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1093)
  at oracle.jdbc.driver.OracleStatement.executeSQLSelect(OracleStatement.java:1402)
  at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1285)
  at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3735)
  at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3847)
  at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeQuery(OraclePreparedStatementWrapper.java:1098)
  at oracle.ucp.jdbc.proxy.oracle$1ucp$1jdbc$1proxy$1oracle$1StatementProxy$2oracle$1jdbc$1internal$1OraclePreparedStatement$$$Proxy.executeQuery(Unknown Source)
  at oracle.dbtools.rest.resource.privileges.jdbc.JDBCPrivilegeMappings.mappings(JDBCPrivilegeMappings.java:166)
  ... 94 more
Caused by: Error : 604, Position : 0, Sql = 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(:1 ,trim(trailing '*' from pm.pattern)) = 1 and pm.privilege_id = p.id
order by pm.pattern desc
, OriginalSql = 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
, Error Msg = ORA-00604: error occurred at recursive SQL level 1
ORA-00942: table or view does not exist

  at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:632)
  ... 110 more

2022-10-18T13:10:22.676Z WARNING An attempt was made to authenticate the locked user [....]
2022-10-18T13:10:22.710Z INFO POST <hostname> /ords/api/sso/v1/get_session 403 The user can not access the ORDS Metadata database objects. Please consult the section in the ORDS documentation titled 'Troubleshooting ORDS Metadata database objects access'.
ORDSMetadataNotFoundException [statusCode=403, logLevel=INFO, reasons=[The user can not access the ORDS Metadata database objects. Please consult the section in the ORDS documentation titled 'Troubleshooting ORDS Metadata database objects access'.]]

Cause

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