Granular schema-level authorities in Db2 11.5.5

In versions prior to Db2 11.5.5, granting a user broad access to all objects within a schema was difficult to do surgically. The practical alternative was to assign DBADM or SECADM, authorities that grant access far beyond what is actually needed. Db2 11.5.5 addresses this with a set of schema-level authorities and privileges designed to implement the Principle of Least Privilege in a straightforward way.

This article is deliberately practical. Every step below shows the exact commands to run and the checks to perform. The lab is designed to be reproduced on a Db2 11.5.5 or later instance.

The problem with the previous model

Access control in Db2 rests on two distinct concepts: authorities, which operate at the instance or database level, and privileges, which operate at the individual object level.

The classic problem arose when a team needed to manage or access all objects within a specific schema. The available options were:

• Assign DBADM, which gives control over the entire database
• Assign SECADM, which gives control over the entire security policy
• Grant privileges object by object, which is operationally heavy and hard to maintain as the schema grows

None of these options is good. The first two violate the Principle of Least Privilege. The third is correct in theory but impractical when a schema has dozens or hundreds of objects.

What this lab builds

By the end of the lab you will have:

• a test database with a populated schema
• four users with distinct access profiles
• a practical demonstration of each new schema authority
• a demonstration of the new schema privileges using a single GRANT
• verification tests confirming what each user can and cannot do
• an audit query against SYSCAT.SCHEMAAUTH

Prerequisites

Use a Db2 instance at version 11.5.5 or later.

You also need:

• Shell access with DBADM or SECADM permissions to create users and grant authorities
• The Db2 CLP

The operating system users referenced in the lab must exist on the system before running the GRANT statements. Run the following commands as root to create them:

useradd -m alice   && echo "alice:passw0rd"   | chpasswd
useradd -m thomas  && echo "thomas:passw0rd"  | chpasswd
useradd -m etl     && echo "etl:passw0rd"     | chpasswd
useradd -m secmgr  && echo "secmgr:passw0rd"  | chpasswd

1. Create the test database and schema

The following steps must be run by the Db2 instance owner.

Create and connect to the test database:

db2 create database SCHLAB
db2 connect to SCHLAB

The procedure definition requires a multi-statement terminator. Save the DDL to a file and run it with db2 -td@:

cat > /tmp/schlab_ddl.sql <<'SQLEOF'
CREATE SCHEMA sales@

CREATE TABLE sales.customers (
  id        INTEGER NOT NULL,
  name      VARCHAR(100),
  email     VARCHAR(100),
  PRIMARY KEY (id)
)@

CREATE TABLE sales.orders (
  id          INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY,
  customer_id INTEGER,
  amount      DECIMAL(10,2),
  PRIMARY KEY (id)
)@

CREATE PROCEDURE sales.register_order (IN p_customer INTEGER, IN p_amount DECIMAL(10,2))
LANGUAGE SQL
BEGIN
  INSERT INTO sales.orders (customer_id, amount)
  VALUES (p_customer, p_amount);
END@
SQLEOF
db2 -td@ -f /tmp/schlab_ddl.sql

Insert some test data:

db2 "INSERT INTO sales.customers VALUES (1, 'Alice Martin', 'alice@example.com')"
db2 "INSERT INTO sales.customers VALUES (2, 'Thomas Chen', 'thomas@example.com')"
db2 "INSERT INTO sales.orders (customer_id, amount) VALUES (1, 150.00)"
db2 "INSERT INTO sales.orders (customer_id, amount) VALUES (2, 320.50)"
db2 commit

2. Set up the user profiles

We will work with four users representing real-world profiles:

Grant CONNECT to the database for all four:

db2 "GRANT CONNECT ON DATABASE TO USER alice"
db2 "GRANT CONNECT ON DATABASE TO USER etl"
db2 "GRANT CONNECT ON DATABASE TO USER thomas"
db2 "GRANT CONNECT ON DATABASE TO USER secmgr"

3. SELECTIN: read access across the full schema

Before this feature, granting read access to all objects in a schema required one GRANT SELECT per table and View. Now:

db2 "GRANT SELECTIN ON SCHEMA sales TO USER alice"

Verify as alice:

db2 connect to SCHLAB user alice using passw0rd
db2 "SELECT * FROM sales.customers"
db2 "SELECT * FROM sales.orders"

Both queries should succeed. Confirm that alice cannot insert data:

db2 "INSERT INTO sales.customers VALUES (3, 'Test User', 'test@example.com')"

This should fail with SQL0551N: alice has neither INSERTIN nor a direct INSERT on the table.

End the session:

db2 connect reset

4. INSERTIN and EXECUTEIN: ETL pipeline

User etl needs to insert data and call the stored procedure, but should not be able to read existing data:

db2 connect to SCHLAB
db2 "GRANT INSERTIN ON SCHEMA sales TO USER etl"
db2 "GRANT EXECUTEIN ON SCHEMA sales TO USER etl"

Verify as etl:

