Skip to Content
author's profile photo Former Member
Former Member

SQLException with native SQL and regular DB user

Posted on Jun 03, 2004 at 08:44 AM 7 Views

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

SAP NetWeaver Application Server
Add comment
10|10000 characters needed characters exceeded

4 Answers

  • author's profile photo Former Member
    Former Member
    Posted on Jun 04, 2004 at 02:22 PM

    Hey, Martin.

    Can you post the full stack trace here?

    Thanks.

    -Yuri

    Add comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Jun 05, 2004 at 07:47 PM

    I'll do that next Friday.

    Regards

    Martin

    Add comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Jun 11, 2004 at 08:00 AM

    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

    #

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member
      Jun 11, 2004 at 03:34 PM

      Hey Martin.

      If you haven't figured it out yet, here is the excerpt from an Oracle docs:

      "Connecting with Administrative Privileges: Example

      This example illustrates that a user is assigned another schema (SYS) when connecting with the SYSDBA system privilege.

      Assume that user scott has issued the following statements:

      CONNECT scott/password

      CREATE TABLE admin_test(name VARCHAR2(20));

      Later, scott issues these statements:

      CONNECT scott/password AS SYSDBA

      SELECT * FROM admin_test;

      User scott now receives the following error:

      ORA-00942: table or view does not exist

      This is because scott now references the SYS schema by default. The table was created in the scott schema.

      "

      So, I think, if you create a global synonym for your table you should be able to see it.

      create public synonym yourtable for yourtable;

      Let me know if it doesn't work.

      -Yuri

  • author's profile photo Former Member
    Former Member
    Posted on Jun 11, 2004 at 03:50 PM

    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 comment
    10|10000 characters needed characters exceeded