Hibernate/JPA и MS SQL Server - открыть симметричный ключ перед DecryptByKey

Question

В базе данных SQL Server 2012 у меня есть таблица со столбцом varbinary(128), в которой хранятся данные, зашифрованные ключом, поддерживаемым сертификатом (AppCert) (Secret_Key), и средством проверки подлинности с использованием хэша первичного ключа SHA2_512:

CREATE TABLE [Lookup].[SecretStuff] (
    [Id] tinyint NOT NULL, 
    [Secret] varbinary(128) NOT NULL
        CONSTRAINT [PK_Lookup-SecretStuff_Id] PRIMARY KEY CLUSTERED ([Id] ASC), 
        CONSTRAINT [UN_Lookup-SecretStuff_Secret] UNIQUE NONCLUSTERED ([Secret])
);

OPEN SYMMETRIC KEY [Secret_Key] DECRYPTION BY CERTIFICATE [AppCert];
INSERT INTO [Lookup].[SecretStuff] ([Id], [Secret]) VALUES (1, ENCRYPTBYKEY(KEY_GUID('Secret_Key'), CONVERT(nvarchar(512), 'I have a secret'), 1, HASHBYTES('SHA2_512', CONVERT(varbinary(128), CONVERT(tinyint, 1)))));
CLOSE SYMMETRIC KEY [Secret_Key];

OPEN SYMMETRIC KEY [Secret_Key] DECRYPTION BY CERTIFICATE [AppCert];
SELECT [Id], CONVERT(nvarchar, DECRYPTBYKEY([Secret], 1, HASHBYTES('SHA2_512', CONVERT(varbinary, [Id])))) AS [Secret] FROM [Lookup].[SecretStuff];
CLOSE SYMMETRIC KEY [Secret_Key];

Это все прекрасно работает. Теперь у меня есть приложение Spring Boot, использующее JPA/Hibernate, которое подключено и протестировано / проверено для работы с этой базой данных. Класс SecretStuff:

@Entity
@Table(name="SecretStuff", schema="Lookup")
public class SecretStuff {

    @Id
    @Column(name = "Id", 
            nullable = false)
    private Integer id;

    @Column(name = "Secret", 
            nullable = false, 
            unique = true)
    @Size(min = 1, max = 255)
    @ColumnTransformer(
            read = "CONVERT(nvarchar(89), DECRYPTBYKEY([Secret], 1, 
                    HASHBYTES('SHA2_512', CONVERT(varbinary(128), [Id]))))")
    private String secret;
// getters/setter omitted
}

Когда я тестирую класс SecretStuff, я вижу следующий сгенерированный Hibernate SQL:

select
    secretstuf0_.Id as Id1_7_0_,
    CONVERT(nvarchar(89), DECRYPTBYKEY(secretstuf0_.[Secret],
    1,
    HASHBYTES('SHA2_512',
    CONVERT(varbinary(128),
    secretstuf0_.[Id])))) as Secret2_7_0_ 
from
    Lookup.SecretStuff secretstuf0_ 
where
    secretstuf0_.Id=?

Совершенно разумный запрос, который выполняет и возвращает 1 строку с Id = 1 Secret = NULL, потому что ключ SYMMETRIC не открывается перед выполнением запроса.

Мой вопрос:Как я могу выполнить команду OPEN SYMMETRIC KEY... в одной транзакции перед запросом и команду CLOSE SYMMETRIC KEY... в одной транзакции после запроса?

Я использовал Hibernate.initialize внутри методов класса обслуживания, чтобы лениво извлекать отношения ко многим внутри транзакций. Буду ли я использовать подобный подход здесь? Как?

Я видел эту статью, когда набирал этот вопрос, но ему уже пару лет, и он использует подход EntityManager с NativeQuery. Есть ли обновленный способ управления этим с помощью JPA/Hibernate?

Answer 1

Хотя это и не точное решение, которое я искал, статья, на которую я ссылался в своем первоначальном вопросе, и некоторая помощь от Trevor Abell, привели меня к следующему коду, который, кажется, соответствует моим потребностям. Я выкладываю это здесь на случай, если кто-то еще ищет подобное решение. Я все еще открыт для других решений.

Ключ использовал EntityManager для запуска команд SYMMETRIC KEY вокруг запроса. Я добавлю интерфейс @Repository и класс @Service для полноты, но ключом был класс @Service:

@Repository
public interface SecretStuffRepository extends JpaRepository<SecretStuff, Integer> {}

@Service
@Transactional(readOnly = true)
public class SecretStuffService {

    @PersistenceContext
    private EntityManager entityManager;

    @Autowired
    private SecretStuffRepository repository;

    public Iterable<SecretStuff> findAll() {

        this.entityManager.createNativeQuery("
              OPEN SYMMETRIC KEY [Secret_Key] 
              DECRYPTION BY CERTIFICATE [AppCert];
          ").executeUpdate();
        Iterable<SecurityQuestion> questions = this.repository.findAll();
        this.entityManager.createNativeQuery("
              CLOSE SYMMETRIC KEY [Secret_Key];
          ").executeUpdate();
        return stuff;
    }
}

Теперь, когда я тестирую SecretStuffService, я вижу следующий сгенерированный Hibernate SQL:

Hibernate: 
    OPEN SYMMETRIC KEY [Secret_Key] DECRYPTION 
BY
    CERTIFICATE [AppCert];
Hibernate: 
    select
        secretstuf0_.Id as Id1_5_0_,
        CONVERT(nvarchar(89),
        DECRYPTBYKEY(secretstuf0_.[Secret],
        1,
        HASHBYTES('SHA2_512',
        CONVERT(varbinary(128),
        secretstuf0_.[Id])))) as Secret2_5_0_ 
    from
        Lookup.SecretStuff secretstuf0_ 
    where
        secretstuf0_.Id=?
Hibernate: 
    CLOSE SYMMETRIC KEY [Secret_Key];

Вызов SecretStuffService.findAll() теперь открывает ключ, вызывает метод findAll () хранилища и закрывает ключ внутри той же транзакции. Помните, что NativeQuery в этом случае относится только к Microsoft SQL Server, поэтому вам нужно будет заменить соответствующую команду для вашего поставщика базы данных.