Authorization IDs and current registers in DB2

Authorization IDs

An authorization ID is a short name Db2 associates with a process that issues SQL. Privileges are granted to authorization IDs (and roles), not to “the terminal” or “the COBOL program” as physical objects. When your batch job, CICS transaction, or TSO session talks to Db2, the subsystem already knows which IDs belong to that process.

Primary authorization ID

Every process has exactly one primary authorization ID. It identifies the process in traces and is the usual starting point for privileges. For a process started through the TSO attachment facility, the primary ID is typically identical to the TSO logon ID. Other attachments (batch, CICS, DDF) obtain a primary ID through connection and security exit rules at your site.

Think of the primary ID as your badge number at the door. Logs and performance traces use it so DBAs can see which identity did the work. It is not always the ID that owns every object you create—ownership and dynamic privilege checks also involve the SQL ID and schema rules described below.

Secondary authorization IDs

Secondary authorization IDs are optional additional IDs available to the same process. In many shops they come from RACF groups. If group APPLDEV has SELECT on a table, and your user is connected with that group as a secondary ID, you can use that privilege while signed on as yourself.

Secondary IDs are how teams share access without sharing passwords: grant to the group (secondary ID), connect users to the group, and individuals keep their own primary IDs for auditing. You cannot invent an arbitrary secondary ID at will—the security exit and RACF (or equivalent) decide which secondaries a process receives.

SQL ID versus roles

Among the primary and secondary IDs, one is designated the SQL ID—the value of the CURRENT SQLID special register. That ID is the one exercised for certain dynamic SQL authorization checks. Separately, in a trusted context, a process can also use a role. Roles carry privileges granted to the role name; CURRENT SQLID itself is not set to a role.

Beginners often confuse “my TSO ID,” “my RACF group,” and “the schema of the table.” Keep three labels separate: identity (auth IDs), privilege holder (auth ID or role), and name qualifier (schema).

CURRENT SQLID

CURRENT SQLID is the special register that holds the current SQL authorization ID. Initially it is usually your primary ID (unless an authorization exit sets something else). You can change it during a session:

1
2
SET CURRENT SQLID = 'HRTEAM';
SET CURRENT SQLID = USER;   -- back to primary ID

Unless an authorization ID of the process has SYSADM authority (and site parameters such as SEPARATE SECURITY allow broader choices), the new SQL ID must be the primary ID or one of the secondary IDs of the process. That rule prevents you from impersonating someone else’s ID just by typing SET CURRENT SQLID.

Why change SQLID? Common reasons:

• Create objects under a team ID — so ownership matches the group that will maintain the objects
• Exercise group privileges — when a secondary ID holds the GRANT you need for dynamic SQL
• Match shop standards — many sites require dynamic work to run under a designated application SQLID

Static SQL embedded in a program is authorized mainly through the privileges of the plan or package owner (and related bind options), not by flipping CURRENT SQLID at run time the same way interactive dynamic SQL does. That is why a COBOL program can SELECT from a table even when your personal ID lacks SELECT—if the package owner was granted access and you are allowed to execute the package.

CURRENT SCHEMA

A schema is the qualifier in a two-part name such as HR.EMPLOYEE. When you write an unqualified name in dynamic SQL—SELECT * FROM EMPLOYEE—Db2 must pick a schema. That default comes from CURRENT SCHEMA for applicable dynamic statements.

1
2
3
SET CURRENT SCHEMA = 'HR';
SELECT EMPNO, LASTNAME FROM EMPLOYEE;
-- resolves as HR.EMPLOYEE for this dynamic statement

Important beginner facts:

• CURRENT SCHEMA mainly affects dynamic SQL — static CREATE and references often use bind QUALIFIER and ownership rules instead
• Setting SCHEMA does not update PATH — if you need functions in schema HR resolved by path search, include HR in CURRENT PATH separately
• SQLID and SCHEMA can differ — on Db2 for z/OS, qualification and ownership rules depend on the object type when the two registers disagree; always be deliberate when you set them to different values

In some distributed Db2 documentation, CURRENT SQLID is treated as a synonym of CURRENT SCHEMA. On Db2 for z/OS, treat them as related but not identical ideas: SQLID is about authorization identity; SCHEMA is about name qualification. Read z/OS manuals when your shop’s behavior matters.

CURRENT PATH

CURRENT PATH (the dynamic SQL path) is an ordered list of schema names. Db2 walks that list to resolve unqualified references to functions, procedures, distinct types, and similar objects in dynamic SQL—contexts where a simple “default schema for tables” is not enough.

1
2
SET PATH = SYSIBM, SYSFUN, SYSPROC, HR, PAYROLL;
VALUES CURRENT PATH;

If you omit built-in schemas such as SYSIBM, SYSFUN, SYSPROC, and SYSIBMADM, Db2 still assumes them at the front of the path in a defined order. Your application schemas usually appear after those system schemas so built-in functions keep winning name resolution unless you intentionally override path order.

For static SQL, the bind-time PATH option plays the analogous role. Dynamic and static paths can differ, which surprises people who test a function in SPUFI (dynamic) and then see different resolution inside a bound package (static).

How the three registers work together

A practical session might look like this:

1
2
3
4
5
6
7
8
9
10
11
-- Act with team privileges for dynamic SQL
SET CURRENT SQLID = 'APPTEAM';
 
-- Qualify unqualified tables under the APP schema
SET CURRENT SCHEMA = 'APP';
 
-- Resolve unqualified UDFs / procedures from APP after system schemas
SET PATH = SYSTEM PATH, APP;
 
SELECT COUNT(*) FROM ORDERS;
CALL REFRESH_CACHE();

Reading VALUES CURRENT SQLID, CURRENT SCHEMA, and CURRENT PATH (or equivalent displays in your tool) is the fastest way to debug “wrong table” and “function not found” problems in dynamic SQL.

Explain It Like I'm Five

Imagine a clubhouse. Your name tag is your authorization ID—it proves who you are and which rooms you may enter. Sometimes you also wear a team badge (a secondary ID) so you can use the team’s keys. CURRENT SQLID is which badge you are holding up when you ask for a key right now. CURRENT SCHEMA is the default drawer label when you say “get the folder named EMPLOYEE” without saying which cabinet. CURRENT PATH is the list of cabinets you search, in order, when you ask for a special tool (a function) and only give its short name. Badges, drawer labels, and search lists are different—even though they often start with the same word as your name.

Exercises

1. List your primary authorization ID and any secondary IDs available in a training Db2 subsystem (ask a DBA if you cannot display them). Which one is CURRENT SQLID right after you connect?
2. In a dynamic SQL tool, display CURRENT SCHEMA, change it with SET CURRENT SCHEMA, then SELECT from an unqualified table name. Explain which two-part name you hit.
3. Why might SET CURRENT SQLID = 'BOSS' fail even if you know that user exists?
4. After SET CURRENT SCHEMA = 'HR', why might an unqualified user-defined function still not resolve until you also change CURRENT PATH?
5. Explain one difference between authorizing a static COBOL package and authorizing a dynamic SELECT you type interactively.