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 | +---------------+