2.6 Cross-Schema Map Requests

A database user can issue a map request specifying a theme that uses data associated with another database user, to select data from tables that the other data source user is authorized to access.

For example, assume that user SCOTT wants to issue a map request using data associated with user MVDEMO. In general, user SCOTT must be granted SELECT access on relevant tables owned by user MVDEMO, and the <theme> element should generally specify any tables in schema-name.table-name format. In this example scenario:

• For a geometry table, grant the SELECT privilege on the geometry table of MVDEMO to SCOTT (see Example 2-53).

• For a GeoRaster table, grant the SELECT privilege on the GeoRaster table and raster data table or tables of MVDEMO to SCOTT (see Example 2-54).

• For a topology data model table, grant the SELECT privilege on the topology table, topology column index table, and related topology information tables (topology-name_EDGE$, topology-name_NODE$, topology-name_FACE$, topology-name_RELATION$) of MVDEMO to SCOTT (see Example 2-55).

• For network data model tables, grant the SELECT privilege on the network link, node, path, and path-link tables of MVDEMO to SCOTT (see Example 2-56).

Example 2-53 shows a dynamic theme that accesses the MVDEMO.STATES geometry table from a data source defined on the SCOTT user.

Example 2-54 shows a dynamic theme that accesses the MVDEMO.GEORASTER_TABLE GeoRaster table and its RDT from a data source defined on the SCOTT user. Specify the base (GeoRaster) table in schema-name.table-name format.

Example 2-55 shows a dynamic theme that accesses the MVDEMO.LAND_PARCELS topology table and information tables for the CITY_DATA topology from a data source defined on the SCOTT user. Specify the feature table and the topology in schema-name.object-name format, if they are owned by a different schema than the one associated with the data source.

In Example 2-55, you must grant SELECT on the topology column index table name (<topology-column-index-table-name>) because the spatial index table associated with the feature table topology column is used by the map visualization component in topology queries. You can determine the topology column index table name as follows. Assume the following information:

• Topology feature table owner: MVDEMO

• Topology feature table name: LAND_PARCELS

• Topology feature table topology column name: FEATURE

The following query returns the index table name (in this example, MDTP_14E60$):

SQL> select sdo_index_table from all_sdo_index_info
     where table_owner = 'MVDEMO'
     and table_name = 'LAND_PARCELS'
     and column_name = 'FEATURE'
 
SDO_INDEX_TABLE
--------------------------------
MDTP_14E60$

Then, modify the last GRANT statement in Example 2-55 to specify the <topology-column-index-table-name>. In this case:

SQL> grant select on MDTP_14E60$ to SCOTT;

Example 2-56 shows a dynamic theme that accesses the MVDEMO.BI_TEST network and its link, node, path, and path-link tables. Specify the network name in schema-name.network-name format.

Example 2-53 Cross-Schema Access: Geometry Table

SQL> grant select on STATES to SCOTT;
. . .
<themes>
  <theme name="theme1">
     <jdbc_query
       datasource="scottds"
       spatial_column="geom"
       render_style="MVDEMO:C.COUNTIES"
       jdbc_srid="8265"
       >SELECT geom from MVDEMO.STATES</jdbc_query>
   </theme>
</themes>

Example 2-54 Cross-Schema Access: GeoRaster Table

SQL> grant select on GEORASTER_TABLE to SCOTT;
SQL> grant select on RDT_GEOR1 to SCOTT;
. . .
<themes>
  <theme name="georaster_theme">
    <jdbc_georaster_query
       georaster_table="MVDEMO.georaster_table"
       georaster_column="georaster"
       raster_table="rdt_geor1"
       raster_id="1"
       jdbc_srid="8307"
       datasource="scottds"
       asis="false">
     </jdbc_georaster_query>
  </theme>
</themes>

Example 2-55 Cross-Schema Access: Topology Feature Table

SQL> grant select on CITY_DATA_FACE$ to SCOTT;
SQL> grant select on CITY_DATA_EDGE$ to SCOTT;
SQL> grant select on CITY_DATA_NODE$ to SCOTT;
SQL> grant select on CITY_DATA_RELATION$ to SCOTT;
SQL> grant select on LAND_PARCELS to SCOTT;
SQL> grant select on <topology-column-index-table-name> to SCOTT;
. . .
<themes>
  <theme name="topo_theme" >
     <jdbc_topology_query
       topology_name="MVDEMO.CITY_DATA"
       feature_table="MVDEMO.LAND_PARCELS"
       spatial_column="FEATURE"
       render_style="MVDEMO:C.COUNTIES"
       jdbc_srid="0"
       datasource="scottds"
       asis="false">select feature from MVDEMO.land_parcels
     </jdbc_topology_query>
   </theme>
</themes>

Example 2-56 Cross-Schema Access: Network Tables

SQL> grant select on BI_TEST_LINK$ to SCOTT;
SQL> grant select on BI_TEST_NODE$ to SCOTT;
SQL> grant select on BI_TEST_PATH$ to SCOTT;
SQL> grant select on BI_TEST_PLINK$ to SCOTT;
. . .
<themes>
  <theme name="net_theme" >
     <jdbc_network_query
       network_name="MVDEMO.BI_TEST"
       network_level="1"
       jdbc_srid="0"
       datasource="scottds"
       link_style="MVDEMO:C.RED"
       node_style="MVDEMO:M.CIRCLE"
       node_markersize="5"
       asis="false">
     </jdbc_network_query>
   </theme>
</themes>