In our project, we have two users in the database: applicative user and admin user. The admin user is the owner of all the objects (tables,layers,sequences, etc...) and the applicative user has only limited privileges, mainly read-write to these objects.
When creating a new database table, you only have to run the GRANT
SQL command on the table to give the access privileges to the app user.
When creating a new layer, you just can't only give privileges to the table which represents the layer, because when creating a layer, the features are held in several tables (s,f,a,d tables). It's a bit difficult to use the grant command for each of these tables.
To grant privileges to a layer you need to use ESRI's tools.
ArcCatalog
Right click on the layer in ArcCatalog -> Privileges -> Select the privileges and enter the user to which you want to grant the privileges.
You can also use the Change Privileges tool in the ArcCatalog toolbox.
SDE command
From the command line in your SDE server use sdelayer
:
sdelayer -o {grant | revoke}
-l <table,spatial column name>
-U <user>
-A <SELECT,UPDATE,INSERT,DELETE>
[-i <service]
[-s <server_name]
[-D <database]
-u <DB_User_name>
[-p <DB_User_password>]
Example
Suppose we have a layer named streets_g. I
n order to grant select privileges to our app user we'll use this command:
sdelayer -o grant -l streets_g,shape -U appUser -A SELECT -u strongUser -p password
Deployments
For deployments I recommend to create a script which contains the sdelayer
command for each of the layers in your database.
You can also create a model using the ArcCatalog toolbox which uses the Change Privileges tool.