Tomcat: user lacks privilege or object not found

[Josep M. Montserrat]

I can not access an access file from an application web using a jar that seems to have no problems.

Application Context in jar file:

<?xml version="1.0" encoding="UTF-8"?>
<beans default-lazy-init="true"  
    xmlns="http://www.springframework.org/schema/beans" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xmlns:aop="http://www.springframework.org/schema/aop" xmlns:tx="http://www.springframework.org/schema/tx" xmlns:mvc="http://www.springframework.org/schema/mvc"
    xmlns:jee="http://www.springframework.org/schema/jee" xmlns:context="http://www.springframework.org/schema/context"
    xmlns:p="http://www.springframework.org/schema/p"
    xsi:schemaLocation="
        http://www.springframework.org/schema/beans 
        http://www.springframework.org/schema/beans/spring-beans-4.0.xsd
        http://www.springframework.org/schema/context
        http://www.springframework.org/schema/context/spring-context-4.0.xsd
        http://www.springframework.org/schema/aop
        http://www.springframework.org/schema/aop/spring-aop-4.0.xsd
        http://www.springframework.org/schema/mvc
        http://www.springframework.org/schema/mvc/spring-mvc-4.0.xsd
        http://www.springframework.org/schema/tx
        http://www.springframework.org/schema/tx/spring-tx-4.0.xsd
        http://www.springframework.org/schema/jee 
        http://www.springframework.org/schema/jee/spring-jee-4.0.xsd">

    <context:annotation-config />

    <context:component-scan base-package="cat.dinet.lblchem.sec" />

    <bean id="persistenceExceptionTranslationPostProcessor" class="org.springframework.dao.annotation.PersistenceExceptionTranslationPostProcessor" />
    <context:property-placeholder location="classpath:db-lblchem-sec.properties" />

    <bean id="dataSourceBase" class="org.apache.tomcat.jdbc.pool.DataSource">
            <property name="driverClassName" value="${jdbc.driverClassName}" />
            <property name="url" value="${jdbc.url}" />
            <property name="username" value="${jdbc.username}" />
            <property name="password" value="${jdbc.password}" />
    </bean>

    <bean id="sessionFactoryBase" class="org.springframework.orm.hibernate4.LocalSessionFactoryBean">
        <property name="dataSource" ref="dataSourceBase" />
        <property name="packagesToScan" value="cat.dinet.lblchem.sec.model" />
        <property name="hibernateProperties">
            <props> 
                <prop key="hibernate.dialect">${hibernate.dialect}</prop>
                <prop key="hibernate.show_sql">${hibernate.show_sql}</prop>
                <prop key="hibernate.format_sql">${hibernate.format_sql}</prop>
                <prop key="hibernate.connection.autocommit">${hibernate.connection.autocommit}</prop>
                <prop key="hibernate.cache.use_second_level_cache">false</prop>
                <prop key="hibernate.cache.use_query_cache">true</prop>
                <prop key="hibernate.cache.provider_class">org.hibernate.cache.EhCacheProvider</prop>
                <prop key="hibernate.cache.region.factory_class">org.hibernate.cache.ehcache.EhCacheRegionFactory</prop>
            </props>
        </property>
        <qualifier value="sessionFactoryBase" />
    </bean>

    <bean id="transactionManagerBase" class="org.springframework.orm.hibernate4.HibernateTransactionManager">
        <property name="sessionFactory" ref="sessionFactoryBase" />
        <qualifier value="transactionManagerBase" />
    </bean>

</beans>

JDBC Connection prperties:

jdbc.driverClassName=net.ucanaccess.jdbc.UcanaccessDriver
jdbc.url=jdbc:ucanaccess://E:/lblchem32/data/lblchem-sec.mdb
jdbc.username=admin
jdbc.password=

hibernate.dialect=com.hxtt.support.hibernate.HxttAccessDialect
hibernate.show_sql=true
hibernate.format_sql=true
hibernate.default_schema=public
hibernate.hbm2ddl.auto=validate
hibernate.connection.autocommit=false

When build the jar with Maven install:

Hibernate: 
    create table HT_dn_sec_user (usr_id int not null, hib_sess_id CHAR(36) null)
Hibernate: 
    create table HT_dn_sec_user_role (uro_id int not null, hib_sess_id CHAR(36) null)
