Why does uCanAccess fail to execute query with error `user lacks privilege or object not found` when table and column exist?

Question

I have a database with a number of tables, three of which are as follows:

Controllers_Readers
-------------------
ControllerID: Number (Long Integer), Default 0, Indexed (Y, no Duplicated)
ReaderID: Number (Long Integer), Default 0, Indexed (Y, No Duplicates)

Controllers
-----------
ControllerID: Number (Long Integer), Default 0, Indexed (Y, No Duplicates)
Description: Short Text (32)
Name: Short Text (32)
...

Readers
-------
ReaderID: Number (Long Integer), Default 0, Indexed (Y, No Duplicates)
Direction: Number (Long Integer)
Description: Short Text (32)
Name: Short Text (32)
...

I have the following Java code to check if a table exists and if it contains a particular column, based on this answer to a question about table metadata:

/* Use the metadata to check if a table exists and has a specific column */
  public static boolean tableHasColumn(Connection con, String tableName, String colName)
    throws ClassNotFoundException, SQLException, InstantiationException,
    IllegalAccessException 
  {
    boolean exists = false, hasCol = false;

    ResultSet rs = null, r = null;
    Statement s = null;

    try {
      DatabaseMetaData dbm = con.getMetaData();
      rs = dbm.getTables(null, null, tableName, null);
      if (rs.next() && rs.getRow() > -1) {
        exists = true;
        s = con.createStatement();
        r = s.executeQuery("SELECT * FROM " + tableName + " LIMIT 1");
        if (r.next()) {
          ResultSetMetaData rsmd = r.getMetaData();
          for (int col = 1; col < rsmd.getColumnCount(); col++) {
            String name = rsmd.getColumnName(col);
            logger.debug("Found column \"" + name + "\" in \"" + tableName + "\".");
            if (name.equals(colName)) {
              hasCol = true;
              break;
            }
          }
        }
      }
    } finally {
      if (null != rs) {
        try {
          rs.close();
        } catch (SQLException ex) {
          logger.warn(
            "Failed to close Resultset to DB while checking if table exists!"
          );
        }
      }
    }
    logger.debug(
      "Table (" + tableName + ") Exists: " + String.valueOf(exists).toUpperCase()
      + "\tHas Column (" + colName + "): " + String.valueOf(hasCol).toUpperCase()
    );

    return (exists && hasCol);
  }

Supplying values of "Controllers_Readers" and "ControllerID" for tableName and colName, respectively, results in a return value of true. However, when I run the following query, I get an error message of org.hsqldb.HsqlException: user lacks privilege or object not found: CONTROLLERS_READERS.CONTROLLERID :

SELECT C.Description AS cName, C.ControllerID AS CID,
  D.DirectionText as dName, D.Direction AS dirNum, R.Description AS rName,
  R.ReaderID AS RID, AP.Name AS aName, AP.Description as aDescrip, AP.PointID
FROM Controllers AS C, Controllers_Readers AS CR, Readers AS R,
  Directions AS D, AccessPoints AS AP, Points_Controllers as PC
WHERE Controllers_Readers.ControllerID = Controllers.ControllerID 
  AND Readers.ReaderID = Controllers_Readers.ReaderID
  AND Directions.Direction = Readers.Direction AND 
  Points_Controllers.ControllerID = Controllers.ControllerID
  AND AccessPoints.PointID = Points_Controllers.PointID
GROUP BY Controllers.Description, Controllers.ControllerID, 
  Directions.DirectionText, Directions.Direction, Readers.Name, 
  Readers.Description, Readers.ReaderID,  AccessPoints.Name, 
  AccessPoints.Description, AccessPoints.PointID 
ORDER BY Controllers.Description, Controllers.ControllerID, 
  Directions.DirectionText, Directions.Direction, Readers.Name, 
  Readers.Description, Readers.ReaderID,  AccessPoints.Name,
  AccessPoints.Description, AccessPoints.PointID

The relevant log output is as follows:

