Issue

• Running a web application based on Seam 2.2.2.Final against a read-only database.
  Created a read-only user and used it in the ds.xml. While starting the application, however when trying to access one of its web service endpoints, getting error:

16:35:31,665 WARN  [JDBCExceptionReporter] SQL Error: 1031, SQLState: 42000
16:35:31,665 ERROR [JDBCExceptionReporter] ORA-01031: insufficient privileges

16:35:31,665 ERROR [AbstractFlushingEventListener] Could not synchronize database state with session
org.hibernate.exception.SQLGrammarException: Could not execute JDBC batch update
    at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:90)
    at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:66)
    at org.hibernate.jdbc.AbstractBatcher.executeBatch(AbstractBatcher.java:275)
    at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:265)
    at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:167)
    at org.hibernate.event.def.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:321)
    at org.hibernate.event.def.DefaultAutoFlushEventListener.onAutoFlush(DefaultAutoFlushEventListener.java:64)
    at org.hibernate.impl.SessionImpl.autoFlushIfRequired(SessionImpl.java:999)
    at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1185)
    at org.hibernate.impl.QueryImpl.list(QueryImpl.java:102)
    at org.hibernate.ejb.QueryImpl.getSingleResult(QueryImpl.java:116)
    at a.b.TestUser.findByUserid(TestUser.java:416)

While invoking findByUserid() the above exception occurs

//code snippet where it fails
public TestUser findByUserid(String userid){
        Query q = getEntityManager().createNamedQuery("userByUserid");
        q.setParameter("userid", userid);

        try{
            TestUser tu = (TestUser) q.getSingleResult();  //fails here.
            setInstance(tu);

            return getInstance();
        } catch (NoResultException e){
            return null;
        }
    }   

How to resolve the issue?

• This application is running in production without any issues. User is trying to run this SEAM based application against existing database in dev but with a user with READONLY privileges. Using TOAD, user is able to run the select query on all tables in the schema and access the indexes as well. User is using NamedQuery and its not the issue.

@NamedQuery(name = "userByUserid", 
           query = "select tu from TsetUser tu " +
                "where lower(userid) = lower(:userid) ")
....
 Query q = getEntityManager().createNamedQuery("userByUserid");
 q.setParameter("userid", userid);
        try{
           TestUser tu = (TestUser) q.getSingleResult();
......

Question is - can SEAM+hibernate support the read-only database ?

• Instead of accessing via read-only user, DBA copied the whole database to local pc and made it read-only. Via TOAD, user can query the table. When user ran the web service at deployed application, it failed at the same native query:

10:07:20,653 WARN  [JDBCExceptionReporter] SQL Error: 16000, SQLState: 72000
10:07:20,653 ERROR [JDBCExceptionReporter] ORA-16000: database open for read-only access