Hibernate: 
    create table HT_dn_sec_right (rig_id int not null, hib_sess_id CHAR(36) null)
Hibernate: 
    create table HT_dn_sec_role_right (rri_id int not null, hib_sess_id CHAR(36) null)
Hibernate: 
    create table HT_dn_sec_role (rol_id int not null, hib_sess_id CHAR(36) null)
....

I try several junit test that run without errors:

    @Test
    public void getUserByLoginname() {
        DnSecUser obj = this.getDnSecUserDAO().getUserByLoginname("admin");
        System.out.println("admin has id=" + obj.getId());
        Assert.assertNotNull(obj);
    }

with the following output

Hibernate: 
    select
        dnsecuser0_.usr_id as usr_id1_6_,
        dnsecuser0_.usr_firstname as usr_fir19_6_,
        dnsecuser0_.usr_lastname as usr_las21_6_,
        dnsecuser0_.usr_loginname as usr_log22_6_,
        dnsecuser0_.usr_password as usr_pas24_6_,
    ....
        dnsecuser0_.version as version29_6_ 
    from
        dn_sec_user dnsecuser0_ 
    where
        dnsecuser0_.usr_loginname=?
admin has id=3

In the web app I there are a context.xml

<Context>
    ....
    <Resource name="jdbc/lblchem-sec" auth="Container" type="javax.sql.DataSource" factory="org.apache.tomcat.jdbc.pool.DataSourceFactory"
        driverClassName="net.ucanaccess.jdbc.UcanaccessDriver" 
        url="jdbc:ucanaccess://E:/lblchem32/data/lblchem-sec.mdb" 
        username="admin" password=""
        initialSize="5" maxActive="20" maxIdle="12" minIdle="8" timeBetweenEvictionRunsMillis="34000"
        minEvictableIdleTimeMillis="55000" validationQuery="SELECT 1" validationInterval="34000" testOnBorrow="true"
        removeAbandoned="true" removeAbandonedTimeout="55" defaultAutoCommit="false" />
</Context>

On running Tomcat:

2018-02-23 11:54:46,906 DEBUG  [main] DatabaseImpl M[readSystemCatalog] - Finished reading system catalog.  Tables: [HT_dn_sec_right, HT_dn_sec_role, HT_dn_sec_role_right, HT_dn_sec_user, HT_dn_sec_user_role, ..., dn_sec_right, dn_sec_role, dn_sec_role_right, dn_sec_user, dn_sec_user_role, ...] (Db=lblchem-sec.mdb)
<code>
Hibernate: 
    create table HT_dn_sec_user (usr_id int not null, hib_sess_id CHAR(36) null)
Hibernate: 
    create table HT_dn_sec_user_role (uro_id int not null, hib_sess_id CHAR(36) null)
Hibernate: 
    create table HT_dn_sec_right (rig_id int not null, hib_sess_id CHAR(36) null)
Hibernate: 
    create table HT_dn_sec_role_right (rri_id int not null, hib_sess_id CHAR(36) null)
Hibernate: 
    create table HT_dn_sec_role (rol_id int not null, hib_sess_id CHAR(36) null)

But when I try to login:

Hibernate: 
    select
        dnsecuser0_.usr_id as usr_id1_6_,
        dnsecuser0_.usr_firstname as usr_fir19_6_,
        dnsecuser0_.usr_lastname as usr_las21_6_,
        dnsecuser0_.usr_loginname as usr_log22_6_,
        dnsecuser0_.usr_password as usr_pas24_6_,
    ....
        dnsecuser0_.version as version29_6_ 
    from
        dn_sec_user dnsecuser0_ 
    where
        dnsecuser0_.usr_loginname=?
2018-02-23 11:55:25,864 ERROR  [http-nio-8080-exec-5] SqlExceptionHelper M[logExceptions] - UCAExc:::4.0.3  user lacks privilege or object not found: DN_SEC_USER

I try accdb format and diferent driver options (memory=false;keepMirror=...) but with the same result.
Any help would be appreciate

[Gord Thompson]

If you are using

hibernate.dialect=com.hxtt.support.hibernate.HxttAccessDialect

 
then you should be using the JDBC driver from HXTT, not UCanAccess.

(UCanAccess does not currently have a Hibernate dialect, although one is under development.)