HSQLDB - user lacks privilege or object not found

[Timothy Sam]

Hi Ranchers!

I'm currently facing a problem when running a JUnit test within an ANT script. My Unit test calls for an HSQLDB query. Outside of this JUnit test, all my queries like creating a table, insert data to the table and selecting data from the table runs fine. However, when time comes to call my test case class, it throws out a nested exception is java.sql.SQLException: user lacks privilege or object not found: USERS_TBL

I'm running an in-memory database
db.driver=org.hsqldb.jdbcDriver
db.url=jdbc:hsqldb:mem:.
db.user=sa
db.pw=

Below is the stack trace:

[junit] StatementCallback; bad SQL grammar [SELECT user_seq_id, username, pa
ssword FROM users_tbl]; nested exception is java.sql.SQLException: user lacks pr
ivilege or object not found: USERS_TBL
[junit] org.springframework.jdbc.BadSqlGrammarException: StatementCallback;
bad SQL grammar [SELECT user_seq_id, username, password FROM users_tbl]; nested
exception is java.sql.SQLException: user lacks privilege or object not found: US
ERS_TBL
[junit] at org.springframework.jdbc.support.SQLStateSQLExceptionTranslat
or.doTranslate(SQLStateSQLExceptionTranslator.java:98)
[junit] at org.springframework.jdbc.support.AbstractFallbackSQLException
Translator.translate(AbstractFallbackSQLExceptionTranslator.java:72)
[junit] at org.springframework.jdbc.support.AbstractFallbackSQLException
Translator.translate(AbstractFallbackSQLExceptionTranslator.java:80)
[junit] at org.springframework.jdbc.support.AbstractFallbackSQLException
Translator.translate(AbstractFallbackSQLExceptionTranslator.java:80)
[junit] at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTempla
te.java:406)
[junit] at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate
.java:455)
[junit] at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate
.java:463)
[junit] at com.mycompany.dao.spring.UserDAOSpringImpl.findUsers(UserDAO
SpringImpl.java:77)

Below is the Spring JDBC code

        private static final String SELECT_USERS = "SELECT user_seq_id, username, password " +
	"FROM users_tbl";
	
	@Override
	public List<UserDTO> findUsers() {
		List<UserDTO> userList = null;
		
		if (jdbcTemplate.getDataSource() != null){
			userList = jdbcTemplate.query(SELECT_USERS, new UserDTORowMapper() );
		}
		
		//if null, return an empty list
		if (userList == null){
			userList = new ArrayList<UserDTO>(0);
		}
		
		return userList;
	}

	public static final class UserDTORowMapper implements RowMapper{
		
		@Override
		public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
			UserDTO userDTO = new UserDTO();
			userDTO.setUserSequenceId( rs.getInt("user_seq_id") );
			userDTO.setUsername( rs.getString("username") );
			userDTO.setPassword( rs.getString("password") );
			
			return userDTO;
		}
		
	}

Thank you very much and I look forward to your kind answers.

[Jeanne Boyarsky]

Timothy,
What user do you run as when you are outside the test. is it the same "sa" user?

[Timothy Sam]

Hi Jeanne

Thanks for the reply! Yes, I also run as user "sa"

[Timothy Sam]

Uhmmm... Help? I tried to GRANT ALL PRIVILEGES ON USERS_TBL TO PUBLIC before running the JUnit tests but still not luck...

[Zongjian Feng]

exception:

  org.hibernate.exception.SQLGrammarException: could not execute query
	......
	at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:197)
Caused by: java.sql.SQLException: user lacks privilege or object not found: SMSRTN0_.SMS19_1_
	at org.hsqldb.jdbc.Util.sqlException(Util.java:200)
        ....
  

Console output:

  Hibernate: 
    select
        smsrtn0_.ID as ID1_,
        ... ...
        smsrtn0_.LOCALMSG as LOCALMSG1_,
        smsrtn0_.SMS_SEGMENTSUM as SMS19_1_
    from
        TB_SMS_RTN smsrtn0_ 
    where
        smsrtn0_.SMS_SOLEID='1'  

