3 Replies Latest reply on Mar 5, 2010 2:56 PM by alwu-Oracle

    Access to models not owned by a user

    758021
      Hello all,

      We were ready to deploy a semantic project with Oracle 11.2 and Java (Jena Adaptor). But we have come across a problem. The client (a gov administration) expects users in the db managed this way:

      User SYS: DBA user who creates the semantic network.
      User APP_ADMIN: create models (and correspondent tables) for the application.
      User APP_FUNC: select, inser, update, detele rdf triples on the application models. All grants conceded over mdsys.rdfm_model, mdsys.semm_model and even the model table owned by APP_ADMIN.

      The APP_FUNC user can read correctly the models. But all write operations resut in the next exception:
      ------
      SLF4J: The requested version 1.5.8 by your slf4j binding is not compatible with [1.5.5, 1.5.6]
      SLF4J: See http://www.slf4j.org/codes.html#version_mismatch for further details.
      813 [main] WARN oracle.spatial.rdf.client.jena.GraphOracleSem - Could not perform query
      java.sql.SQLException: ORA-00942: la tabla o vista no existe

           at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:439)
           at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:395)
           at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:802)
           at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:436)
           at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:186)
           at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:521)
           at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:205)
           at oracle.jdbc.driver.T4CPreparedStatement.executeForRows(T4CPreparedStatement.java:1008)
           at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1307)
           at oracle.jdbc.driver.OraclePreparedStatement.sendBatch(OraclePreparedStatement.java:3753)
           at oracle.jdbc.driver.OraclePreparedStatementWrapper.sendBatch(OraclePreparedStatementWrapper.java:1140)
           at oracle.spatial.rdf.client.jena.GraphOracleSem.flushAdd(GraphOracleSem.java:1400)
           at oracle.spatial.rdf.client.jena.GraphOracleSem.graphBaseFind(GraphOracleSem.java:2427)
           at oracle.spatial.rdf.client.jena.GraphOracleSem.graphBaseFind(GraphOracleSem.java:2297)
           at oracle.spatial.rdf.client.jena.GraphOracleSem.graphBaseFind(GraphOracleSem.java:2164)
           at com.hp.hpl.jena.graph.impl.GraphBase.find(GraphBase.java:240)
           at org.fundacionctic.ogd.data.support.OracleSupport.testInsertarDatos(OracleSupport.java:56)
           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:597)
           at junit.framework.TestCase.runTest(TestCase.java:154)
           at junit.framework.TestCase.runBare(TestCase.java:127)
           at junit.framework.TestResult$1.protect(TestResult.java:106)
           at junit.framework.TestResult.runProtected(TestResult.java:124)
           at junit.framework.TestResult.run(TestResult.java:109)
           at junit.framework.TestCase.run(TestCase.java:118)
           at org.eclipse.jdt.internal.junit.runner.junit3.JUnit3TestReference.run(JUnit3TestReference.java:130)
           at org.eclipse.jdt.internal.junit.runner.TestExecution.run(TestExecution.java:38)
           at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:460)
           at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:673)
           at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:386)
           at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:196)
      ------

      Seem like the user cant 'see' the tablesor views. Even when the APP_FUNC user is given DBA role, the exception is thrown. Actually we cant think of any solution. So far we had not had this problem because we used the same user to create the model and do all operations on them.

      So the question is.. Only the owner of a model can make operations on it through the jena adaptor? Is there any way to change that? And if not... Is there any kind of official documentation on this issue so we can justify this change in the administration?

      Hoping need, thanks.
        • 1. Re: Access to models not owned by a user
          alwu-Oracle
          Hi,

          Let me know if this helps.

          From SQLPlus, login as APP_FUNC, and then create synonyms for application tables owned by APP_ADMIN.

          For example, assume APP_ADMIN owns a semantic model with name "MODEL1" and you want APP_FUNC to
          write into it (through its application table actually).

          SQLPLUS> connect APP_FUNC/<password>
          SQLPLUS> create synonym MODEL1_tpl for APP_ADMIN.MODEL1_tpl;

          Note that, you still need all the grants you have done. The suffix "_tpl" is the default value Jena Adaptor uses
          to create an application table name.

          Obviously, you can do the synonym creations using JDBC calls.

          Thanks,

          Zhe Wu
          • 2. Re: Access to models not owned by a user
            758021
            Thank you very much Alwu. It worked !!

            Was that so simple? I mean, we are not experts as oracle administrators, and maybe we overlooked something basic about this issue?

            I think that user management we have is quite common, and perhaps this use case should be mentioned somewhere in the semantic documentation.

            Thanks again.
            • 3. Re: Access to models not owned by a user
              alwu-Oracle
              Hi,

              Could you please share some information about the application you are deploying?
              You can email me using alan dot wu at oracle dot com. Just some high level
              descriptions will do.

              Thanks,

              Zhe Wu