Table of Contents

1. Overview
2. Prerequisites
3. Understanding Repository Authentication
4. Configuration Steps
5. Role Mapping Methods
   • Method 1: Db2 Authorities
   • Method 2: Db2 Groups
   • Method 3: Db2 UDF (User-Defined Functions)
   • Method 4: Db2 Roles
6. Testing and Verification
7. Best Practices
8. Troubleshooting

Overview

Repository authentication in IBM Db2 Genius Hub allows you to delegate both authentication and authorization to your repository database. This approach provides centralized user management and leverages your existing database security infrastructure.

Key Benefits

• Centralized Management: Manage users in one place (the repository database)
• Flexible Authentication: Repository database can delegate to LDAP, Kerberos, or other systems
• Granular Control: Map database authorities, groups, roles, or UDFs to console privileges
• Security: Leverage existing database security mechanisms

How It Works

When a user attempts to log into Db2 Genius Hub with repository authentication enabled:

1. The console creates a JDBC connection using the user's credentials
2. The repository database authenticates the user (or delegates to LDAP/Kerberos)
3. The console evaluates role mapping rules to determine console privileges
4. If the user matches at least one rule, they gain access with the corresponding role

Prerequisites

Before configuring repository authentication, ensure:

• Repository Database Configured: Your repository database is properly set up in Db2 Genius Hub
• Database Access: You have DBADM or SECADM authority on the repository database
• User Accounts: Database user accounts are created for console users
• Network Connectivity: Console server can connect to the repository database
• Backup Admin Access: Keep at least one setup admin account as backup

Understanding Repository Authentication

Authentication Flow

┌─────────────┐
│   User      │
│  Attempts   │
│   Login     │
└──────┬──────┘
       │
       ▼
┌─────────────────────────────────┐
│  Db2 Genius Hub Console         │
│  Creates JDBC Connection        │
│  with User Credentials          │
└──────┬──────────────────────────┘
       │
       ▼
┌─────────────────────────────────┐
│  Repository Database            │
│  Authenticates User             │
│  (or delegates to LDAP/Kerberos)│
└──────┬──────────────────────────┘
       │
       ▼
┌─────────────────────────────────┐
│  Console Evaluates              │
│  Role Mapping Rules             │
└──────┬──────────────────────────┘
       │
       ▼
┌─────────────────────────────────┐
│  User Granted Console Role      │
│  (Admin, DBA, or User)          │
└─────────────────────────────────┘

Console Roles

IBM Db2 Genius Hub supports four user role mapping methods for repository authentication:

• Db2 Authorities
• Db2 Groups
• Db2 UDF
• Db2 Roles

For each role mapping method, you can configure mappings for the following roles in the console:

• Console Administrator
• Database Administrator
• Database User

Configuration Steps

Let's first review the steps we'll perform after completing the prerequisite tasks, including user creation and the required Db2 authorization or role mapping configuration (Db2 Authorities, Db2 Groups, Db2 UDF, or Db2 Roles). Once those are complete, we'll come back and set the required values.

Step 1: Access Authentication Settings

1. Log into Db2 Genius Hub as a setup admin
2. Navigate to Administration → Authentication
3. You'll see the authentication type selection page

Step 2: Select Repository Authentication

1. Select the Repository radio button
2. Click Next to proceed to role mapping configuration

⚠️ Important: Once you enable repository authentication and save, the setup admin account will be invalidated. Ensure you have at least one database user properly configured before saving!

Step 3: Configure Role Mapping

Choose one of the four role mapping methods. Only one method can be active at a time. Read the instructions below to learn how to create users, map privileges, and configure them with the console.

User Creation (Common Step)

Before configuring any role mapping method, create user accounts in the repository database.

Create OS Users (Linux)

# ----------------------------------------------------------------------
# Create operating system users required for DB2 access and administration
# ----------------------------------------------------------------------

# Create DB2 administrative user
sudo useradd -m -s /bin/bash db2admin
sudo passwd db2admin

# Create database administrator user
sudo useradd -m -s /bin/bash dbauser1
sudo passwd dbauser1

# Create application/developer user
sudo useradd -m -s /bin/bash devuser1
sudo passwd devuser1

# ----------------------------------------------------------------------
# Verify user creation and group membership at OS level
# ----------------------------------------------------------------------

id db2admin
id dbauser1
id devuser1

