Hello *,
I've set up a datasource in the visual administrator to an external Oracle database, which works fine for vendor SQL. Native SQL works only if I use the DB system admin account. If I take a user with access rights only for the application's DB schema, I get an SQLException when I try to execute "dataSource.getConnection()" with the message
... ORA-00942: Tabelle oder View nicht vorhanden
(table or view not available)
It seems as if WAS tries to access tables outside the applications's schema on opening the connection.
What do I need to do to avoid this exception with native SQL and a restricted DB user?
Regards
Martin
The <b>stack trace</b> for the above mentioned exception:
#1.5#0030051B5190003900000027000008A40003DC90F1981DE3#1086940016809#System.err#sap.com/SIS_EAR#System.err#Guest#2#####SAPEngine_Application_Thread[impl:3]_29##0#0#Path##Plain###com.sap.engine.services.dbpool.exceptions.BaseSQLException: ResourceException occured in method ConnectionFactoryImpl.getConnection(): com.sap.engine.services.dbpool.exceptions.BaseResourceException: SQLException is thrown by the physical connection: java.sql.SQLException: ORA-00942: Tabelle oder View nicht vorhanden
at com.sap.engine.services.dbpool.cci.ConnectionFactoryImpl.getConnection(ConnectionFactoryImpl.java:59)
at com.ruhrgas.ee35.ejb.EE35SupplierBean.getObjectValues(EE35SupplierBean.java:63)
at com.ruhrgas.ee35.ejb.EE35SupplierObjectImpl0.getObjectValues(EE35SupplierObjectImpl0.java:212)
at com.ruhrgas.ee35.ejb.EE35Supplier_Stub.getObjectValues(EE35Supplier_Stub.java:132)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:324)
at com.sap.engine.services.webservices.runtime.EJBImplementationContainer.invokeMethod(EJBImplementationContainer.java:99)
at com.sap.engine.services.webservices.runtime.RuntimeProcessor.process(RuntimeProcessor.java:145)
at com.sap.engine.services.webservices.runtime.RuntimeProcessor.process(RuntimeProcessor.java:67)
at com.sap.engine.services.webservices.runtime.servlet.ServletDispatcherImpl.doPost(ServletDispatcherImpl.java:92)
at SoapServlet.doPost(SoapServlet.java:51)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:760)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:853)
at com.sap.engine.services.servlets_jsp.server.HttpHandlerImpl.runServlet(HttpHandlerImpl.java:373)
at com.sap.engine.services.servlets_jsp.server.HttpHandlerImpl.handleRequest(HttpHandlerImpl.java:250)
at com.sap.engine.services.httpserver.server.RequestAnalizer.startServlet(RequestAnalizer.java:319)
at com.sap.engine.services.httpserver.server.RequestAnalizer.startServlet(RequestAnalizer.java:297)
at com.sap.engine.services.httpserver.server.RequestAnalizer.invokeWebContainer(RequestAnalizer.java:696)
at com.sap.engine.services.httpserver.server.RequestAnalizer.handle(RequestAnalizer.java:221)
at com.sap.engine.services.httpserver.server.Client.handle(Client.java:92)
at com.sap.engine.services.httpserver.server.Processor.request(Processor.java:146)
at com.sap.engine.core.service630.context.cluster.session.ApplicationSessionMessageListener.process(ApplicationSessionMessageListener.java:37)
at com.sap.engine.core.cluster.impl6.session.UnorderedChannel$MessageRunner.run(UnorderedChannel.java:71)
at com.sap.engine.core.thread.impl3.ActionObject.run(ActionObject.java:37)
at java.security.AccessController.doPrivileged(Native Method)
at com.sap.engine.core.thread.impl3.SingleThread.execute(SingleThread.java:94)
at com.sap.engine.core.thread.impl3.SingleThread.run(SingleThread.java:140)
Caused by: com.sap.engine.services.dbpool.exceptions.BaseResourceException: SQLException is thrown by the physical connection: java.sql.SQLException: ORA-00942: Tabelle oder View nicht vorhanden
at com.sap.engine.services.dbpool.spi.CPManagedConnectionFactoryImpl.createManagedConnection(CPManagedConnectionFactoryImpl.java:179)
at com.sap.engine.services.connector.jca.ConnectionHashSet.match(ConnectionHashSet.java:269)
at com.sap.engine.services.connector.jca.ConnectionManagerImpl.allocateConnection(ConnectionManagerImpl.java:188)
at com.sap.engine.services.dbpool.cci.ConnectionFactoryImpl.getConnection(ConnectionFactoryImpl.java:51)
... 28 more
Caused by: java.sql.SQLException: ORA-00942: Tabelle oder View nicht vorhanden
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134)
at oracle.jdbc.ttc7.TTIoer.processError(TTIoer.java:289)
at oracle.jdbc.ttc7.Oall7.receive(Oall7.java:579)
at oracle.jdbc.ttc7.TTC7Protocol.doOall7(TTC7Protocol.java:1894)
at oracle.jdbc.ttc7.TTC7Protocol.parseExecuteDescribe(TTC7Protocol.java:831)
at oracle.jdbc.driver.OracleStatement.doExecuteQuery(OracleStatement.java:2496)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:2840)
at oracle.jdbc.driver.OracleStatement.executeQuery(OracleStatement.java:623)
at com.sap.sql.jdbc.oracle.OraclePortingServices.getDatabaseHost(OraclePortingServices.java:169)
at com.sap.sql.jdbc.direct.DirectConnectionFactory.createContextFactory(DirectConnectionFactory.java:332)
at com.sap.sql.jdbc.direct.DirectConnectionFactory.createDirectPooledConnection(DirectConnectionFactory.java:189)
at com.sap.sql.connect.OpenSQLConnectionFactory.createDirectPooledConnection(OpenSQLConnectionFactory.java:229)
at com.sap.engine.services.dbpool.spi.CPManagedConnectionFactoryImpl.createManagedConnection(CPManagedConnectionFactoryImpl.java:174)
... 31 more
#
Yuri,
thanks for your reply.
I don't think it's the problem that's covered in the excerpt from the Oracle doc. The connection can be established for both users with <i>Vendor SQL</i>. For <i>Native SQL</i> it works only with SYSDBA access rights. Moreover, the exception is thrown on the Java statement:
conn = ((DataSource)ctx.lookup("jdbc/SIS_DS")).getConnection();
That is, <i>before</i> I use any of my tables.
That's why I assume WAS tries to access tables outside the applications's schema on opening the connection through the Native SQL layer.
Regards
Martin
Add a comment