DB2 - Database Security

Db2 - Modern Database Security

This chapter provides a comprehensive guide to securing a modern Db2 database, covering fundamental principles and contemporary best practices.

Core Security Principles

Effective database security is a multi-layered strategy built on the ‘CIA Triad’: Confidentiality, Integrity, and Availability. In Db2, this is achieved through a robust framework of authentication, authorization, encryption, and auditing.

• Confidentiality: Preventing unauthorized disclosure of data. Achieved via access control and encryption.
• Integrity: Ensuring data is accurate and trustworthy. Achieved via constraints, transaction control, and privileges.
• Availability: Ensuring authorized users can access the database when needed. Achieved via proper maintenance, high-availability setups, and security against denial-of-service attacks.

1. Authentication: Verifying User Identity

Authentication is the process of confirming a user’s identity before granting them access. Modern Db2 supports a flexible authentication architecture using plug-ins, allowing integration with various security services.

Authentication Methods

• Operating System (OS) Authentication: The default method, where Db2 trusts the OS to have authenticated the user. Common in on-premises, single-domain environments.
• Lightweight Directory Access Protocol (LDAP): Centralizes user identity management by integrating with services like Active Directory or OpenLDAP.
• Kerberos: Provides strong, ticket-based authentication suitable for secure enterprise environments.
• GSS-API Plug-ins: A generic framework allowing for other security mechanisms, including custom solutions.

Securing Connections: Data-in-Transit Encryption

It is critical to encrypt data moving between the client and the Db2 server. This is achieved by configuring Transport Layer Security (TLS/SSL).

To enable TLS, you must configure the SSL_SVR_KEYDB (path to keystore) and SSL_SVR_LABEL (certificate label) DBM CFG parameters and set the DB2COMM registry variable to include ‘SSL’.

-- Example: Configure DBM CFG for TLS/SSL
UPDATE DBM CFG USING SSL_SVR_KEYDB /path/to/keystore.kdb
UPDATE DBM CFG USING SSL_SVR_STASH /path/to/keystore.sth
UPDATE DBM CFG USING SSL_SVR_LABEL my_cert_label
UPDATE DBM CFG USING SSL_SVCENAME <ssl_port_number>

-- Update the registry variable to enable TLS communication
-- The TCPIP value is kept to allow non-TLS connections if needed.
db2set DB2COMM=SSL,TCPIP

-- Restart the instance to apply changes
db2stop
db2start

2. Authorization: Managing Permissions

Once a user is authenticated, authorization determines what they are allowed to do. Db2 uses a granular system of authorities and privileges, best managed through Role-Based Access Control (RBAC).

The Hierarchy of Control

1. System-Level Authorities: Broad control over the Db2 instance (e.g., SYSADM, SYSCTRL).
2. Database-Level Authorities: Control over a specific database (e.g., DBADM, SECADM, DATAACCESS).
3. Object-Level Privileges: Specific permissions on tables, views, schemas, etc. (e.g., SELECT, INSERT, EXECUTE).

Key Authorities

• SYSADM (System Administrator): Highest instance-level authority. Can perform any action on the instance and all its databases. Use with extreme caution.
• SECADM (Security Administrator): Manages database security policies, roles, and privileges. A crucial role for separating duties from DBADM.
• DBADM (Database Administrator): Full control over a specific database’s objects and data, but cannot manage security policies unless also granted SECADM.
• DATAACCESS: Authority to read and write data in all user tables within a database. Useful for ETL or service accounts.
• ACCESSCTRL: Authority to grant and revoke privileges and authorities within a database.

Best Practice: Role-Based Access Control (RBAC)

Instead of granting privileges directly to users, the best practice is to create roles for specific job functions, grant privileges to those roles, and then grant the roles to users or groups.

-- 1. Create a role for application developers
CREATE ROLE APP_DEVELOPER;

-- 2. Grant necessary privileges to the role
-- Developers can connect, create tables, and use the 'APP_SCHEMA'
GRANT CONNECT ON DATABASE TO ROLE APP_DEVELOPER;
GRANT CREATETAB ON DATABASE TO ROLE APP_DEVELOPER;
GRANT CREATEIN ON SCHEMA APP_SCHEMA TO ROLE APP_DEVELOPER;
GRANT USE OF TABLESPACE USERSPACE1 TO ROLE APP_DEVELOPER;

-- 3. Grant the role to a user group
GRANT ROLE APP_DEVELOPER TO GROUP DEVS;

3. Data Protection: Encryption and Masking

Data-at-Rest Encryption

Db2 Native Encryption transparently encrypts database files, log files, and backups on disk. This protects data if the physical storage is compromised. It is configured at the database level.

-- Example: Create an encrypted database
CREATE DATABASE MY_SECURE_DB
ENCRYPT;

Row and Column Access Control (RCAC)

RCAC (formerly LBAC) provides fine-grained security to control access to specific rows and columns based on defined rules. This is essential for multi-tenant applications and regulatory compliance (like GDPR).

-- Example: Row permission to ensure managers can only see their own employees'
CREATE PERMISSION MGR_SEE_OWN_REPORTS ON EMPLOYEE
FOR ROWS WHERE VERIFY_GROUP_FOR_USER(SESSION_USER, 'MANAGERS') = 1
AND EMP_MANAGER_ID = SESSION_USER
ENFORCED FOR ALL ACCESS;

ALTER TABLE EMPLOYEE ACTIVATE ROW ACCESS CONTROL;

4. Auditing: Tracking Database Activity

The Db2 audit facility tracks who did what, and when. It is a critical tool for security monitoring, forensics, and compliance.

-- 1. Configure audit to track all successful and failed database connections
db2audit configure scope connect status both

-- 2. Start the audit policy
db2audit start

-- 3. To view the audit log, you must first archive and then extract it
db2audit archive
db2audit extract file my_audit_log.txt

Security Best Practices Summary

• Principle of Least Privilege: Always grant the minimum set of permissions necessary for a user or application to function.
• Separate Duties: Use distinct roles like SECADM and DBADM to prevent any single user from having absolute power.
• Encrypt Everything: Use TLS for data-in-transit and Native Encryption for data-at-rest.
• Use Roles: Manage permissions with RBAC for scalability and clarity.
• Audit Regularly: Configure and review audit logs to monitor for suspicious activity.
• Secure Credentials: Application credentials should be stored in secure vaults (like HashiCorp Vault or CyberArk), not in configuration files.