Privileges - Granted for records created by other users in the same feature class?

[ionarawilson1]

I need to have an application (it could be web editing using ArcGIS or a web application, whatever works) where users of my agency can edit layers of a feature service pulling data from a SDE Enterprise geodatabase. However I need to have the users to be able to see or edit records created by other users  - in the same feature class , but not all users can see everybody's records. So let's say there is user A1, A2,  A3 and A4. A1 can see and edit records created in the feature class "Points" by A2 and A4, but A3 can only edit his own records. A2 can edit and see records of A3 and A1, but not A4. How could this be done? Is there a way to configure security so there are privileges granted for different users for records created by other users in the same feature class, and not only privileges granted only for the specific user's records in the feature class?

Thank you for any help!

[TerryGiles]

SDE privileges are at the table/feature class level, not at the row level.  What you might try doing is using the new editor tracking functionality and multiple services, one for each user & use a definition query in the map layer(s) to filter by user.  Might be a bit of a headache to manage depending on how many users you have though.

[ionarawilson1]

Thank you Terry,  but let me ask you something. If we have all people in the same office being able to view and edit all the other user's records in the same office, would it be possible to create a role (in SQL Server Manager studio) for every office and give the role privileges to alter anything in the database whose field is igual to the name of the office? So we would have only one feature class with a field that would have a field called "Office" and we would have A, B, C, D, etc for each office. So in SQL manager I would create a role called "OfficeA" that allowed all the users in that role to edit records where office = A, so all the users in office A would be assigned that role. Then I would go back to arccatalog, add that role to the dataset and check the privileges for that role allowing it to edit, delete, etc... Would that possibly work?


If not possible and I use a service for each office, would it be possible to define a definito query that cannot be changed? Like, if the user is editing the feature class in ArcGIS Desktop, he could go to the properties, and change the definition query. Is there a way to prevent that? Thanks

[TerryGiles]

Row level permissions are not possible with SDE even when using roles to the best of my knowledge. 

In terms of using different services w/ definition queries & if that def. query can be changed - it depends.  If someone has access to the MXD used to create a service then yes, they could edit or remove the def. query in ArcMap and see/edit all data in SDE.  But if they only have access to the service then no, they cannot alter it. 
 
When you publish a service & enable feature access capabilities you have a couple of ways to edit - you can create a web app and have people edit that way or if they add the feature service to ArcMap & right click on the layer you have the option to copy local (see attached image), make edits, and then push back to the service. Either editing scenario via the the service will honor the definition query.

A workflow I've used for similar situations is this -
- Create a version in SDE for each work location/office
- Add the featureclass(es) of interest to a map and filter with a definition query (office = 123)
- publish the map as a service with feature service capabilities
- users can now edit either via ArcMap or our web app
- when edits are complete the user lets me know & I reconcile & post the data, remove the version, Analyze & Compress the database.

Something similar could work for you, but you'll want to test first to make sure it fits your needs.  A couple of things to remember if you do go this route -
- the connection to the database must be made with a user account that has full edit privileges not just select
- if you're using Server 10.1, you will need to register each version in the database as a data store in you server
- in ArcMap if you use the 'copy local' function by default it goes to a file geodatabase named after the feature service.  You can also pull it to a personal SDE instance but you need to open the Distributed Geodatabase toolbar -> Options to enable this.
- the options you enable/disable in the feature service capabilities carry over to web editing for sure - not 100% what happens if a user copies local/edits in ArcMap and violate any constraints on the feature service (e.g. you disable the Create option in the service but the user makes a new feature in ArcMap).  My guess is 'bad things' happen, you may want to explicitly test this too.
- there are probably other alternatives or workflows that what I've outlined here.

[ionarawilson1]

Hi Terry,

So even if I use procedures to create views in SQL, this is not going to be honored by SDE? So I am going to have users editing the data in ArcGIS Desktop. Can they view the definition query then and change it?
The edits are going to happen constantly, so what do I do in that case? Because you said you create versions and then you remove the version after the users let you know and you reconcile the data. So do you do that in a daily basis, if daily changes are being made? How do you leave the versions available to users if you remove them? Do you have any materials or links to tutorials that I can read about versions, and reconciling, compressing, etc?

Thank you!!!

[TerryGiles · Accepted solution]

I'm 99.99% certain SDE does not support any form of row level security....it may be an option in the underlying RDBMS but not a function of SDE.

Re: can a user change the definition query - again, it depends on if they're using a MXD you created with just contains a layer pointing to SDE w/ a def query (then yes they can edit/remove the def query) or if they're copying data from a Feature Service and editing that in ArcMap (then no).

You don't need to delete versions, it's just how my work flow was set - we had longer periods of editing and when they were done, they were done.  For continual updates, you should look into running reconcile & post on a daily (or more often if needed) basis and do the database analyze, compress, analyze daily to keep the versioning 'state tree' to a minimum... the bigger the tree gets the slower your SDE will perform.  I don't use versioning as much as I used to, but am pretty certain reconcile & post have been combined into one geoprocessing tool (with options on how to handle conflicts) so you could run it in python as a scheduled task.

To get more info on versions, reconciling, etc... start with the online help topic here - http://resources.arcgis.com/en/help/main/10.1/index.html#//003n00000003000000.  There are also some videos, blogs, etc.. on the geodata portion of resources.esri.com.  If you have the time and $$ the multi-day 'Managing editing workflows in a multiuser geodatabase' course is really good too.

TG

[ionarawilson1]

No, they are going to copy from a feature Service, so that's great news! I'll check the link you sent me! I'll take the course if I followed the other route, but apprarently I won't need it. You have been of great help to me ! Thanks a lot!