Tomcat: user does not have sufficient privileges or object not found

0

I'm trying to access an access file from a web application using the jdbc ucanaccess 4.0.3 driver. The tests when building the used jar are executed without problem.

Application Context in jar file:

<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>

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 building the jar with Maven:

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 have also built several junit tests:

@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)

     

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 (role_id int not null, hib_sess_id CHAR (36) null)

But when I try to login 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 have tried changing the format of Access to accdb, changing parameters of the connection (memory = false; keepMirror = ...) but with the same results.

Any help will be appreciated.

    
asked by cushieOwner 23.02.2018 в 13:51
source

1 answer

0

Finally I found the error. When defining the jndiname resource in the context.xml file, there was an error in the validation query:

<Resource name="jdbc/MyDbSec" auth="Container" type="javax.sql.DataSource" factory="org.apache.tomcat.jdbc.pool.DataSourceFactory"
    driverClassName="net.ucanaccess.jdbc.UcanaccessDriver" 
    url="jdbc:ucanaccess://C:/data/myDb-sec.accdb" 
    username="" 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" />

I usually work with PostgresSQL but the validation query for HSQLDB is as follows:

SELECT 1 FROM any_existing_table WHERE 1=0

(Narurally, you have to replace 'any_existing_table' with the name of a table that exists, it worked for me with the prefix 'HT_' in the name.)

Finally, connect and perform CRUD operations correctly. Maybe it will help someone!

    
answered by 28.02.2018 в 18:10