Authorization IDs in DB2 for z/OS

Db2 security architecture

z/OS security is a partnership, not a single switch.

• Authentication — proving who you are (password, PassTicket, certificate). This is almost always RACF (or ACF2/Top Secret) before Db2 trusts the name
• Connection and sign-on exits — Db2 modules that set the primary ID, SQL ID, and optional secondary ID list when a process connects or a transaction signs on
• Authorization — Db2 catalog privileges (GRANT/REVOKE), administrative authorities (SYSADM, SECADM, DBADM, and the rest), ownership, and optionally roles in a trusted context
• External access control — optional exit DSNX@XAC so RACF profiles can replace or supplement some SQL authorization checks
• Data access control — row permissions and column masks, which are a later topic; they still evaluate using authorization IDs and roles

A process that is not authenticated never gets a usable primary ID. A process that is authenticated but has no GRANT and no covering authority fails SQL with a privilege error (often SQLCODE -551 or -552). Mixing those layers is how shops keep TSO users, CICS transactions, batch jobs, and DDF clients on different privilege paths while sharing one subsystem.

SEPARATE_SECURITY changes who may grant security-related authorities. When it is YES, SECADM (not SYSADM) owns many GRANT/REVOKE and row/column access control duties. That split is part of architecture, not a trivia option.

Authorization IDs

When IDs are assigned, every process receives exactly one primary authorization ID. All other IDs on that process are secondary authorization IDs. One ID from that set—primary or secondary—is the current SQL ID. You can change the SQL ID during the session with SET CURRENT SQLID.

An authorization ID is a Db2 name up to eight characters in the usual short form (the same length many TSO IDs use). It is recorded on objects as OWNER, on GRANTs as GRANTEE and GRANTOR, and in traces as the primary ID that identifies the process.

Primary authorization ID

The primary authorization ID generally identifies the process.

• TSO attachment — typically identical to the TSO logon ID
• Batch — the USER on the JOB statement (subject to the exit)
• CICS / IMS — the ID presented at connect or at transaction sign-on
• DDF — the ID from the DRDA security handshake after the exit runs

Statistics and performance traces use the primary ID to identify the process. Package OWNER can be a different ID (the binder). Do not assume OWNER = primary ID of the person who runs the program; EXECUTE on the package is how end users run someone else’s privileges.

The connection exit can change the incoming name before it becomes the primary ID. That is how some shops map a long distributed name to an eight-character Db2 ID.

Secondary authorization IDs

A secondary authorization ID is optional. It holds additional privileges available to the process. The usual source is a RACF group connected to the user. If PAYROLL is a RACF group and a secondary ID, then GRANT SELECT ON HR.EMP TO PAYROLL lets every member of that group SELECT, without a GRANT per TSO ID.

IBM’s recommendation is exactly that: grant to a secondary ID or to a role rather than to many primary IDs. Assignment of which groups become secondary IDs happens in the exit, not in SQL. You do not GRANT a secondary ID into existence; RACF (or the exit) supplies the list, up to the documented maximum (on the order of a thousand IDs).

Default IBM connection/sign-on exits do not return secondary IDs. If your shop wants RACF groups as secondary IDs, you install the IBM sample exits (source DSN3SATH / DSN3SSGN, executable names DSN3@ATH / DSN3@SGN) or a vendor/customized exit, and you assemble them into the Db2 exit library.

1
2
3
4
5
-- Privileges on the group, not on each person:
GRANT SELECT, INSERT ON TABLE HR.EMPLOYEE TO PAYROLL;
 
-- A user whose secondary IDs include PAYROLL can now SELECT/INSERT.
-- Their primary ID (for example STEVE) does not need its own GRANT.

Connection versus sign-on

DSN3@ATH (connection) runs when a TSO user, batch job, or address space connects to Db2. DSN3@SGN (sign-on) runs when a user in a multi-user address space (CICS, IMS) wants Db2 resources. Sign-on is how two CICS transactions on the same region get different primary IDs. Both exits can return secondary lists. If they do not, you only have the primary ID to GRANT to.

