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

SQLException with native SQL and regular DB user

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

Add a comment
10|10000 characters needed characters exceeded

Related questions

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

    • Former Member

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

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.