Individual user schemas vs schemas based on functions.

[OvidioRivero]

We are working setting up and enterprise system with both web applications as well as desktop users.  We are confronted with a choice on how to grant access to desktop users and are evaluating a few options.

1) Create schemas for each individual accessing the database (for example MARY, PAUL, JANE). Classify the users in categories based on their functions in the organization and use roles to control their level of access to the data.

2) Create a limited number of schemas based on functions(reader or  editor). Create roles based on the the schemas that own data and grant the roles to the  READER or EDITOR schemas.  For example if the data is under the VECTOR schema  there would be a Vector_read_role granted to the READER schema.

I favor option 1 because I believe it gives more granular control of privileges, can be configured to use OS authentication, versions are identified with the user name among others.   I think though that option 2 is much easier to implement and administer than version 1.

Does anyone know of best practices on this issue?  Any ESRI whitepapers or documentation making recommendations or at least saying what is the most comon practice.

Thanks,

Ovidio

[JimMcAbee]

If you can specify what RDBMS you are using that would also aid in providing a more accurate answer.  If it is Microsoft SQL Server, then data owners, must have a schema that is the same name as the user (data owner).  The data owner should be a "head-less" account (e.g. planning, dpw, etc..).   Editors and Viewers can be either database or OS/external authenticated and would not own any data and thus just require access to the schema for any data manipulation language (DML) they may be doing.   The data owner would grant permission to just view (select) or edit (insert, update, delete) to the editors and viewers through ArcCatalog.   Roles can also be used to group the users, and the permissions could be granted to roles through ArcCatalog.

More information is available in the help at the following location:

http://help.arcgis.com/en/arcgisserver/10.0/help/arcgis_server_dotnet_help/index.html#/What_are_user...

[OvidioRivero]

Thank you for your response. We had a debate in your organization but we took one course of action, the second described below.         

The database is Oracle. Data owners are created the way you describe.  My question was about data users. I wanted to have individual user accounts ex Mary, Jim, Tom.  These accounts would belong to user groups controled by roles. The Roles would be based on the office or organizational group the users belonged to for example Planning Office, Engineering office etc. 

Our organization had different rules set up and I was proposing to change them. Our organization has the following rules: For every schema that owns data there are two roles SELECT_ROLE and UPDATE_ROLE.  The roles grant select or (SELECT,UPDATE,INSERT and DELETE) to all layers in that schema.  There are no individual accounts. There are generic accounts per office for example Planning, Engineering  etc.      They maintain this is simpler to manage and I agree but I don't think it is flexible. Planning people may need to edit 1 feature class in the engineering schema while they don't need to see all of the layers in engineering.  Planning people do not need to see every feature class in the Planning schema even for example.