Skip to main content
Pentaho Documentation

Set Up SAML/JDBC Hybrid Configuration for Pentaho Server

This article explains how  to create a hybrid between a third-party Security Assertion Markup Language (SAML) user Identification Provider (IdP) and a JDBC security table for role definitions. This type of hybrid is common in situations where SAML roles cannot be redefined for Pentaho Server use. These instructions are provided to walk you through an example SAML set up. If you want to extend your SAML set up further, please work with your Customer Success Manager.

The default role of Authenticated is assigned to any SAML-authenticated user, unless you specify otherwise.

These instructions describe how to switch the Pentaho Server's authentication back-end from the Pentaho data access object to a SAML/JDBC hybrid.

Before You Begin

Before you begin configuring SAML and JDBC for the Pentaho Server, you will need to verify completion of the following tasks: 

Task Description
Check for Functional Directory Server Make sure that you have a working directory server with an established configuration.
Verify Database with User Roles Verify that you have a database populated with your user roles.
Set Up SAML for Pentaho Server Make sure that you have completed the tasks described in Set Up SAML for Pentaho Server.

Preparing the Pentaho Server for Hybrid SAML/JDBC 

After you finish the prerequisite tasks above, you need to perform the following steps to set up the hybrid SAML/JDBC configuration:

  • Step 1: Create User/Authorities Database Tables
  • Step 2: Set Up the JDBC Connection Properties
  • Step 3: Update the JDBC Security Queries
  • Step 4: Enable SAML/JDBC Authorization

Step 1: Create User/Authorities Database Tables

Create and populate the following three database tables:

  1. Create a USERS table with the following structure:
Column Name Column Type Column Description
username VARCHAR(50) The user name.
password VARCHAR(50) This column value is not considered in a hybrid SAML/JDBC solution; all authentication takes place in the third-party authentication service. you can fill this column with <empty string>, ignored, etc.
enabled VARCHAR(5) Set to ‘true’ if user is enabled, ‘false’ if not enabled.

Populate the USERS table with your usernames and passwords as shown in the following example:

INSERT INTO USERS VALUES('username','Password1','1',NULL)

Users are authenticated using their Active Directory password.

  1. Create an AUTHORITIES table with the following structure:
Column Name Column Type Column Description
authority VARCHAR(50) The Pentaho role, such as Administrator, Report Author, etc.

 Populate your user roles in the AUTHORITIES table as shown in the following example:

INSERT INTO AUTHORITIES VALUES('DBPentAdmins','Super User')
INSERT INTO AUTHORITIES VALUES('DBPentHR','HR Users')
INSERT INTO AUTHORITIES VALUES('DBPentFinance','Finance Users')
INSERT INTO AUTHORITIES VALUES('DBPentUsers','User has not logged in')

  1. Create a GRANTED_AUTHORITIES table with the following structure:
Column Name Column Type Column Description
username VARCHAR(50) The user name.
authority VARCHAR(5) Associated Pentaho role.

Associate your users with roles in the GRANTED_AUTHORITIES table as shown in the following example:

INSERT INTO GRANTED_AUTHORITIES VALUES('admin','DBPentAdmins')
INSERT INTO GRANTED_AUTHORITIES VALUES('admin','DBPentUsers')
INSERT INTO GRANTED_AUTHORITIES VALUES('tiffany','DBPentUsers')
INSERT INTO GRANTED_AUTHORITIES VALUES('tiffany','DBPentFinance')
INSERT INTO GRANTED_AUTHORITIES VALUES('pat','DBPentUsers')
INSERT INTO GRANTED_AUTHORITIES VALUES('pat','DBPentHR')

Step 2: Set Up the JDBC Connection Properties

Perform the following steps to update the configuration file used for JDBC connections:

  1. Navigate to the pentaho-solutions/system directory and open the applicationContext-spring-security-jdbc.properties file with any text editor.
  2. Update the properties listed below to match your JDBC database.
Property Description
datasource.driver.classname Fully-qualified Java class name of the JDBC driver you are using.
datasource.url Connection URL to be passed to your JDBC driver to establish a connection.
datasource.username Connection username to be passed to our JDBC driver to establish a connection.
datasource.password Connection password to be passed to our JDBC driver to establish a connection.
datasource.validation.query SQL query that is used to validate connections from this pool before returning them to the caller. This query must be a SELECT statement that returns at least one row.
datasource.pool.max.wait Maximum number of milliseconds that the pool will wait when there are no available connections. For a connection to be returned before throwing an exception, or <= 0, to wait indefinitely. Default is -1.
datasource.pool.max.active Maximum number of active connections that can be allocated from this pool at the same time, or negative for no limit. Default value is 8.
datasource.max.idle Maximum number of connections that can remain idle in the pool, without extra ones being destroyed, or negative for no limit. Default value is 8.
datasource.min.idle Minimum number of active connections that can remain idle in the pool, without extra ones being created when the evictor runs, or 0 to create none. Default value is 0.
  1. Save and close the file.

Step 3: Update the JDBC Security Queries

If you created your tables using different names than those in the examples above, you must perform the following steps to update your JDBC security queries:

  1. Navigate to the server/pentaho-server/pentaho-solutions/system directory and open the following files with any text editor:
  • applicationContext-pentaho-security-jdbc.xml
  • applicationContext-spring-security-jdbc.xml
  1. Update the queries in these files with your table names, user and role values
  2. Update the field names such as username, password, and enabled that are expected by spring framework security as shown in the following example:
SELECT userid as username, 'password' as password, 'enabled' as enabled FROM USERS_ROLES WHERE userid= ? ORDER BY userid

Be sure to use an alias if you are using different field names.

  1. Save and close the files.
  2. Stop the Pentaho Server.
  3. Copy your respective database JDBC driver to the tomcat/lib directory.

Step 4: Enable SAML/JDBC Authorization 

Last, you will need to enable a JDBC Authorization bean:

  1. Locate the authorization.provider property in the  pentaho.saml.cfg file.
  2. Change the authorization.provider value to jdbc.
  3. Save and close the file.
  4. Restart the Pentaho Server.