Configuring IBM Db2 for z/OS privileges

This task describes how to grant the required IBM® Db2® for z/OS privileges to an application user ID or service account.

Before you begin

Ensure that you have:
  • IBM Db2 for z/OS administrator authority.
  • The user ID or service account used by the application.
  • The schema name that contains the application tables.

Procedure

  1. Identify the IBM Db2 user ID or service account to which you grant privileges. For example, Application user ID (APPUSER) or Service account (SVCACCT).
  2. Grant table-level privileges based on the operations that the application performs. 
    -- For tables requiring read access
GRANT SELECT ON <SCHEMA>.<TABLE_NAME> TO <USER_ID>;

-- For tables requiring create (insert) access
GRANT INSERT ON <SCHEMA>.<TABLE_NAME> TO <USER_ID>;

-- For tables requiring update access
GRANT UPDATE ON <SCHEMA>.<TABLE_NAME> TO <USER_ID>;

-- For tables requiring delete access
GRANT DELETE ON <SCHEMA>.<TABLE_NAME> TO <USER_ID>;
    Example: Grant full create, retrieve, update, and delete access to a table
    GRANT SELECT, INSERT, UPDATE, DELETE
ON MYSCHEMA.EMPLOYEES
TO APPUSER;
  3. Grant SELECT privileges on the required Db2 system catalog tables to enable metadata discovery. 
    GRANT SELECT ON SYSIBM.SYSTABLES TO <USER_ID>;
GRANT SELECT ON SYSIBM.SYSTABAUTH TO <USER_ID>;
GRANT SELECT ON SYSIBM.SYSCOLUMNS TO <USER_ID>;
GRANT SELECT ON SYSIBM.SYSINDEXES TO <USER_ID>;
GRANT SELECT ON SYSIBM.SYSKEYS TO <USER_ID>;
GRANT SELECT ON SYSIBM.SYSFOREIGNKEYS TO <USER_ID>;
GRANT SELECT ON SYSIBM.SYSRELS TO <USER_ID>;
GRANT SELECT ON SYSIBM.SYSKEYCOLUSE TO <USER_ID>;
  4. Verify that the privileges were granted successfully by querying the system catalog. 
    -- Verify table-level privileges
SELECT *
FROM SYSIBM.SYSTABAUTH
WHERE GRANTEE = '<USER_ID>'
  AND GRANTEETYPE = ' ';

-- Verify system catalog access
SELECT *
FROM SYSIBM.SYSTABAUTH
WHERE GRANTEE = '<USER_ID>'
  AND TCREATOR = 'SYSIBM';

Permission templates by use case

This section provides sample authorization statements for common permission scenarios.
  • Template 1: Read-only access 
    -- Application tables
GRANT SELECT ON <SCHEMA>.<TABLE_NAME> TO <USER_ID>;

-- System catalog tables (required for discovery)
GRANT SELECT ON SYSIBM.SYSTABLES   TO <USER_ID>;
GRANT SELECT ON SYSIBM.SYSTABAUTH  TO <USER_ID>;
GRANT SELECT ON SYSIBM.SYSCOLUMNS  TO <USER_ID>;
GRANT SELECT ON SYSIBM.SYSINDEXES  TO <USER_ID>;
GRANT SELECT ON SYSIBM.SYSKEYS     TO <USER_ID>;
``
  • Template 2: Full create, retrieve, update, and delete access
    -- Application tables
GRANT SELECT, INSERT, UPDATE, DELETE
  ON <SCHEMA>.<TABLE_NAME>
  TO <USER_ID>;

-- System catalog tables (required for discovery)
GRANT SELECT ON SYSIBM.SYSTABLES        TO <USER_ID>;
GRANT SELECT ON SYSIBM.SYSTABAUTH       TO <USER_ID>;
GRANT SELECT ON SYSIBM.SYSCOLUMNS       TO <USER_ID>;
GRANT SELECT ON SYSIBM.SYSINDEXES       TO <USER_ID>;
GRANT SELECT ON SYSIBM.SYSKEYS          TO <USER_ID>;
GRANT SELECT ON SYSIBM.SYSFOREIGNKEYS   TO <USER_ID>;
GRANT SELECT ON SYSIBM.SYSRELS          TO <USER_ID>;
GRANT SELECT ON SYSIBM.SYSKEYCOLUSE     TO <USER_ID>;
``
  • Template 3: Schema-level permissions 
    -- Grant permissions on all tables in a schema
GRANT SELECT
  ON SCHEMA <SCHEMA_NAME>.*
  TO <USER_ID>;

GRANT INSERT
  ON SCHEMA <SCHEMA_NAME>.*
  TO <USER_ID>;

GRANT UPDATE
  ON SCHEMA <SCHEMA_NAME>.*
  TO <USER_ID>;

GRANT DELETE
  ON SCHEMA <SCHEMA_NAME>.*
  TO <USER_ID>;
``

Troubleshooting

This section describes common issues and their resolutions when working with Db2 for z/OS table access and metadata discovery.

  • Issue: “Table not found” errors

    Solution: Verify that the user has SELECT privilege on the specified table and that the schema name is correct.

  • Issue: "Discovery returns no tables:

    Solution: Verify that the user has SELECT privilege on SYSIBM.SYSTABLES and SYSIBM.SYSTABAUTH.

  • Issue: "Column metadata not available":

    Solution: Grant SELECT privilege on SYSIBM.SYSCOLUMNS.

  • Issue: "Primary key information missing"

    Solution: Grant SELECT privilege on SYSIBM.SYSINDEXES and SYSIBM.SYSKEYS.