Post date: Nov 26, 2014 9:40:59 PM
Users and their permissions can be extremely complex, so it is best to simplify them as much as possible. Permissions are best managed using groups OR individual users, not a combination of the two. Additionally, user groups should not be members of one another. Thus, changes can be managed more effectively; changes to one group or user's permissions will not ripple unexpectedly through a complex system of dependent permissions.
A database should have at least three key roles (if not breaking down access on a per-user basis, which is best avoided for simplicity's sake, if possible) in addition to the sde user: readonly, editor, and creator/data-owner/administrator*. It is best practice not to use the sde user, but to have an additional creator/data-owner/administrator role for creating and dropping tables.
Read-only Roles
Generally, read-only roles should have access to the whole database (unless some features/tables are indeed restricted), but should not be able to edit existing data, nor create new data. Read-only, as opposed to creation, permissions do not require a unique schema, so a user group can be created for these permissions, and users can be added to this group for access.
Create the readonly group by right-clicking group roles in pgAdmin and selecting New Group Role…
Enter the group role name. No other settings should need to be changed.
This user does not require a schema, but needs to be added to and existing schemas in the database, as the schemas contain the data tables. Expand the database, and expand the Schemas item. Right-click a schema and select the Properties option. Under the Privileges tab, select the readonly role from the drop down, check USAGE, then click Add/Change. Under the Default Privileges tab, select the readonly user from the drop down, uncheck ALL, check SELECT, then click Add/Change. Click OK.
Database permissions need to be set. Right-click on the database and select the Properties option. Under the privileges tab, select readonly from the drop down, check CONNECT, then click Add/Change. Click OK.
Now the readonly user group is setup, except the group does not have any privileges granted on any feature classes or tables in the SDE database. See the section below about setting permissions within ArcGIS. Read-only groups and users should only have SELECT permissions enabled. Once this step is completed for all data in the database for which readonly users should have access, the user group should be setup correctly. Now, individual users can be added to the read-only group and should be able to connect to the DB with read-only access.
*A note on schemas
The way SDE works, each user who creates datasets must have a schema with a matching name. Any tables created by that user, they become the owner and their schema is "part" of the dataset. However, this can lead to very difficult to manage databases where the path of a dataset is not necessarily consistent or predictable. Therefore, it is better practice to restrict users from creating tables, except in the case of CREATE users. That is, each user needing to create tables needs to have it's own schema. The sde user should never be used for data creation. The sde user's sole role is to manage all the ancillary tables that SDE requires. Data should be stored in other user schemas.
The public user schema is also important, as it contains the spatial reference information used by SDE. Do not remove the public user schema.
Edit User Roles
Like read-only users, edit users should have access to the entire database (again, unless specific restrictions should be in place), but should also have permission to edit the data. Again, a user group is good for managing users with edit permissions. Configuring this user is exactly like the read-only user group, aside from two minor differences:
When setting Default Privileges (step 3 above), also check INSERT, UPDATE, and DELETE.
When assigning permissions on each feature class or dataset in ArcGIS, also check INSERT, UPDATE, and DELETE. See the section below on setting permissions within ArcGIS.
Creator/Data-Owner/Administrator Users
I have struggled with what terminology to use when referring to such users. I don't feel like such users are truly database admins, but they can use some admin functions in the Arc Toolbox. As well, each creator is a data-owner, and therefore must grant privileges to other users if they require access. Indeed, it seems only true database admins should be allowed to create data at all within an SDE database (if it is a production database, then the schema should be the responsibility of the database admin).
It is also important to realize that the schema name is part of the path to the data. That is, if the database is named "GIS_DB", for example, and the db has two creator schemas, named "user1" and "user2", respectively, then an FC name "data1" created by user1 in GIS_DB will have the full name "GIS_DB.user1.data1". Another FC named "data2" from user2 will, conversely, be "GIS_DB.user2.data2". In ArcGIS, this naming convention is not a huge deal, but does significantly impact python scripts, SQL queries, or other such means of accessing the data. The important point here is that no user can create data in a schema that is not named exactly the same as his or her user name, and the schema is part of the table's path in the database. If the creator of an FC is unknown, then finding the data in the database is not straightforward.
To create a schema for a create user in pgAdmin, right-click the "Schemas" item in the database. This will bring up a New Schema wizard. Enter the name for the new schema (same as the create user), and pick the create user as an owner. In the privileges tab, add the sde user will ALL, the read-only group with USAGE, and the edit group with ALL. In the Default Privileges tab, add the sde user with ALL, the read-only group with SELECT, and the edit user group with SELECT, INSERT, UPDATE, and DELETE.
Creator users can be added to the edit user group, so that permissions on other create user schemas can be managed more simply, or they can have their own create user group as well.
Setting Permissions within ArcGIS for Each Feature Class and Dataset
The owner of a dataset is responsible for sharing the data with other users to allow them access. In general, storing data in feature datasets makes this process much easier, as the entire feature dataset is shared with the same access. Thus, if ten feature classes are stored in a feature dataset, sharing need only be granted on the feature dataset, not on all ten feature classes. However, keep in mind that ALL feature classes in the feature dataset are shared with the SAME permissions, so if one or more features classes in a feature dataset require different permissions they need to be moved out of the feature dataset.
To grant access to other users, as the data owner connect to the database. Find the dataset to be shared and right-click it. In the contextual menu should be an submenu called "Manage", under which is another item called "Set Permissions" (please verify this item name). Selecting this will open a window to which users and roles can be added. When they are added, four check boxes will appear for each user or role corresponding to INSERT, SELECT, UPDATE, and DELETE permissions. Check these boxes to grant permissions as necessary to each user or role that was added.
I believe Arc has some other tools in the data management toolbox to help automate this process, but I have not used them so I cannot comment on them.
Miscellaneous Key Tips
postgres generally uses all lowercase characters for names and such. It is best to stick with this convention to avoid silly errors.
Don't forget to refresh the display in pgAdmin. At least in the current version, one must refresh all of the categories individually (i.e. refresh the databases, refresh the group roles, etc.), or click on the pg server item and then click the refresh button.
Don't use the sde user for any data creation or manipulation