Misunderstandings in ArcSDE Users/Permissions

[SebastianKrings]

Hello,

I fail in getting started with user permissions on ArcSDE for SQL Server.
Everything is installed on a Windows Server 2008 R2, using a SDE Service.

Here some troubles:

1)

http://help.arcgis.com/en/arcgisdesktop/10.0/help/index.html#/A_quick_tour_of_adding_and_administeri...
"When you add users, you also assign them a default schema in the database. If the user is going to own data in the geodatabase, the default schema must have the same name as the user name. If it does not, the user cannot create datasets, copy datasets into the geodatabase, or use geoprocessing tools that result in new datasets."

Well I have a Windows User which is also in SQL als Login and assigned to a database. Hes referencing sde as default schema. When I connect in ArcCatalog to the SDE and want to try to create a new dataset, it succeeded. I do not understand why. It doesnt matter which schema he owns and this happens with still all windowsusers, who get access to the specific database. The quote above says "must have the same name". Well as I can see, it do not have to. Why is it this way?

2)
I access the data base through ArcCatalog with a User, which has a default schema with the same name. Now I am able to own data. I create a new dataset and there I try to create a feature class. I will get the following error:

Failed to create feature class.
Database user name and current user schema do not match.
[server.feature class]
The geometric network was not found.

Well concerning this error I think it should not matter what privilegs this user have on the database. The error is complaining about the schema which is assigned as default schema and both have the same name.
I still added the user some db_roles with no difference, I granted privilegs on the dataset in ArcCatalog with no differences.

3)
When granting rights on a dataset in ArcCatalog where are these privilegs stored? On database, where? The database user is not affected this way or? I cannot see any changes in security on the database when granting or revoking some rights. I also cannot see any changes in ArcCatalog. After I granted full access, I still am not able to create a feature class or edit an existing one.

4)
Using an Windows account in the database, the User is named as "instance\user".
When adding a default schema which shall be named like the user, is it right to call the schema also "instance\user" or just "user"? As it doesnt matter if theres an equal default schema in creating a dataset and the message that they were not equal always appears when creating something else I also cannot see any difference.

5)

http://help.arcgis.com/en/arcgisdesktop/10.0/help/index.html#/A_quick_tour_of_adding_and_administeri...
SQL Server contains fixed server and database roles that you can use. These roles have a predefined set of permissions in the SQL Server instance and databases. Alternatively, you can create your own roles and set permissions on them.

http://help.arcgis.com/en/arcgisdesktop/10.0/help/index.html#/User_permissions_for_geodatabases_in_S...
Minimum single spatial database model permissions (table below)

When I read those texts I still do not really now which roles or permissions to give to a user to grant him the different types of GIS users (DataViewer, DataCreator, DataEditor, ArcSDEAdministrator). It is also cofusing to know that you can grant permissions in ArcCatalog not knowing if they are equal to database grants or if theres a difference and how they will work together.
The users have Logins in the SQL Instance, they have users at the databases they shall have access to and they have a default schema. On all three points I am able to grant roles, permissions etc. I am looking for a best practice concerning to ArcSDE.
I could Imagin to create a base_role for each of the GIS user types. Then adding windows or database users to the databases and assign them the specific roles to manage the access to data and the geodatabase.

6)
Concerning to #5 and to the fact everyone can create datasets in ArcCatalog I am looking for a way to get full access as admin to all data and datasets in ArcCatalog. If anyone creates a dataset but I dont want him to do this, I still cannot delete it on my own because an error tells me I am not the owner of this dataset. So only the person who created this is able to delete. Thats not the administrative way I am looking for.


Well I have some more points, but I do not want to overload. Maybe some things will solve on their own by ansering my questions above.
I hope you can help me and bring some light in.

Thanks very much for helping here.

[SebastianKrings]

hello,

the mystery is continuing.
I found out why everyone is able to create a dataset (but this still does not answer why it is this like). After creating a new geodatabase by postinstallation the public role of the sql database is permittet (insert, update, delete, execute) on all tables starting with "GDB_...". So it seems that the SDE Postinstallation automatically grants all these permissions to the public role of the geodatabase. In my opinion this is a big mistake. Also it diverges to the quota of #1 in my first post.
Why is it this way, what can I do against this (except revoking those permissions per table per database)?

And I have a further point to discuss.

7)
I want to use Windows Groups instead of single Users to acces the Database. I created three roles (dataViewer, dataEditor, dataCreator) in the geodatabase with the specific permissions they need. Then I created also three windows groups and added some users. Users participating in editors or creators group also participate in the viewer group to get the select permissions. In the SQL instance I add the groups as logins and assign each group the specific database role.
Then I start ArcCatalog from another mashine and connect to the database. A User only being in the Viewer group only is able to view (select) contents (and of course create datasets as mentioned above). A User beeing in the creators group (and also on viewer group) is able to create datasets and feature classes (but just within datasets they own). Now automatically they were granted to edit their data (update, delete, insert). Well it could make sense that users who creates data are also permitted to edit it but theres nothing in esri documentation telling this. The overview of right for the dataCreator implies (in my mind) that the creator will only be able to create data but not to edit it. Also also I cannot create data in datasets of other users because I am not the owner. Well I thought a creator is able to create everything and everywhere.... (I know that in ArcCatalog I can change permissions but this way is extremly uncomfortable for many users)
The last user, the editor, should just edit Data. But hes also allowed to create data like the dataCreator. After I did this I can see in the Database that a new User is added to the database (of the logged in user, not the group) and a schema has also been added. Why is SDE doing this automatically and why is it doing this anyhow? I cannot find this workflow in esri documention. Due to this the dataEditor and the dataCreator have still the same permissions. Thats not normal!


