Roles and administrative authorities in DB2

What a role is (and is not)

IBM’s wording is precise: a privilege lets an ID run certain SQL or touch another user’s objects. A role groups those privileges so you can grant and revoke them as a set. The role is created with SQL. It is not a RACF group, not a Unix group, and not a CICS transaction class.

A role cannot be used outside a trusted context, with one documented exception: a user who currently has a role can GRANT privileges from that role to an authorization ID. Day to day, if there is no trusted connection, there is no role on the process.

Role names must not begin with SYS and must not be DBADM, NONE, NULL, PUBLIC, or SECADM. Those names are reserved so they cannot collide with authorities and special IDs.

CREATE ROLE

The statement is intentionally tiny. You create an empty named bucket, then GRANT privileges to it the same way you GRANT to a user.

1
2
3
4
5
6
CREATE ROLE CTXROLE;
CREATE ROLE TELLER;
CREATE ROLE PAYROLL_APP;
 
GRANT SELECT, INSERT ON TABLE BANK.ACCOUNT TO ROLE TELLER;
GRANT EXECUTE ON PACKAGE BANKAPP.ACCT01 TO ROLE TELLER;

Authorization to issue CREATE ROLE is SECADM, and also SYSADM or SYSCTRL when those authorities are still allowed to manage security objects. If the subsystem parameter SEPARATE_SECURITY is YES, ordinary SYSADM and SYSCTRL lose that ability; SECADM (and installation SYSADM) remain. Dynamic CREATE ROLE requires DYNAMICRULES RUN behavior.

If the statement is embedded, the owner of the plan or package needs the authority. If it is dynamic inside a trusted context defined with ROLE AS OBJECT OWNER, the role associated with the primary ID is checked instead of CURRENT SQLID.

DROP ROLE

1
DROP ROLE TELLER;

Drop fails if the role still owns objects or is still named on a trusted context (default role or a WITH USE FOR assignment). Typical cleanup order:

• TRANSFER OWNERSHIP of objects the role owns, or drop those objects
• ALTER TRUSTED CONTEXT to remove DEFAULT ROLE or DROP USE FOR the users who pointed at the role
• Then DROP ROLE

Revoking privileges from the role is not the same as dropping it. An empty role can still be assigned on a context; users would connect successfully and simply gain no extra privilege from that role.

Role membership

Db2 for z/OS does not use GRANT role TO user as the membership mechanism. Membership is a property of a trusted context.

1
2
3
4
5
6
7
8
9
CREATE TRUSTED CONTEXT BANK_CICS
  BASED UPON CONNECTION USING SYSTEM AUTHID CICSPROD
  ATTRIBUTES (JOBNAME 'CICSPROD')
  DEFAULT ROLE TELLER
  ENABLE
  WITH USE FOR
    ALICE ROLE TELLER,
    BOB   ROLE TELLER WITH AUTHENTICATION,
    PUBLIC;

Three membership styles:

• DEFAULT ROLE — every user of this context who does not have a more specific assignment gets this role
• WITH USE FOR authid ROLE role-name — that primary authorization ID gets this role, which overrides the default
• EXTERNAL SECURITY PROFILE — any primary ID permitted to a named RACF profile can use the context; you can attach a role to that profile

PUBLIC on WITH USE FOR means any user of the trusted connection uses the default role (or no role if there is no default). Precedence is: specific authorization name, then external security profile, then PUBLIC.

On a trusted connection, for ordinary SQL, Db2 considers privileges of the role plus privileges of the authorization ID. CREATE, GRANT, and REVOKE are stricter: they use the role if ROLE AS OBJECT OWNER is in effect, otherwise the authorization ID — not both stacked the same way. That stops people from mixing “I am a teller role” with “I personally have SYSADM” in confusing grantor names.

Role inheritance (what people mean, and what Db2 does)

Other database products let role A contain role B. Db2 for z/OS does not implement that hierarchy. “Inheritance” in this product means:

• Privilege inheritance from the role to the session — while the trusted connection is active, you inherit the role’s grants
• Default versus user-specific assignment — the user-specific role replaces the default; it does not stack two roles
• Object ownership inheritance — with WITH ROLE AS OBJECT OWNER AND QUALIFIER, new objects, GRANTs, and REVOKEs belong to the role, so the next person in that job inherits the objects without TRANSFER OWNERSHIP from a departed employee