Grant Basic Database Access

The CONNECT authority is the minimum database‑level privilege required for any user to establish a session with a Db2 database. Without this authority, a user cannot log in or perform any database operations, regardless of other object‑level or role‑based privileges that may be defined. All additional authorities, roles, or privileges are effective only after a successful database connection, making CONNECT a mandatory baseline requirement for all users who need database access.

# ----------------------------------------------------------------------
# Grant basic database access to OS users
# This step must be performed as the DB2 instance owner
# ----------------------------------------------------------------------

# Switch to DB2 instance owner
su - db2inst1

# Connect to the repository database
db2 "CONNECT TO REPODB"

# Grant minimum required CONNECT authority to users
db2 "GRANT CONNECT ON DATABASE TO USER db2admin"
db2 "GRANT CONNECT ON DATABASE TO USER dbauser1"
db2 "GRANT CONNECT ON DATABASE TO USER devuser1"

Role Mapping Methods

Method 1: Db2 Authorities

Db2 Authorities are database‑level privileges defined and managed within Db2 that control a user’s ability to connect to the database, access data, and perform administrative tasks such as schema management, security administration, or routine execution. Common authorities include CONNECT, DATAACCESS, SQLADM, SECURITYADM, and DBADM. In this role‑mapping approach, the console evaluates the authorities granted to a user in the database and automatically assigns corresponding console roles, eliminating the need for separate role management within the console itself. This method ensures consistent access control, leverages existing Db2 security configurations, and simplifies administration by using database authorities as the single source of truth.

Console Configuration

Configuration Example:

• Console Administrator authorities: DBADM|SECADM
• Database Administrator authorities: SQLADM|DATAACCESS
• Database User authorities: CONNECT

Db2 Side Setup

1. Check Current User Authorities

# ----------------------------------------------------------------------
# Check database-level authorities for specific users
# Note: Authorization IDs in DB2 catalog tables are stored in UPPERCASE
# ----------------------------------------------------------------------

# Check authorities for DB2ADMIN
db2 "SELECT * FROM SYSCAT.DBAUTH WHERE GRANTEE = 'DB2ADMIN'"

# Check authorities for DBAUSER1
db2 "SELECT * FROM SYSCAT.DBAUTH WHERE GRANTEE = 'DBAUSER1'"

# Check authorities for DEVUSER1
db2 "SELECT * FROM SYSCAT.DBAUTH WHERE GRANTEE = 'DEVUSER1'"

# ----------------------------------------------------------------------
# List all explicitly granted database-level authorities
# This view helps identify users and roles with administrative or data
# access permissions. Default privileges (such as CONNECT via PUBLIC)
# may also be included.
# ----------------------------------------------------------------------

db2 "
SELECT GRANTEE,
       DBADMAUTH,
       SECURITYADMAUTH,
       SQLADMAUTH,
       DATAACCESSAUTH,
       CONNECTAUTH
FROM SYSCAT.DBAUTH
WHERE GRANTEE NOT LIKE 'SYS%'
ORDER BY GRANTEE
"

2. Grant Authorities to Users

# ----------------------------------------------------------------------
# Grant database authorities based on user roles
# All commands must be executed by the DB2 instance owner
# ----------------------------------------------------------------------

# Connect to the repository database
db2 "CONNECT TO REPODB"

# ----------------------------------------------------------------------
# Console Administrator Role
# Full database administration privileges
# ----------------------------------------------------------------------

# Grant DBADM authority to console administrator
db2 "GRANT DBADM ON DATABASE TO USER DB2ADMIN"

# (Optional) Grant SECURITYADM authority if security management is required
# db2 "GRANT SECADM ON DATABASE TO USER SECURITYADMIN"


# ----------------------------------------------------------------------
# DBA Role
# SQL and object management privileges
# ----------------------------------------------------------------------

# Grant SQLADM authority to DBA user
db2 "GRANT SQLADM ON DATABASE TO USER DBAUSER1"

# (Optional) Grant full data access if required
# db2 "GRANT DATAACCESS ON DATABASE TO USER DBAUSER2"


# ----------------------------------------------------------------------
# Application / Developer Role
# Minimum access required to connect to the database
# ----------------------------------------------------------------------

# Grant CONNECT authority to application or developer user
db2 "GRANT CONNECT ON DATABASE TO USER DEVUSER1"

