Authentication with Database Not Working

[vsharma1985]

Hi

Jllort

thanks a Lot for replying so instantly to the queries . As per my understanding we can have authentication for user either by having credentials from user-properties and roles-properties Files. It can also be acheived by maintaining the user deatils with LDAP server like Active Directory and With any RDBMS like MySQL.

For my application , initially i tried to authenticate the user with properties file which are placed at JBOSS_HOME/prop folder.and Modified the login-config.xml File with following authentication policy.

Code: Select all

<application-policy name = "OpenKM">
  <authentication>
    <login-module code="org.jboss.security.auth.spi.UsersRolesLoginModule" flag = "required">
      <module-option name="usersProperties">props/openkm-users.properties</module-option>
      <module-option name="rolesProperties">props/openkm-roles.properties</module-option>
    </login-module>
    <login-module code="org.jboss.security.ClientLoginModule" flag="required" />
  </authentication>
</application-policy>

and my openkm.cfg details are as follows.

Code: Select all

repository.config=repository.xml
repository.home=repository
system.user=system
default.user.role=UserRole
default.admin.role=AdminRole
#principal.adapter=es.git.openkm.principal.DatabasePrincipalAdapter
principal.adapter=es.git.openkm.principal.UsersRolesPrincipalAdapter
max.file.size=5
max.search.results=25
#system.demo=on
#update.info=on
#system.ocr=/usr/bin/tesseract
#system.openoffice=on
max.file.size=100
max.search.results=25
application.url=http://localhost:8080/OpenKM/es.git.openkm.frontend.Main/index.jsp 

The Above modication done really worked for me . but I want to have this authentication done with Database i am Using i.e. MySQL .

For this to make happen i done following Changes in configuration File.

1. In Login-config.xml File

Code: Select all

<application-policy name = "OpenKM">
       <authentication>
	        <login-module code="org.jboss.security.auth.spi.DatabaseServerLoginModule" flag = "required">
            <module-option name="dsJndiName">java:/OKMAuthDS</module-option>
            <module-option name="principalsQuery">select usr_pass as PASSWD from users where usr_id=?</module-option>
            <module-option name="rolesQuery">select ur_role as ROLEID, 'Roles' from user_role where ur_user=?</module-option>
            <module-option name="hashAlgorithm">MD5</module-option>
            <module-option name="hashEncoding">base64</module-option>
          </login-module>
           <login-module code="org.jboss.security.ClientLoginModule" flag="required" />
	   </authentication>
    </application-policy>

2.In openkm.cfg

Code: Select all

repository.config=repository.xml
repository.home=repository
system.user=system
default.user.role=UserRole
default.admin.role=AdminRole
principal.adapter=es.git.openkm.principal.DatabasePrincipalAdapter
#principal.adapter=es.git.openkm.principal.UsersRolesPrincipalAdapter
max.file.size=5
max.search.results=25
#system.demo=on
#update.info=on
#system.ocr=/usr/bin/tesseract
#system.openoffice=on
max.file.size=100
max.search.results=25
application.url=http://localhost:8080/OpenKM/es.git.openkm.frontend.Main/index.jsp 

3. just for reference my openkm.ds File is like

Code: Select all

