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 he or she need to edit or create data? Or would this person only need to query the data?

Privileges are set at different levels. The table in this topic 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 should be granted or revoked using ArcGIS clients and must be done by the dataset owner.

Db2 grants CREATETAB, BINDADD, CONNECT, and IMPLICITSCHEMA database authority plus the USE privilege on the USERSPACE1 table space and SELECT privilege on the system catalog views to the PUBLIC group by default. To remove any of these database authorities, a database administrator must explicitly revoke them from PUBLIC.

If any of these privileges are removed from PUBLIC, they need to be granted to individual database users or groups. For example, if CONNECT is revoked from PUBLIC, it needs to be granted to anyone who needs to connect to the database. Similarly, if SELECT on the system catalog views or tables is revoked from PUBLIC, individual users or groups must be granted SELECT on the following or they will not be able to connect to the geodatabase:

• SYSIBM.SYSDUMMY1 (catalog view)
• SYSCAT.ROLEAUTH
• SYSCAT.DBAUTH
• SYSCAT.TABAUTH

Db2 minimum privileges

Type of user	Database privileges	Dataset privileges	Notes
Data viewer	CONNECT to database EXECUTE on MON_GET_CONNECTION	SELECT on database objects, SELECT on SYSIBM.SYSDUMMY1, SYSCAT.ROLEAUTH, SYSCAT.DBAUTH, SYSCAT.TABAUTH	If your database is configured to use shared log file tables (the default), the following additional privleges are needed: CREATETAB IMPLICIT_SCHEMA The MON_GET_CONNECTION function cleans up defunct processes from the PROCESS_INFORMATION table when the user connects. To clean up connections, the user must be able to execute MON_GET_CONNECTION.
Data editor	CONNECT to database CREATEIN, ALTERIN, and DROPIN for the necessary schema EXECUTE on MON_GET_CONNECTION	SELECT, INSERT, ALTER, and DELETE privileges on other user's tables. CONTROL, ALTER, DELETE, INSERT, SELECT, UPDATE REFERENCES, SELECT on SYSIBM.SYSDUMMY1 SELECT on SYSCAT.ROLEAUTH, SYSCAT.DBAUTH, SYSCAT.TABAUTH	Table and feature class owners use the Privileges dialog box or Change Privileges geoprocessing tool in ArcGIS to grant editing privileges on their data to other users. The MON_GET_CONNECTION function cleans up defunct processes from the PROCESS_INFORMATION table when the user connects. To clean up connections, the user must be able to execute MON_GET_CONNECTION.
Data creator	CONNECT to database CREATETAB in database CREATEIN, ALTERIN, and DROPIN for the necessary schema EXECUTE on MON_GET_CONNECTION	CONTROL on database objects, SELECT on SYSIBM.SYSDUMMY1 SELECT on SYSCAT.ROLEAUTH, SYSCAT.DBAUTH, SYSCAT.TABAUTH	The MON_GET_CONNECTION function cleans up defunct processes from the PROCESS_INFORMATION table when the user connects. To clean up connections, the user must be able to execute MON_GET_CONNECTION.
Geodatabase administrator (the sde user)	DBADM authority SYSCTRL or SYSADM authority EXECUTE on MON_GET_CONNECTION		The DBADM authority gives the sde user all privileges against all objects in the database and allows him or her to grant these privileges to others. It is required to create or upgrade a geodatabase. DBADM authority is also necessary to remove client connections from the database. In addition, the sde user must have either SYSCTRL or SYSADM authority to remove client connections from the database. The MON_GET_CONNECTION function cleans up defunct processes from the PROCESS_INFORMATION table when the user connects. To clean up connections, the sde user must be able to execute MON_GET_CONNECTION.