topic Re: GDB and SQL permissions in Data Management Questions

GDB and SQL permissions

Marco_AlejandroBonilla — Thu, 23 Oct 2014 14:13:00 GMT

Hi,

I've been reading some help topics and GeoNet posts about managing permissions in GDB, eventhough I am overwhelmed by all the implied considerations on implementing GDB security.

My question seems relatively simple but I can't solve my issue yet, so:

What are the minimum permissions that my users must have in the database in SQL Server in order for me to manage their select/editing permissions with arcCatalog for mi Enterprise Geodatabase?

Thank you

Marco

Re: GDB and SQL permissions

Sravan_KumarUddarraju — Thu, 23 Oct 2014 15:53:08 GMT

Grant Select, update, Delete, Insert permissions to the user.

If there are many users,

1) Create role

2) Provide permissions (Select, update delete, insert) to the role

3) Assign the users to that role.

Srav

Re: GDB and SQL permissions

Marco_AlejandroBonilla — Thu, 23 Oct 2014 16:03:36 GMT

Thanks for the answer, although,

That's for Geodatabase permissions' managment, through ArcCatalog, but how the permissions inside the SQL Server (database server) must stand in order to avoid issues in that first assignment?

Greetings,

Re: GDB and SQL permissions

AsrujitSengupta — Thu, 23 Oct 2014 16:11:27 GMT

What exactly are these users supposed to do?

Edit particular data in the geodatabase? edit all data in the sde geodatabase? only preview the data in the sde geodatabase?

Re: GDB and SQL permissions

StevenGraf1 — Thu, 23 Oct 2014 16:16:55 GMT

You shouldn't have to grant them any permissions inside SQL Server, just add them to the database. Once they are added to the database, you can grant them permissions on only data you own inside ArcCatalog.

Steven

Re: GDB and SQL permissions

Marco_AlejandroBonilla — Thu, 23 Oct 2014 16:20:56 GMT

Asrujit, there are many users with different requirements, some of them will just preview the data meanwhile other users will edit data; also, schema must remain unchanged.

TKS

Re: GDB and SQL permissions

Marco_AlejandroBonilla — Thu, 23 Oct 2014 16:29:21 GMT

Steven, the problem is that In the enterprise where I'm working, the permissions were managed through SQL and also through ArcCatalog because they were having some issues in editing data. So, I'm trying to rebuild the users/rol administration in order to optimize the permissions' control. For this reason, I'm, in first place, rebuilding the groups and logins in Active Directory, after that, I must establish the minimum permissions in SQL in order to manage the GDB permissions only from ArcCatalog and avoid any (GDB nor SQL) permissions issues.

TKS,

Re: GDB and SQL permissions

Marco_AlejandroBonilla — Thu, 23 Oct 2014 16:32:33 GMT

So, in the past, in SQL, some permissions were established (Owner, R/W, Read-Only).

TKS

Re: GDB and SQL permissions

StevenGraf1 — Thu, 23 Oct 2014 16:47:22 GMT

My workflow is pretty simple,

1st. Create the Active Directory user.

2nd. Add the user to the database through SQL Mgmt Studio.

3rd. Add the user to the Ent GDB through ArcCatalog.

4th. Add permissions to either Feature Datasets or Feature Classes.

Keep in mind that you can't create a geodatabase user for an entire windows group. ArcGIS Help (10.2, 10.2.1, and 10.2.2)

Hope this helps in your migration.

Steven

Re: GDB and SQL permissions

AsrujitSengupta — Thu, 23 Oct 2014 16:48:23 GMT

The best I can think of would be to create separate groups and add the Windows logins to the groups as desired.

The groups will have permissions based on your need.

Example:

db_datareader: for previewing purpose only

db_datareader: for users who are going to edit all the data in the geodatabase

grant privileges from ArcCatalog: for users who are supposed to edit certain data only.

The reason for suggesting groups-- you have Windows AD setup and won't have to add each login manually and setup permissions.

Re: GDB and SQL permissions

brianbond — Thu, 23 Oct 2014 16:50:52 GMT

Marco, your original question states what are the minimum permissions to manage a users permissions that comes in from Active Directory (AD) groups and log-in; the minimum is almost nothing. From my experience, adding a Public Role to the database, everyone should be able to connect to the database and see all the feature classes/tables in the SQL Enterprise Geodatabase.

From my experience, for editor's connecting to the SQL database with authentication from an AD Group, only db_datareader Database role membership is granted to that AD Group and I then control their edit rights using ArcCatalog granting Select, Insert, Update, Delete to the specific feature classes or feature dataset. For the users that you want only to view data, from my experience, that AD Group is not granted any Database role membership and as long as the Public Role granted to the database, the AD user or groups can see the entire database because of the Public Role. If you have to sub-divide off certain Read-Only feature classes to certain AD users; you will have to do more work to set Database role membership in SQL Database to certain AD groups.

Brian

Re: GDB and SQL permissions

Marco_AlejandroBonilla — Thu, 23 Oct 2014 17:10:18 GMT

So, as I could understand, if I set db_datareader permissions to all my groups in SQL, In first place, all those groups will see all the GDB data, and also, I will be able to grant editing permissions to specific groups through ArcCatalog.

Am I rigth?

And Thank you very much,

Re: GDB and SQL permissions

AsrujitSengupta — Thu, 23 Oct 2014 17:23:39 GMT

Yes

Re: GDB and SQL permissions

brianbond — Thu, 23 Oct 2014 17:27:43 GMT

Yes, I agree with Asrujit regarding your recent question about granting edit permissions to specific AD groups through ArcCatalog.