Stack Exchange
Spring Boot Jpa configuration error
using Spring boot 1.4.3.and hibernate 5.0.11
with the following configuration for Spring data jpa
I get SQL Error:
 -5501, SQLState: 42501
user lacks privilege or object not found: ROLE
what is wrong with my config ?
pom.xml extract:
<dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-jpa</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <dependency>
            <groupId>com.zaxxer</groupId>
            <artifactId>HikariCP</artifactId>
        </dependency>
        <dependency>
            <groupId>org.hsqldb</groupId>
            <artifactId>hsqldb</artifactId>
            <scope>runtime</scope>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-tomcat</artifactId>
            <scope>provided</scope>
        </dependency>
</dependencies>     

application.properties:
spring.datasource.hikari.jdbc-url=jdbc:hsqldb:mem:test
spring.datasource.hikari.username=sa
spring.datasource.hikari.password=
spring.jpa.hibernate.ddl-auto=create
spring.jpa.show-sql=true

ConfigForJpa:
@Configuration
@EnableAutoConfiguration
@EntityScan(basePackages = {"io.boot.spring.entities"})
@EnableJpaRepositories(basePackages = {"io.boot.spring.repositories"})
@EnableTransactionManagement
public class ConfigForJPA {

@Bean
public LocalContainerEntityManagerFactoryBean
 entityManagerFactory(EntityManagerFactoryBuilder builder) {
    return builder
            .dataSource(dataSource())
            .packages("io.boot.spring")
            .persistenceUnit("io.boot.springs.entities")
            .build();
}
@Bean
@ConfigurationProperties("spring.datasource.hikari")
public HikariDataSource dataSource() {
    return (HikariDataSource) DataSourceBuilder.create()
            .type(HikariDataSource.class).build();
}

}
Service:
@Service
public class InitDBService {
@Autowired
private RoleRepository roleRepository;

@PostConstruct
public void init(){
    Role roleUser = new Role();
    roleUser.setName("ROLE_USER");
    roleRepository.save(roleUser);

}
}
console:
main] j.LocalContainerEntityManagerFactoryBean : Building JPA container EntityManagerFactory for persistence unit 'io.boot.springs.entities'
main] o.hibernate.jpa.internal.util.LogHelper  : HHH000204: Processing PersistenceUnitInfo [
name: io.boot.springs.entities
main] org.hibernate.Version                    : HHH000412: Hibernate Core {5.0.11.Final}
main] org.hibernate.cfg.Environment            : HHH000206: hibernate.properties not found
main] org.hibernate.cfg.Environment            : HHH000021: Bytecode provider name : javassist
main] o.hibernate.annotations.common.Version   : HCANN000001: Hibernate Commons Annotations {5.0.1.Final}
main] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Started.
main] com.zaxxer.hikari.pool.PoolBase          : HikariPool-1 - Driver does not support get/set network timeout for connections. (feature not supported)
main] org.hibernate.dialect.Dialect            : HHH000400: Using dialect: org.hibernate.dialect.HSQLDialect
main] j.LocalContainerEntityManagerFactoryBean : Initialized JPA EntityManagerFactory for persistence unit 'io.boot.springs.entities'
Hibernate: insert into Role (id, name) values (default, ?)
main] o.h.engine.jdbc.spi.SqlExceptionHelper   : SQL Error: -5501, SQLState: 42501
main] o.h.engine.jdbc.spi.SqlExceptionHelper   : user lacks privilege or object not found: ROLE
main] ationConfigEmbeddedWebApplicationContext : Exception encountered during context initialization - cancelling refresh attempt:
 org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'initDBService': 
 Invocation of init method failed; nested exception is org.springframework.dao.InvalidDataAccessResourceUsageException: could not prepare statement;
 SQL [insert into Role (id, name) values (default, ?)]; nested exception is org.hibernate.exception.SQLGrammarException: could not prepare statement
main] j.LocalContainerEntityManagerFactoryBean : Closing JPA EntityManagerFactory for persistence unit 'io.boot.springs.entities'

Role class:
package io.boot.spring.entities;

import java.util.List;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;
import javax.persistence.ManyToMany;