db2 connect to SCHLAB user etl using passw0rd
db2 "INSERT INTO sales.customers VALUES (3, 'New Customer', 'new@example.com')"
db2 "CALL sales.register_order(3, 99.90)"
db2 commit

Both should succeed. Confirm that etl cannot read data:

db2 "SELECT * FROM sales.customers"

This should fail with SQL0551N.

End the session:

db2 connect reset

5. SCHEMAADM: full control over the schema

SCHEMAADM is the most broad authority. It grants full control over the schema without giving access to the rest of the database:

db2 connect to SCHLAB
db2 "GRANT SCHEMAADM ON SCHEMA sales TO USER thomas"
db2 "GRANT USE OF TABLESPACE USERSPACE1 TO USER thomas"

SCHEMAADM grants authority over schema objects but does not include tablespace access. The GRANT USE OF TABLESPACE is necessary for thomas to create new tables. Without it, CREATE TABLE fails with SQL0286N.

Verify as thomas:

db2 connect to SCHLAB user thomas using passw0rd
db2 "CREATE TABLE sales.campaigns (id INTEGER NOT NULL, name VARCHAR(100), PRIMARY KEY (id))"
db2 "GRANT SELECT ON TABLE sales.campaigns TO USER alice"
db2 "RUNSTATS ON TABLE sales.customers"

All of these should succeed. Confirm that thomas has no access outside the schema:

db2 "CREATE TABLE public.test (id INTEGER)"

This should fail: thomas has no authority over other schemas.

End the session:

db2 connect reset

6. ACCESSCTRL: permission management without data access

ACCESSCTRL at the schema level lets a user grant and revoke permissions on schema objects without giving them direct access to the data:

db2 connect to SCHLAB
db2 "GRANT ACCESSCTRL ON SCHEMA sales TO USER secmgr"

Verify as secmgr:

db2 connect to SCHLAB user secmgr using passw0rd
db2 "GRANT SELECT ON TABLE sales.orders TO USER alice"
db2 "REVOKE SELECT ON TABLE sales.orders FROM USER alice"

Both operations should succeed. Confirm that secmgr cannot read the data:

db2 "SELECT * FROM sales.customers"

This should fail with SQL0551N.

End the session:

db2 connect reset

7. Audit schema authorizations

Schema-level grants are visible in the SYSCAT.SCHEMAAUTH view. Query the current state:

db2 connect to SCHLAB
cat > /tmp/schauth.sql <<'SQLEOF'
SELECT
  GRANTEE,
  GRANTEETYPE,
  SCHEMANAME,
  SELECTINAUTH,
  INSERTINAUTH,
  UPDATEINAUTH,
  DELETEINAUTH,
  EXECUTEINAUTH,
  ALTERINAUTH,
  SCHEMAADMAUTH,
  ACCESSCTRLAUTH,
  DATAACCESSAUTH,
  LOADAUTH
FROM SYSCAT.SCHEMAAUTH
WHERE SCHEMANAME = 'SALES'
ORDER BY GRANTEE
;
SQLEOF
db2 -tvf /tmp/schauth.sql

Each column shows Y (direct grant), G (grant with the ability to propagate) or blank (no grant). This query is the natural starting point for a schema access audit.

8. Revoke authorizations

All grants are reversible with REVOKE:

db2 "REVOKE SELECTIN ON SCHEMA sales FROM USER alice"
db2 "REVOKE INSERTIN ON SCHEMA sales FROM USER etl"
db2 "REVOKE EXECUTEIN ON SCHEMA sales FROM USER etl"
db2 "REVOKE SCHEMAADM ON SCHEMA sales FROM USER thomas"
db2 "REVOKE USE OF TABLESPACE USERSPACE1 FROM USER thomas"
db2 "REVOKE ACCESSCTRL ON SCHEMA sales FROM USER secmgr"

Confirm the state after revocation with the same SYSCAT.SCHEMAAUTH query.

9. Clean up

Drop the test database and remove the OS users created in the prerequisites:

db2 connect reset
db2 drop database SCHLAB

Remove the OS users as root:

userdel -r alice
userdel -r thomas
userdel -r etl
userdel -r secmgr

Key takeaways

• Db2 11.5.5 introduces schema authorities (SCHEMAADM, ACCESSCTRL, DATAACCESS, LOAD) that allow delegating full or partial control over a schema without assigning DBADM or SECADM.
• The new schema privileges (SELECTIN, INSERTIN, UPDATEIN, DELETEIN, EXECUTEIN) eliminate the need to grant permissions object by object on large schemas.
• The model allows implementing the Principle of Least Privilege in a practical and auditable way.
• The visibility of schema authorizations is available in SYSCAT.SCHEMAAUTH and authorizations can be removed via REVOKE.

Useful IBM documentation

For deeper reading, the most useful IBM references for this topic are:

• Authorities overview
• Schema privileges and authorities
• Using the system catalog for security information
• REVOKE (schema privileges and authorities)