Grant and revoke dataset privileges in databases and enterprise geodatabases

Available with Standard or Advanced license.

To allow other database users to view or modify the contents of any data in a relational database or enterprise geodatabase, you must grant them the privileges to do so.

Dive-in:

For enterprise geodatabases and relational databases, the data owner is determined by the user specified in the database connection that is used when the dataset is created using tools in ArcGIS clients. A dataset is any feature class, table, or view in a database or enterprise geodatabase, as well as feature datasets in an enterprise geodatabase.

As the owner of the dataset, you can use the Privileges dialog box, the Change Privileges geoprocessing tool, or the ChangePrivileges_management function in a Python script to specify the privileges a user or group has on a specific dataset.

You can grant the privilege to select data, meaning the user can read and select from but not modify the contents of a dataset. You can also grant update, insert, and delete privileges, which allow the user to modify the contents of a dataset.

The following rules apply to granting and revoking privileges on a dataset in a database or enterprise geodatabase in ArcGIS:

• Only the table owner can alter privileges on the dataset.
• Only the table owner can delete the dataset or alter its schema definition; therefore, even if another user has been granted insert, update, and delete privileges on a dataset, that user cannot alter the schema.
• When you grant the insert, update, or delete privilege to a user, you must also grant the select privilege. Users must be able to read the dataset before they can edit it.
• The dbo and db_owner roles do not appear in the User/Role list on the Privileges dialog box for Microsoft SQL Server databases. These users automatically have privileges granted on all data in the database. The privileges vary by role, and you cannot revoke these privileges.
• PostgreSQL login roles or groups that have been granted superuser status in the database do not appear in the User/Role list on the Privileges dialog box. These users automatically have full privileges on all data, and you cannot revoke these privileges.
• You can only alter a user's privileges on one dataset at a time using the Privileges dialog box. To alter privileges on multiple datasets at once, use the Change Privileges geoprocessing tool or ArcPy function instead.
• Revoking privileges requires an exclusive lock on the dataset. If anyone else is connected to the dataset, you can't revoke privileges.
• You cannot use ArcGIS to grant or revoke privileges for tables in cloud data warehouses.

The following rules apply to granting and revoking privileges on datasets in an enterprise geodatabase only:

• When the dataset is in an enterprise geodatabase, use the ArcGIS tools listed below to grant and revoke privileges. This ensures that users have access to all necessary geodatabase system tables associated with the dataset.
• All feature classes in a feature dataset must have the same privileges applied.
• When privileges are granted to a feature class or table that participates in a relationship class, privileges must be granted to both the origin and destination classes. If edits made to the origin feature class will affect the destination feature class, you must grant update, insert, or delete privileges on both feature classes.

  If the origin and destination feature classes are in the same feature dataset, they have the same set of privileges, because privileges are granted at the feature dataset level. However, when the origin or destination class is not in the same feature dataset, you must ensure the proper privileges are granted to both the origin and destination classes. If the relationship class is either attributed or has many-to-many cardinality, privileges are automatically propagated to the intermediate table (not the destination class) when you assign privileges to the origin class.

• If the dataset is not registered as versioned, you can grant and revoke the update, insert, and delete privileges individually using the Privileges dialog box. For example, you can grant a user select and update privileges, which allow the user to open the dataset and alter existing features but does not allow the user to add new features or delete existing features.
• If the dataset is registered for traditional versioning, the privileges that allow a user to modify a dataset (update, insert, and delete) must be granted and revoked as a group.
• If the dataset is registered for traditional versioning, the geodatabase administrator must have full privileges on it. Therefore, you cannot revoke privileges from the geodatabase administrator on those datasets.
• Datasets that are registered for branch versioning can only be edited through web feature layers, and access to the data is controlled through settings on the web feature layer. Because only the dataset owner can publish the branch versioned data, there is no reason to grant privileges on the data in the geodatabase to other database users.

Use one of the following to grant or revoke privileges on datasets you own:

• Privileges dialog box—You can view the privileges a user or group has on a specific dataset when you use the Privileges dialog box. You can also grant privileges on the dataset to more than one user or group at a time.
• Change Privileges tool—You can grant privileges on multiple datasets to a user or group when you use the Change Privileges geoprocessing tool.
• Python script—You can run a Python script from machines where ArcGIS Server is installed. Privileges can be altered on multiple datasets.

Use the Privileges dialog box

Follow these steps to grant or revoke privileges using the Privileges dialog box in ArcGIS Pro:

1. Connect to the relational database or enterprise geodatabase that contains the data you own and for which you want to grant or revoke privileges.
2. Right-click the dataset and click Privileges.
3. If the user or role with the privileges you want to change is in the list, check or uncheck the boxes for the privileges to grant or revoke, and click OK to apply the changes.

   Note:

   If you uncheck all the privilege boxes, the user or role will be removed from the list.

4. If the user or role is not in the list, do the following:
   1. Click Add User to open the User/Role dialog box.
   2. If you have privileges on the database to view all users and roles in the database, you can check the database users or roles check box next to the user or role name to add it. Otherwise, type the name of the database user, database role, operating system login, or Windows group to which you will grant privileges.
   3. Click OK to close the User/Role dialog box.
   4. Check the boxes of the privileges required for each new user or role and click OK.

Use the Change Privileges tool

You can use the Change Privileges tool to grant or revoke privileges on datasets you own. The tool is in the Geodatabase Administration toolset of the Data Management toolbox.

You can change a user's or group's privileges on multiple datasets at once using this tool. The same privilege will be granted to all the specified datasets.

The Change Privileges tool does not show existing privileges the user may have on the datasets. Also, insert, update, and delete privileges must be granted as a set; they cannot be granted individually.

1. Connect to the enterprise geodatabase or relational database as the owner of the datasets for which you want to grant or revoke privileges.
2. Open the Change Privileges tool.
3. Browse to the database connection and choose the datasets.
4. Type the name of the user or group for which dataset privileges will change.
5. Use the View and Edit drop-down lists to choose the privileges to grant to the specified user or group.

   When you choose Edit, the View privilege must also be granted.

6. Click OK to run the tool.

Use a Python script

To use a Python script to grant or revoke privileges on datasets that you own, use the ChangePrivileges_management function.

1. Create a database connection (.sde file) using the CreateDatabaseConnection_management function.

   You must connect as the owner of the datasets and save the username with the file.
2. Run the ChangePrivileges_management function.