Spring Security ACL:我无法更新ACL(SQLIntegrityConstraintViolation

Question

我正在尝试学习并将一些Spring Security ACL控件应用到我的Spring Boot项目中。因此，我尝试重用Spring Security参考中的代码片段，并根据我的项目需求对其进行了一些调整。

我有以下组件来在启动时用一些初始值填充数据库。

@Component
public class AppBootstrap {
private Authority adminAuth;
private User admin;
private TimeSheet timeSheetAdmin;
private final JdbcMutableAclService jdbcMutableAclService;
private final PlatformTransactionManager transactionManager;
@Autowired
public AppBootstrap(JdbcMutableAclService jdbcMutableAclService, PlatformTransactionManager transactionManager) {
this.jdbcMutableAclService = jdbcMutableAclService;
this.transactionManager = transactionManager;
}
@Bean
public CommandLineRunner initialAuthorities(AuthorityRepository authorityRepository) {
return args -> {
adminAuth = new Authority(ROLE_ADMIN);
authorityRepository.save(adminAuth);
};
}
@Bean
public CommandLineRunner initialUsers(UserRepository userRepository) {
return args -> {
admin = new User("admin",
"{bcrypt}$2a$08$lDnHPz7eUkSi6ao14Twuau08mzhWrL4kyZGGU5xfiGALO/Vxd5DOi", "admin",
"admin", "[e-mail]", true, getDate(2016, JANUARY, 1));
admin.setAuthorities(asList(adminAuth));
userRepository.save(admin);
};
}
@Bean
public CommandLineRunner initialTimeSheets(TimeSheetRepository timeSheetRepository) {
return args -> {
timeSheetAdmin = new TimeSheet(LocalDate.of(2016, MARCH, 1), admin);
timeSheetRepository.save(timeSheetUser);
};
}
@Bean
public CommandLineRunner initialRights() {
return args -> grantPermission(admin, timeSheetAdmin, ADMINISTRATION);
}
MutableAcl grantPermission(User user, TimeSheet timeSheet, Permission p) {
TransactionTemplate transactionTemplate = new TransactionTemplate(transactionManager);
SecurityContext securityContext = SecurityContextHolder.getContext();
var authorities = user.getAuthorities().stream().map(auth -> auth.getName().toString()).collect(toList());
Authentication authentication = new UsernamePasswordAuthenticationToken(user.getUsername(), user.getPass(), AuthorityUtils.createAuthorityList(authorities.toArray(new String[]{})));
securityContext.setAuthentication(authentication);
ObjectIdentity oiTimeSheet = new ObjectIdentityImpl(TimeSheet.class, timeSheet.getId());
Sid sidAdmin = new PrincipalSid(user.getUsername());
MutableAcl acl;
try {
acl = (MutableAcl) jdbcMutableAclService.readAclById(oiTimeSheet);
} catch (NotFoundException nfe) {
acl = transactionTemplate.execute(status -> jdbcMutableAclService.createAcl(oiTimeSheet));
}
acl.insertAce(acl.getEntries().size(), p, sidAdmin, true);
// updating permission
MutableAcl finalAcl = acl;
return transactionTemplate.execute(status -> jdbcMutableAclService.updateAcl(finalAcl));
}
}

每次刷新应用程序上下文时都会运行此方法。我可以看到，如果我只运行一个测试方法(或测试类(，它就会成功运行。这些测试是端到端测试(@RunWith(SpringRunner.class) @SpringBootTest(。另外，单独运行我的@SpringBootApplication也没问题。但是，如果我同时运行所有测试，那么几乎所有的@SpringBootTest都会失败，并出现以下堆栈跟踪

java.lang.IllegalStateException: Failed to load ApplicationContext
at org.springframework.test.context.cache.DefaultCacheAwareContextLoaderDelegate.loadContext(DefaultCacheAwareContextLoaderDelegate.java:125)
at org.springframework.test.context.support.DefaultTestContext.getApplicationContext(DefaultTestContext.java:108)
at org.springframework.test.context.web.ServletTestExecutionListener.setUpRequestContextIfNecessary(ServletTestExecutionListener.java:190)
at org.springframework.test.context.web.ServletTestExecutionListener.prepareTestInstance(ServletTestExecutionListener.java:132)
at org.springframework.test.context.TestContextManager.prepareTestInstance(TestContextManager.java:246)
at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.createTest(SpringJUnit4ClassRunner.java:227)
at org.springframework.test.context.junit4.SpringJUnit4ClassRunner$1.runReflectiveCall(SpringJUnit4ClassRunner.java:289)
at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:12)
at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.methodBlock(SpringJUnit4ClassRunner.java:291)
at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.runChild(SpringJUnit4ClassRunner.java:246)
at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.runChild(SpringJUnit4ClassRunner.java:97)
at org.junit.runners.ParentRunner$3.run(ParentRunner.java:290)
at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:71)
at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:288)
at org.junit.runners.ParentRunner.access$000(ParentRunner.java:58)
at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:268)
at org.springframework.test.context.junit4.statements.RunBeforeTestClassCallbacks.evaluate(RunBeforeTestClassCallbacks.java:61)
at org.springframework.test.context.junit4.statements.RunAfterTestClassCallbacks.evaluate(RunAfterTestClassCallbacks.java:70)
at org.junit.runners.ParentRunner.run(ParentRunner.java:363)
at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.run(SpringJUnit4ClassRunner.java:190)
at org.junit.runners.Suite.runChild(Suite.java:128)
at org.junit.runners.Suite.runChild(Suite.java:27)
at org.junit.runners.ParentRunner$3.run(ParentRunner.java:290)
at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:71)
at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:288)
at org.junit.runners.ParentRunner.access$000(ParentRunner.java:58)
at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:268)
at org.junit.runners.ParentRunner.run(ParentRunner.java:363)
at org.junit.runner.JUnitCore.run(JUnitCore.java:137)
at com.intellij.junit4.JUnit4IdeaTestRunner.startRunnerWithArgs(JUnit4IdeaTestRunner.java:68)
at com.intellij.rt.execution.junit.IdeaTestRunner$Repeater.startRunnerWithArgs(IdeaTestRunner.java:47)
at com.intellij.rt.execution.junit.JUnitStarter.prepareStreamsAndStart(JUnitStarter.java:242)
at com.intellij.rt.execution.junit.JUnitStarter.main(JUnitStarter.java:70)
Caused by: java.lang.IllegalStateException: Failed to execute CommandLineRunner
at org.springframework.boot.SpringApplication.callRunner(SpringApplication.java:800)
at org.springframework.boot.SpringApplication.callRunners(SpringApplication.java:781)
at org.springframework.boot.SpringApplication.run(SpringApplication.java:335)
at org.springframework.boot.test.context.SpringBootContextLoader.loadContext(SpringBootContextLoader.java:139)
at org.springframework.test.context.cache.DefaultCacheAwareContextLoaderDelegate.loadContextInternal(DefaultCacheAwareContextLoaderDelegate.java:99)
at org.springframework.test.context.cache.DefaultCacheAwareContextLoaderDelegate.loadContext(DefaultCacheAwareContextLoaderDelegate.java:117)
... 32 more
Caused by: org.springframework.dao.DataIntegrityViolationException: PreparedStatementCallback; SQL [insert into acl_entry (acl_object_identity, ace_order, sid, mask, granting, audit_success, audit_failure)values (?, ?, ?, ?, ?, ?, ?)Cannot add or update a child row: a foreign key constraint fails (`iresource2test`.`acl_entry`, CONSTRAINT `fk_acl_entry_object` FOREIGN KEY (`acl_object_identity`) REFERENCES `acl_object_identity` (`id`)); nested exception is java.sql.BatchUpdateException: Cannot add or update a child row: a foreign key constraint fails (`iresource2test`.`acl_entry`, CONSTRAINT `fk_acl_entry_object` FOREIGN KEY (`acl_object_identity`) REFERENCES `acl_object_identity` (`id`))
at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:246)
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72)
at org.springframework.jdbc.core.JdbcTemplate.translateException(JdbcTemplate.java:1402)
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:620)
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:634)
at org.springframework.jdbc.core.JdbcTemplate.batchUpdate(JdbcTemplate.java:924)
at org.springframework.security.acls.jdbc.JdbcMutableAclService.createEntries(JdbcMutableAclService.java:138)
at org.springframework.security.acls.jdbc.JdbcMutableAclService.updateAcl(JdbcMutableAclService.java:363)
at com.roberto.bootstrap.AppBootstrap.lambda$grantPermission$6(AppBootstrap.java:144)
at org.springframework.transaction.support.TransactionTemplate.execute(TransactionTemplate.java:140)
at com.roberto.bootstrap.AppBootstrap.grantPermission(AppBootstrap.java:144)
at com.roberto.bootstrap.AppBootstrap.lambda$initialRights$3(AppBootstrap.java:115)
at org.springframework.boot.SpringApplication.callRunner(SpringApplication.java:797)
... 37 more
Caused by: java.sql.BatchUpdateException: Cannot add or update a child row: a foreign key constraint fails (`iresource2test`.`acl_entry`, CONSTRAINT `fk_acl_entry_object` FOREIGN KEY (`acl_object_identity`) REFERENCES `acl_object_identity` (`id`))
at java.base/jdk.internal.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at java.base/jdk.internal.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
at java.base/jdk.internal.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
at java.base/java.lang.reflect.Constructor.newInstance(Constructor.java:488)
at com.mysql.cj.util.Util.handleNewInstance(Util.java:210)
at com.mysql.cj.util.Util.getInstance(Util.java:185)
at com.mysql.cj.util.Util.getInstance(Util.java:192)
at com.mysql.cj.jdbc.exceptions.SQLError.createBatchUpdateException(SQLError.java:218)
at com.mysql.cj.jdbc.ClientPreparedStatement.executeBatchSerially(ClientPreparedStatement.java:864)
at com.mysql.cj.jdbc.ClientPreparedStatement.executeBatchInternal(ClientPreparedStatement.java:453)
at com.mysql.cj.jdbc.StatementImpl.executeBatch(StatementImpl.java:839)
at com.zaxxer.hikari.pool.ProxyStatement.executeBatch(ProxyStatement.java:128)
at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.executeBatch(HikariProxyPreparedStatement.java)
at org.springframework.jdbc.core.JdbcTemplate.lambda$batchUpdate$2(JdbcTemplate.java:938)
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:605)
... 46 more
Caused by: java.sql.SQLIntegrityConstraintViolationException: Cannot add or update a child row: a foreign key constraint fails (`iresource2test`.`acl_entry`, CONSTRAINT `fk_acl_entry_object` FOREIGN KEY (`acl_object_identity`) REFERENCES `acl_object_identity` (`id`))
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:115)
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:95)
at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)
at com.mysql.cj.jdbc.ClientPreparedStatement.executeInternal(ClientPreparedStatement.java:960)
at com.mysql.cj.jdbc.ClientPreparedStatement.executeUpdateInternal(ClientPreparedStatement.java:1116)
at com.mysql.cj.jdbc.ClientPreparedStatement.executeBatchSerially(ClientPreparedStatement.java:843)
... 52 more

