DB2 Security

DB2 security controls who can connect to the database and what each user is allowed to do. It works in layers, from signing on down to individual tables.

Layers of DB2 security

• Authentication :- Proves who the user is at sign-on, usually handled by RACF, ACF2, or Top Secret on z/OS.
• Authorization :- Decides what the verified user may do. DB2 checks privileges stored in its catalog tables.
• System privileges :- Big powers like SYSADM, SYSCTRL, DBADM, and DBCTRL that manage whole subsystems or databases.
• Object privileges :- Fine-grained rights like SELECT, INSERT, UPDATE, DELETE on a specific table or EXECUTE on a procedure.

Authorization IDs and roles

• Every SQL statement runs under an authorization ID, which is usually the TSO userid or the batch job user.
• SET CURRENT SQLID lets a program switch to a secondary ID, so one ID can own objects while another runs the program.
• Roles group privileges together, so granting a role to many users is easier than granting each privilege one by one.
• Trusted contexts allow a middle-tier server to switch user identity safely without sharing passwords.

Example: switching the current SQL ID

• A program can switch to the object-owner ID before accessing tables:-
  EXEC SQL SET CURRENT SQLID = 'PAYROLL' END-EXEC. EXEC SQL SELECT SALARY INTO :HV-SAL FROM EMPLOYEE WHERE EMPNO = :HV-EMPNO END-EXEC.
• All following SQL in the session is checked against the PAYROLL ID until it is switched back.
• The ID being switched to must be one of the user's secondary authorization IDs.

Security best practices

• Grant the smallest privilege needed. Application IDs get SELECT on the tables they read, not DBADM.
• Never share one powerful ID across many applications; use separate IDs so activity can be audited per application.
• Review SYSIBM.SYSTABAUTH and SYSIBM.SYSRESAUTH regularly for privileges nobody uses any more.
• Protect production data in test by masking or scrambling sensitive columns before copying them down.