Privileges for geodatabases in Db2

Privileges determine what someone is authorized to do with the data and the database. Privileges should be assigned based on the type of work the person does within the organization. Is this person involved with administration of the geodatabase? Does this person need to edit or create data? Or would this person only need to query the data?

Privileges are set at different levels. This page lists the minimum required database and dataset privileges for common types of geodatabase users: data viewers, data editors, data creators, and the geodatabase administrator.

You can use IBM Db2 tools or SQL statements to administer database privileges.

Privileges on datasets in geodatabases must be granted or revoked using ArcGIS, and must be done by the dataset owner.

Db2 minimum privileges

Type of userMinimum required privilegesNotes

Data viewer

  • CONNECT to the database
  • BINDADD
  • IMPLICIT_SCHEMA
  • USE on the USERSPACE1 table space
  • SELECT on the following system catalog tables and view:
    • SYSIBM.SYSDUMMY1 (catalog view)
    • SYSCAT.ROLEAUTH
    • SYSCAT.DBAUTH
    • SYSCAT.TABAUTH
  • SELECT on the DB2GSE.ST_GEOMETRY_COLUMNS system table
  • SELECT on other users' tables and feature classes
  • EXECUTE on the MON_GET_CONNECTION function
  • USE on the USER TEMPORARY TABLESPACE that was created when the geodatabase was created

Table and feature class owners must use the Privileges dialog box or Change Privileges geoprocessing tool in ArcGIS to grant the select privilege on their data to other users.

The MON_GET_CONNECTION function cleans up defunct processes from the PROCESS_INFORMATION system table when the user connects. To clean up connections, the user must be able to run MON_GET_CONNECTION.

The USE privilege is required on the USER TEMPORARY TABLESPACE to allow the software to create the global temporary table used to store selection sets that contain more than 1,000 records.

Data editor

Data editors require all the same privileges as a data viewer plus the following:

  • INSERT, UPDATE, and DELETE on other users' tables
  • CONTROL, ALTER, DELETE, INSERT, and UPDATE REFERENCES on SYSIBM.SYSDUMMY1

Table and feature class owners must use the Privileges dialog box or Change Privileges geoprocessing tool in ArcGIS to grant insert, update, and delete privileges on their data to other users.

You can grant any combination of INSERT, UPDATE, and DELETE privileges depending on what editors need to do. Therefore, you can create multiple editor groups and grant the appropriate privileges to each. For example, you can have a full_edit group that has all three privileges plus SELECT on the tables group members need to edit and an updates_only group that has only SELECT and UPDATE privileges on the tables members need to edit.

Data creator

Data creators require all the same privileges as a data viewer plus the following:

  • CREATETAB in database
  • CONTROL on database objects

Geodatabase administrator (the sde user)

  • For Db2 11.5 and earlier, the sde user requires all the same privileges as a data creator plus DBADM authority on the database.
  • For Db2 12.1 and later, the sde user requires all the same privileges as a data creator plus DBADM WITH DATAACCESS WITH ACCESSCTRL on the database.

The DBADM or DBADM WITH DATAACCESS WITH ACCESSCTRL authorities give the sde user all privileges for all objects in the database and allow the sde user to grant these privileges to others, which is required to create or upgrade a geodatabase.

To remove client connections from the database, the sde user must also have either SYSCTRL or SYSADM authority.