Cross-Schema Network Access

5.16 Cross-Schema Network Access

If database users other than the network owner need to read a network into memory, you need to do one of the following options.

For each non-owner user, qualify the network tables with the schema of the network owner in the USER_SDO_NETWORK_METADATA view, as explained in Cross-Schema Access by Specifying Owner in Network Metadata.
For each non-owner user, create views on the Network Data Model tables and update the USER_SDO_NETWORK_METADATA view, as explained in Cross-Schema Access by Using Views.

The second approach requires the extra step of creating views, but the views provide you with flexibility in controlling the parts of the network that are accessible. Each view can provide access to all of the network, or it can use a WHERE clause to provide access to just one or more parts (for example, WHERE STATE_CODE='NY' to restrict the view users to rows for New York) .

Consider the following example scenario:

User1 creates (and thus owns) Network1.
User2 attempts to call the SDO_NET_MEM.NETWORK_MANAGER.READ_NETWORK procedure to read Network1, but receives an error. The error occurs even though User2 has the appropriate privileges on the Network Data Model tables for Network1.

To work around this problem, you must use the approach in either Cross-Schema Access by Specifying Owner in Network Metadata or Cross-Schema Access by Using Views.

Parent topic: Network Data Model Overview

5.16.1 Cross-Schema Access by Specifying Owner in Network Metadata

To enable a non-owner user (with suitable privileges) to access a network, you can specify the network owner in the network metadata. For each non-owner user that will be permitted to access the network, follow these steps:

Ensure that the user has SELECT or READ privilege access to the necessary Network Data Model tables. If the user does not have this access, connect as the network owner and grant it. For example, connect as User1 and execute the following statements:
```
GRANT select ON network1_node$ TO user2;
GRANT select ON network1_link$ TO user2;
GRANT select ON network1_path$ TO user2;
GRANT select ON network1_plink$ TO user2;
```
Connect as the non-owner user. For example, connect as User2.

Use the schema name of the network owner to qualify the Network Data Model tables for the network in the USER_SDO_NETWORK_METADATA view (explained in xxx_SDO_NETWORK_METADATA Views). For example, if the network is not already defined in this view, enter the following while connected as User2:

INSERT INTO user_sdo_network_metadata 
  (network, network_category, geometry_type, 
   node_table_name,node_geom_column,
   link_table_name, link_geom_column, link_direction,
   path_table_name, path_geom_column,
   path_link_table_name)
VALUES
  ('NETWORK1','SPATIAL', 'SDO_GEOMETRY',
   'USER1.NETWORK1_NODE$', 'GEOMETRY',
   'USER1.NETWORK1_LINK$', 'GEOMETRY', 'DIRECTED',
   'USER1.NETWORK1_PATH$', 'GEOMETRY',
   'USER1.NETWORK1_PLINK$');

If the network is already defined in this view, update the definition to qualify each table name with the schema name. For example:

UPDATE USER_SDO_NETWORK_METADATA
  SET node_table_name = 'USER1.NETWORK1_NODE$',
      link_table_name = 'USER1.NETWORK1_LINK$',
      path_table_name = 'USER1.NETWORK1_PATH$',
      path_link_table_name = 'USER1.NETWORK1_PLINK$'
  WHERE network = 'NETWORK1';

In this scenario, User2 can now read NETWORK1 into memory.

Parent topic: Cross-Schema Network Access

5.16.2 Cross-Schema Access by Using Views

To enable a non-owner user (with suitable privileges) to access a network, or specific parts of a network, you can create views. For each non-owner user that will be permitted to access the network, follow these steps:

Ensure that the user has SELECT or READ privilege access to the necessary Network Data Model tables. If the user does not have this access, connect as the network owner and grant it. For example, connect as User1 and execute the following statements:
```
GRANT select ON network1_node$ TO user2;
GRANT select ON network1_link$ TO user2;
GRANT select ON network1_path$ TO user2;
GRANT select ON network1_plink$ TO user2;
```
Connect as the non-owner user. For example, connect as User2.
Create a view on each of the necessary Network Data Model nodes, with each view selecting all columns in the associated table. Qualify the table name with the schema name of the network owner. For example, while connected as User2:
```
CREATE VIEW network1_node$ AS select * from user1.network1_node$;
CREATE VIEW network1_link$ AS select * from user1.network1_link$;
CREATE VIEW network1_path$ AS select * from user1.network1_path$;
CREATE VIEW network1_plink$ AS select * from user1.network1_plink$;
```
Note:

Although this example shows views that include all data in the underlying tables, you can restrict the parts of the network that are available by using a WHERE clause in each view definition (for example, WHERE STATE_CODE='NY').

Add a row specifying the newly created views to the USER_SDO_NETWORK_METADATA view (explained in xxx_SDO_NETWORK_METADATA Views). For example, while connected as User2:

INSERT INTO user_sdo_network_metadata 
  (network, network_category, geometry_type, 
   node_table_name,node_geom_column,
   link_table_name, link_geom_column, link_direction,
   path_table_name, path_geom_column,
   path_link_table_name)
VALUES
  ('NETWORK1','SPATIAL', 'SDO_GEOMETRY',
   'NETWORK1_NODE$', 'GEOMETRY',
   'NETWORK1_LINK$', 'GEOMETRY', 'DIRECTED',
   'NETWORK1_PATH$', 'GEOMETRY',
   'NETWORK1_PLINK$');

In this scenario, User2 can now read into memory those parts of NETWORK1 that are available through the views that were created.

Parent topic: Cross-Schema Network Access