DB2 privileges, GRANT, and REVOKE

Privileges versus authorities

A privilege is a single right, usually on one object: SELECT on HR.EMPLOYEE, EXECUTE on a package, USE of buffer pool BP2. An authority is a named bundle. GRANT DBADM ON DATABASE PAYROLL is shorter and clearer than fifty table GRANTs plus LOAD and REORG. Beginners should still learn the individual privileges, because that is what shows up in SQLCODE -551 messages and in SYSIBM.SYSTABAUTH.

Grantees can be an authorization ID, a ROLE, or PUBLIC. Granting to a RACF group that is a secondary ID, or to a role in a trusted context, scales better than granting to every TSO user.

GRANT

Each object class has its own GRANT form. You can list several privileges, several objects, and several grantees. WITH GRANT OPTION lets the grantee pass the same privilege on. Granting SYSADM or SYSCTRL with GRANT OPTION is valid but redundant, because those authorities already imply the ability to grant.

1
2
3
4
5
6
7
8
9
10
GRANT SELECT ON DEPT TO PUBLIC;
 
GRANT UPDATE (EMPNO, WORKDEPT) ON TABLE HR.EMPLOYEE TO NATZ;
 
GRANT ALL ON TABLE HR.EMPLOYEE TO KWAN, ALONZO
  WITH GRANT OPTION;
 
GRANT CREATEIN, ALTERIN, DROPIN
  ON SCHEMA HR
  TO ROLE HR_DDL;

If you GRANT on a table, package, schema, or other object that does not exist, Db2 rejects the grant. Schema names are an exception: you can GRANT ON SCHEMA for a schema that does not exist yet, including SCHEMA *.

REVOKE

REVOKE removes an explicit grant. The same ID that granted a privilege can revoke it. If two grantors granted SELECT to STEVE, one REVOKE leaves the other grant in place. Authorities such as SECADM or ACCESSCTRL (and SYSADM/SYSCTRL when SEPARATE_SECURITY is NO) can revoke another ID’s grant with BY.

1
2
3
4
5
REVOKE SELECT ON TABLE HR.EMPLOYEE FROM NATZ;
 
REVOKE CREATETAB ON DATABASE DB1 FROM PGMR01 BY ALL;
 
REVOKE CREATETAB, CREATETS ON DATABASE DB1 FROM PGMR01 BY DBUTIL1;

Cascading revoke: if you granted WITH GRANT OPTION and then revoke, dependent grants can disappear depending on REVOKE DEP PRIV. An ID that still has the privilege from another source keeps it. Installation SYSADM IDs are protected from some cascade effects. Plan revokes before you pull SYSADM from an ID that granted half the shop.

Table and view privileges

Views get SELECT, INSERT, UPDATE, and DELETE as applicable; INDEX, REFERENCES, and TRIGGER do not apply to views. Auxiliary tables reject several of these privileges. Granting on a view is how you expose two columns of EMPLOYEE without granting SELECT on the base table.

Database privileges

GRANT ... ON DATABASE names a Db2 database (the CREATE DATABASE object), not a whole subsystem.

• DBADM — create/drop objects and access tables in that database
• DBCTRL — utilities and control without inherent user-data SELECT
• DBMAINT — maintenance-oriented utilities (image copy, runstats, display, start/stop, and related)
• CREATETAB / CREATETS — create tables or table spaces. Implicit databases need CREATETAB on DSNDB04. Work-file declared temp tables are a special PUBLIC case
• DISPLAYDB, STARTDB, STOPDB, DROP — commands and DROP/ALTER DATABASE
• IMAGCOPY, LOAD, RECOVERDB, REORG, REPAIR, STATS — named utilities

Schema privileges

CREATEIN, ALTERIN, and DROPIN on a schema control distinct types, sequences, stored procedures, triggers, and user-defined functions in that schema. GRANT ON SCHEMA * covers every schema, including future ones. SYSPUBLIC is the reserved schema for public aliases.

Index, package, plan, and collection privileges

• Index — the INDEX privilege on a table lets you create indexes; dropping follows ownership/ALTER rules
• Package — BIND, COPY, EXECUTE. EXECUTE is what application users need. BIND is for the person who binds. COPY copies the package
• Plan — BIND and EXECUTE on the application plan
• Collection — CREATE IN a collection (and PACKADM as the bundle). Packages live in collections; PACKADM on PAYROLL.* administers that collection

1
2
3
4
GRANT EXECUTE ON PACKAGE PAYROLL.PAYCALC TO ROLE PAY_USERS;
GRANT BIND, EXECUTE ON PLAN PAYPLAN TO PAYBIND;
GRANT CREATE IN COLLECTION PAYROLL TO PAYBIND;
GRANT PACKADM ON COLLECTION PAYROLL TO PAYDBA;

