Article ID: 247828
Article Last Modified on 8/15/2005
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
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.
Additional query words: kbOracle kbvisID600
Keywords: kbprb KB247828
