DB2 authorization catalog tables

How privilege rows work

Authorization tables are not a single “ACL” blob. Each family stores privileges for one kind of object. A row names a GRANTOR, a GRANTEE, and the object, then uses one CHAR(1) column per privilege.

• GRANTEE — authorization ID, or PUBLIC / PUBLIC* for public grants
• GRANTEETYPE — blank for an authorization ID, L for a role (IBM documentation)
• GRANTOR — who issued GRANT (useful in revoke-by-grantor reasoning)

Administrative authorities such as SYSADM imply many object privileges without a matching SYSTABAUTH row for every table. A catalog query that only reads SYSTABAUTH under-reports what SYSADM can do. Conversely, an access control authorization exit can hide or add access that the catalog does not show. IBM’s Application Programming Guide states that when such an exit is installed, catalog queries are not a reliable list of tables you can access.

SYSIBM.SYSTABAUTH

Table and view privileges. TCREATOR and TTNAME identify the object (note the double T: TTNAME, not TBNAME). This is the table IBM uses in the example “which tables can I access?”

1
2
3
4
5
6
7
8
9
10
11
12
13
14
-- Tables this session user or PUBLIC can access (IBM-style example)
SELECT DISTINCT TCREATOR, TTNAME
  FROM SYSIBM.SYSTABAUTH
 WHERE GRANTEE IN (USER, 'PUBLIC', 'PUBLIC*')
   AND GRANTEETYPE = ' '
  WITH UR;
 
-- Who can UPDATE HR.EMPLOYEE?
SELECT GRANTOR, GRANTEE, GRANTEETYPE, UPDATEAUTH, SELECTAUTH
  FROM SYSIBM.SYSTABAUTH
 WHERE TCREATOR = 'HR'
   AND TTNAME = 'EMPLOYEE'
   AND UPDATEAUTH IN ('Y', 'G')
  WITH UR;

GRANT SELECT ON TABLE HR.EMPLOYEE TO REPORT1 inserts or updates a SYSTABAUTH row. REVOKE removes or clears the flag. Views have their own rows; SELECT on a view does not always mean SELECT on every base table (view definer privileges also matter).

SYSIBM.SYSCOLAUTH

IBM describes SYSCOLAUTH as recording privileges for updating columns. Table-level UPDATEAUTH = Y on SYSTABAUTH means all columns (unless column grants restrict the story). Column-level GRANT UPDATE (col1, col2) produces SYSCOLAUTH rows with CREATOR, TNAME, COLNAME, and UPDATEAUTH set to Y or G.

1
2
3
4
5
SELECT GRANTEE, CREATOR, TNAME, COLNAME, UPDATEAUTH
  FROM SYSIBM.SYSCOLAUTH
 WHERE CREATOR = 'HR'
   AND TNAME = 'EMPLOYEE'
  WITH UR;

Audit both tables together. A user with no SYSTABAUTH UPDATEAUTH might still update SALARY through SYSCOLAUTH. The reverse also happens: table-level UPDATE with no SYSCOLAUTH rows.

SYSIBM.SYSDBAUTH

Database privileges. NAME (or the documented database-name column for your release) identifies the database. Privilege columns use the same Y/G/blank pattern.

1
2
3
4
SELECT GRANTEE, NAME, DBADMAUTH, CREATETABAUTH, LOADAUTH, RECOVERDBAUTH
  FROM SYSIBM.SYSDBAUTH
 WHERE NAME = 'HRDB'
  WITH UR;

DBADM on HRDB is not SYSADM. It is still powerful: many shops treat DBADM grants as production-change control items. CREATETAB without CREATETS may still succeed when Db2 implicitly creates a table space—know your DDL standards before you read the flags as a complete story of who can create objects.

SYSIBM.SYSRESAUTH

Resource privileges that are not tables and not system authorities. IBM’s list: buffer pools, storage groups, collections, table spaces, JARs, and distinct types. QUALIFIER and NAME identify the resource; an object-type column (often OBTYPE or equivalent on your release) distinguishes a buffer pool from a collection.