SQL ID and SET CURRENT SQLID

The SQL authorization ID (SQL ID) holds privileges exercised when the process issues certain dynamic SQL statements. It also participates in implicit schema qualification for some CREATE statements in dynamic SQL. Static SQL uses bind options (QUALIFIER, PATH, DYNAMICRULES) and the package owner instead.

1
2
3
4
SET CURRENT SQLID = 'PAYROLL';
 
SELECT CURRENT SQLID, CURRENT SCHEMA, CURRENT PATH
  FROM SYSIBM.SYSDUMMY1;

• You can set CURRENT SQLID to the primary ID or to a secondary ID
• If the process has SYSADM and SEPARATE_SECURITY=NO, it can set SQLID to any authorization ID
• If SEPARATE_SECURITY=YES, SYSADM can set SQLID only to the primary or a secondary ID of that process
• Those rules apply even when SET CURRENT SQLID is static
• CURRENT SQLID cannot be set to a role

CURRENT SCHEMA and CURRENT PATH are related session values but they are not authorization IDs. SCHEMA qualifies unqualified objects. PATH resolves functions, procedures, and distinct types. Confusing SQLID with SCHEMA is a common source of “I created the table in the wrong schema” tickets.

Roles compared with authorization IDs

A role exists inside a trusted context. When the connection matches that context, the process can inherit privileges granted to the role. Roles are the modern way to avoid shared primary IDs for applications. They do not replace primary/secondary IDs; they add another grantee type. GRANT TO ROLE PAYROLL_APP is not the same as SET CURRENT SQLID = PAYROLL_APP.

How this shows up in daily SQL

Privilege checks ask: does the primary ID, any secondary ID, the current role (if any), PUBLIC, or a covering authority allow this statement? Ownership is another source: the owner of a table can GRANT on it. Installation SYSADM IDs are a special pair set at install time and are not ordinary GRANT targets you should use for applications.

When a dynamic CREATE TABLE has an unqualified name, the qualifier often comes from CURRENT SCHEMA (or older CURRENT SQLID behavior depending on statement and settings). When you debug “who am I?”, start with:

1
2
3
4
SELECT CURRENT SQLID AS SQLID,
       SESSION_USER AS SESSION_USER,
       CURRENT SCHEMA AS SCHEMA
  FROM SYSIBM.SYSDUMMY1;

SESSION_USER is the primary authorization ID. CURRENT SQLID may differ if you SET it to a secondary ID. Catalog views such as SYSIBM.SYSUSERAUTH and SYSIBM.SYSTABAUTH show what those IDs were granted—remember the GRANTEE might be a group, not your TSO ID.

Explain It Like I'm Five

Your primary authorization ID is the name tag on your shirt. Secondary IDs are club badges you also wear (the payroll club, the HR club). Db2 lets you do things that any of those badges allow. CURRENT SQLID is which badge you hold up when you fill out a form in pencil (dynamic SQL). You cannot hold up a “role hat” as SQLID; that hat only works in a special trusted doorway. RACF is the front-door guard who checks your password and lists your clubs. The connection exit is the clipboard that copies those clubs onto Db2’s list. If the clipboard is blank (default exit), you only have the shirt name tag.

Exercises

1. SELECT SESSION_USER and CURRENT SQLID. SET CURRENT SQLID to a secondary ID you hold, then SELECT again. What fails if you pick an ID that is not yours?
2. Ask your security admin whether DSN3SATH is installed. If secondary IDs are empty, what would GRANT TO a RACF group do for you?
3. Explain why granting SELECT to a group ID is usually better than granting SELECT to 40 TSO IDs.
4. Find SEPARATE_SECURITY for your subsystem (DSNTIPP1 / installation panel). How does it change SET CURRENT SQLID for SYSADM?
5. Query SYSIBM.SYSTABAUTH for a table you use and list distinct GRANTEE values. Which are people, which look like groups, and which is PUBLIC?