2018-08-30 14:24:35 [DEBUG] db.Locations:35 - Location: LOCAL   DB Path: \\sentinel.domain\TempStorage\Me\dbs\Redacted.mdb
2018-08-30 14:24:35 [DEBUG] db.Digger:767 - Connect to DB on path: \\sentinel.domain\TempStorage\Me\dbs\Redacted.mdb
2018-08-30 14:24:35 [INFO ] db.Digger:790 - Connecting to jdbc:ucanaccess:////sentinel.domain/TempStorage/Me/dbs//Redacted.mdb;openExclusive=false;concatNulls=false;ignoreCase=true
2018-08-30 14:24:37 [DEBUG] impl.DatabaseImpl:887 - Finished reading system catalog.  Tables: [AccessNumbers, AccessNumbers_Controllers, AccessNumbers_Groups, AccessNumbers_Readers, AccessPoints, Areas, Areas_Controllers, Areas_Points, Areas_Timezones, Controllers, Controllers_Expanders, Controllers_Readers, Directions, Events, Expanders_Readers, Groups, Groups_Areas, Groups_Controllers, Groups_Timezones, Holidays, Points_Controllers, Points_Readers, Points_Timezones, ReaderExpanders, Readers, SelectedAreas, SelectedDates, Sites, Sites_Areas, Status, Storage, Timezones, Transactions, UserInformation, Users] (Db=Redacted.mdb)
2018-08-30 14:25:10 [DEBUG] db.Digger:884 - Found column "ControllerID" in "Controllers_Readers".
2018-08-30 14:25:10 [DEBUG] db.Digger:901 - Table (Controllers_Readers) Exists: TRUE    Has Column: TRUE
2018-08-30 14:25:10 [ERROR] db.Digger:1180 - [UcanaccessSQLException]: UCAExc:::4.0.2 user lacks privilege or object not found: CONTROLLERS_READERS.CONTROLLERID
    net.ucanaccess.jdbc.UcanaccessSQLException: UCAExc:::4.0.2 user lacks privilege or object not found: CONTROLLERS_READERS.CONTROLLERID
    at net.ucanaccess.jdbc.UcanaccessStatement.executeQuery(UcanaccessStatement.java:210)
    at db.Digger.loadAllControllerReaders(Digger.java:947)
    at db.Digger.main(Digger.java:1177)
Caused by: java.sql.SQLSyntaxErrorException: user lacks privilege or object not found: CONTROLLERS_READERS.CONTROLLERID
    at org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source)
    at org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source)
    at org.hsqldb.jdbc.JDBCStatement.fetchResult(Unknown Source)
    at org.hsqldb.jdbc.JDBCStatement.executeQuery(Unknown Source)
    at net.ucanaccess.jdbc.UcanaccessStatement.executeQuery(UcanaccessStatement.java:208)
    ... 2 more
Caused by: org.hsqldb.HsqlException: user lacks privilege or object not found: CONTROLLERS_READERS.CONTROLLERID
    at org.hsqldb.error.Error.error(Unknown Source)
    at org.hsqldb.error.Error.error(Unknown Source)
    at org.hsqldb.ExpressionColumn.checkColumnsResolved(Unknown Source)
    at org.hsqldb.QueryExpression.resolve(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)

Note: db.Digger and db.Locations are classes in my application. loadAllControllerReaders is the method that executes the SQL query`.

Running the same query against the DB through MS Access (Office 365) gives me a result set with values in the columns. Why does uCanAccess/HSQLDB fail to do the same (failing to find the ControllerID column in Controllers_Readers)? How do I get it to recognise that that column exists in the table?

Answer 1

I was able to recreate your issue under HSQLDB itself (version 2.4.1). You have aliased your table names in the FROM clause

... FROM Controllers AS C, Controllers_Readers AS CR, ...

but your WHERE clause uses the un-aliased table names instead of the aliases

... WHERE Controllers_Readers.ControllerID = Controllers.ControllerID ...

so the actual table names are not being recognized. This can be confirmed with

Statement st = conn.createStatement();
st.execute("CREATE TABLE MY_TABLE (ID INT PRIMARY KEY, DT DATETIME)");
st.execute("INSERT INTO MY_TABLE (ID, DT) VALUES (1, '2018-12-23 00:11:22')");
ResultSet rs = st.executeQuery("SELECT X.DT FROM MY_TABLE AS X WHERE MY_TABLE.ID=1");

which also produces

java.sql.SQLSyntaxErrorException: user lacks privilege or object not found: MY_TABLE.ID
...
Caused by: org.hsqldb.HsqlException: user lacks privilege or object not found: MY_TABLE.ID

You'll need to use the appropriate aliases in your remaining clauses (WHERE, GROUP BY, ORDER BY) as well.