Shared permissions of geodatabase objects in an enterprise environment
The workflow example outlined below details the concepts behind leveraging SQL Server capabilities in an enterprise environment in order to support workflow functionalities amidst a multi-project, multi-team and multi-administrator GIS environment allowing for versatile functionality and robust data management while maintaining security and data integrity.
As of the 2005 release of SQL Server Microsoft introduced a new security model that allowed for advanced permission capabilities, specifically the “Take Ownership” functionality https://msdn.microsoft.com/en-us/library/ms187359.aspx With the introduction of this, Database Administrators (DBA’s) gained a much more granular and fluid capability to architect and manage databases along with security and how users interacted with the database. As a result there was an increase to security, an increase to capabilities tracking/logging users, more transparency via accountability and less management work on the DBA.
In the discussion forums dated Jan 2013 ( https://geonet.esri.com/thread/65738) an ESRI rep named Shannon Shields addresses this topic and indicates it’s been reviewed for future releases by ESRI but to date still has not been changed. I submitted a ticket via the support channels recently as well and this was assigned as NIM011230 but I’ve been informed this page is the best way to get this high on the list of updates to be made.
Our workflows are outlined below in the current state with which ESRI does not recognize and leverage the underlying database capabilities of SQL Server (ESRI SQL permissions without inheritance image). The intended workflow environment that could be obtained by ESRI recognizing and utilizing the underlying database capabilities are outline in the second workflow (ESRI SQL permissions with inheritance image).
ESRI SQL Permissions without Inheritance (current capabilities)
ESRI SQL Permissions with Inheritance (future/potential capabilities)
IF ESRI was to allow the database to manage these permissions there would be many benefits to organizations and users as described above and in the scenarios in the images. This means ESRI either removes current limitations it’s using that prevents these permissions to be passed along or that it adopts these capabilities and integrates them into current ESRI capabilities and security.