3. Verify Authority Grants

# ----------------------------------------------------------------------
# Verify database-level authorities for individual users
# Authorization IDs are stored in uppercase in DB2 catalog tables
# ----------------------------------------------------------------------

# Verify Console Administrator privileges
db2 "SELECT GRANTEE, DBADMAUTH, SECURITYADMAUTH, SQLADMAUTH, 
       DATAACCESSAUTH, CONNECTAUTH
FROM SYSCAT.DBAUTH
WHERE GRANTEE = 'DB2ADMIN'"


# Verify DBA role privileges
db2 "SELECT GRANTEE, DBADMAUTH, SECURITYADMAUTH, SQLADMAUTH, 
       DATAACCESSAUTH, CONNECTAUTH
FROM SYSCAT.DBAUTH
WHERE GRANTEE = 'DBAUSER1'"


# Verify Application / Developer user privileges
db2 "SELECT GRANTEE, DBADMAUTH, SECURITYADMAUTH, SQLADMAUTH, 
       DATAACCESSAUTH, CONNECTAUTH
FROM SYSCAT.DBAUTH
WHERE GRANTEE = 'DEVUSER1'"

Verify all grants

# ----------------------------------------------------------------------
# Verify database-level authorities for multiple users in a single query
# Displays explicitly granted database privileges for selected users
# ----------------------------------------------------------------------

db2 "SELECT GRANTEE, DBADMAUTH, SECURITYADMAUTH, SQLADMAUTH, 
       DATAACCESSAUTH, CONNECTAUTH
FROM SYSCAT.DBAUTH
WHERE GRANTEE IN ('DB2ADMIN', 'DBAUSER1', 'DEVUSER1')"

Expected Output:

+----------+--------+--------------+--------+-------------+---------+
| GRANTEE  | DBADM  | SECURITYADM  | SQLADM | DATAACCESS  | CONNECT |
+----------+--------+--------------+--------+-------------+---------+
| DB2ADMIN | YES    | NO           | NO     | YES         | YES     |
| DBAUSER1 | NO     | NO           | YES    | NO          | YES     |
| DEVUSER1 | NO     | NO           | NO     | NO          | YES     |
+----------+--------+--------------+--------+-------------+---------+

Now configure it on the Db2 Genius Hub as shown below.

Method 2: Db2 Groups

Db2 Groups provide a centralized, OS‑integrated way to manage database access by assigning privileges to groups instead of individual users. In this method, Db2 database authorities or object privileges are granted to operating system (or LDAP) groups, and any user who is a member of those groups automatically inherits the associated database access. The console maps these Db2 groups to predefined console roles, ensuring that user privileges are determined by group membership rather than manual user‑level grants. This approach simplifies administration, improves scalability, and supports consistent enforcement of role‑based access control by aligning database security with existing enterprise group management practices.

Console Configuration

Configuration Example:

• Console Administrator groups: ADMINGROUP|SYSADMINS
• Database Administrator groups: DBAGROUP|DBATEAM
• Database User groups: DEVELOPERS|ANALYSTS

Db2 Side Setup

1. Check Current Groups

# ----------------------------------------------------------------------
# Check current database groups and user group memberships
# This helps identify group-based access and inherited database privileges
# ----------------------------------------------------------------------

# List all groups with database-level authorities
db2 "
SELECT DISTINCT GRANTEE
FROM SYSCAT.DBAUTH
WHERE GRANTEETYPE = 'G'
ORDER BY GRANTEE
"

# ----------------------------------------------------------------------
# Check OS / LDAP group memberships for specific users
# These groups may grant database privileges indirectly
# ----------------------------------------------------------------------

db2 "SELECT * FROM TABLE(SYSPROC.AUTH_LIST_GROUPS_FOR_AUTHID('DB2ADMIN'))"
db2 "SELECT * FROM TABLE(SYSPROC.AUTH_LIST_GROUPS_FOR_AUTHID('DBAUSER1'))"
db2 "SELECT * FROM TABLE(SYSPROC.AUTH_LIST_GROUPS_FOR_AUTHID('DEVUSER1'))"

2. Create Groups (OS Level)

On Linux/Unix:

# ----------------------------------------------------------------------
# Create operating system groups for role-based access control
# These groups will be used to manage database privileges centrally
# ----------------------------------------------------------------------