cause: physical table do not have the column called SMS_SEGMENTSUM
so: alter table tb_sms_rtn add column SMS_SEGMENTSUM varchar(10);
and my test case works.

[Karthik Shiraly]

I know this is an old thread, but I'm putting up my findings to help others who faced this issue, and were sent here by google.

I faced this same error, with the following steps:

- Tables were created by an SQL script which had CREATE TABLE statements and a COMMIT.

- Ant was used to execute this script, with hsqldb in standalone mode using "jdbc:hsqldb:file:..." URL under "SA" user credential. Ant reported success and terminated.

- A junit test attempted to insert a record, but failed with error: user lacks privilege or object not found : MYTABLE

- When I opened the .script file, I observed that it didn't have any of my CREATE TABLE statements in it.

- Switched hsqldb version, but faced same problem with both hsqldb v1.8.1.2 (stable) and v2.0.0-rc8 (release candidate).

After going through the guides in depth, I inferred that the SHUTDOWN command is very important to hsqldb. It does a clean shutdown and persists all cached information only if SHUTDOWN is called. It does not actually save the CREATE TABLE statements in the .script file even if a COMMIT is done, till a SHUTDOWN is done.

So I added a "SHUTDOWN;" SQL command at the end of my table creation SQL script, and sure enough everything worked after that.

I feel the documentation could have mentioned the importance of shutdown explicitly for new users. It is kind of mentioned, but not very strongly, and the implications of not shutting down can't be easily inferred by users who're used to other database products, where shutdown is mostly implicit.

I haven't experimented in memory mode or server mode, so I'm not sure whether these observations are true there too.

- Karthik Shiraly

[Jan Cumps]

Thank you for posting this, Karthik.
Welcome to JavaRanch!

[Som Bal]

Something to be aware of -

if you are using Ant to launch hbm2ddl for schema export, in <hbm2ddl> task make sure the export is turned to true.

Otherwise, the error could be similar to the one discussed here.
I spend few hours to figure this out.

    <target name="schemaexport" depends="compile, copymetafiles"
        description="Exports DDL to file in build directory">

        <hibernatetool destdir="${build.dir}">
            <classpath path="${build.classes.dir}"/>
            <jpaconfiguration persistenceunit="SomBlueprintData"/>
            <hbm2ddl
                drop="true"
                create="true"
                export="true"
                outputfilename="${proj.shortname}-ddl.sql"
                delimiter=";"
                format="true"/>
        </hibernatetool>
    </target>

[Rajani Gummadi]

First of all, I apologize to dig this old thread out, but I thought this would be the best continuation as my problem is exactly the same as the subject line says.

Secondly, I never used HSQLDB before and I installed it, set up my settings

Type : HSQL Database Engine Standalone
Driver : org.hsqldb.jdbcDriver
URL : jdbc:hsqldb:file:MyDB
User : SA
Password : <<Null>> //No password

I created a table called Employee under schema PUBLIC and from the script I see the following statements

CREATE USER SA PASSWORD DIGEST 'd41d8cd98f00b204e9800998ecf8427e'
ALTER USER SA SET LOCAL TRUE
CREATE SCHEMA PUBLIC AUTHORIZATION DBA
SET SCHEMA PUBLIC
CREATE MEMORY TABLE PUBLIC.EMPLOYEE(ID INTEGER NOT NULL PRIMARY KEY,EMP_NAME VARCHAR(20),EMP_DEPT VARCHAR(20),EMP_SALARY DOUBLE)
ALTER SEQUENCE SYSTEM_LOBS.LOB_ID RESTART WITH 1
SET DATABASE DEFAULT INITIAL SCHEMA PUBLIC
GRANT USAGE ON DOMAIN INFORMATION_SCHEMA.YES_OR_NO TO PUBLIC
GRANT USAGE ON DOMAIN INFORMATION_SCHEMA.TIME_STAMP TO PUBLIC
GRANT USAGE ON DOMAIN INFORMATION_SCHEMA.CARDINAL_NUMBER TO PUBLIC
GRANT USAGE ON DOMAIN INFORMATION_SCHEMA.CHARACTER_DATA TO PUBLIC
GRANT USAGE ON DOMAIN INFORMATION_SCHEMA.SQL_IDENTIFIER TO PUBLIC
GRANT DBA TO SA
SET SCHEMA SYSTEM_LOBS
INSERT INTO BLOCKS VALUES(0,2147483647,0)
SET SCHEMA PUBLIC
INSERT INTO EMPLOYEE VALUES(1,'ABC','ITG',5000.0E0)

