This Client Server article is republished in its entirety from 2002 for reference purposes. It was written for GeoGraphics v07.02.04.19 and may not apply to any other version.
By Mark Allain Group Leader, GeoCertification 30 April 2002
Bentley's MicroStation GeoGraphics iSpatial Edition (version 07.02.04.19, for Windows NT and Windows 2000) is capable of storing both attribute data and spatial geometry in an Oracle 8i database. In order to facilitate project sharing that includes access to geometry, some additional functionality has been added to this version of MicroStation GeoGraphics. The process of properly sharing a schema is comprised of two main parts:
The Project Administrator activates sharing of the project The user who will access the shared project configures the MicroStation GeoGraphics workspace
In order to share a spatial project, the project administrator (the schema owner for the project's tables) must tell the database to ‘share' the geometry tables.
Go into Project Setup and select Spatial > Sharing. A prompt asks if you wish to set the schema's tables to Public. Select ‘Yes.' MicroStation GeoGraphics will return the dialog shown in Figure 3 if the operation is successful. To better understand what has happened in the database, look at the Oracle data dictionary view, USER_TAB_PRIVS. This view contains information on grants on objects for which the user is the owner, grantor, or grantee.
For this example, work with the delivered sample project of Mytown_Spatial and Oracle user MYTOWN. If you wish to use these queries against your own project, please make the necessary changes to your SQL as required.
Log into Oracle's SQL Plus or SQL Worksheet as the Project Administrator. If you enter the SQL statement below to query USER_TAB_PRIVS, you will see all the object privileges that have been granted to PUBLIC.
column grantee format a10column owner format a10column table_name format a30column privilege format a10
select grantee, owner, table_name, privilege fromuser_tab_privs where grantor = 'MYTOWN'order by table_name;
Notice that the required object privileges for all objects in the Project Administrator's schema have been granted to PUBLIC. This means these objects are now accessible to any Oracle user in the database. To revoke these privileges:
Go into Project Setup and select Spatial > Sharing You will be prompted to set the schema's tables to Public; Select ‘No' MicroStation GeoGraphics will return the dialog shown in Figure 4 if the operation was successful Now, run the query of USER_TAB_PRIVS again. You will see that the object privileges no longer exist.
The user who will access the shared project must set the variable MS_GEOPROJADMIN = . Using the same example, where user MYTOWN shared the project, the user who intends to access the shared schema would set this variable to: MS_GEOPROJADMIN = MYTOWN.
This change can be made directly in a configuration file, such as geograph.ucf, or through the Workspace > Configuration dialog from within MicroStation GeoGraphics. This variable must be created, it will not already exist.
Follow along to see what happens when a user connects to a shared project and has this variable set to a valid shared project. We already know that privileges for the objects in the Project Administrator's schema have been granted. However, at this point these objects can only be accessed from another schema when you preface the object name with the schema owner's name. For example, the table UGLAYER in the shared MYTOWN schema can only be selected as MYTOWN.UGLAYER from other schemas. Since MicroStation GeoGraphics needs to see these tables without a schema preface, you will have to create synonyms for these tables.
When users who have defined MS_GEOPROJADMIN login to their projects, synonyms are created for all the objects they have privileges for but are owned by their Project Administrator. You can verify the existence of these synonyms by querying the USER_SYNONYMS data dictionary table. The query below (again using the MYTOWN example) shows the new synonym name in comparison to how the object had to be referenced before the synonym was assigned.
column synonym_name format a25column NAME_B4_SYNONYM format a40
select synonym_name, table_owner || '.' ||table_name NAME_B4_SYNONYM from user_synonyms where table_owner = 'MYTOWN';Synonyms are created for all objects in the Project Administrator's schema, as long as the synonyms (or objects) do not already exist.
Finally, a new Oracle metadata view (USER_SDO_GEOM_METADATA) is established by first dropping any existing metadata view. Copying the up-to-date metadata information for the Project Administrator's schema creates a new metadata view.
If you are comfortable working within the Oracle server environment, you can set up your project sharing to a specific user. The script below mimics the MicroStation GeoGraphics sharing functionality, but grants privileges to only a specified user rather than to PUBLIC. Be certain that you fully understand the script below before running it on your database. REM grant_all_schema.sqlREM Bentley Systems 10/8/2001REM REM run in the schema of the spatial project ownerREM substitute the USERNAME you wish togrant privileges to for GRANTEE REMREM to REVOKE these granted privileges editthe script to read REVOKE instead of GRANTREM and edit the script to read FROM granteerather than TO granteeREM run the edited script in the schema of thespatial project ownerSpool privs.sql set echo off select 'Grant Select, Update, Delete, Insert on'||Object_Name||' to GRANTEE; ' from User_Objects Where Object_Type in ('TABLE','VIEW'); select 'Grant Execute on '||Object_Name||' toGRANTEE; ' from User_Objects Where Object_Type in ('TYPE'); select 'Grant Alter, Select on '||Object_Name||' toGRANTEE; ' from User_Objects Where Object_Type in ('SEQUENCE'); Spool off @privs.sql
One last step must be taken to replicate the Project Sharing implemented by MicroStation GeoGraphics. To ensure security, revoke Insert, Update and Delete from the MicroStation GeoGraphics system tables. These were granted in the grant_all_schema.sql script above in order to greatly simplify the script. You can make these additional adjustments with the SQL below, again substituting the actual user name in place of GRANTEE. REM revoke_ggsys_privs.sqlREM Bentley Systems 10/8/2001REM REM run in the schema of the spatial project ownerREM revokes previously granted privileges onGeoGraphics system table
revoke insert, update, delete on category from GRANTEE;revoke insert, update, delete on feature from GRANTEE;revoke insert, update, delete on maps from GRANTEE;revoke insert, update, delete on mscatalog from GRANTEE ;revoke insert, update, delete on ugcategory from GRANTEE;revoke insert, update, delete on ugcommand from GRANTEE;revoke insert, update, delete on ugfeature from GRANTEE;revoke insert, update, delete on ugjoin_cat from GRANTEE;revoke insert, update, delete on ugmap from GRANTEE;revoke insert, update, delete on ugtable_cat from GRANTEE;
After setting up these privileges, you only need to set the MS_GEOPROJADMIN variable for the user accessing the shared schema. MicroStation GeoGraphics will then create the required synonyms at project login and update your Oracle Spatial metadata to the Project Administrator's metadata.
Client Server Archive
Geospatial Product TechNotes And FAQs
Bentley's Technical Support Group requests that you please confine any comments you have on this Wiki entry to this "Comments or Corrections?" section. THANK YOU!