If a plan issues INSERT and SELECT, you grant those to the owner at bind time and grant EXECUTE to end users. That is the classic z/OS pattern: users never receive ad-hoc table privileges for production data.

Sequence, routine, variable, and type privileges

• Sequence — USAGE (NEXT VALUE / PREVIOUS VALUE) and ALTER
• Routine (functions and procedures) — EXECUTE, and related ALTER rights via schema ALTERIN. Granting on a native SQL procedure applies to all versions
• Variable — READ and WRITE on global variables
• Type — USAGE on distinct types (and JAR USAGE for Java routines)

1
2
3
4
GRANT USAGE ON SEQUENCE HR.EMPNO_SEQ TO ROLE HR_APP;
GRANT EXECUTE ON PROCEDURE HR.UPD_SALARY TO ROLE HR_APP;
GRANT READ, WRITE ON VARIABLE HR.GV_DEPT TO ROLE HR_APP;
GRANT USAGE ON TYPE HR.MONEY TO ROLE HR_DDL;

USE privileges: buffer pools, storage groups, table spaces

Creating a table space or index that uses a buffer pool, storage group, or (for tables) an existing table space requires USE of that resource, unless a covering authority implies it.

1
2
3
GRANT USE OF BUFFERPOOL BP2 TO HRDBA;
GRANT USE OF STOGROUP HRSTO TO HRDBA;
GRANT USE OF TABLESPACE HRDB.HRTS TO HRDBA;

System privileges and administrative authorities

Related system privileges include DATAACCESS (read/write user data, execute routines, use sequences and global variables), ACCESSCTRL (grant/revoke without being SECADM), BINDAGENT, ARCHIVE, CREATESG, CREATETMTAB, CREATE_SECURE_OBJECT, DISPLAY, RECOVER, STOPALL, STOSPACE, and TRACE. GRANT them ON SYSTEM.

1
2
3
GRANT SYSCTRL ON SYSTEM TO OPSLEAD;
GRANT SQLADM ON SYSTEM TO TUNER1;
GRANT DBADM ON DATABASE PAYROLL TO PAYDBA;

When SEPARATE_SECURITY=YES, only SECADM (not SYSADM) grants ACCESSCTRL, System DBADM, DATAACCESS, and CREATE_SECURE_OBJECT, and only SECADM activates row or column access control. Know that parameter before you copy a GRANT script from an older subsystem.

Ownership and TRANSFER OWNERSHIP

The owner of an object holds implicit privileges on it, including the ability to GRANT. Dynamic CREATE typically makes the SQL authorization ID the owner (subject to schema rules). Static CREATE follows bind QUALIFIER/owner rules.

TRANSFER OWNERSHIP moves ownership to another ID or role so you are not stuck with a departed employee as OWNER. Revoking SYSADM from that employee does not always change OWNER; transfer explicitly.

1
2
3
TRANSFER OWNERSHIP OF TABLE HR.EMPLOYEE
  TO ROLE HR_OWNER
  REVOKE PRIVILEGES;

REVOKE PRIVILEGES on the transfer drops the old owner’s implicit rights. Omit it only when you intend the old owner to keep access.

PUBLIC

PUBLIC means every user at the current server. Catalog and sample objects sometimes come with PUBLIC SELECT. Production employee tables should not. Implicit PUBLIC CREATETAB on a work-file database for declared temporary tables is not a normal catalog grant you REVOKE.

Explain It Like I'm Five

Privileges are permission slips. GRANT writes a slip that says “Nat may update these two columns.” REVOKE tears up a slip you wrote. If two teachers both wrote a slip, you must tear up both. PUBLIC is a slip taped to the classroom door for everyone. Authorities are a whole binder of slips (the “database teacher” binder). Ownership is “this is my backpack,” which includes the right to hand out slips about that backpack. TRANSFER OWNERSHIP gives the backpack to someone else. EXECUTE on a plan is a ticket to ride a bus the driver already has licenses for—you do not need your own driver’s license for every street (table) the bus uses.

Exercises

1. Grant SELECT on a scratch table to a classmate, WITH GRANT OPTION. Have them grant SELECT to a third ID. Revoke your grant and see whether the third ID still has SELECT (depends on REVOKE DEP PRIV and other sources).
2. List every privilege needed to CREATE TABLE in a table space you do not own (database CREATETAB, USE of the table space and buffer pool/stogroup, schema rights if using UDTs).
3. Write GRANT EXECUTE ON PACKAGE for an application collection and explain why the package owner still needs table privileges at BIND.
4. Compare SYSCTRL and SYSADM: which one can SELECT from HR.EMPLOYEE without an extra GRANT?
5. Query SYSIBM.SYSTABAUTH, SYSIBM.SYSUSERAUTH, and SYSIBM.SYSPACKAUTH for your ID and for PUBLIC. Note grantor versus grantee.