Enabling and disabling Ranger security support for Db2 Big SQL

Ranger support is available for the Db2® Big SQL service. Ranger is a framework to enable, monitor and manage comprehensive data security across the Hadoop platform. The Db2 Big SQL Ranger plugin can be enabled to control access to tables, views, and nicknames. Db2 Big SQL native authorization controls can be used for other database objects.

For details about security functions performed by Ranger see Operations managed by the Db2 Big SQL Ranger plugin.

For details about how the Db2 Big SQL Ranger plugin affects access to other HDP service Ranger plugins see Db2 Big SQL compatibility with Ranger plugins for other HDP services.

For details about how to migrate a set of permissions set for Hive through the Ranger service into a Db2 Big SQL Ranger policy, see the IMPORT HIVE RANGER POLICIES option in the HCAT_SYNC_OBJECTS stored procedure.

Before you begin

You must first install Ranger. For complete instructions, see Installing Ranger.

When the Db2 Big SQL Ranger plugin is enabled, Ranger can audit all access to Db2 Big SQL tables, views, and nicknames. You can select either HDFS or Solr as an audit destination. Once the Ranger plugin has been enabled any audit configuration changes must be done manually to the Db2 Big SQL audit configuration located at $BIGSQL_HOME/conf/ranger-bigsql-audit.xml. For details about Ranger auditing see Enabling auditing using the Db2 Big SQL Ranger plugin.

To enable the Db2 Big SQL Ranger plugin, passwordless SSH for the ID running the Ambari agent (either a root or non-root ID) must be set up from the Db2 Big SQL head node to the Ranger server host.

On a Kerberos enabled cluster, for Db2 Big SQL Ranger audits to be available you need to perform the following additional steps:
  1. In Ambari, go to Ambari-infra service > Configs > Advanced. Under the property Advanced infra-solr-security-json, add the bigsql user to the property Ranger audit service users.

    {default_ranger_audit_users},bigsql

    Note: Make sure that there is no blank space between the comma (,) and bigsql.
  2. Restart the Ambari-infra service.

Procedure

Before enabling the Db2 Big SQL Ranger plugin, you must manually add the username defined for Ranger in Ambari, to Ranger. This username is the value set for the property ranger_admin_username, which is defined in Ambari under Ranger > Configs > Advanced. By default, ranger_admin_username is set to the value amb_ranger_admin, but this value can be changed during Ranger installation. In both Kerberized and non-Kerberized environments this user is required for enabling and disabling the Db2 Big SQL Ranger plugin. This differs from the Ranger plugins for other core services that do you require this user in Kerberized environments.
  1. Under Ranger > Configs > Advanced, make a note of the value that is set for ranger_admin_username.
  2. From the Ranger UI, go to Settings > Users/Groups.
  3. Check if the value set for ranger_admin_username already exists. If so, no further action is required.
  4. Select Add New User.
  5. Enter the following user details:
    1. User Name = the value that is set for ranger_admin_username (by default this is amb_ranger_admin).
    2. New Password = the value of ranger_admin_password under Ranger > Configs > Advanced. A default password is in place if the password was not modified during the install. In such a case, update the password to a matching value in both the Ranger UI and the Ambari UI. The password must contain a minimum of eight characters including at least one alphabetic and one numeric character.
    3. First Name = the value that is set for ranger_admin_username (by default this is amb_ranger_admin).
    4. Select Role = Admin
    5. Specify a user group for the Groups field.
To enable the Db2 Big SQL Ranger plugin:
  1. In Ambari, select IBM Db2 Big SQL > Service Actions > Enable Ranger Plugin > OK.
  2. Restart the Ranger service.
This process updates the bigsql.external.access.control.manager property in bigsql-conf.xml, setting up Ranger to authorize Db2 Big SQL tables, views, and nicknames. It then restarts the Db2 Big SQL service in order for the change to take effect.

This process also updates the available action in metainfo.xml to Disable Ranger Plugin, and restarts the Ambari Server. Note that the status of the Custom Actions task executed by the Ambari server does not appear green, indicating a normal running state, but rather appears orange. The orange color is expected and does not indicate a problem in the execution of the Custom Actions. For a discussion about why Db2 Big SQL restarts the Ambari Server see the IBM® developerWorks® article Why Db2 Big SQL performs an Ambari Server restart.

Note:
  1. Impersonation is supported with the Db2 Big SQL Ranger plugin. Whether or not Impersonation is enabled, all authorizations in the Db2 Big SQL Ranger plugin are performed as the connected user. When Impersonation is enabled, the bigsql.impersonation.create.table.grant.public configuration parameter controls whether access is automatically granted to public for any new Hadoop table. This configuration parameter does not trigger the creation of new Ranger policies. If you want all I/O authorization control for a particular Hadoop table to occur only in HDFS, manually create a policy in the Db2 Big SQL Ranger plugin for the table granting access to all required users or groups.
  2. Update $BIGSQL_HOME/conf/ranger-bigsql-security.xml to update these properties, otherwise the defaults will be used:
    Table 1.
    Property Name Default Value Unit
    ranger.plugin.bigsql.policy.pollIntervalMs 30000 Milliseconds
    ranger.plugin.bigsql.policy.rest.client.connection.timeoutMs 120000 Milliseconds
    ranger.plugin.bigsql.policy.rest.client.read.timeoutMs 30000 Milliseconds
  3. Update $BIGSQL_HOME/conf/ranger-bigsql-audit.xml to update these properties, otherwise the defaults will be used:
    Property Name Default Value
    xasecure.audit.destination.hdfs.batch.filespool.dir /tmp/audit/hdfs/spool
  4. If you perform an upgrade of Db2 Big SQL with the Db2 Big SQL Ranger plugin enabled, the plugin will be functional at the new level, but the service action menu in the Ambari UI will revert to Enable Ranger Plugin. If you wish to disable the Db2 Big SQL Ranger plugin, you must first choose the Enable Ranger Plugin action from the service action menu. This operation will have no effect other than to make the Disable Ranger Plugin action available.

To disable the Db2 Big SQL Ranger plugin:
On the Ambari page of Db2 Big SQL, select Service Actions > Disable Ranger Plugin. This removes the Db2 Big SQL service for the cluster from Ranger, including all the policies therein. It then resets the bigsql.external.access.control.manager property in bigsql-conf.xml and restarts Db2 Big SQL for the change to take effect. The action also updates the available action in metainfo.xml to Enable Ranger Plugin and so restarts ambari-server too.

Disabling the Db2 Big SQL Ranger plugin will wipe out all existing policies. Before disabling the plugin you may want to export the policies using the export feature in the Ranger UI.

When the Db2 Big SQL Ranger plugin is disabled, the system reverts to using native Db2 Big SQL authorization controls. Native Db2 Big SQL security authorizations should be examined in detail to ensure only the desired object access is granted. One key difference is when the Db2 Big SQL Ranger plugin is disabled, users will have full access to objects they own. This is different from when the Db2 Big SQL Ranger plugin was enabled, in which case a Ranger policy was required to grant a user access to an object that they own.

What to do next

To set up SSL for the Db2 Big SQL Ranger plugin see Configuring the Db2 Big SQL Ranger plugin for SSL using self-signed certificates.