Hi Listers, Thanks to those who responded to my query regarding sharing Oracle 8i spatial data between different Oracle users - particular thanks to Carlos Montalvillo for his suggested SQL script to modify the Oracle MDSYS view called SDO_INDEX_METADATA. I have attached that example script in lieu of a full summary, because at this stage I regret that I have not had the time to dabble further myself, so I cannot offer a firm alternative. I might add that the other responses I received were from people in similar situations faced with the same question and therefore keen to hear of any conclusive responses I received to my posting. Should anybody at MapInfo or Oracle care to weigh in, you will have an attentive audience! SUMMARY =========== My understanding of the data sharing problem with MI Pro and Oracle 8.1.5 spatial is as follows: - the user (schema) who uploads MI data into Oracle using Easyloader will have full access to edit that data within MI pro, including spatial data - any other Oracle users (separate login schemas) who might be granted all manner of access privileges to the data will NOT be able to edit the SPATIAL attributes of that data. (They MAY be able to edit the DATA attributes, provided they have sufficient access privileges.) - the problem lies not with the data table itself, but with the spatial index tables which Oracle Spatial maintains. This seems to be the domain of the MDSYS Oracle Spatial schema... MDSYS =========== The Oracle schema MDSYS contains an abundance of database objects which are the heart and soul of the spatial capability. Amongst them is a table called SDO_INDEX_METADATA_TABLE which contains details of ALL of the spatial indexes for ALL users/schemas in the database. However this data is made available to individual users selectively, based upon the spatial tables and indexes which exist in their own schemas (ie. each user sees only those spatial indexes for tables which they have loaded themselves.) This filtering is achieved using an Oracle view called SDO_INDEX_METADATA whose contents "changes" according to the Oracle user ID of the user who queries it. The view exists in the MDSYS schema, has public select privileges granted, and also has a public synonym of the same name. Thus ANY Oracle spatial user can query: "select * from sdo_index_metadata" and see a list of all of the details of all of THEIR OWN spatial indexes. This seems to be the root of the problem in that editing another user's spatial data requires associated updates to the spatial indexes, and unless you can "see" those index tables, you are not able to apply the necessary changes. SOLUTION 1 =========== As I mentioned previously, Carlos Montalvillo kindly shared his solution to this problem. Basically this solution consists of a replacement view for MDSYS.SDO_INDEX_METADATA. In this case, the filter conditions in the "where" clause which matched SDO_INDEX_OWNER with the current Oracle user ID have simply been removed. Thus, after replacing the view and regranting privileges and synonyms as indicated, every Oracle user who queries: "select * from sdo_index_metadata" will see a list of all of the details of EVERYBODY'S spatial indexes. >From Carlos' account this would appear to expose sufficient data to the various Oracle and MI processes to allow MI users to share Oracle spatial data and allow full editing of spatial attributes. My immediate concern, not having implemented this solution, is that the exposure of data may be considered excessive by Oracle DBAs (in particular the DBAs associated with the current project I am working on!). The reason this issue has emerged at all is because of the need to implement Oracle security. I also wonder about the other Oracle spatial componentry within MDSYS which may, in fact, depend upon this filtering within the MDSYS view. Even if that is a long shot, making fundamental changes to database objects in MDSYS carries a fair bit more responsibility with it than I would like to shoulder for any commercial system! Carlos' script and instructions follow: Below is a Script that doesn't need any changing to run. Logon to server (Using SQL Plus) and the account MDSYS/MDSYS . Then edit copy / paste the commands To access data in other schemas, you will need to run the following script. Because it replaces an existing public view owned by MDSYS, you will need to have MDSYS access to run it. --Begin script-- DROP VIEW sdo_index_metadata; CREATE VIEW sdo_index_metadata as select SDO_INDEX_OWNER, SDO_INDEX_NAME, SDO_INDEX_TABLE, SDO_INDEX_PRIMARY, SDO_TSNAME, SDO_COLUMN_NAME, SDO_LEVEL, SDO_NUMTILES, SDO_MAXLEVEL, SDO_COMMIT_INTERVAL, SDO_FIXED_META, SDO_TABLESPACE, SDO_INITIAL_EXTENT, SDO_NEXT_EXTENT, SDO_PCTINCREASE, SDO_MIN_EXTENTS, SDO_MAX_EXTENTS from SDO_INDEX_METADATA_TABLE where ( exists (select table_name from all_tables where table_name=sdo_index_table and owner=sdo_index_owner) or exists (select view_name from all_views where view_name=sdo_index_table and owner=sdo_index_owner) or exists (select table_name from all_object_tables where table_name=sdo_index_table and owner=sdo_index_owner)); GRANT SELECT ON sdo_index_metadata TO public; DROP PUBLIC SYNONYM sdo_index_metadata; CREATE PUBLIC SYNONYM sdo_index_metadata FOR mdsys.sdo_index_metadata; --End script-- SOLUTION 2 =========== My preference is for a similar approach to Solution 1, but without changing the objects in MDSYS. Once again, this approach is untested, but I will be pursuing something along the following lines: - grant select on mdsys.sdo_index_metadata_table to public (to allow users to have their own views based upon this table) - for each user who will be accessing the shared spatial data, create a local copy of the view sdo_index_metadata which is the same as the original MDSYS view but with ADDED filter criteria in the where clauses: e.g. "... (select table_name from all_tables where table_name=sdo_index_table and owner=sdo_index_owner and (sdo_index_owner=<CURRENTUSER> OR sdo_index_owner=<DATAOWNER>))..." Under this regime (hopefully) every NOMINATED user (ie. for whom this extra view has been created) who queries: "select * from sdo_index_metadata" will see a list of all of the details of THEIR OWN spatial indexes PLUS the spatial indexes of the DATAOWNER user specified in the view. CONCLUSION =========== No doubt there are other variations on this theme. Provided the underlying principal works, (ie. making users' spatial index details "visible" to others will allow those others to edit the spatial data) any number of solutions may be appropriate for a given deployment. As and when I have more to offer regarding the particular solution to our current requirements, I will post some more information. And in the mean time, if anyone else "cracks it" please put me on the brag list! Cheers, David Jerrard Exa-Min Technologies ---------------------------------------------------------------------- To unsubscribe from this list, send e-mail to [EMAIL PROTECTED] and put "unsubscribe MAPINFO-L" in the message body, or contact [EMAIL PROTECTED]