<?xml version="1.0" ?>
<datasources>
<!-- OpenKM User Activity -->
<local-tx-datasource>
<jndi-name>OKMActivityDS</jndi-name>
<connection-url>jdbc:mysql://localhost:3306/openkm</connection-url>
<driver-class>com.mysql.jdbc.Driver</driver-class>
<user-name>root</user-name>
<password>root</password>
<min-pool-size>5</min-pool-size>
<max-pool-size>20</max-pool-size>
<idle-timeout-minutes>0</idle-timeout-minutes>
<track-statements/>
<!--<security-domain>HsqlDbRealm</security-domain>-->
<prepared-statement-cache-size>32</prepared-statement-cache-size>
<metadata>
<type-mapping>MySQL</type-mapping>
</metadata>
<!-- <depends>jbosservice=Hypersonic,database=OKMActivity</depends> -->
</local-tx-datasource>
<!-- OpenKM User Auth -->
<local-tx-datasource>
<jndi-name>OKMAuthDS</jndi-name>
<connection-url>jdbc:mysql://localhost:3306/openkm</connection-url>
<driver-class>com.mysql.jdbc.Driver</driver-class>
<user-name>root</user-name>
<password>root</password>
<min-pool-size>5</min-pool-size>
<max-pool-size>20</max-pool-size>
<idle-timeout-minutes>0</idle-timeout-minutes>
<track-statements/>
<!--<security-domain>HsqlDbRealm</security-domain>-->
<prepared-statement-cache-size>32</prepared-statement-cache-size>
<metadata>
<type-mapping>MySQL</type-mapping>
</metadata>
<!--<depends>jbosservice=Hypersonic,database=OKMAuth</depends> -->
</local-tx-datasource>
<local-tx-datasource>
<jndi-name>OKMDashboardStatsDS</jndi-name>
<connection-url>jdbc:mysql://localhost:3306/openkm</connection-url>
<driver-class>com.mysql.jdbc.Driver</driver-class>
<user-name>root</user-name>
<password>root</password>
<min-pool-size>5</min-pool-size>
<max-pool-size>20</max-pool-size>
<idle-timeout-minutes>0</idle-timeout-minutes>
<track-statements/>
<!--<security-domain>HsqlDbRealm</security-domain>-->
<prepared-statement-cache-size>32</prepared-statement-cache-size>
<metadata>
<type-mapping>MySQL</type-mapping>
</metadata>
<!-- <depends>jbosservice=Hypersonic,database=OKMDashboardStats</depends>-->
</local-tx-datasource>
<local-tx-datasource>
    <jndi-name>OKMWorkflowDS</jndi-name>
    <connection-url>jdbc:mysql://localhost:3306/okmworkflow</connection-url>
    <driver-class>com.mysql.jdbc.Driver</driver-class>
    <user-name>root</user-name>
    <password>root</password>
    <min-pool-size>5</min-pool-size>
    <max-pool-size>20</max-pool-size>
    <idle-timeout-minutes>5</idle-timeout-minutes>
    <track-statements/>
    <!--<security-domain>HsqlDbRealm</security-domain>-->
    <prepared-statement-cache-size>32</prepared-statement-cache-size>
    <metadata>
       <type-mapping>mySQL</type-mapping>
    </metadata>
  </local-tx-datasource>
</datasources>

and Finally my DatabasePrincipalAdapter.java File

Code: Select all

package es.git.openkm.principal;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Iterator;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import es.git.openkm.core.Config;
import es.git.openkm.dao.AuthDAO;
import es.git.openkm.dao.bean.Role;
import es.git.openkm.dao.bean.User;
public class DatabasePrincipalAdapter implements PrincipalAdapter {
	private static Logger log = LoggerFactory.getLogger(DatabasePrincipalAdapter.class);
	public Collection<String> getUsers() throws PrincipalAdapterException {
		log.debug("getUsers()");
		ArrayList<String> list = new ArrayList<String>();

		try {
			Collection<User> col = AuthDAO.getInstance().findAllUsers();
			
			for (Iterator<User> it = col.iterator(); it.hasNext(); ) {
				User dbUser = it.next();
				
				if (!Config.PRINCIPAL_DATABASE_FILTER_INACTIVE_USERS.equals("on") || dbUser.isActive()) {
					list.add(dbUser.getId());
				}
			}
		} catch (SQLException e) {
			throw new PrincipalAdapterException(e.getMessage(), e);
		}
		log.debug("getUsers: "+list);
		return list;
	}
	public Collection<String> getRoles() throws PrincipalAdapterException {
		log.debug("getRoles()");
		ArrayList<String> list = new ArrayList<String>();
			try {
			Collection<Role> col = AuthDAO.getInstance().findAllRoles();
			
			for (Iterator<Role> it = col.iterator(); it.hasNext(); ) {
				Role dbRole = it.next();
				list.add(dbRole.getId());
			}
		} catch (SQLException e) {
			throw new PrincipalAdapterException(e.getMessage(), e);
		}
		
		log.debug("getRoles: "+list);
		return list;
	}
	public Collection<String> getMails(Collection<String> users) throws PrincipalAdapterException {
		log.debug("getMails()");
		ArrayList<String> list = new ArrayList<String>();

		try {
			for (Iterator<String> it = users.iterator(); it.hasNext(); ) {
				String userId = it.next();
				es.git.openkm.dao.bean.User user = AuthDAO.getInstance().findUserByPk(userId);
				if (user != null && !user.getEmail().equals("")) {
					list.add(user.getEmail());
				}
			}
		} catch (SQLException e) {
			throw new PrincipalAdapterException(e.getMessage(), e);
		}

		log.debug("getMails: "+list);
		return list;
	}
}

