PRB: Sharing a Centralized Database Diagram in Oracle Using the Data Tools

This article was previously published under Q247828
This article has been archived. It is offered "as is" and will no longer be updated.
When trying to share a centralized database diagram within an Oracle database using different logins, users are unable to see and access the database diagram.

NOTE: A strong knowledge of Oracle is recommended to understand the content of this article.
Database diagrams do not support cross-schema permissions (which Oracle supports).
Most organizations have one login and password that owns the schema for a particular application. That login is secured and only designated people have access. This is not a supported scenario with Visual InterDev.

The workaround is for users that need access to the centralized database diagram to use the schemas owner's user ID and password.
This behavior is by design of the Data Tools.
In Oracle there is a MICROSOFTDTPROPERTIES table for each schema (for example, user name) in the same way that there is one DTPROPERTIES table for each database in Microsoft SQL Server. Since database diagrams do not support cross-schema tables (in other words, you cannot put a Joe.Table1 on a diagram in Mary's schema), there is no way to accomplish a centralized store of diagrams for the whole Oracle database. This is a fundamental difference between SQL Server and Oracle. An Oracle server has one database with multiple schemas, whereas a SQL Server server has multiple databases, each of which can have objects owned by different users.

When using the Data Tools to Oracle besides creating a table called MICROSOFTDTPROPERTIES, two procedures also get created:
And, a function called DT_ADDUSEROBJECT is created.

Creating synonyms to the above table, stored procedures and function and assigning administrative rights does not work. Initially, it works; however, after subsequent access to the centralized database diagram, these permissions must be reset.
kbOracle kbvisID600

Article ID: 247828 - Last Review: 02/28/2014 00:29:44 - Revision: 3.2

Microsoft Visual InterDev 6.0 Standard Edition, Microsoft Visual Studio 6.0 Professional Edition, Microsoft Visual Studio 6.0 Service Pack 3, Microsoft Visual Studio 6.0 Enterprise Edition

  • kbnosurvey kbarchive kbprb KB247828