Database Named User Login Best Practices

[JasonBartling1]

Our team is in the process of transitioning our users from logging into shared accounts to database named user accounts.  The shared accounts had the benefit of organizing all database objects under one schema but the disadvantage of accountability when it comes to database access and potential data loss when that password is opened up to multiple people.

For those that have made this transition, what approach did you take to keeping database objects from the same team organized?  Now when a new database object is created it will be created for a single user and need to be shared accordingly to other users and roles that need access if the user is not granted create table in any schema which is not really what is desired.  There are also headaches when a user separates from the organization if they have important objects under their schema.  It just seems like a lot more work to manage, but maybe there is no way around it.

On a final note, much of this database access will be done through ArcGIS Pro.

[George_Thompson]

You did not mention what RDBMS you are using. I put down some thoughts below and specific SQL Server thoughts.

Can you confirm what RDBMS you are using?

In general, my recommendations would be to have the data owners and SDE still be DB accounts (not shared). There would be a person that is responsible for the data schema and maintenance of it. This also follows the best practice to give users the lowest privileges possible to complete their tasks.

The data owners could then grant permissions to users that need to edit / view data, using AD groups (if using SQL Server). This would give you the most security and also help if someone left the company and their AD account is deactivated you would not need to do anything in SQL Server.

This would also allow you to not have all the different data schema owners for each user. 

Let me know if you have any other questions.

[JasonBartling1]

We are using Oracle as the RDBMS.  I thought about having one "data owner" per area, but this would essentially be another shared account like we already have for those that have access to the password.  You would have to rely on that user to not share out those credentials. 

We are still going to keep our shared accounts but all of the passwords are going to change so that only certain people like our team and the DBAs have them.  I was just looking for a way for the individual named user accounts to still have autonomy when creating new objects. 

The idea right now is to do all the privileges with roles that are granted by our DBAs.

[George_Thompson]

Ok, so ignore the SQL Server specific items 🙂

For Oracle, that would be the approach. A single user that owns the schema. You could then use Oracle roles (like AD within Oracle) to grant access to the datasets as needed, like you mentioned.

I would HIGHLY recommend that you use our GP (an UI) tools (can be scripted via Python) for granting privileges to the correct objects. If the data owner grants access to a role, then a user could be added to a role by the DBA and the permissions should be applied correctly. Here is the reference in the docs: https://pro.arcgis.com/en/pro-app/latest/help/data/geodatabases/manage-oracle/user-accounts-groups.h... (Look at the "Tips for grouping users" section)/ https://pro.arcgis.com/en/pro-app/latest/help/data/geodatabases/manage-oracle/privileges-oracle.htm

Let us know if you have any other questions.

[JasonBartling1]

Thanks George.  I will take a look at the docs you linked.

[MarceloMarques]

Read my database guide books with detailed information on how to configure a data owner user and how to create an editor user and viewer user and grant privileges to the editor / viewer users.

read the Production Mapping Guide Books

community.esri.comn - Mapping and Charting Solutions (MCS) Enterprise Databases Best Practices

• Database Connections Best Practices

[VinceAngelo]

Best practice is the same across RDBMS platforms, utilizing the "Least Privileges" principle:

• Create the table owned by a close-held login account (e.g., "HIGHWAY_DEPT")
• Create ROLEs for appropriate access (e.g., "HIGHWAY_BROWSE","HIGHWAY_EDITOR")
• GRANT appropriate role access to the the table ("GRANT select ON highway_dept.streets TO highway_browse")
• Create logins for each staff member
• Create logins for each discrete web application (e.g., "web_app1") -- NEVER use the data owner to publish data!!
• GRANT appropriate roles to each staff member's login ("GRANT highway_editor TO vinny_boombatz")
• GRANT appropriate roles to each staff member's login ("GRANT highway_browse TO web_app1")
• Enable EDITOR_TRACKING on each table (as the owner)

I guess there needs to be one other:

• Sanction any user who releases a password (their own, or the owner login) outside the those with approved access

Depending on the organization, data can be developed using the personal login/schema, and once deemed "ready" (by review board, common acclaim, or personal attestation), the "admin" can recreate the table and contents with the "department" login and grant appropriate access.

Note: I do this myself, even if I'm the only data team member, so web services can't see anything that isn't "ready". If the table definitions are dynamic, I'll number the tables ("streets_t1") and use views to expose them ("streets_v10").  If a new column is necessary, I'll expose it, in necessary order, via "streets_v11". Major changes get a version kick ("streets_t2" + "streets_v20").

If you're using the Wild West approach now, it can take some time to adjust, but the benefits to implementing rational access only increase as the project team adds members.

- V