# Create administrative and application groups
sudo groupadd admingroup
sudo groupadd dbagroup
sudo groupadd developers

# ----------------------------------------------------------------------
# Assign users to the appropriate groups based on their roles
# ----------------------------------------------------------------------

# Add console administrator to admin group
sudo usermod -a -G admingroup db2admin

# Add DBA user to DBA group
sudo usermod -a -G dbagroup dbauser1
# sudo usermod -a -G dbagroup dbauser2

# Add developer/application user to developers group
sudo usermod -a -G developers devuser1
# sudo usermod -a -G developers devuser2

# ----------------------------------------------------------------------
# Verify group membership for each user
# ----------------------------------------------------------------------

groups db2admin
groups dbauser1
groups devuser1

3. Grant Authorities to Groups in Db2

# ----------------------------------------------------------------------
# Grant database authorities to OS groups
# This enables group-based access control in Db2
# Commands must be executed as the Db2 instance owner
# ----------------------------------------------------------------------

# Switch to Db2 instance owner
su - db2inst1

# Connect to the repository database
db2 "CONNECT TO REPODB"

# ----------------------------------------------------------------------
# Admin Group: Full database administration
# ----------------------------------------------------------------------

# Grant DBADM authority to admin group
db2 "GRANT DBADM ON DATABASE TO GROUP ADMINGROUP"

# ----------------------------------------------------------------------
# DBA Group: SQL and object administration
# ----------------------------------------------------------------------

# Grant SQLADM authority to DBA group
db2 "GRANT SQLADM ON DATABASE TO GROUP DBAGROUP"

# (Optional) Grant full data access if required
# db2 "GRANT DATAACCESS ON DATABASE TO GROUP DBAGROUP"

# ----------------------------------------------------------------------
# Developers Group: Basic database access
# ----------------------------------------------------------------------

# Grant CONNECT authority to developers group
db2 "GRANT CONNECT ON DATABASE TO GROUP DEVELOPERS"

# (Optional) Grant read-only access to a specific schema
# db2 "GRANT SELECT ON ALL TABLES IN SCHEMA IBMCONSOLE TO GROUP DEVELOPERS"

4. Verify Group Authorities

# ----------------------------------------------------------------------
# Verify database-level authorities granted to groups
# This confirms that group-based privileges have been applied correctly
# ----------------------------------------------------------------------

db2 "
SELECT GRANTEE,
       GRANTEETYPE,
       DBADMAUTH,
       SQLADMAUTH,
       DATAACCESSAUTH,
       CONNECTAUTH
FROM SYSCAT.DBAUTH
WHERE GRANTEETYPE = 'G'
  AND GRANTEE IN ('ADMINGROUP', 'DBAGROUP', 'DEVELOPERS')
ORDER BY GRANTEE
"

Now configure it on the Db2 Genius Hub as shown below.

Method 3: Db2 UDF (User-Defined Functions)

Db2 UDFs (User‑Defined Functions) enable fine‑grained and highly flexible authorization by using function execution privileges to control console access. In this approach, the console invokes specific Db2 UDFs to determine whether a user is allowed to perform certain actions or assume particular roles. Access is granted based on the user’s EXECUTE privilege on these functions, and the UDF logic itself can be simple (for example, returning a constant) or complex, incorporating custom business rules, database authorities, group membership, or other contextual checks. This method offers the greatest flexibility, supports advanced authorization requirements, and allows full customization beyond standard authority or group‑based mappings.

Console Configuration

Default UDF Names:

• Console Administrator UDF: IBMCONSOLE.CANADMINISTER
• Database Administrator UDF: IBMCONSOLE.CANDBA
• Database User UDF: IBMCONSOLE.CANVIEW

Db2 Side Setup

1. Create Authorization UDFs

The console creates default UDFs when the repository database is initialized, but you can also create custom ones. This example is just for reference, using the same names and definitions as those used by the console for UDFs. If you want to use different names or implement different validation logic, you can do so.

Check if UDFs Already Exist

# ----------------------------------------------------------------------
# Verify existing UDFs in the IBMCONSOLE schema
# Confirms whether required console-related routines are present
# ----------------------------------------------------------------------

# Connect to the repository database
db2 "CONNECT TO REPODB"

