Skip to main content
Pentaho Documentation

Set up LDAP/JDBC Hybrid Configuration for BA Server

You might need to create a hybrid between an LDAP security solution and a JDBC security table for role definitions. This is common in situations where LDAP roles can't be redefined for BA Server use. These instructions help you switch the BA Server's authentication back-end from the Pentaho data access object to an LDAP/JDBC hybrid. 

Before You Begin

Before you begin configuring LDAP and JDBC for the BA Server, you'll need to verify a couple of things. 

Task Description
Verify Successful Default Pentaho Security Deployment Make sure your BA Server has been successfully deployed using default Pentaho Security (Jackrabbit authentication).
Configure Pentaho for LDAP Authentication Verify that your Pentaho system is configured for LDAP authentication.
Verify Database with User Roles Verify that you have a database populated with your user roles.

Preparing the BA Server for Hybrid LDAP/JDBC 

After you finish the prerequisite tasks above, there are a few things that you need to do in order set up a hybrid LDAP/JDBC configuration successfully. The table structure described here is for example purposes. 

These sections will guide you through the remaining steps of this process:

  • Create User/Authorities Database Tables
  • Set Up Inserts for Tables
  • Update JDBC Security Queries
  • Enable JDBC Authorization Beans
  • Verify LDAP/JDBC Configuration

Step 1: Create User/Authorities Database Tables

You'll need to create a few database tables in order to get LDAP and JDBC to work together.

  1. Create a table called USERS:
Column Name Column Type Column Description
username VARCHAR(50) The User name.
password VARCHAR(50) This column value is not considered in a hybrid LDAP/JDBC solution.
enabled VARCHAR(100) Set to ‘true’ if user is enabled, ‘false’ if not enabled.
  1. Create a table called AUTHORITIES:
Column Name Column Type Column Description
authority VARCHAR(50) The Pentaho role, such as Administrator, Report Author, etc.
  1. Create a table called GRANTED_AUTHORITIES:
Column Name Column Type Column Description
username VARCHAR(50) The User name.
authority VARCHAR(5) Associated Pentaho role.

Step 2: Update User and Role Values for Tables

Next, you'll need to perform a series of updates for the tables you just created. Users will be authenticated using their Active Directory password. 

Some syntax examples are provided here for you to customize with your own values.

  1. Update usernames and passwords in the USERS table as shown: 
INSERT INTO USERS VALUES('username','Password1','1',NULL)
  1. Update roles in the AUTHORITIES table as shown:
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. Update users with their associated roles in the GRANTED_AUTHORITIES table as shown:
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 3: Update JDBC Security Queries

You might have different names for your created tables than are provided in these examples. If so, after you have updated your user and role values in your tables, you need to update a couple of  queries and other items to match your system names.

  1. Locate the /biserver-ee/pentaho-solutions/system directory and update these two files with the noted information.
    1. applicationContext-pentaho-security-jdbc.xml
    2. applicationContext-spring-security-jdbc.xml

Update the query, as well as field names such as username, password, and enabled that are expected by spring framework security. Be sure to use an alias if you are using different field names. 

SELECT userid as username, 'password' as password, 'enabled' as enabled FROM USERS_ROLES WHERE userid= ? ORDER BY userid
  1. Stop the BA server.
  2. Copy your respective database JDBC driver to the tomcat/lib directory.

Step 4: Enable JDBC Authorization Beans

Last, you'll need to enable some JDBC Authorization beans.

Update security.properties

  1. Stop the BA Server.
  2. Locate the biserver-ee/pentaho-solutions/system directory.
  3. Open the security.properties file with any text editor.
    1. Locate the LDAP property bean and add the role provider as shown here:
provider=ldap
role.provider=jdbc
  1. Save and close the file.

Update applicationContext-spring-security-jdbc.properties

  1. Open the applicationContext-spring-security-jdbc.properties file.
  2. Add or update this database information with your system values.
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.

Update applicationContext-pentaho-security-jdbc.xml

  1. Open the applicationContext-pentaho-security-jdbc.xml file.
  2. Change the entry key to show your admin role for your database.
From:
<util:map id="jdbcRoleMap">
<entry key="Admin" value="Administrator"/>
</util:map>
To:
<util:map id="jdbcRoleMap">
<entry key="DBPentAdmins" value="Administrator"/>
</util:map>
  1. Save and close the file.

Update pentahoObjects.spring.xml 

  1. Open the pentahoObjects.spring.xml file. 
  2. Change these beans as shown, then save and close the file:
From:
<pen:bean id="activeUserRoleListService" class="org.pentaho.platform.api.engine.IUserRoleListService">
<pen:attributes>
<pen:attr key="providerName" value="${security.provider}"/>
</pen:attributes>
</pen:bean>
To:
<pen:bean id="activeUserRoleListService" class="org.pentaho.platform.api.engine.IUserRoleListService">
<pen:attributes>
<pen:attr key="providerName" value="jdbc"/>
</pen:attributes>
</pen:bean>

 Update applicationContext-spring-security-ldap.xml

  1. Open the applicationContext-spring-security-ldap.xml
  2. Remove the bean for org.springframework.security.ldap.populator.DefaultLdapAuthoritiesPopulator and replace it with this one: 
<bean id="populator" class="org.springframework.security.ldap.populator.UserDetailsServiceLdapAuthoritiesPopulator">
<constructor-arg ref="jdbcUserDetailsService" />
</bean>
  1. Save and close the file.

Update repository.spring.properties

  1. Open the repository.spring.properties file.
    1.  Locate the value for the singleTenantAdminUserName and make sure that it points to the correct admin user for your system.
  2. Restart the BA Server.

Step 5: Verify LDAP/JDBC Configuration 

Pentaho should now be successfully configured with Hybrid Authentication that has users being authenticated through LDAP, and the roles being authorized through JDBC. You can verify this by logging into PUC as an admin and checking in the Users & Roles tab in the Administration perspective.