3

How can you open a featureclass if you are not the schema owner but have been given privs to select or view the data?

So given a name of a featureclass, say "STREETS" - how can I open this if I am using an SDE connection with username "VIEWER" (given privs to see "EDITOR" owned data) but the owner of "STREETS" is "EDITOR"? - using the following code:

IFeatureWorkspace.OpenFeatureClass(string name)

This will work if you are the owner, you just put:

IFeatureWorkspace.OpenFeatureClass("STREETS") 

But if you are not the owner then you have to fully qualify it, a la:

IFeatureWorkspace.OpenFeatureClass("EDITOR.STREETS")

Is there anyway round this? Bearing in mind my users will not have access to an sde connection for the data owner.

  • 2
    This is how databases work. Objects are owned by users, and you need to prefix the table with owner to successfully reference it. – Vince Aug 19 '14 at 15:36
  • But you can't have everyone accessing the database with schema owner credentials or sde connection with those credentials. – Vidar Aug 19 '14 at 15:38
  • 1
    Best practice is to create roles, grant access to tables to roles, and grant users membership in roles. Then users connect as themselves and access other-owned data with appropriate permission. – Vince Aug 19 '14 at 15:49
  • 1
    Are you working under the assumption that an "owner.table" reference utilizes the "owner" connection ? It doesn't; instead it uses the RDBMS security model to locate the table owned by that user in the RDBMS catalog, based on the privileges granted to the connected user. – Vince Aug 19 '14 at 16:48
  • 2
    You must specify an owner if the connection user is not the owner. In fact, it's best practice to always specify the owner when programmatically accessing tables. – Vince Aug 20 '14 at 12:05
2

Relational database software generally permits multiple tables with the same name if they are:

  • Owned by different users, or
  • Placed in different databases (for RDBMSes which support multiple containers within an instance)

The usual way to distinguish which table is being referenced is by prefixing it with the owner name (and a period separator). The default behavior is to assume that a table without an owner prefix is owned by the current user, which is why you need to specify the owner of an other-owned table. Of course, security rules may prevent you from accessing tables owned by other users, so a "table does not exist" error may really be saying, "the table may exist, but if it does, you don't have SELECT access on it, so I'm not going to tell you about it."

Some databases (Oracle is one) support "public synonyms," which override the default naming by checking the list of synonyms before prepending the connected username. However, ArcGIS does not support synonyms, so this will not be part of an ArcObjects solution.

It's best practice to always specify a table owner when scripting. ArcObjects provides helper functions to parse and qualify table and field references in an RDBMS-independent fashion. It's also advisable to avoid use of a data owner connection when testing, partially to detect incomplete table and field references, and partially to prevent data corruption during testing (a self-inflicted denial of service attack).

Recommended use of the SQL security model involves creating logins for each individual who will connect to the database, "data owner" users to manage spatial datasets (the password(s) for which is closely held by the geodatabase administrator(s)), and database roles for each type of access to be granted to the users. If there is any unauthenticated access (as in a web application), then there should also be a user created for this as well (with a bare minimum of permissions). Then the necessary access to data tables can be granted to roles, and the roles granted to users (in a geodatabase, the GRANTs should be performed with ArcGIS tools, so ancillary tables all get the correct permissions). In this way, you can prevent situations where the "Bill" user owns core business data even though Bill moved on to a different job three years earlier. It takes a bit more work up front, but it makes for much less difficulty in maintenance later.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.