Manual JDBC Connection Configuration

Last updated
Save as PDF

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.

If you are using the Pentaho 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.

Stop the Pentaho Server.
Open /pentaho-solutions/system/security.properties with a text editor.
Change the value of the provide property to jdbc.
Set up the connection to the database that holds the users and authorities:
1. Open the /pentaho-solutions/system/applicationContext-spring-security-jdbc.properties file with a text editor. Find the following two lines and change the jdbcDriver and URL the appropriate values.
```
datasource.driver.classname=org.hsqldb.jdbcDriver
```
```
datasource.url=jdbc:hsqldb:hsql://localhost:9002/userdb
```
2. Change the user name and password by editing the following two items:
```
\datasource.username=sa, datasource.password=
```
3. Set the validation query by editing its row. Examples of different validation queries are shown 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 the following 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.
If needed, modify the user queries that pull information about users and authorities:
1. Open /pentaho-solutions/system/applicationContext-spring-security-jdbc.xml with a text editor.
2. Find the following line and change the SQL query returning the user and roles for which the user is a member to the appropriate statement:
```
<value>
    <![CDATA[SELECT username, authority FROM GRANTED_AUTHORITIES WHERE username = ? ORDER BY authority]]>
</value>
```
3. Find the following line and change the SQL query that determines the user, password, and whether they can log in to the appropriate statement:
```
<value>
    <![CDATA[SELECT username, password, enabled FROM USERS WHERE username = ? ORDER BY username]]>
</value>
```
If needed, modify the following role queries that pull information about users and authorities.
1. Open the /pentaho-solutions/system/applicationContext-pentaho-security-jdbc.xml file with a text editor.
2. Find the following line and change the SQL query showing the roles for security on objects to the appropriate statement:
```
<value>
    <![CDATA[SELECT distinct(authority) as authority FROM AUTHORITIES ORDER BY authority]]>
</value>
```
3. Find the following line and change the SQL query that returns all users in a specific role to the appropriate statement:
```
<value>
    <![CDATA[SELECT distinct(username) as username FROM GRANTED_AUTHORITIES where authority = ? ORDER BY username]]>
</value>
```
4. Find the following line and change the SQL query that returns all users by order to the appropriate statement:
```
<value>
    <![CDATA[SELECT distinct(username) as username FROM USERS ORDER BY username]]>
</value>
```
5. Save the file and close the editor.
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 the following 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.
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 the following lines and change the entry key to the key assigned to the administrator role in your JDBC authentication database:
```

<util:map id="jdbcRoleMap">
   <entry key="Admin" value="Administrator"/>
</util:map>
```
3. Save and close the file.
Start the Pentaho Server.

The server is configured to authenticate users against the specified database.