0 Replies Latest reply on Dec 15, 2018 9:31 AM by AdamBr

    ORDS 18.3, 18c XE - ORA-01031: insufficient privileges

    AdamBr

      Hello,

       

      I recently installed Oracle DB 18c XE, APEX 18.2 and ORDS 18.3 running under Tomcat 8 on Centos 7. APEX is installed into CBD and ORDS directly into PDB. I imported schema reservation_system with impdb, apex workspace and application with apex import. App is working fine.

       

      Then I tried to create RESTful service with SQL Workshop in APEX workspace administration. I registered schema with ORDS and let it install sample service. Then when I try to run any request I end up with 500 Internal server error.

       

      Debug trace:

      mapped request using: /res/* to: *RESERVATION_SYSTEM|apex|pu|, cryptoKeys=*****, id=2063562678411579, identityDomain=__NO_IDENTITY_DOMAIN__

      [TE] GET /ords_dev/res/hr/version/ start: 2018-12-15T09:05:33.235Z duration: 708ms

       

      Stack Trace:

      InternalServerException [statusCode=500, reasons=[An unexpected error with the following message occurred: InternalServerException [statusCode=500, reasons=[]]]]

      at oracle.dbtools.http.errors.ServletResponseExceptionMapper.mapError(ServletResponseExceptionMapper.java:84)

      at oracle.dbtools.http.errors.ErrorPageRenderer.<init>(ErrorPageRenderer.java:43)

      at oracle.dbtools.http.errors.ErrorPageRenderer.<init>(ErrorPageRenderer.java:34)

      at oracle.dbtools.http.errors.ErrorPageFilter.doFilter(ErrorPageFilter.java:113)

      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:82)

      at oracle.dbtools.http.entrypoint.EntryPointServlet.service(EntryPointServlet.java:102)

      at oracle.dbtools.entrypoint.WebApplicationRequestEntryPoint.service(WebApplicationRequestEntryPoint.java:50)

      at javax.servlet.http.HttpServlet.service(HttpServlet.java:742)

      at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:231)

      at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)

      at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)

      at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)

      at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)

      at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:198)

      at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:96)

      at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:478)

      at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:140)

      at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:80)

      at org.apache.catalina.valves.AbstractAccessLogValve.invoke(AbstractAccessLogValve.java:624)

      at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:87)

      at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:342)

      at org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:799)

      at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:66)

      at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:868)

      at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1455)

      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: com.google.common.util.concurrent.UncheckedExecutionException: InternalServerException [statusCode=500, reasons=[]]

      at com.google.common.cache.LocalCache$Segment.get(LocalCache.java:2050)

      at com.google.common.cache.LocalCache.get(LocalCache.java:3952)

      at com.google.common.cache.LocalCache$LocalManualCache.get(LocalCache.java:4871)

      at oracle.dbtools.rest.resource.jdbc.JDBCDispatchMetaData.canDispatch(JDBCDispatchMetaData.java:60)

      at oracle.dbtools.rest.resource.jdbc.JDBCDispatchMetaData.canDispatch(JDBCDispatchMetaData.java:35)

      at oracle.dbtools.dispatch.DispatchableChooser.choose(DispatchableChooser.java:36)

      at oracle.dbtools.rest.resource.modules.ModulesChooser.canDispatch(ModulesChooser.java:46)

      at oracle.dbtools.rest.resource.jdbc.JDBCResourceDispatcher.canDispatch(JDBCResourceDispatcher.java:68)

      at oracle.dbtools.rest.resource.jdbc.JDBCResourceDispatcher.canDispatch(JDBCResourceDispatcher.java:39)

      at oracle.dbtools.rest.resource.cache.ResourceCache.load(ResourceCache.java:120)

      at oracle.dbtools.rest.resource.cache.ResourceCache.canDispatch(ResourceCache.java:90)

      at oracle.dbtools.rest.resource.cache.CachedResourceDispatcher.canDispatch(CachedResourceDispatcher.java:52)

      at oracle.dbtools.rest.resource.cache.CachedResourceDispatcher.canDispatch(CachedResourceDispatcher.java:25)

      at oracle.dbtools.dispatch.DispatchableChooser.choose(DispatchableChooser.java:36)

      at oracle.dbtools.http.entrypoint.Dispatcher.choose(Dispatcher.java:73)

      at oracle.dbtools.http.entrypoint.Dispatcher.dispatch(Dispatcher.java:103)

      at oracle.dbtools.http.entrypoint.EntryPoint$FilteredServlet.service(EntryPoint.java:243)

      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:66)

      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:101)

      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:161)

      at oracle.dbtools.url.mapping.URLMappingBase.doFilter(URLMappingBase.java:96)

      at oracle.dbtools.url.mapping.filter.URLMappingFilter.doFilter(URLMappingFilter.java:127)

      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:83)

      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.errors.ErrorPageFilter.doFilter(ErrorPageFilter.java:85)

      ... 35 more

      Caused by: InternalServerException [statusCode=500, reasons=[]]

      at oracle.dbtools.http.errors.jdbc.JDBCServletResponseExceptions.handle(JDBCServletResponseExceptions.java:54)

      at oracle.dbtools.rest.resource.modules.jdbc.JDBCModuleResources.generatorMetadata(JDBCModuleResources.java:66)

      at oracle.dbtools.rest.resource.jdbc.JDBCResourcesProvider.generatorMetadata(JDBCResourcesProvider.java:109)

      at oracle.dbtools.rest.resource.jdbc.JDBCDispatchMetaData$1.call(JDBCDispatchMetaData.java:64)

      at oracle.dbtools.rest.resource.jdbc.JDBCDispatchMetaData$1.call(JDBCDispatchMetaData.java:60)

      at com.google.common.cache.LocalCache$LocalManualCache$1.load(LocalCache.java:4876)

      at com.google.common.cache.LocalCache$LoadingValueReference.loadFuture(LocalCache.java:3528)

      at com.google.common.cache.LocalCache$Segment.loadSync(LocalCache.java:2277)

      at com.google.common.cache.LocalCache$Segment.lockedGetOrLoad(LocalCache.java:2154)

      at com.google.common.cache.LocalCache$Segment.get(LocalCache.java:2044)

      ... 86 more

      Caused by: java.sql.SQLException: ORA-00604: error occurred at recursive SQL level 1

      ORA-01031: insufficient privileges

       

      at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:494)

      at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:446)

      at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1052)

      at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:537)

      at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:255)

      at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:610)

      at oracle.jdbc.driver.T4CCallableStatement.doOall8(T4CCallableStatement.java:249)

      at oracle.jdbc.driver.T4CCallableStatement.doOall8(T4CCallableStatement.java:82)

      at oracle.jdbc.driver.T4CCallableStatement.executeForRows(T4CCallableStatement.java:924)

      at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:950)

      at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1099)

      at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3640)

      at oracle.jdbc.driver.T4CCallableStatement.executeInternal(T4CCallableStatement.java:1318)

      at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3687)

      at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeQuery(OraclePreparedStatementWrapper.java:1165)

      at oracle.ucp.jdbc.proxy.oracle$1ucp$1jdbc$1proxy$1StatementProxy$2oracle$1jdbc$1internal$1OracleCallableStatement$$$Proxy.executeQuery(Unknown Source)

      at oracle.dbtools.common.stmt.jdbc.BindableQueryImpl.executeQuery(BindableQueryImpl.java:32)

      at oracle.dbtools.rest.resource.modules.jdbc.JDBCModuleResources.handlerMetadata(JDBCModuleResources.java:86)

      at oracle.dbtools.rest.resource.modules.jdbc.JDBCModuleResources.generatorMetadata(JDBCModuleResources.java:64)

      ... 94 more

      Caused by: Error : 604, Position : 0, Sql = select nvl(h.items_per_page,m.items_per_page) items_per_page, t.etag_type, t.etag_query, h.source_type, cursor(select p.name, p.bind_variable_name, p.source_type,p.access_method, p.param_type from user_ords_parameters p where p.handler_id = h.id) parameters, h.source from user_ords_modules m, user_ords_templates t, user_ords_handlers h where m.status = 'PUBLISHED' and t.id = h.template_id and m.id = t.module_id and h.id = :1 , OriginalSql = select nvl(h.items_per_page,m.items_per_page) items_per_page, t.etag_type, t.etag_query, h.source_type, cursor(select p.name, p.bind_variable_name, p.source_type,p.access_method, p.param_type from user_ords_parameters p where p.handler_id = h.id) parameters, h.source from user_ords_modules m, user_ords_templates t, user_ords_handlers h where m.status = 'PUBLISHED' and t.id = h.template_id and m.id = t.module_id and h.id = ?, Error Msg = ORA-00604: error occurred at recursive SQL level 1

      ORA-01031: insufficient privileges

       

      at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:498)

      ... 112 more

       

      I tried to run the statement under RESERVATION_SYSTEM user and found out that the problem is in the cursor statement:

      SELECT cursor(select p.name, p.bind_variable_name, p.source_type,p.access_method, p.param_type from user_ords_parameters p) parameters FROM DUAL;

      resolve in:

      ORA-00604: error occurred at recursive SQL level 1

      ORA-01031: insufficient privileges

       

      This statement runs fine:

      select p.name, p.bind_variable_name, p.source_type,p.access_method, p.param_type from user_ords_parameters p;

       

      The workaround is to grant read permission on ords_metadata.ords_schemas and ords_metadata.ords_parameters to schema RESERVATION_SYSTEM then RESTful service runs fine but that's not right:).

       

      Any help appreciated.

       

      Thank you,

       

      Adam