I'm trying to have users only be able to access their own configuration records, but allow users with ROLE_ADMIN to access any record.
I'm having trouble where using the SpEL expression ?#{principal.username} in a select query, as in the findByUid method, works just fine, but the exact same expression in a delete query causes a syntax error. I've narrowed the problem down to that specific expression by using the commented-out query on the deleteByUid method. The commented-out query on deleteByUid does not throw the syntax error, but of course, denies any non-ROLE_ADMIN users the ability to delete their own configuration records.
Is there some other secret song and dance that I need to do to make this work?
public interface WidgetConfigRepository extends JpaRepository<WidgetConfig,Long> {
@Query("SELECT wc FROM WidgetConfig wc WHERE (wc.user.login = ?#{principal.username} OR ?#{hasRole('ROLE_ADMIN') ? 'true' : 'false'} = 'true') AND wc.uid = :uid")
WidgetConfig findByUid(@Param("uid") String uid);
@Query("SELECT wc FROM WidgetConfig wc WHERE (wc.user.login = ?#{principal.username} OR ?#{hasRole('ROLE_ADMIN') ? 'true' : 'false'} = 'true') AND wc.name LIKE LOWER(CONCAT('%',:name,'%'))")
Page findLikeName(@Param("name") String name, Pageable pageable);
@Override
@Query("SELECT wc FROM WidgetConfig wc WHERE wc.user.login = ?#{principal.username} OR ?#{hasRole('ROLE_ADMIN') ? 'true' : 'false'} = 'true'")
Page findAll(Pageable pageable);
@Query("SELECT wc FROM WidgetConfig wc WHERE (wc.user.login = ?#{principal.username} OR ?#{hasRole('ROLE_ADMIN') ? 'true' : 'false'} = 'true') AND wc.type = :type")
Page findAllByType(@Param("type") String type, Pageable pageable);
@Modifying
// @Query("DELETE FROM WidgetConfig wc WHERE ?#{hasRole('ROLE_ADMIN') ? 'true' : 'false'} = 'true' AND wc.uid = :uid")
@Query("DELETE FROM WidgetConfig wc WHERE (wc.user.login = ?#{principal.username} OR ?#{hasRole('ROLE_ADMIN') ? 'true' : 'false'} = 'true') AND wc.uid = :uid")
int deleteByUid(@Param("uid") String uid);
}
2017-08-17 11:03:52.845 DEBUG 8725 --- [nio-8080-exec-5] g.n.g.g.g.p.aop.logging.LoggingAspect : Enter: gov.nasa.gsfc.gmsec.gss.portal.web.rest.WidgetConfigResource.deleteWidgetConfig() with argument[s] = [dashboard-1502982221773]
2017-08-17 11:03:52.845 DEBUG 8725 --- [nio-8080-exec-5] g.n.g.g.g.p.w.rest.WidgetConfigResource : REST request to delete WidgetConfig : dashboard-1502982221773
2017-08-17 11:03:52.856 DEBUG 8725 --- [nio-8080-exec-5] o.h.e.t.spi.AbstractTransactionImpl : begin
2017-08-17 11:03:52.857 DEBUG 8725 --- [nio-8080-exec-5] o.h.e.j.internal.LogicalConnectionImpl : Obtaining JDBC connection
2017-08-17 11:03:52.857 DEBUG 8725 --- [nio-8080-exec-5] o.h.e.j.internal.LogicalConnectionImpl : Obtained JDBC connection
2017-08-17 11:03:52.857 DEBUG 8725 --- [nio-8080-exec-5] o.h.e.t.internal.jdbc.JdbcTransaction : initial autocommit status: true
2017-08-17 11:03:52.857 DEBUG 8725 --- [nio-8080-exec-5] o.h.e.t.internal.jdbc.JdbcTransaction : disabling autocommit
2017-08-17 11:03:52.857 DEBUG 8725 --- [nio-8080-exec-5] g.n.g.g.g.p.aop.logging.LoggingAspect : Enter: gov.nasa.gsfc.gmsec.gss.portal.service.WidgetConfigService.deleteByUid() with argument[s] = [dashboard-1502982221773]
2017-08-17 11:03:52.857 DEBUG 8725 --- [nio-8080-exec-5] g.n.g.g.g.p.s.i.WidgetConfigServiceImpl : Request to delete WidgetConfig : dashboard-1502982221773
2017-08-17 11:03:52.863 DEBUG 8725 --- [nio-8080-exec-5] org.hibernate.SQL : delete from widget_config cross join jhi_user user1_ where (login=? or ?='true') and uid=?
Hibernate: delete from widget_config cross join jhi_user user1_ where (login=? or ?='true') and uid=?
2017-08-17 11:03:52.866 DEBUG 8725 --- [nio-8080-exec-5] o.h.engine.jdbc.spi.SqlExceptionHelper : could not prepare statement [delete from widget_config cross join jhi_user user1_ where (login=? or ?='true') and uid=?]
org.h2.jdbc.JdbcSQLException: Syntax error in SQL statement "DELETE FROM WIDGET_CONFIG CROSS[*] JOIN JHI_USER USER1_ WHERE (LOGIN=? OR ?='true') AND UID=? "; SQL statement:
delete from widget_config cross join jhi_user user1_ where (login=? or ?='true') and uid=? [42000-194]
at org.h2.message.DbException.getJdbcSQLException(DbException.java:345)
(large stack trace left out for brevity)
2017-08-17 11:03:52.892 WARN 8725 --- [nio-8080-exec-5] o.h.engine.jdbc.spi.SqlExceptionHelper : SQL Error: 42000, SQLState: 42000
2017-08-17 11:03:52.892 ERROR 8725 --- [nio-8080-exec-5] o.h.engine.jdbc.spi.SqlExceptionHelper : Syntax error in SQL statement "DELETE FROM WIDGET_CONFIG CROSS[*] JOIN JHI_USER USER1_ WHERE (LOGIN=? OR ?='true') AND UID=? "; SQL statement:
delete from widget_config cross join jhi_user user1_ where (login=? or ?='true') and uid=? [42000-194]
2017-08-17 11:03:52.909 DEBUG 8725 --- [nio-8080-exec-5] o.h.jpa.spi.AbstractEntityManagerImpl : Mark transaction for rollback
2017-08-17 11:03:52.923 ERROR 8725 --- [nio-8080-exec-5] g.n.g.g.g.p.aop.logging.LoggingAspect : Exception in gov.nasa.gsfc.gmsec.gss.portal.service.WidgetConfigService.deleteByUid() with cause = 'org.springframework.dao.InvalidDataAccessResourceUsageException: could not prepare statement; SQL [delete from widget_config cross join jhi_user user1_ where (login=? or ?='true') and uid=?]; nested exception is org.hibernate.exception.SQLGrammarException: could not prepare statement' and exception = 'org.springframework.dao.InvalidDataAccessResourceUsageException: could not prepare statement; SQL [delete from widget_config cross join jhi_user user1_ where (login=? or ?='true') and uid=?]; nested exception is org.hibernate.exception.SQLGrammarException: could not prepare statement'
2017-08-17 11:03:52.923 DEBUG 8725 --- [nio-8080-exec-5] o.h.e.t.spi.AbstractTransactionImpl : rolling back
2017-08-17 11:03:52.924 DEBUG 8725 --- [nio-8080-exec-5] o.h.e.t.internal.jdbc.JdbcTransaction : rolled JDBC Connection
2017-08-17 11:03:52.924 DEBUG 8725 --- [nio-8080-exec-5] o.h.e.t.internal.jdbc.JdbcTransaction : re-enabling autocommit
2017-08-17 11:03:52.924 INFO 8725 --- [nio-8080-exec-5] i.StatisticalLoggingSessionEventListener : Session Metrics {
155229 nanoseconds spent acquiring 1 JDBC connections;
0 nanoseconds spent releasing 0 JDBC connections;
349635 nanoseconds spent preparing 1 JDBC statements;
0 nanoseconds spent executing 0 JDBC statements;
0 nanoseconds spent executing 0 JDBC batches;
0 nanoseconds spent performing 0 L2C puts;
0 nanoseconds spent performing 0 L2C hits;
0 nanoseconds spent performing 0 L2C misses;
0 nanoseconds spent executing 0 flushes (flushing a total of 0 entities and 0 collections);
29111 nanoseconds spent executing 1 partial-flushes (flushing a total of 0 entities and 0 collections)
}
2017-08-17 11:03:52.924 DEBUG 8725 --- [nio-8080-exec-5] o.h.e.j.internal.LogicalConnectionImpl : Releasing JDBC connection
2017-08-17 11:03:52.925 DEBUG 8725 --- [nio-8080-exec-5] o.h.e.j.internal.LogicalConnectionImpl : Released JDBC connection
2017-08-17 11:03:52.925 ERROR 8725 --- [nio-8080-exec-5] g.n.g.g.g.p.aop.logging.LoggingAspect : Illegal argument: [dashboard-1502982221773] in gov.nasa.gsfc.gmsec.gss.portal.web.rest.WidgetConfigResource.deleteWidgetConfig()
2017-08-17 11:03:52.925 ERROR 8725 --- [nio-8080-exec-5] g.n.g.g.g.p.aop.logging.LoggingAspect : Exception in gov.nasa.gsfc.gmsec.gss.portal.web.rest.WidgetConfigResource.deleteWidgetConfig() with cause = 'org.springframework.dao.InvalidDataAccessResourceUsageException: could not prepare statement; SQL [delete from widget_config cross join jhi_user user1_ where (login=? or ?='true') and uid=?]; nested exception is org.hibernate.exception.SQLGrammarException: could not prepare statement' and exception = 'org.springframework.dao.InvalidDataAccessResourceUsageException: could not prepare statement; SQL [delete from widget_config cross join jhi_user user1_ where (login=? or ?='true') and uid=?]; nested exception is org.hibernate.exception.SQLGrammarException: could not prepare statement'
2017-08-17 11:03:52.944 DEBUG 8725 --- [nio-8080-exec-5] g.n.g.g.g.p.aop.logging.LoggingAspect : Enter: gov.nasa.gsfc.gmsec.gss.portal.web.rest.errors.ExceptionTranslator.processRuntimeException() with argument[s] = [java.lang.IllegalArgumentException: org.springframework.dao.InvalidDataAccessResourceUsageException: could not prepare statement; SQL [delete from widget_config cross join jhi_user user1_ where (login=? or ?='true') and uid=?]; nested exception is org.hibernate.exception.SQLGrammarException: could not prepare statement]
2017-08-17 11:03:52.949 DEBUG 8725 --- [nio-8080-exec-5] g.n.g.g.g.p.aop.logging.LoggingAspect : Exit: gov.nasa.gsfc.gmsec.gss.portal.web.rest.errors.ExceptionTranslator.processRuntimeException() with result = <500 Internal Server Error,gov.nasa.gsfc.gmsec.gss.portal.web.rest.errors.ErrorVM@3caeaf1c,{}>
2017-08-17 11:03:52.951 WARN 8725 --- [nio-8080-exec-5] .m.m.a.ExceptionHandlerExceptionResolver : Resolved exception caused by Handler execution: java.lang.IllegalArgumentException: org.springframework.dao.InvalidDataAccessResourceUsageException: could not prepare statement; SQL [delete from widget_config cross join jhi_user user1_ where (login=? or ?='true') and uid=?]; nested exception is org.hibernate.exception.SQLGrammarException: could not prepare statement
Based on Cepr0's answer, I came up with this:
@Modifying
@Query("DELETE FROM WidgetConfig wc WHERE (wc.user = (SELECT u FROM User u WHERE u.login = ?#{principal.username}) OR ?#{hasRole('ROLE_ADMIN') ? 'true' : 'false'} = 'true') AND wc.uid = :uid")
int deleteByUid(@Param("uid") String uid);
I'm still a little bit mystified as to why this works, but my original approach didn't.
Maybe this can help (not tested):
delete from WidgetConfig wc
where
wc.user = (
select u from User u where u.id = ?1 and (
u.login = ?#{principal.username} or
?#{hasRole('ROLE_ADMIN') ? 'true' : 'false'} = 'true'
)
)
(And I'm not sure of the need for single quotes around 'true' and 'false'...)