From HSQLDB, when I did select, it worked and retrieved me a record (1,ABC,ITG,5000)

So I thought the table creation is fine and then moved onto write a sample JDBC app to retrieve the record

public class MyJDBC {
	public static void main(String[] args){
		try {
			//Class.forName("org.hsqldb.jdbc.JDBCDriver");
			Class.forName("org.hsqldb.jdbcDriver");
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		try {
			Connection c = DriverManager.getConnection("jdbc:hsqldb:file:MyDB","SA","");
			Statement s = c.createStatement();
			ResultSet rs = s.executeQuery("SELECT * FROM PUBLIC.EMPLOYEE");
			while(rs.next()){
				System.out.println(rs.getInt(0));
				System.out.println(rs.getString(1));
				System.out.println(rs.getString(2));
				System.out.println(rs.getDouble(3));
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
	}

As you see, it is a very simple program to retrieve the records. But upon running, it is failing at ResultSet rs = s.executeQuery("SELECT * FROM PUBLIC.EMPLOYEE"); with the exception

java.sql.SQLException: user lacks privilege or object not found: EMPLOYEE
	at org.hsqldb.jdbc.Util.sqlException(Unknown Source)
	at org.hsqldb.jdbc.JDBCStatement.fetchResult(Unknown Source)
	at org.hsqldb.jdbc.JDBCStatement.executeQuery(Unknown Source)
	at org.learning.jdbc.MyJDBC.main(MyJDBC.java:27)
Caused by: org.hsqldb.HsqlException: user lacks privilege or object not found: EMPLOYEE
	at org.hsqldb.error.Error.error(Unknown Source)
	at org.hsqldb.error.Error.error(Unknown Source)
	at org.hsqldb.SchemaManager.getTable(Unknown Source)
	at org.hsqldb.ParserDQL.readTableName(Unknown Source)
	at org.hsqldb.ParserDQL.readTableOrSubquery(Unknown Source)
	at org.hsqldb.ParserDQL.XreadTableReference(Unknown Source)
	at org.hsqldb.ParserDQL.XreadFromClause(Unknown Source)
	at org.hsqldb.ParserDQL.XreadTableExpression(Unknown Source)
	at org.hsqldb.ParserDQL.XreadQuerySpecification(Unknown Source)
	at org.hsqldb.ParserDQL.XreadSimpleTable(Unknown Source)
	at org.hsqldb.ParserDQL.XreadQueryPrimary(Unknown Source)
	at org.hsqldb.ParserDQL.XreadQueryTerm(Unknown Source)
	at org.hsqldb.ParserDQL.XreadQueryExpressionBody(Unknown Source)
	at org.hsqldb.ParserDQL.XreadQueryExpression(Unknown Source)
	at org.hsqldb.ParserDQL.compileCursorSpecification(Unknown Source)
	at org.hsqldb.ParserCommand.compilePart(Unknown Source)
	at org.hsqldb.ParserCommand.compileStatements(Unknown Source)
	at org.hsqldb.Session.executeDirectStatement(Unknown Source)
	at org.hsqldb.Session.execute(Unknown Source)
	... 3 more

Is my query in extracting records wrong ? or is that because of the statement in .script file

CREATE MEMORY TABLE PUBLIC.EMPLOYEE(ID INTEGER NOT NULL PRIMARY KEY,EMP_NAME VARCHAR(20),EMP_DEPT VARCHAR(20),EMP_SALARY DOUBLE)

which may necessarily mean that the table is created in memory, but not accessible from another process like external java application but on same jvm.

[Jan Cumps]

Rajani Gummadi wrote:First of all, I apologize to dig this old thread out, but I thought this would be the best continuation as my problem is exactly the same as the subject line says.

...

We ask you to create a new one. You have a different issue (otherwise, the resolution posted in this thread would have resolved your problem)