• Buffer pool — USE privilege to assign objects to that pool
• Storage group — USE of the STOGROUP
• Table space — USE on a specific space (CREATE TABLE IN …)
• Collection — CREATE IN / BIND into that collection (PACKADM-related grants also show up in this family)
• Distinct type / JAR — USAGE and related routine-support privileges

1
2
3
4
SELECT GRANTEE, QUALIFIER, NAME, OBTYPE
  FROM SYSIBM.SYSRESAUTH
 WHERE GRANTEE = 'HRBIND'
  WITH UR;

If a BIND fails with an authorization SQLCODE on a collection, SYSRESAUTH is the first catalog stop—before you assume SYSPACKAUTH is wrong.

SYSIBM.SYSUSERAUTH

System authorities and subsystem-level privileges. One row per grantee that holds any of these. This is where SYSADM lives in the catalog.

1
2
3
4
5
6
SELECT GRANTEE, SYSADMAUTH, SYSCTRLAUTH, SYSOPRAUTH,
       BINDADDAUTH, SQLADMAUTH, DATAACCESSAUTH, ACCESSCTRLAUTH
  FROM SYSIBM.SYSUSERAUTH
 WHERE SYSADMAUTH IN ('Y', 'G')
    OR SYSCTRLAUTH IN ('Y', 'G')
  WITH UR;

Keep SYSADM grants tiny. SYSCTRL cannot do everything SYSADM can on user data; IBM documents those gaps (for example SYSCTRL versus user tables). DATAACCESS and ACCESSCTRL exist so shops can split “read all data” from “grant all privileges” without handing out SYSADM. Your exact column list depends on catalog level—DESCRIBE SYSIBM.SYSUSERAUTH or query SYSCOLUMNS when you write a reusable audit script.

SYSIBM.SYSROUTINEAUTH

Privileges on user-defined functions and stored procedures. EXECUTE is the privilege application IDs need for CALL and function invocation. SCHEMA, SPECIFICNAME (and NAME) identify the routine; match them to SYSROUTINES. GRANT EXECUTE ON PROCEDURE writes here, not to SYSTABAUTH.

1
2
3
4
SELECT GRANTEE, SCHEMA, SPECIFICNAME, EXECUTEAUTH
  FROM SYSIBM.SYSROUTINEAUTH
 WHERE SCHEMA = 'HR'
  WITH UR;

Functions used in views and constraints create extra authorization questions: the definer must be allowed to execute the function. Routine grants are easy to forget in schema-copy checklists.

A privilege-audit pattern

When a user says “I cannot SELECT,” walk the catalog in this order:

• SYSUSERAUTH — SYSADM / DATAACCESS would already allow it
• SYSTABAUTH — table or view SELECTAUTH for the ID, groups you mapped, PUBLIC
• SYSDBAUTH — DBADM on the database
• Exit / RACF — if the shop uses an authorization exit, stop trusting the catalog as complete

Package EXECUTE is SYSPACKAUTH (previous page). Sequence USAGE is SYSSEQUENCEAUTH. Schema CREATEIN / ALTERIN / DROPIN is SYSSCHEMAAUTH. Trusted contexts use SYSCONTEXT tables. Know the family; do not force everything into SYSTABAUTH.

Explain It Like I'm Five

Authorization tables are permission stickers. SYSTABAUTH is a sticker on a toy box (table): “you may look inside” (SELECT) or “you may put toys in” (INSERT). SYSCOLAUTH is a sticker on one toy only. SYSDBAUTH is a sticker on the whole classroom. SYSRESAUTH is a sticker on special furniture (buffer pools, collections). SYSUSERAUTH is a principal badge that says “this person may do almost everything.” SYSROUTINEAUTH is a sticker on a kitchen machine (procedure). Y means you may; G means you may and you may give stickers to friends; blank means this sticker is not for that action.

Exercises

1. Run IBM’s SYSTABAUTH query with USER and PUBLIC. Compare the list to tables you actually use.
2. Find every GRANTEE with SYSADMAUTH in ('Y','G') on SYSUSERAUTH.
3. Explain a case where SYSTABAUTH UPDATEAUTH is blank but the user can still UPDATE one column.
4. Query SYSROUTINEAUTH for a schema and list who can EXECUTE each SPECIFICNAME.
5. Write two sentences on why RACF Db2 classes can make these tables incomplete.