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

Article translations Article translations
Article ID: 247828 - View products that this article applies to.
This article was previously published under Q247828
This article has been archived. It is offered "as is" and will no longer be updated.
Expand all | Collapse all

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.

Properties

Article ID: 247828 - Last Review: February 28, 2014 - Revision: 3.2
APPLIES TO
  • 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
Keywords: 
kbnosurvey kbarchive kbprb KB247828

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com