@Entity
public class Role {

@Id
@GeneratedValue
private Integer id;

private String name;

@ManyToMany(mappedBy = "roles")
private List<User> users;

getters & setters...

}
configForJpa2:
@Configuration
@EnableAutoConfiguration
@EntityScan(basePackages = {"io.boot.spring.entities"})
@EnableJpaRepositories(basePackages = {"io.boot.spring.repositories"})
@EnableTransactionManagement
public class ConfigForJPA {

@Bean
@ConfigurationProperties("spring.datasource.hikari")
public HikariDataSource dataSource() {
    return (HikariDataSource) DataSourceBuilder.create()
            .type(HikariDataSource.class).build();
}

}
Console2:
main] j.LocalContainerEntityManagerFactoryBean : Building JPA container EntityManagerFactory for persistence unit 'default'
main] o.hibernate.jpa.internal.util.LogHelper  : HHH000204: Processing PersistenceUnitInfo [ame: default
main] org.hibernate.Version                    : HHH000412: Hibernate Core {5.0.11.Final}
main] org.hibernate.cfg.Environment            : HHH000206: hibernate.properties not found
main] org.hibernate.cfg.Environment            : HHH000021: Bytecode provider name : javassist
main] o.hibernate.annotations.common.Version   : HCANN000001: Hibernate Commons Annotations {5.0.1.Final}
main] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Started.
main] com.zaxxer.hikari.pool.PoolBase          : HikariPool-1 - Driver does not support get/set network timeout for connections. 
main] org.hibernate.dialect.Dialect            : HHH000400: Using dialect: org.hibernate.dialect.HSQLDialect
main] org.hibernate.tool.hbm2ddl.SchemaExport  : HHH000227: Running hbm2ddl schema export
ibernate: alter table blog drop constraint FKpxk2jtysqn41oop7lvxcp6dqq
main] org.hibernate.tool.hbm2ddl.SchemaExport  : HHH000389: Unsuccessful: alter table blog drop constraint 
main] org.hibernate.tool.hbm2ddl.SchemaExport  : user lacks privilege or object not found: PUBLIC.BLOG
ibernate: alter table item drop constraint FK60ndn1v2u4j38nfc5yahfkb7e
main] org.hibernate.tool.hbm2ddl.SchemaExport  : HHH000389: Unsuccessful: alter table item drop constraint 
main] org.hibernate.tool.hbm2ddl.SchemaExport  : user lacks privilege or object not found: PUBLIC.ITEM
ibernate: alter table user_roles drop constraint FKj9553ass9uctjrmh0gkqsmv0d
main] org.hibernate.tool.hbm2ddl.SchemaExport  : HHH000389: Unsuccessful: alter table user_roles drop constraint 
main] org.hibernate.tool.hbm2ddl.SchemaExport  : user lacks privilege or object not found: PUBLIC.USER_ROLES
ibernate: alter table user_roles drop constraint FK7ecyobaa59vxkxckg6t355l86
main] org.hibernate.tool.hbm2ddl.SchemaExport  : HHH000389: Unsuccessful: alter table user_roles drop constraint 
main] org.hibernate.tool.hbm2ddl.SchemaExport  : user lacks privilege or object not found: PUBLIC.USER_ROLES
ibernate: drop table blog if exists
ibernate: drop table item if exists
ibernate: drop table role if exists
ibernate: drop table user if exists
ibernate: drop table user_roles if exists
ibernate: create table blog (id  (start with 1), name varchar(255), url varchar(255), user_id integer, primary key (id))
ibernate: create table item (id  (start with 1), description varchar(255), link varchar(255), published_date timestamp, title varchar(255), blog_id integer, primary key (id))
ibernate: create table role (id  (start with 1), name varchar(255), primary key (id))
ibernate: create table user (id  (start with 1), email varchar(255), name varchar(255), password varchar(255), primary key (id))


Is the Role object mapped in the hibernate ?
You can post the Role class.

I posted the Role class

Id say init is completing before the tables are created. Can you remove the PostConstruct and run it thru, Then try accessing the Role table from a console? You can use insert.sql in the resources directory to set up data

You need verify if the user that you provided in the application.properties has privileges to insert in the database.

o.k. but how should I verify that the user "sa" has the necessary privilege?

This depend of your database in MySQL for example the query to do this :  SHOW GRANTS [FOR user] (https://dev.mysql.com/doc/refman/5.7/en/show-grants.html)

I am also facing the same issuee..

It seems to be a sql related error and not a JPA one. A good approach is to login to your rdbms with the same credentials used for your connection string and check if the table corresponding to your entity can be queried with you current user.
You can use dbeaver to do so since it supports hsqldb.

o.k. but how should I verify that the user "sa" has the necessary privilege?

GRANT the permissions to the 'sa' and make sure that you issue a shutdown 'SHUTDOWN'

username  "sa" is the default user in hsqlDB which I am using
as "in memory DB " for test and it has all privilages.
when I minimize the configForJpa and let the hibernate do
its magic tables are created but I get another error :
Hibernate: alter table user_roles drop constraint
I posted the Console and configForJpa2

For me, same set of application.yml properties works perfectly for a different project.