我有几个问题：

• 为什么我运行多个测试类时会出现此错误
• 如何找到解决方法？有没有更好的方法来实现同样的目标

谢谢！：-(

Answer 1

由于这个问题没有那么流行，我尝试了一种不同的方法，它似乎很有效：-(

首先，我创建了一个新的ACLService类，以隔离与JdbcMutableAclService的所有交互。

package com.roberto.security.service;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.security.acls.model.*;
import org.springframework.stereotype.Service;
/**
* Service class to handle ACL permissions.
*/
@Service
public class ACLService {
private final MutableAclService mutableAclService;
@Autowired
public ACLService(MutableAclService mutableAclService) {
this.mutableAclService = mutableAclService;
}
/**
* Insert an ACL entry
* @param oid represents the model object
* @param recipient represents the principal (user, group of users, etc)
* @param permission quite explicit name...
* @return the new ACL database entry
*/
public MutableAcl addPermission(ObjectIdentity oid, Sid recipient, Permission permission) {
MutableAcl acl;
try {
acl = (MutableAcl) mutableAclService.readAclById(oid);
} catch (NotFoundException nfe) {
acl = mutableAclService.createAcl(oid);
}
acl.insertAce(acl.getEntries().size(), permission, recipient, true);
return mutableAclService.updateAcl(acl);
}   
}

然后，我创建了另一个集成测试，它正常运行，没有IllegalStateException。现在我只需要从我的引导程序类中调用它。

package com.roberto.security.service;
import com.roberto.model.TimeSheet;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.security.acls.domain.BasePermission;
import org.springframework.security.acls.domain.ObjectIdentityImpl;
import org.springframework.security.acls.domain.PrincipalSid;
import org.springframework.security.acls.model.*;
import org.springframework.security.test.context.support.WithUserDetails;
import org.springframework.test.annotation.DirtiesContext;
import org.springframework.test.context.junit4.SpringRunner;
import org.springframework.transaction.annotation.Transactional;
import java.util.List;
import static org.junit.Assert.*;
import static org.springframework.test.annotation.DirtiesContext.ClassMode.BEFORE_EACH_TEST_METHOD;
/**
* This test handles basic interaction between our codebase
* Spring Security ACL and the underlying database model
*/
@SpringBootTest
@RunWith(SpringRunner.class)
public class ACLServiceIntegrationTest {
private Authentication authentication;
private ObjectIdentity oid ;
private Sid sid;
private Permission administration = BasePermission.ADMINISTRATION;
@Autowired
private ACLService aclService;
@Autowired
private MutableAclService mutableAclService;
@Autowired
private PermissionEvaluator permissionEvaluator;
@Before
public void setUp() {
authentication = TestSecurityContextHolder.getContext().getAuthentication();
sid = new PrincipalSid(((JwtUser) authentication.getPrincipal()).getUsername());
oid = new ObjectIdentityImpl(TimeSheet.class, 1);
}
@Test
@WithUserDetails("admin")
public void testBeans() {
assertNotNull(aclService);
assertNotNull(mutableAclService);
assertNotNull(permissionEvaluator);
}
@Test
@Transactional
@WithUserDetails("admin")
@DirtiesContext(methodMode = AFTER_METHOD)
public void addPermissionIntegrationTest() {
assertFalse(permissionEvaluator.hasPermission(authentication, oid.getIdentifier(), oid.getType(), administration));
MutableAcl acl = aclService.addPermission(oi, sid, administration);
assertTrue(permissionEvaluator.hasPermission(authentication, oid.getIdentifier(), oid.getType(), administration));
assertEquals(TimeSheet.class.toString().split(" ")[1], acl.getObjectIdentity().getType());
assertTrue(acl.getEntries().stream().anyMatch(e -> e.getSid().equals(sid) && e.getPermission().equals(administration)));
assertTrue(acl.isGranted(List.of(administration), List.of(sid), true));
}
}

Answer 2

我遇到了同样的问题
在我的情况下，acl缓存是脏的。我必须在每次测试后清除缓存，然后所有测试都运行绿色，即使一起调用也是如此
我为此创建了一个TestExctionListener。只需用它来注释你的抽象测试类：

@TestExecutionListeners(
value = ClearAclCacheTestExecutionListener.class,
mergeMode = TestExecutionListeners.MergeMode.MERGE_WITH_DEFAULTS) 
public class AbstractTest{...}

public class ClearAclCacheTestExecutionListener extends AbstractTestExecutionListener {
@Autowired
private AclCache aclCache;
@Override
public void beforeTestClass(TestContext testContext) {
testContext.getApplicationContext()
.getAutowireCapableBeanFactory()
.autowireBean(this);
}
@Override
public void afterTestMethod(TestContext testContext) throws Exception {
super.afterTestMethod(testContext);
aclCache.clearCache();
}
}