topic Re: Provide code-level to attribute fields in a SDE geodatabase. in Data Management Questions

Provide code-level to attribute fields in a SDE geodatabase.

El_BoukfaouiReda — Mon, 23 Dec 2013 06:47:26 GMT

Dear,

I have an SDE geodatabase and I want to introduce code-level access to the attribute fields in each feature class restricting the attributes to the following group of users :
- Public.
- Private(Internal).

Is there any way of proceeding using ArcGIS technology products? Is it handled depending through the RDBMS ?

Please help,

Thank you !

Re: Provide code-level to attribute fields in a SDE geodatabase.

VinceAngelo — Mon, 23 Dec 2013 12:10:32 GMT

How do you define "code-level access"? I've never seen that term before.

What RDBMS are you using? ArcSDE doesn't have anything to do with
security models -- Those are provided by the RDBMS.

- V

Re: Provide code-level to attribute fields in a SDE geodatabase.

MarcoBoeringa — Mon, 23 Dec 2013 12:19:15 GMT

How do you define "code-level access"? I've never seen that term before.

I think he may be referring to Oracle Virtual Private Database functionality, as that seems to be doing what he describes:

"Oracle Virtual Private Database (VPD) enables you to create security policies to control database access at the row and column level."

Re: Provide code-level to attribute fields in a SDE geodatabase.

El_BoukfaouiReda — Tue, 24 Dec 2013 04:22:04 GMT

How do you define "code-level access"? I've never seen that term before.

I haven't been precise saying this, it is true but it is concerning the database access policies and administration.

I will formulate the problem otherwise, then :
''I do have an SDE geodatabase with feature datasets and feature classes.
I need to configure the access for each database user, to the database items(feature datasets, feature classes, attribute fields).

For the code-level access, I supposed that there is a code provided to each attribute field or each group of users(public, private...). The same code would be affected to each field attribute/feature class/feature dataset which could grant or refuse the access to the data within the database...

Is there anyway to configure this using ArcGIS or MS SQL Server 2008 R2 (as my database is implemented in it) ?

What RDBMS are you using? ArcSDE doesn't have anything to do with security models -- Those are provided by the RDBMS.

I am using MS SQL Server 2008 R2.
Do I need to implement a security model to manage the access of the each database user to the feature classes (respectively the attribute fields) of each feature class/feature dataset ?

I don't think that SDE permissions are that granular, perhaps you will need to split those private attributes into a separate table and use a primary/foreign key relationship. Then you can use a spatial join that combines the private attributes with the feature class and only grant access to private (internal) users. If the spatial view does not handle the one to many relationship well, you can add an extra step where you export the spatial view to a feature class and automate that process.

I already generated a complete GIS data model - relationship classes, domains and subtypes included - previously.
Could you please describe deeply and in a procedural way(steps) what you just explained before please?
N.B: I am using MS SQL Server 2008 R2 as a RDBMS.

Thank you!

Re: Provide code-level to attribute fields in a SDE geodatabase.

VinceAngelo — Tue, 24 Dec 2013 15:39:41 GMT

ArcSDE permissions aren't that granular, and database extensions that permit row/column level
security are not supported by Esri (if you can make it work, congrats, but Tech Support can't help
you if it doesn't work). You'd need to pay careful attention to the extra tables created by ArcSDE
for versioning, archiving, etc, since you couldn't use ArcGIS to grant the permissions. Adding
relationship classes, domains, etc into the equation increases the level of difficulty by an order of
magnitude. I certainly wouldn't want to attempt this, and I've been working with ArcSDE since
version 1.3 and SQL-Server since 6.0.

Using multiple tables and views to join them is a less fearsome approach, but it's unlikely you'll
find someone to donate days/weeks of time to give you detailed instructions on how to proceed.
The best you can hope for is someone having published a paper on how they did something
similar, then using that as a roadmap.

- V

Re: Provide code-level to attribute fields in a SDE geodatabase.

El_BoukfaouiReda — Sun, 02 Feb 2014 06:54:45 GMT

ArcSDE permissions aren't that granular, and database extensions that permit row/column level
security are not supported by Esri (if you can make it work, congrats, but Tech Support can't help
you if it doesn't work). You'd need to pay careful attention to the extra tables created by ArcSDE
for versioning, archiving, etc, since you couldn't use ArcGIS to grant the permissions. Adding
relationship classes, domains, etc into the equation increases the level of difficulty by an order of
magnitude. I certainly wouldn't want to attempt this, and I've been working with ArcSDE since
version 1.3 and SQL-Server since 6.0.

Using multiple tables and views to join them is a less fearsome approach, but it's unlikely you'll
find someone to donate days/weeks of time to give you detailed instructions on how to proceed.
The best you can hope for is someone having published a paper on how they did something
similar, then using that as a roadmap.

- V

If it is not supported by Esri, what then would be the usual procedure(steps) to follow to get this done?
I mean to forbid the access for an SQL Server 2008 R2 Database user, to an attribute field table, which could contain sensitive information?

Thank you.

Re: Provide code-level to attribute fields in a SDE geodatabase.

MarcoBoeringa — Sun, 02 Feb 2014 09:42:09 GMT

If it is not supported by Esri, what then would be the usual procedure(steps) to follow to get this done?
I mean to forbid the access for an SQL Server 2008 R2 Database user, to an attribute field table, which could contain sensitive information?

Thank you.

Vince already stressed this, you can't set permissions / privileges at the level of individual attribute fields of a Feature Class in an ESRI Geodatabase, you can set permissions / privileges (read or deny) for individual users to an entire Feature Class, including all its fields.

To set permissions on a Feature Class, read the Help:
What are user privileges?

and especially:
Granting and revoking privileges on datasets

Re: Provide code-level to attribute fields in a SDE geodatabase.

VinceAngelo — Sun, 02 Feb 2014 13:51:00 GMT

I wrote that you can't use ArcGIS tools to accomplish this. If the database
supports this functionality, then a forum of the database in use would be a
better place to get help. You may need a great deal of geodatabase knowledge
to avoid breaking ArcGIS functionality.

- V

Re: Provide code-level to attribute fields in a SDE geodatabase.

MarcoBoeringa — Sun, 02 Feb 2014 15:22:36 GMT

I wrote that you can't use ArcGIS tools to accomplish this.

Correct, I didn't mean to apply otherwise, but I assumed the OP primarily wished to use the "ArcGIS Tools"...

If the database supports this functionality, then a forum of the database in use would be a
better place to get help. You may need a great deal of geodatabase knowledge to avoid breaking ArcGIS functionality.

Agree, trying to use domains and subtypes on "restricted" fields, is probably a scenario for a big headache... just to mention one...

I think the solution Sol originally proposed, and you also mentioned, by setting up dedicated joins for fields with restrictions, and spatial views at the database side who's access can be managed by the RDBMS, and using Query Layers to access them, is the closest thing to a realistic solution of this.

Re: Provide code-level to attribute fields in a SDE geodatabase.

Anonymous User — Tue, 23 Sep 2014 19:55:06 GMT

Do you have access either to ArcGIS Server or ArcGIS Online? If your need is to hide some attributes/fields, but show geometry, it can be accomplished using web-based tools. Otherwise, as noted by others, you invite complex and unsupported database behaviors and/or Desktop customization with ArcObjects.