topic Re: Database or OS Authentication? How does this affect data owner? in Geodatabase Questions

Database or OS Authentication? How does this affect data owner?

NeelKumar — Tue, 10 Mar 2020 20:44:55 GMT

We are thinking about moving toward OS Authentication for our enterprise geodatabases on SQL Server 17. I'm curious how this will affect data owner ship and the ability of edit schemas/domains. We have two Users set up, Viewer and Modify So in this example, a user with OS Authentication to an SDE creates a feature class and applies the correct privileges to the feature class for the Viewer and Modify users. Can someone else make their own connection to the SDE and make schema changes/edit the domain? Or will that be restricted to the person who originally created the data?

If we go the database authentication route, I'm thinking about creating two 'SDE' accounts: SDE_Admin and SDE_Editor. SDE_Admin would have full rights to the geodatabase but SDE_Editor would only have the ability to create and modify data but not delete. Then put at Admin SDE in a secure location for only the admin while the Editor SDE connection would be in a shared location? Would a setup like this be feasible/make sense?

Re: Database or OS Authentication? How does this affect data owner?

ColinWerle1 — Tue, 10 Mar 2020 22:57:27 GMT

Hi Neel,

If you have a headless account (db authentication) creating/owning the data, managing domains, etc. you will not have to worry about future user changes to the environment (e.g. user turnover, new users, etc.). A headless user can be easily managed by a single user, or multiple, and you can change the password as you wish if necessary. If the geodatabase system tables are owned by sde, then that sde user mapped to the geodatabase is your geodatabase administrator, no need for a new user called "sde_admin".

OS authenticated users are certainly helpful for efficiently tracking edits, versioning, etc. in the geodatabase. So if you would like to use OS authenticated users, then it sounds like a possible configuration could be having a db authenticated user create the data and domains, then map OS authenticated users to the database and grant necessary privileges on the datasets that they will be editing.

Extra documentation:
Privileges for geodatabases in SQL Server—Help | Documentation

Modifying and deleting attribute domains—ArcGIS Help | Documentation

Hope this helps!

Colin

Re: Database or OS Authentication? How does this affect data owner?

NeelKumar — Wed, 11 Mar 2020 01:00:04 GMT

Thanks! My reasoning behind having the sde_admin/sde_editor users is to prevent data from being deleted by non-admins. With one headless sde account, anyone who finds the sde connection file could, in theory, start editing or deleting data.

Re: Database or OS Authentication? How does this affect data owner?

ColinWerle1 — Wed, 11 Mar 2020 16:56:54 GMT

In that case, I would avoid saving passwords on the admin connections when you create them, which is typically a good habit to practice in general if you can.

Here is some other good doc as well: User accounts and groups—Help | Documentation

Re: Database or OS Authentication? How does this affect data owner?

DanaNolan — Mon, 16 Mar 2020 19:45:41 GMT

When we had to move to OSA, I needed to keep the DBA account for schema changes. So I have do have 2 connections and only use the DBA one when I need to.

OSA links to network security, so it has the huge advantage of automatically keeping up with personnel changes. Whenever someone's Active Directory account is removed because they have left, they cannot access our enterprise data, without having to involve the server administrator. When someone new comes in, we can refer to the Active Directory privileges groups for file and database access that we want for that person. Our DBA passwords were ancient, so of no use really to protect anything.

Re: Database or OS Authentication? How does this affect data owner?

NeelKumar — Tue, 24 Mar 2020 13:53:30 GMT

Collin,

To follow up with your original response..would it work if we had a connection using the 'SDE' login (database authentication) and this user could create datasets, assign permissions, ect..and then a separate OS authentication sde connections for users to actually work with the data?

Thanks

Neel