Skip to main content
Pentaho Documentation

Manual JDBC Connection Configuration

Overview

Provides instruction on how to manually configure a JDBC connection.

You must have existing security tables in a relational database in order to proceed with this task.

Follow the instructions below to switch from Pentaho default security to JDBC security, which will allow you to use your own security tables.

Note: If you are using the BA Server and choose to switch to a JDBC security shared object, you will no longer be able to use the role and user administration settings in the Administration portion of the User Console.
  1. Stop the BA Server by running the stop-pentaho script.
  2. Open /pentaho-solutions/system/security.properties with a text editor.
  3. Change the value of the provide property to jdbc.
  4. Set up the connection to the database that holds the user/authorities.
    1. Open the /pentaho-solutions/system/applicationContext-spring-security-jdbc.properties file with a text editor. Find these two lines and change the jdbcDriver and URL as appropriate.
      datasource.driver.classname=org.hsqldb.jdbcDriver
      datasource.url=jdbc:hsqldb:hsql://localhost:9002/userdb
    2. Change the user name and password by editing these two items.
      \datasource.username=sa, datasource.password=
    3. Set the validation query by editing this row. There are examples of different validation queries in the file.
      datasource.validation.query=SELECT 1 FROM INFORMATION_SCHEMA.SYSTEM_USERS
    4. Set the wait timeout, max pool, and max idle by editing these three items to change the defaults.
      datasource.pool.max.wait=-1, datasource.pool.max.active=8, datasource.max.idle=4
    5. Save the file and close the editor.
  5. If you need to, modify these two queries that pull information about users/authorities.
    1. Open /pentaho-solutions/system/applicationContext-spring-security-jdbc.xml with a text editor.
    2. Find this line and change the query that returns the user and roles that the user is a member of as appropriate.
      <value>
          <![CDATA[SELECT username, authority FROM GRANTED_AUTHORITIES WHERE username = ? ORDER BY authority]]>
      </value>
    3. Find this line and change the query that determines the user, password, and whether they can log in as appropriate.
      <value>
          <![CDATA[SELECT username, password, enabled FROM USERS WHERE username = ? ORDER BY username]]>
      </value>
  6. If you need to, modify these three queries that pull information about users/authorities.
    1. Open the /pentaho-solutions/system/applicationContext-pentaho-security-jdbc.xml file with a text editor.
    2. Find this line and change the query that shows the roles for security on objects as appropriate.
      <value>
          <![CDATA[SELECT distinct(authority) as authority FROM AUTHORITIES ORDER BY authority]]>
      </value>
    3. Find this line and change the query that returns all users in a specific role as appropriate.
      <value>
          <![CDATA[SELECT distinct(username) as username FROM GRANTED_AUTHORITIES where authority = ? ORDER BY username]]>
      </value>
    4. Find this line and change the query that returns all users in a specific role as appropriate.
      <value>
          <![CDATA[SELECT distinct(username) as username FROM USERS ORDER BY username]]>
      </value>
    5. Save the file and close the editor.
  7. Update the default Pentaho admin user on the system to map to your JDBC admin user.
    1. Open the /pentaho-solutions/system/repository.spring.properties file with a text editor.
    2. Find these lines and change the default value from <admin> to map to your <admin username> in your JDBC system.
      singleTenantAdminUserName=<Admin User>
    3. Save the file and close the editor.
  8. To fully map the JDBC's admin role to other configuration files, specify the name of the administrator role for your JDBC authentication database in the applicationContext-pentaho-security-jdbc.xml file.
    1. Open the /pentaho-solutions/system/applicationContext-pentaho-security-jdbc.xml file with a text editor.
    2. Find these lines and change the entry key to the key assigned to the administrator role in your JDBC authentication database.
      <!-- map ldap role to pentaho security role -->
      <util:map id="jdbcRoleMap">
         <entry key="Admin" value="Administrator"/>
      </util:map>
    3. Save and close the file.
  9. Start the server by running the start-pentaho script.
The server is configured to authenticate users against the specified database.