I really hope that someone (also someone from esri) can explain me everything or most of my questions. The SDE behavior is very confusing me.

Thanks a lot.

[SebastianKrings]

I found this today (oracle):
http://support.esri.com/en/knowledgebase/techarticles/detail/35408

a quick search found this (oracle):
http://help.arcgis.com/en/arcgisdesktop/10.0/help/index.html#//002n0000002v000000

but its different from this (SQL Server):
http://help.arcgis.com/en/arcgisdesktop/10.0/help/index.html#/User_permissions_for_geodatabases_in_S...

A further hint was given here (in the last step the right were revoked):
http://oracledbatips.com/arcsde/Upgrading-an-ArcSDE-geodatabase-from-91-to-93.html

Did ESRI forgot something in the documentation of SQL Server or is the SDE behavior in granting  public access by SDE?
Its told that SDE needs rights back when revoking from public. But what are these rights needed for? For further upgrades? Or for operating?
Were it enough to revoke all non-select-privilegs from public and assing them to the sde-user?
Maybe theres a complete script available doing this? I could imagine that many users would assign it.


If youre all afraid of anwering my posts (because I have too much questions at once) you also can just discuss only one or two points of my seven. Any help would be grateful.
Thanks.

[RobertHu]

I agree with you. You posted too many questions at once, and many of them are too long. Those who are capable to provide helps may not want to write a book to answer your questions. Others may not even have patience to go through the questions. Make it short and specific, easier to test and isolate the problem, you'll have better chance to get answers. It is just my opinion.

By the way, I checked my SDE GDBs on both my dev and production boxes. The "public" DB role only has permission on sde owned objects, as well as some sys objects. I used the sde schema while creating all my GDBs. So, I don't think the SDE postinstallation is flawed, at least when using sde schema. Are you using dbo schema?

If you setup your user correctly, a DB reader shouldn't be able to create GDB feature class in ArcCatalog. You will get error as,

Failed to create feature class.
Database user name and current user schema do not match. [GDB_TEST.MY_TEST.My_FC]

However, that user will be able to create feature dataset. But you may not be able to do anything with that FD.

My system: Windows Server 2008 R2 Standard/SQL Server 2008 R2 Standard/ArcSDE 10.0 (sp1)

Thanks!

[SebastianKrings]

I hi agree with you, too. Shorter is easier to read. But then everyone askes more and more details and then it becomes confused I think. So thats why I post all my studies.

I am using SDE schema, but I did also with DBO and the results are the same.
Youre also right the Dataset owner is not able to create a feature class, my fault if I said this. But I dont want that everyone is able to create datasets. Theres no preventive control.
You said public is only assembled with SDE objects. When I have a look at it in management studio I can see that all non-select permissions have the SDE user as grantor. Also only ArcSDE objects (mainly tables and procedures) are affected, so I could call them SDE objects. But thats enough to create datasets.

I am afraid of revoking all permissions. I not really know what they are needed for after installation finished.

Thanks.

[SebastianKrings · Accepted solution]

After so much questions are not working, a last post to this before opening another smaller one.

Here some "maybe" answers to my own questions:
-----------------------------------------------------------------------------
1)
Because the postinstallation adds INSERT, UPDATE and DELETE permissions to public role for GDB Tables its possible for any read-only user to create feature datasets. It is also possible to login to the database locally and edit the entries there. So every "data viewer" is able to destroy the geodatabase by default settings. In my mind this is a bad security implementation and documentation by esri.
I am going to reopen this question in a new topic because its arising some serious security problems.



2)
Error messages in all ArcGIS applications are often well known misleading or incorrect.



3)
I have no answer to this question, I am missing an overview of who has which permissions. Because of its possible to grant and revoke permissions either within the database or within ArcCatalog an overview would be extremly helpful. Another negativ implementation/ documentation by esri.

4)
I cannot see any difference, so there shouldtn be one.



5)
Heres my best practice:
- Create Windows groups called "sde_data_viewer" "sde_data_editor" and "sde_data_creator".
- Create Logins of this three groups within the SQL instance.
- Create for each geodatabase three roles: "sde_data_viewer" "sde_data_editor" and "sde_data_creator".
- Give each database role the permissions it needs (SELECT // INSERT, UPDATE, DELETE, EXECUTE // CREATE TABLE, CREATE PREOCEDURE).
- Assign each Login to the specific database role (1:1).
- Create a database schema called "sde_data_creator" (the same name as the database role) and make the database role "sde_data_creator" to its owner.
- Assign every Windows User to the Windows-SDE-Group it needs (users who will be assigned to windows group "sde_data_creator" should also be assigned to the windows group "sde_data_viewer").

advantages:
- central user management ind windows environment

disadvantages:
- any data_creator is added automatically as database user and database schema when he creates a featureclass (or equivalent) in ArcMap the first time.



6)
Being an SDE Administrator(the 4th group next to DataViewer etc.) (so member of either the dbo or sde user, dependent of used installation schema,) is able to delete Datasets.


7)
I dont know why it is doing so. I think I have to live with it (see disadvantage of #5).