Security in Db2 Big SQL

Important: IBM Cloud Pak® for Data Version 4.8 will reach end of support (EOS) on 31 July, 2025. For more information, see the Discontinuance of service announcement for IBM Cloud Pak for Data Version 4.X.

Upgrade to IBM Software Hub Version 5.1 before IBM Cloud Pak for Data Version 4.8 reaches end of support. For more information, see Upgrading from IBM Cloud Pak for Data Version 4.8 to IBM Software Hub Version 5.1.

Db2® Big SQL can operate under different security models depending on the use case.

Platform roles, users, and groups

Platform roles — including predefined roles such as Business Analyst, Data Engineer, and Data Scientist — are not recognized by Db2 Big SQL. Granting access to Db2 Big SQL is done at the user and group level, and authorization is controlled at the user and group levels.

You can create users and groups at the platform level, as described in Managing users. You can also connect to an enterprise LDAP server and add LDAP users to platform user groups, as described in Managing user groups. If Db2 Big SQL is connecting to a remote Hadoop cluster, it is recommended that you connect Cloud Pak for Data to the same LDAP server.

The user who provisioned a Db2 Big SQL instance cannot connect to that instance, but can grant access to other platform users.
Important: To prevent conflicts, do not create a user named db2inst1 at the platform level.

Group membership in Db2 Big SQL

When a user runs an SQL statement in Db2 Big SQL, authorization checks are performed to ensure that the user has the required permissions on objects that are referenced in the statement. For example, when a query is running against a Hive table, an authorization check is done to ensure that the user holds the SELECT permission on the table.

A user can be granted permission through a policy that is defined on a group to which the user belongs. The groups for a specific user are managed by the Cloud Pak for Data platform. For more information, see Configuring, monitoring, and managing access to Db2 Big SQL instances.

Kerberos

To be able to work with a secure Hadoop cluster, Db2 Big SQL must be part of the remote cluster's Kerberos realm. During provisioning, Db2 Big SQL creates the necessary service keytabs. These keytabs must be manually refreshed when they expire.

Authentication for JDBC connections to Db2 Big SQL

There are two supported authentication schemes when you connect to a Db2 Big SQL instance by using ODBC or JDBC:
User name and password
You can specify the user name and password of a platform user who has been granted access to the Db2 Big SQL instance in the JDBC connection URL. The following example shows a typical connection URL for this scenario:
jdbc:db2:host:port/bigsql:user=<platform-user-name>;password=<platform-user-password>
JWT authentication token
Db2 Big SQL supports authentication with a JSON web token (JWT) that is generated by the platform. To do this, specify the following parameters in the JDBC connection URL:
  • securityMechanism=15
  • pluginName=IBMIAMauth
  • accessToken=<jwt-access-token>
Important:
  • The connection in this case must be made over SSL.
  • If the connection parameters also include a user name or a password, the connection is rejected (SQL4461N): "The connection failed as more than one login option was provided. Provide only one of the options: Access token, API Key or username/password."
The following example shows a typical connection URL for this scenario:
jdbc:db2:host:port/bigsql:securityMechanism=15;pluginName=IBMIAMauth;accessToken=<jwt-access-token>;sslConnection=true;sslTrustStoreLocation=<trust-store-path>;sslTrustStorePassword=<ssl-truststore-pwd>;

Data security

When Db2 Big SQL is connected to a Hadoop cluster, data security can be based on database authorizations (authorizations that are enforced by the database engine) or based on Hadoop security (authorizations that are enforced by the Apache Hadoop stack, such as HDFS file permissions). When Db2 Big SQL is connected to an object store, the data in the buckets that are configured at provisioning time is accessible to all Db2 Big SQL users that can create tables. You can prevent users from accessing data on an object store by revoking the privilege to create tables.

Security of data that resides on a Hadoop cluster

When Db2 Big SQL is connected to a Hadoop cluster, data security can be based on database authorizations (authorizations that are enforced by the database engine) or based on Hadoop data security (authorizations that are enforced by the Apache Hadoop stack, such as HDFS file permissions).

When using database authorizations, you can define data access policies on tables by using standard SQL GRANT or REVOKE statements. Use the GRANT statement to give certain privileges to users, and the REVOKE statement to remove certain privileges from users. If you are the database administrator, you can grant authority to other users. In the following example, an administrator is granting USER1 access to a table named SALES:
GRANT INSERT, SELECT, DELETE ON myschema.SALES TO USER user1
There are two different forms of Hadoop data security: one that uses Ranger and one that is based on HDFS file permissions.
  • If the remote Hadoop cluster is secured with Ranger, Db2 Big SQL honors the corresponding Hive policies for operations against Hive tables. This includes access policies as well as column masking and row filtering policies.
  • If the remote Hadoop cluster is not secured with Ranger, Db2 Big SQL can be set up to use impersonation so that access can be controlled through file permissions on HDFS.

    When using Db2 Big SQL impersonation while connected to Hadoop, you can query only tables whose files you have permission to read. For more information, see Enabling or disabling impersonation in Db2 Big SQL.

Security of data that resides on an object store

When Db2 Big SQL is connected to an object store, data security is based on database authorizations (authorizations that are enforced by the database engine).

By default, the data in the buckets that are configured at provisioning time is accessible to all Db2 Big SQL users who can create tables. Therefore, it is recommended to restrict the privileges that are needed to create tables to a set of predefined users, typically those that were given the admin role in the Db2 Big SQL Manage access page in the Cloud Pak for Data UI. SQL authorizations on those tables can then be used to manage other users' access to the data. The privilege to create a table can be revoked or granted by running the following statements:
-- Revoke CREATE table privilege from all users
REVOKE CREATETAB ON DATABASE FROM role DASHDB_ENTERPRISE_USER

-- Grant CREATE table privilege to a subset of users
GRANT CREATETAB ON DATABASE TO USER user1, USER user2, USER user3
When using database authorizations, you can also use standard GRANT or REVOKE statements to define data access policies on tables. Use the GRANT statement to give certain privileges to users, and the REVOKE statement to remove certain privileges from users. If you are the database administrator, you can grant authority to other users. In the following example, an administrator is granting USER1 access to a table named SALES:
GRANT INSERT, SELECT, DELETE ON myschema.SALES TO USER user1

Security of data in motion

In Db2 Big SQL on Cloud Pak for Data, client/server communications can be encrypted by using Transport Layer Security (TLS).

Communication among the nodes in this cluster is not encrypted. A best practice is to configure inter-cluster communications on a private network to isolate this traffic for both security and performance.