User permissions for geodatabases in DB2

The tables in this topic list the minimum required database privileges for common types of users: data viewers, data editors, data creators, and the ArcSDE administrator.

Permissions for users in ArcSDE geodatabases on DB2 on Linux, UNIX, and Windows are different than those required for ArcSDE geodatabases on DB2 on the IBM z operating system (z/OS). Therefore, there are two different tables of user permissions.

DB2 on Linux, UNIX, and Windows

DB2 grants full permissions to users by default. (In other words, the PUBLIC group is granted CREATETAB, BINDADD, CONNECT, and IMPLICITSCHEMA database authority plus USE privilege on the USERSPACE1 table space and SELECT privilege on the system catalog views.) To remove a database authority, a database administrator must explicitly revoke the database authority from PUBLIC.

If any of these privileges are removed from PUBLIC, they need to be granted to individual users or groups. For example, if CONNECT is revoked from PUBLIC, it needs to be granted to users so they can connect to the database. Similarly, if SELECT on the system catalog views is revoked from PUBLIC, individual users or groups must be granted SELECT on, at a minimum, the SYSIBM.SYSDUMMY1 catalog view, or they will not be able to connect to the geodatabase.

Type of user

Database permissions

Dataset permissions

Notes

Data viewer

CONNECT to database

SELECT on database object, SELECT on SYSIBM.SYSDUMMY1

If your database is configured to use shared ArcSDE log files (the default), additional privileges may be needed. See Log file table configuration options for more information.

Data editor

  • CONNECT to database
  • CREATEIN, ALTERIN, and DROPIN for the necessary schema

CONTROL, ALTER, DELETE, INSERT, SELECT, UPDATE REFERENCES, SELECT on SYSIBM.SYSDUMMY1

Data creator

  • CONNECT to database
  • CREATETAB in database
  • CREATEIN, ALTERIN, and DROPIN for the necessary schema

CONTROL on database objects, SELECT on SYSIBM.SYSDUMMY1

ArcSDE administrator

  • DBADM authority
  • SYSMON authority

    Add the ArcSDE administrator user to an operating system group and grant SYSMON to the group.

  • SYSCTRL or SYSADM authority

The DBADM authority gives the ArcSDE administrator all privileges against all objects in the database and allows him or her to grant these privileges to others (an authority similar to system roles in other DBMS types). The SYSMON authority is needed to access the DB2 snapshot API, which is required to clean out defunct ArcSDE processes from the PROCESS_INFORMATION system table. DBADM and SYSMON authority are also required to create or upgrade a geodatabase.

DBADM authority is also necessary to remove client connections from the database. In addition to DBADM, the administrator must have either SYSCTRL or SYSADM authority to remove client connections from the database.

DB2 for z/OS

Security on z/OS is higher than on other platforms. Most permissions are not automatically granted to Public by default; you need to grant permissions to individual user IDs or groups.

Type of user

Database permissions

Dataset permissions

Notes

Data viewer

SELECT on user-defined database objects, SELECT on the following system tables:

  • SYSIBM.SYSTABAUTH
  • SYSIBM.SYSDBAUTH
  • SYSIBM.SYSROUTINES
  • SYSIBM.SYSTABCONST
  • SYSIBM.SYSINDEXES
  • SYSIBM.SYSKEYS
  • SYSIBM.SYSCOLUMNS
  • SYSIBM.SYSCHECKS
  • SYSIBM.SYSSCHEMAAUTH
  • SYSIBM.SYSTABLES
  • SYSIBM.SYSSEQUENCES
  • SYSIBM.SYSDUMMY1

Data editor

Same as for data viewers plus CONTROL, ALTER, DELETE, INSERT, SELECT, UPDATE REFERENCES on database objects

Data creator

CREATETAB and CREATETS

Same as for data viewers plus CONTROL on database objects

ArcSDE administrator

Same as for data viewers plus the following:

  • BINDADD
  • CREATE ON COLLECTION SDE
  • DBADM

DB2 z/OS permissions

You can use DB2 Control Center to administer user privileges. Or you can use SQL statements to grant and revoke privileges and permissions.

Dataset privileges should be granted or revoked by the dataset owner using the Change Privileges geoprocessing tool that is available in ArcGIS Desktop. See Granting and revoking privileges on datasets and Change Privileges for instructions.

Related Topics


11/18/2013