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.

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