6.9.1.4.7 Querying Another User’s Property Graph

You can query another user’s property graph data if you have been granted the appropriate privileges in the database. For example, to query GRAPH1 in SCOTT’s schema, you must have READ privilege on SCOTT.GRAPH1GE$, SCOTT.GRAPH1VT$, SCOTT.GRAPH1GT$, and SCOTT.GRAPH1VD$.

Example 6-22 PgqlExample15.java

PgqlExample15.java shows how another user can query a graph in SCOTT’s schema.

import java.sql.Connection;

import oracle.pg.rdbms.pgql.PgqlConnection;
import oracle.pg.rdbms.pgql.PgqlResultSet;
import oracle.pg.rdbms.pgql.PgqlStatement;

import oracle.ucp.jdbc.PoolDataSourceFactory;
import oracle.ucp.jdbc.PoolDataSource;

/**
 * This example shows how to query a property graph located in another user's
 * schema. READ privilege on GE$, VT$, GT$ and VD$ tables for the other user's
 * property graph are required to avoid ORA-00942: table or view does not exist.
 */
public class PgqlExample15
{

  public static void main(String[] args) throws Exception
  {
    int idx=0;
    String host               = args[idx++]; 
    String port               = args[idx++]; 
    String sid                = args[idx++]; 
    String user               = args[idx++]; 
    String password           = args[idx++];
    String graph              = args[idx++];

    Connection conn = null;
    PgqlStatement ps = null;
    PgqlResultSet rs = null;

    try {

      //Get a jdbc connection
      PoolDataSource  pds = PoolDataSourceFactory.getPoolDataSource();
      pds.setConnectionFactoryClassName("oracle.jdbc.pool.OracleDataSource");
      pds.setURL("jdbc:oracle:thin:@"+host+":"+port +":"+sid);
      pds.setUser(user);
      pds.setPassword(password);     
      conn = pds.getConnection();

      // Get a PGQL connection
      PgqlConnection pgqlConn = PgqlConnection.getConnection(conn);
      pgqlConn.setGraph(graph);

      // Set schema so that we can query Scott's graph
      pgqlConn.setSchema("SCOTT");

      // Create a PgqlStatement
      ps = pgqlConn.createStatement();

      // Execute query to get a ResultSet object
      String pgql = 
        "SELECT v.\"fname\" AS fname, v.\"lname\" AS lname "+
        "FROM MATCH (v)";
      rs = ps.executeQuery(pgql, "");


      // Print query results
      rs.print();
    }
    finally {
      // close the result set
      if (rs != null) {
        rs.close();
      }
      // close the statement
      if (ps != null) {
        ps.close();
      }
      // close the connection
      if (conn != null) {
        conn.close();
      }
    }
  }
}

The following SQL statements create database user USER2 and grant the necessary privileges. You can also use the OraclePropertyGraph.grantAccess Java API to achieve the same effect.

SQL> grant connect, resource, unlimited tablespace to user2 identified by user2;

Grant succeeded.

SQL> grant read on scott.test_graphvt$ to user2;

Grant succeeded.

SQL> grant read on scott.test_graphge$ to user2;

Grant succeeded.

SQL> grant read on scott.test_graphgt$ to user2;

Grant succeeded.

SQL> grant read on scott.test_graphvd$ to user2;

Grant succeeded.

The output for PgqlExample15.java for the test_graph data set when connected to the database as USER2 is as follows. Note that test_graph should have already been loaded (using GraphLoaderExample.java code) as GRAPH1 by user SCOTT before running PgqlExample15.

+---------------+
| FNAME | LNAME |
+---------------+
| Susan | Blue  |
| Bill  | Brown |
| Ray   | Green |
| John  | Black |
+---------------+