[Bug]: new users get added to HQ office

Describe the bug

CDA appears to assign that user the "HQ" role by default. the exception is not handled when a user tries to edit another user from another office.

Expected behavior

Expect users in the HQ office to be re-assigned to other offices by users of the other office with the CWMS User Admin role for that office.

@tracyfraley1 attempted to assign a new staff member roles using CWMS-CLI to the LRH office and the error below was shown.

I was able to assign this user to LRH, possibly due to having the CWMS User Admin role over HQ itself?

Expected

1. Cleanup the response errors
2. Consider allowing offices to assign users to other offices if they are in the HQ office
3. Consider allowing displaying users of all offices in the users query. i.e. confirm the like/regex works cross office roles

To Reproduce

cwms-cli users roles add -a $CDA_API_ROOT -k $CDA_API_KEY -u user.user.user.XXXXXXXXX --roles "CWMS Users,All Users" -o LRH

Priority

Medium

Logs/Incident Identifier

2026-09-01 09:47:41;INFO;logger configured
2026-09-01 09:47:44;ERROR;CDA Error: response=<Response [500]>
Error: CWMS API Error (https://water.dev.cwbi.us/cwms-data/user/user.user.user.XXXXXXXXX /roles/LRH). {"message":"Database Error","incidentIdentifier":"d5fe5bec-a25e-4a23-b790-5146e88ac827","source":"Unknown","details":{"stackTraceLines":["org.jooq.exception.DataAccessException: Error while running ConnectionCallable","\tat org.jooq.impl.DefaultDSLContext.connectionResult(DefaultDSLContext.java:664)","\tat org.jooq.impl.DefaultDSLContext.connection(DefaultDSLContext.java:696)","\tat cwms.cda.data.dao.UserDao.addRoles(UserDao.java:92)","\tat cwms.cda.api.auth.users.roles.AddRoleController.handle(AddRoleController.java:58)","\tat cwms.cda.security.CdaAccessManager.manage(CdaAccessManager.java:43)","\tat io.javalin.http.JavalinServlet.addHandler$lambda-0(JavalinServlet.kt:96)","\tat io.javalin.http.JavalinServlet$lifecycle$2$1$1.invoke(JavalinServlet.kt:43)","\tat io.javalin.http.JavalinServlet$lifecycle$2$1$1.invoke(JavalinServlet.kt:43)","\tat io.javalin.http.JavalinServletHandler.executeNextTask(JavalinServletHandler.kt:99)","\tat io.javalin.http.JavalinServletHandler.queueNextTaskOrFinish$lambda-1(JavalinServletHandler.kt:85)","\tat java.base/java.util.concurrent.CompletableFuture.uniComposeStage(CompletableFuture.java:1106)","\tat java.base/java.util.concurrent.CompletableFuture.thenCompose(CompletableFuture.java:2241)","\tat io.javalin.http.JavalinServletHandler.queueNextTaskOrFinish$javalin(JavalinServletHandler.kt:85)","\tat io.javalin.http.JavalinServletHandler.executeNextTask$lambda-11$lambda-10(JavalinServletHandler.kt:119)","\tat java.base/java.util.concurrent.CompletableFuture.uniApplyNow(CompletableFuture.java:680)","\tat java.base/java.util.concurrent.CompletableFuture.uniApplyStage(CompletableFuture.java:658)","\tat java.base/java.util.concurrent.CompletableFuture.thenApply(CompletableFuture.java:2100)","\tat io.javalin.http.JavalinServletHandler.executeNextTask(JavalinServletHandler.kt:119)","\tat io.javalin.http.JavalinServletHandler.queueNextTaskOrFinish$lambda-1(JavalinServletHandler.kt:85)","\tat java.base/java.util.concurrent.CompletableFuture.uniComposeStage(CompletableFuture.java:1106)","\tat java.base/java.util.concurrent.CompletableFuture.thenCompose(CompletableFuture.java:2241)","\tat io.javalin.http.JavalinServletHandler.queueNextTaskOrFinish$javalin(JavalinServletHandler.kt:85)","\tat io.javalin.http.JavalinServletHandler.executeNextTask$lambda-11$lambda-10(JavalinServletHandler.kt:119)","\tat java.base/java.util.concurrent.CompletableFuture.uniApplyNow(CompletableFuture.java:680)","\tat java.base/java.util.concurrent.CompletableFuture.uniApplyStage(CompletableFuture.java:658)","\tat java.base/java.util.concurrent.CompletableFuture.thenApply(CompletableFuture.java:2100)","\tat io.javalin.http.JavalinServletHandler.executeNextTask(JavalinServletHandler.kt:119)","\tat io.javalin.http.JavalinServletHandler.queueNextTaskOrFinish$lambda-1(JavalinServletHandler.kt:85)","\tat java.base/java.util.concurrent.CompletableFuture.uniComposeStage(CompletableFuture.java:1106)","\tat java.base/java.util.concurrent.CompletableFuture.thenCompose(CompletableFuture.java:2241)","\tat io.javalin.http.JavalinServletHandler.queueNextTaskOrFinish$javalin(JavalinServletHandler.kt:85)","\tat io.javalin.http.JavalinServletHandler.executeNextTask$lambda-11$lambda-10(JavalinServletHandler.kt:119)","\tat java.base/java.util.concurrent.CompletableFuture.uniApplyNow(CompletableFuture.java:680)","\tat java.base/java.util.concurrent.CompletableFuture.uniApplyStage(CompletableFuture.java:658)","\tat java.base/java.util.concurrent.CompletableFuture.thenApply(CompletableFuture.java:2100)","\tat io.javalin.http.JavalinServletHandler.executeNextTask(JavalinServletHandler.kt:119)","\tat io.javalin.http.JavalinServletHandler.queueNextTaskOrFinish$lambda-1(JavalinServletHandler.kt:85)","\tat java.base/java.util.concurrent.CompletableFuture.uniComposeStage(CompletableFuture.java:1106)","\tat java.base/java.util.concurrent.CompletableFuture.thenCompose(CompletableFuture.java:2241)","\tat io.javalin.http.JavalinServletHandler.queueNextTaskOrFinish$javalin(JavalinServletHandler.kt:85)","\tat io.javalin.http.JavalinServlet.service(JavalinServlet.kt:89)","\tat javax.servlet.http.HttpServlet.service(HttpServlet.java:623)","\tat cwms.cda.ApiServlet.service(ApiServlet.java:444)","\tat javax.servlet.http.HttpServlet.service(HttpServlet.java:623)","\tat org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:201)","\tat org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:146)","\tat org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:57)","\tat org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:170)","\tat org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:146)","\tat cwms.cda.servlet.W3CTraceFilter.doFilter(W3CTraceFilter.java:54)","\tat org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:170)","\tat org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:146)","\tat org.apache.catalina.filters.CorsFilter.handleNonCORS(CorsFilter.java:340)","\tat org.apache.catalina.filters.CorsFilter.doFilter(CorsFilter.java:167)","\tat org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:170)","\tat org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:146)","\tat org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:166)","\tat org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:88)","\tat org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:534)","\tat org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:129)","\tat org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:86)","\tat org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:71)","\tat org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:350)","\tat org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:409)","\tat org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:71)","\tat org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:1344)","\tat org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:2254)","\tat org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:74)","\tat org.apache.tomcat.util.threads.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:976)","\tat org.apache.tomcat.util.threads.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:494)","\tat org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:80)","\tat java.base/java.lang.Thread.run(Thread.java:829)","Caused by: java.sql.BatchUpdateException: ORA-20998: ERROR: Permission Denied. Your account needs \"CWMS DBA\" or \"CWMS User Admin\" privileges to use the cwms_sec package.","ORA-06512: at \"CWMS_20.CWMS_ERR\", line 80","ORA-06512: at \"CWMS_20.CWMS_SEC\", line 108","ORA-06512: at \"CWMS_20.CWMS_SEC\", line 865","ORA-06512: at \"CWMS_20.CWMS_SEC\", line 905","ORA-06512: at line 1","",https://docs.oracle.com/error-help/db/ora-20998/,"\tat oracle.jdbc.driver.OraclePreparedStatement.generateBatchUpdateException(OraclePreparedStatement.java:11447)","\tat oracle.jdbc.driver.OraclePreparedStatement.executeBatchFromQueue(OraclePreparedStatement.java:10942)","\tat oracle.jdbc.driver.OraclePreparedStatement.executeLargeBatch(OraclePreparedStatement.java:10479)","\tat oracle.jdbc.driver.OracleStatement.executeBatch(OracleStatement.java:5692)","\tat oracle.jdbc.driver.OracleStatementWrapper.executeBatch(OracleStatementWrapper.java:304)","\tat java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)","\tat java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)","\tat java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)","\tat java.base/java.lang.reflect.Method.invoke(Method.java:566)","\tat org.apache.tomcat.jdbc.pool.interceptor.AbstractQueryReport$StatementProxy.invoke(AbstractQueryReport.java:232)","\tat com.sun.proxy.$Proxy39.executeBatch(Unknown Source)","\tat java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)","\tat java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)","\tat java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)","\tat java.base/java.lang.reflect.Method.invoke(Method.java:566)","\tat org.apache.tomcat.jdbc.pool.interceptor.AbstractQueryReport$StatementProxy.invoke(AbstractQueryReport.java:232)","\tat com.sun.proxy.$Proxy39.executeBatch(Unknown Source)","\tat java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)","\tat java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)","\tat java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)","\tat java.base/java.lang.reflect.Method.invoke(Method.java:566)","\tat org.apache.tomcat.jdbc.pool.interceptor.StatementDecoratorInterceptor$StatementProxy.invoke(StatementDecoratorInterceptor.java:331)","\tat com.sun.proxy.$Proxy39.executeBatch(Unknown Source)","\tat java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)","\tat java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)","\tat java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)","\tat java.base/java.lang.reflect.Method.invoke(Method.java:566)","\tat org.apache.tomcat.jdbc.pool.StatementFacade$StatementProxy.invoke(StatementFacade.java:144)","\tat com.sun.proxy.$Proxy39.executeBatch(Unknown Source)","\tat cwms.cda.data.dao.UserDao.lambda$addRoles$2(UserDao.java:101)","\tat org.jooq.impl.DefaultDSLContext.lambda$connection$12(DefaultDSLContext.java:697)","\tat org.jooq.impl.DefaultDSLContext.connectionResult(DefaultDSLContext.java:658)","\t... 71 more"]}}

CURL Commands

CDA Version

2026.09.01-develop

Additional context

ERROR: Permission Denied. Your account needs "CWMS DBA" or "CWMS User Admin" privileges to use the cwms_sec package.","ORA-06512: at "CWMS_20.CWMS_ERR", line 80","ORA-06512: at "CWMS_20.CWMS_SEC", line 108","ORA-06512: at "CWMS_20.CWMS_SEC", line 865","ORA-06512: at "CWMS_20.CWMS_SEC",

When a new user authenticates to CDA via keycloak they must, to my knowledge, then hit an endpoint on CDA/swagger to register their user. If they use the login button in swagger this may do that for them. It is a form of self registration.

Start with UserDao.java:92-101 and AddRoleController.java:58, following the users roles add command and the permission failure shown in the incident log. Check how CdaAccessManager.java:43 applies office-scoped User Admin permissions and how cross-office users are queried. Done should include a defined resolution for HQ users, clearer response errors, and verification that permitted office assignments work as intended.