My Query is that even after modifying these file respectively i am not able to access the application as i was able to do in previous case where authentication was happening with the help of properties File.

I am trying for this since last two weeks .but no progress . can u please help me out.... thanks in advance.

[jllort]

You want to configure in mysql no ?

Let's do it by phases, first authentication.
0- I recomend you make it in a clean installation.
1- Seeing your openkm-ds datasource, you've only created one database. Better If you create several.
2- Start with <jndi-name>OKMAuthDS</jndi-name>

Put autoreconnect parameter
<connection-url>jdbc:mysql://localhost:3306/okm_auth?autoReconnect=true</connection-url>

Before doing mysql change, navigate across actual hypersonic database structure, you must create table structure in your mysql ( have you done it ? )
take a look here, http://wiki.openkm.com/index.php/Browsi ... d_database

3- Change login-config.xml
If you execute mysql query, you'll see must change something to go right ( remove ' character from initial query )
<module-option name="principalsQuery">select usr_pass as PASSWD from users where usr_id=? and usr_active=true</module-option>

4- You don't need to create your own principalAdapter it's not needed, if you create database tables correctly and change openkm-ds.xml connection poll. Give the default adapter it runs right, you don't need to create your own.
principal.adapter=es.git.openkm.principal.DatabasePrincipalAdapter

After you've done it successfully and you'll authenticate with a user in OpenKM, we'll make others changes.

Note: There's a reson why installation in mysql is a protected resource in wiki, it's not easy to configure. Do you want to use it in a production environment or only want to testing OpenKM in mysql ? In some cases is more cheaper pay few money to OpenKM network ( 30 € annual subscription ) or simply query some support to only install in your network, really with few money you'll get well done installed and you'll not lose so much time ( more expensive ). It's only a suggestion.

[vsharma1985]

Thanks Jllort

your are a truly great helping hand for openkm users. I figured out the problem . It was required to Comment the lines in login-config.xml

<!-- <module-option name="hashAlgorithm">MD5</module-option>
<module-option name="hashEncoding">base64</module-option>
-->
as the following above lines were introducing the mechanism of hashing password , so it was creating problem when ever we try to fetch the record with the help of principal Queries.

so below the Authentication poilicy worked for me while i need authentication done for users with Database.

Code: Select all

<application-policy name = "OpenKM">
    <authentication>
        <login-module code="org.jboss.security.auth.spi.DatabaseServerLoginModule" flag = "required">
            <module-option name="dsJndiName">java:/OKMAuthDS</module-option>
            <module-option name = "unauthenticatedIdentity">guest</module-option> 
            <module-option name="principalsQuery">select usr_pass as PASSWD from users where usr_id=?</module-option>
            <module-option name="rolesQuery">select ur_role as ROLEID, 'Roles' from user_role where ur_user=?</module-option>
        </login-module>
        <login-module code="org.jboss.security.ClientLoginModule" flag="required" />
    </authentication>
</application-policy>

thanks Once agian!!!!