1
2
3
4
5
6
CREATE TRUSTED CONTEXT WAS_PROD
  BASED UPON CONNECTION USING SYSTEM AUTHID WASPROD
  ATTRIBUTES (JOBNAME 'WASPROD')
  DEFAULT ROLE PAYROLL_APP WITH ROLE AS OBJECT OWNER AND QUALIFIER
  ENABLE
  WITH USE FOR SALLY;

In that context the role is also the default CURRENT SCHEMA and is substituted into the SQL PATH in place of CURRENT SQLID. Without ROLE AS OBJECT OWNER, the process authorization ID owns created objects and must hold the creation privileges personally.

If you need a hierarchy of people, use RACF group nesting for secondary IDs, or issue several GRANTs to several roles and assign the right role on the right trusted context. Do not expect CREATE ROLE MANAGER LIKE TELLER.

Administrative authorities next to roles

A role is a container you invent. An administrative authority is a named bundle IBM already defined. You GRANT SYSADM, SECADM, DBADM, PACKADM, and the others to an ID or to a role. Putting SYSADM on a role that is only available in a tightly constrained trusted context is a common hardening pattern: the authority exists, but not from every TSO session.

SECADM is the authority that owns role DDL when security is separated. It can create, drop, and comment on roles and trusted contexts; create and activate row permissions and column masks; create audit policies in SYSIBM.SYSAUDITPOLICIES; and grant or revoke grantable privileges. It does not include inherent SELECT on user tables. SYSADM, by contrast, can read data unless you turn SEPARATE_SECURITY to YES (and even then SYSADM remains powerful for non-security work).

Database-level authorities (DBADM, DBCTRL, DBMAINT) still apply to a database. You can GRANT DBADM ON DATABASE PAYROLL TO ROLE PAYROLL_APP so the application role can manage objects in that database only while the trusted connection is up.

SEPARATE_SECURITY in one paragraph

Default NO: SYSADM keeps implicit SECADM capability, including managing roles and granting privileges granted by others. YES: SYSADM cannot manage roles, trusted contexts, permissions, or masks, and cannot grant ACCESSCTRL, system DBADM, DATAACCESS, or CREATE_SECURE_OBJECT — SECADM does. Installation SYSADM is unchanged either way, which is why that ID is an audit hotspot.

Roles versus RACF groups versus PUBLIC

• RACF group — secondary AUTHID, available on ordinary connections, good for “everyone in payroll”
• Db2 role — extra privilege only in a trusted place (this CICS region, this WAS job, this SSL client IP)
• PUBLIC — everyone, including IDs you have not thought about; use sparingly for EXECUTE on a plan
• Administrative authority — coarse IBM-defined power; grant to few IDs or to a role that is hard to reach

VERIFY_ROLE_FOR_USER in a row permission or column mask tests the role that is in effect on the current trusted connection. VERIFY_GROUP_FOR_USER tests RACF group membership (secondary IDs). Use the function that matches how you assigned access.

Explain It Like I'm Five

A role is a costume in a play. CREATE ROLE sews the costume. GRANT pins tools onto it (the SELECT badge, the EXECUTE badge). You are only allowed to wear the costume on a special stage (a trusted context). On the playground (ordinary TSO) you are just yourself. Db2 does not let one costume contain another costume. If the stage rule says “the costume owns the toys,” then when you build a new toy on that stage, the costume owns it — so the next actor who wears the costume already has the toy.

Exercises

1. Write CREATE ROLE statements for APP_READ and APP_WRITE, then GRANT suitable table privileges to each role.
2. Sketch a trusted context that gives ALICE APP_WRITE and everyone else APP_READ as the default. Which assignment wins for ALICE?
3. Check SEPARATE_SECURITY at your site (DSNTIPP1 / ZPARM). Who is allowed to CREATE ROLE?
4. Explain why DROP ROLE fails if the role is still DEFAULT ROLE on a trusted context.
5. Decide whether a RACF group or a Db2 role is the better fit for “any batch job submitted by payroll staff” versus “only the CICS region named CICSPROD.”