# List user-defined functions and routines starting with 'CAN'
db2 "
SELECT ROUTINESCHEMA,
       ROUTINENAME,
       ROUTINETYPE,
       OWNER
FROM SYSCAT.ROUTINES
WHERE ROUTINESCHEMA = 'IBMCONSOLE'
  AND ROUTINENAME LIKE 'CAN%'
"

Create UDFs (if they don't exist)

# ----------------------------------------------------------------------
# Create console authorization UDFs
# These functions are used by the console to evaluate role-based access
# ----------------------------------------------------------------------

# Connect to repository database
db2 "CONNECT TO REPODB"


# ----------------------------------------------------------------------
# Create schema to hold console authorization routines
# ----------------------------------------------------------------------

db2 "CREATE SCHEMA IBMCONSOLE"


# ----------------------------------------------------------------------
# Console Administrator role UDF
# ----------------------------------------------------------------------

db2 "CREATE FUNCTION IBMCONSOLE.CANADMINISTER()
RETURNS INTEGER
SPECIFIC IBMCONSOLE.CANADMINISTER
LANGUAGE SQL
CONTAINS SQL
NO EXTERNAL ACTION
DETERMINISTIC
RETURN 1"


# ----------------------------------------------------------------------
# DBA role UDF
# ----------------------------------------------------------------------

db2 "CREATE FUNCTION IBMCONSOLE.CANDBA()
RETURNS INTEGER
SPECIFIC IBMCONSOLE.CANDBA
LANGUAGE SQL
CONTAINS SQL
NO EXTERNAL ACTION
DETERMINISTIC
RETURN 1"


# ----------------------------------------------------------------------
# User / Viewer role UDF
# ----------------------------------------------------------------------

db2 "CREATE FUNCTION IBMCONSOLE.CANVIEW()
RETURNS INTEGER
SPECIFIC IBMCONSOLE.CANVIEW
LANGUAGE SQL
CONTAINS SQL
NO EXTERNAL ACTION
DETERMINISTIC
RETURN 1"

2. Grant Execute Privileges

# ----------------------------------------------------------------------
# Grant EXECUTE privileges on console authorization UDFs
# This controls access to console roles based on assigned privileges
# ----------------------------------------------------------------------

# Grant administrator UDF to console admin user
db2 "GRANT EXECUTE ON FUNCTION IBMCONSOLE.CANADMINISTER() TO USER db2admin"
# db2 "GRANT EXECUTE ON FUNCTION IBMCONSOLE.CANADMINISTER() TO USER secadmin"

# Grant DBA UDF to DBA users or groups
db2 "GRANT EXECUTE ON FUNCTION IBMCONSOLE.CANDBA() TO USER dbauser1"
# db2 "GRANT EXECUTE ON FUNCTION IBMCONSOLE.CANDBA() TO USER dbauser2"
# db2 "GRANT EXECUTE ON FUNCTION IBMCONSOLE.CANDBA() TO GROUP dbagroup"


# Grant user/viewer UDF to application or developer users
db2 "GRANT EXECUTE ON FUNCTION IBMCONSOLE.CANVIEW() TO USER devuser1"
# db2 "GRANT EXECUTE ON FUNCTION IBMCONSOLE.CANVIEW() TO USER analyst1"
# db2 "GRANT EXECUTE ON FUNCTION IBMCONSOLE.CANVIEW() TO GROUP developers"

3. Verify UDF Grants

# ----------------------------------------------------------------------
# Verify console authorization UDFs and their EXECUTE privileges
# ----------------------------------------------------------------------

# List all console-related UDFs in the IBMCONSOLE schema
db2 "
SELECT ROUTINESCHEMA,
       ROUTINENAME,
       ROUTINETYPE,
       OWNER
FROM SYSCAT.ROUTINES
WHERE ROUTINESCHEMA = 'IBMCONSOLE'
  AND ROUTINENAME LIKE 'CAN%'
"

# ----------------------------------------------------------------------
# Verify EXECUTE privileges on UDFs
# Note: ROUTINEAUTH stores system-generated specific names
# ----------------------------------------------------------------------

db2 "
SELECT SCHEMA,
       SPECIFICNAME,
       GRANTEE,
       EXECUTEAUTH
FROM SYSCAT.ROUTINEAUTH
WHERE SCHEMA = 'IBMCONSOLE'
ORDER BY SPECIFICNAME, GRANTEE
"

# ----------------------------------------------------------------------
# Resolve system-generated SPECIFICNAME values to actual function names
# ----------------------------------------------------------------------

db2 "
SELECT R.ROUTINENAME,
       A.GRANTEE,
       A.EXECUTEAUTH
FROM SYSCAT.ROUTINEAUTH A
JOIN SYSCAT.ROUTINES R
  ON A.SCHEMA = R.ROUTINESCHEMA
 AND A.SPECIFICNAME = R.SPECIFICNAME
WHERE A.SCHEMA = 'IBMCONSOLE'
ORDER BY R.ROUTINENAME, A.GRANTEE
"

4. Test UDF Execution

# ----------------------------------------------------------------------
# Test console authorization UDFs by connecting as each user
# Each function should return 1 if EXECUTE privilege is correctly granted
# ----------------------------------------------------------------------

# Test Console Administrator access
db2 "CONNECT TO REPODB USER DB2ADMIN USING <password>"
db2 "VALUES IBMCONSOLE.CANADMINISTER()"

# Test DBA role access
db2 "CONNECT TO REPODB USER DBAUSER1 USING <password>"
db2 "VALUES IBMCONSOLE.CANDBA()"

# Test User / Viewer access
db2 "CONNECT TO REPODB USER DEVUSER1 USING <password>"
db2 "VALUES IBMCONSOLE.CANVIEW()"

Now configure it on the Db2 Genius Hub as shown below.

Method 4: Db2 Roles

Db2 Roles provide a native, database‑level role‑based access control (RBAC) mechanism that allows privileges to be grouped and managed centrally within Db2. In this method, database authorities or object privileges are granted to Db2 roles, and users are assigned to those roles instead of receiving direct grants. The console then maps these Db2 roles to corresponding console roles, ensuring that access is determined by role membership at the database level. This approach simplifies user management, supports least‑privilege access, and allows roles to be enabled or disabled dynamically without changing individual privileges, making it well suited for structured, scalable environments that prefer Db2‑managed RBAC over OS groups or custom UDF logic.

Console Configuration

Configuration Example:

• Console Administrator roles: CONSOLE_ADMIN|SYSTEM_ADMIN
• Database Administrator roles: DB_ADMIN|DATA_ADMIN
• Database User roles: APP_USER|READ_ONLY

Db2 Side Setup

1. Check Existing Roles

# ----------------------------------------------------------------------
# Verify Db2 roles and role-to-user assignments
# This confirms that role-based access control (RBAC) is configured correctly
# ----------------------------------------------------------------------

# Connect to the repository database
db2 "CONNECT TO REPODB"


# ----------------------------------------------------------------------
# List all database roles
# Displays roles defined in the database along with their creation time
# ----------------------------------------------------------------------
db2 "SELECT ROLENAME, CREATE_TIME FROM SYSCAT.ROLES ORDER BY ROLENAME"


# ----------------------------------------------------------------------
# Check role grants for individual users
# This shows which Db2 roles are assigned directly to each user
# ----------------------------------------------------------------------

# Check roles granted to console administrator
db2 "SELECT ROLENAME, GRANTEE, GRANTEETYPE FROM SYSCAT.ROLEAUTH WHERE GRANTEE = 'DB2ADMIN' ORDER BY ROLENAME"
# Check roles granted to DBA user
db2 "SELECT ROLENAME, GRANTEE, GRANTEETYPE FROM SYSCAT.ROLEAUTH WHERE GRANTEE = 'DBAUSER1' ORDER BY ROLENAME"
# Check roles granted to application / developer user
db2 "SELECT ROLENAME, GRANTEE, GRANTEETYPE FROM SYSCAT.ROLEAUTH WHERE GRANTEE = 'DEVUSER1' ORDER BY ROLENAME"

2. Create Roles

# ----------------------------------------------------------------------
# Create Db2 roles and grant database authorities
# These roles will be mapped to console roles
# ----------------------------------------------------------------------

# Connect to the repository database
db2 "CONNECT TO REPODB"

# ----------------------------------------------------------------------
# Console Administrator Role
# Full database and security administration
# ----------------------------------------------------------------------

db2 "CREATE ROLE CONSOLE_ADMIN"
db2 "GRANT DBADM ON DATABASE TO ROLE CONSOLE_ADMIN"
db2 "GRANT SECADM ON DATABASE TO ROLE CONSOLE_ADMIN"

# ----------------------------------------------------------------------
# Database Administrator Role
# SQL, data access, and load privileges
# ----------------------------------------------------------------------

db2 "CREATE ROLE DB_ADMIN"
db2 "GRANT SQLADM ON DATABASE TO ROLE DB_ADMIN"
db2 "GRANT DATAACCESS ON DATABASE TO ROLE DB_ADMIN"
db2 "GRANT LOAD ON DATABASE TO ROLE DB_ADMIN"

# ----------------------------------------------------------------------
# Application User Role
# Basic access for application or developer usage
# ----------------------------------------------------------------------

db2 "CREATE ROLE APP_USER"
db2 "GRANT CONNECT ON DATABASE TO ROLE APP_USER"
db2 "GRANT CREATETAB ON DATABASE TO ROLE APP_USER"

# ----------------------------------------------------------------------
# Optional: Read-Only Role
# Uncomment and adjust schema name as required
# ----------------------------------------------------------------------

# db2 "CREATE ROLE READ_ONLY"
# db2 "GRANT CONNECT ON DATABASE TO ROLE READ_ONLY"
# db2 "GRANT SELECT ON ALL TABLES IN SCHEMA PRODSCHEMA TO ROLE READ_ONLY"

3. Grant Roles to Users

# ----------------------------------------------------------------------
# Grant Db2 roles to users and groups
# Role membership determines effective database privileges
# ----------------------------------------------------------------------

# Grant Console Administrator role
db2 "GRANT ROLE CONSOLE_ADMIN TO USER DB2ADMIN"
# db2 "GRANT ROLE CONSOLE_ADMIN TO USER SECADMIN"

# ----------------------------------------------------------------------
# Grant Database Administrator role
# ----------------------------------------------------------------------

db2 "GRANT ROLE DB_ADMIN TO USER DBAUSER1"
# db2 "GRANT ROLE DB_ADMIN TO USER DBAUSER2"
# db2 "GRANT ROLE DB_ADMIN TO GROUP DBAGROUP"

# ----------------------------------------------------------------------
# Grant Application User role
# ----------------------------------------------------------------------

db2 "GRANT ROLE APP_USER TO USER DEVUSER1"
# db2 "GRANT ROLE APP_USER TO USER DEVUSER2"
# db2 "GRANT ROLE APP_USER TO GROUP DEVELOPERS"

# ----------------------------------------------------------------------
# Optional: Grant Read-Only role
# ----------------------------------------------------------------------

# db2 "GRANT ROLE READ_ONLY TO USER ANALYST1"
# db2 "GRANT ROLE READ_ONLY TO GROUP ANALYSTS"

4. Verify Role Grants

# ----------------------------------------------------------------------
# Verify Db2 role assignments and role-based authorities
# ----------------------------------------------------------------------

# Check Db2 roles granted to specific users
# This confirms role-to-user mappings
db2 "
SELECT GRANTEE,
       GRANTEETYPE,
       ROLENAME
FROM SYSCAT.ROLEAUTH
WHERE GRANTEE IN ('DB2ADMIN', 'DBAUSER1', 'DEVUSER1')
ORDER BY GRANTEE, ROLENAME
"

# ----------------------------------------------------------------------
# Check database-level authorities granted to roles
# This shows which privileges each role provides
# ----------------------------------------------------------------------

db2 "
SELECT GRANTEE,
       DBADMAUTH,
       SECURITYADMAUTH,
       SQLADMAUTH,
       DATAACCESSAUTH,
       CONNECTAUTH,
       LOADAUTH
FROM SYSCAT.DBAUTH
WHERE GRANTEETYPE = 'R'
ORDER BY GRANTEE
"

5. Test Role Membership

# ----------------------------------------------------------------------
# Verify roles enabled for the current user
# This shows which Db2 roles are active in the current session
# ----------------------------------------------------------------------

# Connect to the repository database as the user
db2 "CONNECT TO REPODB USER DB2ADMIN USING <password>"

# Verify the current authorization ID
db2 "VALUES CURRENT USER"

# List roles granted to the currently connected user
db2 "
SELECT ROLENAME
FROM SYSCAT.ROLEAUTH
WHERE GRANTEE = CURRENT USER
"

Now configure it on the Db2 Genius Hub as shown below.