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

Last updated on DECEMBER 08, 2022

Applies to:

Symptoms

On : 21.2 version, All Issues

User losing access to ORDS schema objects

Using Apex and ORDS with Oracle database. application works fine when database starts and eventually web server returns following error.

Application starts working again if 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.