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


The information in this article applies to:

  • Microsoft Visual InterDev 6.0
  • Microsoft Visual Studio, Professional Edition 6.0
  • Microsoft Visual Studio 6.0 SP3
  • Microsoft Visual Studio, Enterprise Edition 6.0
This article was previously published under Q247828

SYMPTOMS

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.

CAUSE

Database diagrams do not support cross-schema permissions (which Oracle supports).

RESOLUTION

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.

STATUS

This behavior is by design of the Data Tools.

MORE INFORMATION

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:

DT_DROPUSEROBJECTBYID
DT_SETPROPERTYBYID

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.
Modification Type:MinorLast Reviewed:8/15/2005
Keywords:kbprb KB247828
©2004 Microsoft Corporation. All rights reserved.