DECRYPT_RAW

DECRYPT_RAW

DECRYPT_RAW()는 BINARY 키로 BINARY 값을 복호화해요.

함께 보기:

ENCRYPT , ENCRYPT_RAW , DECRYPT , TRY_DECRYPT , TRY_DECRYPT_RAW

출처: Snowflake SQL Reference

본문

문법

DECRYPT_RAW( <value_to_decrypt> , <key> , <iv> ,
         [ [ [ <additional_authenticated_data> , ] <encryption_method> , ] <aead_tag> ]
       )

인자

필수:

value_to_decrypt

복호화할 이진 값이에요.

key

데이터를 암호화/복호화하는 데 사용할 키예요. 키는 BINARY 값이어야 해요. 길이만 맞으면 아무 값이나 될 수 있어요. 예를 들어 AES128의 경우 키는 128비트(16바이트)여야 하고, AES256의 경우 키는 256비트(32바이트)여야 해요. 값을 암호화하는 데 사용한 키로 값을 복호화해야 해요.

iv

이 매개변수는 이 데이터를 암호화하고 복호화하는 데 사용할 초기화 벡터(Initialization Vector, IV)를 포함해요. IV는 특정 길이의 BINARY 값이어야 해요. GCM의 경우 이 필드는 96비트(12바이트)여야 해요. GCM 암호화 방식은 이 필드가 다른 크기여도 허용하지만, Snowflake는 현재 96비트만 지원해요. CCM의 경우 56비트(7바이트)여야 해요. ECB의 경우 이 매개변수는 필요하지 않아요. 그 외 지원되는 모든 암호화 모드에서는 128비트(16바이트)여야 해요. 이 값은 첫 번째 암호화 라운드를 초기화하는 데 사용돼요. 특히 GCM 같은 암호화 모드에서는 같은 IV와 키 조합을 두 번 이상 사용하면 안 돼요. 이 매개변수가 NULL로 설정되면 구현은 호출할 때마다 새 의사 난수 IV를 선택해요.

선택:

<algorithm>-<mode> [ /pad: <padding> ]

additional_authenticated_data

부가 인증 데이터(Additional Authenticated Data, AAD)는 복호화 과정에서 기밀성과 무결성이 보장되는 추가 데이터예요. 그러나 이 AAD는 암호화되지 않으며 ENCRYPT 또는 ENCRYPT_RAW 함수가 반환하는 값에 필드로 포함되지 않아요. AAD가 암호화 함수(ENCRYPT 또는 ENCRYPT_RAW)에 전달되면, 복호화 함수(DECRYPT 또는 DECRYPT_RAW)에도 같은 AAD를 전달해야 해요. 복호화 함수에 전달된 AAD가 암호화 함수에 전달된 AAD와 일치하지 않으면 복호화는 실패해요. AAD와 패스프레이즈의 차이는, 패스프레이즈는 비밀로 유지해야 하지만(그렇지 않으면 암호화가 사실상 무용지물) AAD는 공개된 채로 남길 수 있다는 점이에요. AAD는 공개 정보와 암호화된 값이 서로 연관되어 있음을 인증하는 데 도움을 줘요. ENCRYPT 함수의 예제 섹션에는 AAD가 일치할 때와 일치하지 않을 때의 동작을 보여주는 예제가 있어요. ENCRYPT_RAW와 DECRYPT_RAW의 경우 AAD의 데이터 타입은 BINARY여야 해요. ENCRYPT와 DECRYPT의 경우 AAD의 데이터 타입은 VARCHAR 또는 BINARY일 수 있고, 암호화된 값의 데이터 타입과 일치할 필요는 없어요. AAD는 GCM(기본값) 같은 AEAD 지원 암호화 모드에서만 지원돼요.

encryption_method

이 문자열은 데이터를 암호화/복호화하는 데 사용할 방식을 지정해요. 이 문자열은 하위 필드를 포함해요. 알고리즘은 현재 다음으로 제한돼요: 'AES': 패스프레이즈가 전달되면(예: ENCRYPT에) 함수는 AES-256 암호화(256비트)를 사용해요. 키가 전달되면(예: ENCRYPT_RAW에) 함수는 키 길이에 따라 128, 192, 256비트 암호화를 사용해요. 알고리즘은 대소문자를 구분하지 않아요. 모드는 메시지를 암호화하는 데 사용할 블록 암호 모드를 지정해요. 다음 표는 지원되는 모드와 그 모드 중 어떤 것이 패딩을 지원하는지 보여줘요.

모드는 대소문자를 구분하지 않아요. 패딩은 블록 크기의 배수가 아닌 길이의 메시지를 어떻게 패딩할지 지정해요. 패딩은 ECB와 CBC 모드에만 적용되며, 다른 모드에서는 무시돼요. 패딩의 가능한 값은: 'PKCS': 블록 패딩에 PKCS5를 사용. 'NONE': 패딩 없음. ECB 또는 CBC 모드를 사용할 때는 사용자가 패딩을 직접 처리해야 해요. 패딩은 대소문자를 구분하지 않아요. 기본 설정: 'AES-GCM'. 모드를 지정하지 않으면 GCM이 사용돼요. 패딩을 지정하지 않으면 PKCS가 사용돼요.

aead_tag

이 BINARY 값은 AEAD 지원 복호화 모드가 암호화된 데이터의 무결성과 기밀성을 확인하는 데 필요해요. ENCRYPT_RAW 함수가 반환한 AEAD 태그를 사용해요. 아래 예제는 이 값에 접근하고 사용하는 방법을 보여줘요.

반환

이 함수는 복호화된 값을 반환해요. 반환되는 값의 데이터 타입은 BINARY예요.

사용 지침

• ENCRYPT()로 암호화한 데이터를 복호화하려면 DECRYPT()를 사용해요. DECRYPT_RAW()는 사용하지 마세요.

• ENCRYPT_RAW()로 암호화한 데이터를 복호화하려면 DECRYPT_RAW()를 사용해요. DECRYPT()는 사용하지 마세요.

• 함수의 매개변수는 보안을 위해 마스킹돼요. 다음 같은 민감한 정보는 쿼리 로그에서 볼 수 없고 Snowflake에도 노출되지 않아요.

  • 암호화하거나 복호화할 문자열 또는 이진 값.

  • 패스프레이즈 또는 키.

• 함수는 암호화와 복호화를 효과적으로 수행하기 위해 FIPS 호환 암호화 라이브러리를 사용해요.

• 데이터를 복호화하는 데 사용하는 패스프레이즈 또는 키는 그 데이터를 암호화하는 데 사용한 패스프레이즈 또는 키와 동일해야 해요.

• 암호화된 이진 값에서 필드(암호문, 초기화 벡터 또는 태그)를 추출할 때는 다음을 사용해요.

as_binary(get(encrypted_value, '<field_name>'))

예를 들어:

as_binary(get(encrypted_value, 'ciphertext'))

encrypted_value:*field_name*::binary는 사용하지 마세요. 필드 접근 연산자 :는 추출된 필드 값을 문자열로 변환하는데, 소스가 BINARY이므로 그 문자열이 항상 유효한 UTF-8 문자열은 아니기 때문이에요.

예제

이 예제는 암호화와 복호화를 보여줘요.

가독성을 위해 BINARY_OUTPUT_FORMAT을 HEX로 설정해요:

_to_hex
ALTER SESSION SET BINARY_OUTPUT_FORMAT='HEX';

테이블을 만들고 로드해요.

이 예제를 단순화하기 위해 암호화/복호화 키가 암호화된 값과 함께 테이블에 저장돼요. 이는 안전하지 않아요. 키는 암호화된 데이터를 저장하는 테이블에 암호화되지 않은 값으로 절대 저장해서는 안 돼요.

CREATE OR REPLACE TABLE binary_table (
    encryption_key BINARY,   -- DO NOT STORE REAL ENCRYPTION KEYS THIS WAY!
    initialization_vector BINARY(12), -- DO NOT STORE REAL IV'S THIS WAY!!
    binary_column BINARY,
    encrypted_binary_column VARIANT,
    aad_column BINARY);

INSERT INTO binary_table (encryption_key,
                          initialization_vector,
                          binary_column,
                          aad_column)
    SELECT SHA2_BINARY('NotSecretEnough', 256),
            SUBSTR(TO_BINARY(HEX_ENCODE('AlsoNotSecretEnough'), 'HEX'), 0, 12),
            TO_BINARY(HEX_ENCODE('Bonjour'), 'HEX'),
            TO_BINARY(HEX_ENCODE('additional data'), 'HEX')
    ;

암호화:

UPDATE binary_table SET encrypted_binary_column =
    ENCRYPT_RAW(binary_column,
        encryption_key,
        initialization_vector,
        aad_column,
        'AES-GCM');
+------------------------+-------------------------------------+
| number of rows updated | number of multi-joined rows updated |
|------------------------+-------------------------------------|
|                      1 |                                   0 |
+------------------------+-------------------------------------+

이는 DECRYPT_RAW() 함수의 해당 호출을 보여줘요. 초기화 벡터(IV)는 암호화된 값에서 가져오므로 초기화 벡터를 별도로 저장할 필요가 없어요. 마찬가지로 AEAD 태그도 암호화된 값에서 읽어와요.

이 예제를 단순화하기 위해 암호화/복호화 키가 암호화된 값과 함께 테이블에서 읽어와요. 이는 안전하지 않아요. 키는 암호화된 데이터를 저장하는 테이블에 암호화되지 않은 값으로 절대 저장해서는 안 돼요.

SELECT 'Bonjour' as original_value,
       binary_column,
       hex_decode_string(to_varchar(binary_column)) as decoded,
       encrypted_binary_column,
       decrypt_raw(as_binary(get(encrypted_binary_column, 'ciphertext')),
                  encryption_key,
                  as_binary(get(encrypted_binary_column, 'iv')),
                  aad_column,
                  'AES-GCM',
                  as_binary(get(encrypted_binary_column, 'tag')))
           as decrypted,
       hex_decode_string(to_varchar(decrypt_raw(as_binary(get(encrypted_binary_column, 'ciphertext')),
                  encryption_key,
                  as_binary(get(encrypted_binary_column, 'iv')),
                  aad_column,
                  'AES-GCM',
                  as_binary(get(encrypted_binary_column, 'tag')))
                  ))
           as decrypted_and_decoded
    FROM binary_table;
+----------------+----------------+---------+---------------------------------------------+----------------+-----------------------+
| ORIGINAL_VALUE | BINARY_COLUMN  | DECODED | ENCRYPTED_BINARY_COLUMN                     | DECRYPTED      | DECRYPTED_AND_DECODED |
|----------------+----------------+---------+---------------------------------------------+----------------+-----------------------|
| Bonjour        | 426F6E6A6F7572 | Bonjour | {                                           | 426F6E6A6F7572 | Bonjour               |
|                |                |         |   "ciphertext": "CA2F4A383F6F55",           |                |                       |
|                |                |         |   "iv": "416C736F4E6F745365637265",         |                |                       |
|                |                |         |   "tag": "91F28FBC6A2FE9B213D1C44B8D75D147" |                |                       |
|                |                |         | }                                           |                |                       |
+----------------+----------------+---------+---------------------------------------------+----------------+-----------------------+

이전 예제는 DECRYPT_RAW()에 대한 긴 호출을 중복했어요. WITH 절을 사용해 중복을 줄일 수 있어요.

WITH
    decrypted_but_not_decoded as (
        decrypt_raw(as_binary(get(encrypted_binary_column, 'ciphertext')),
                      encryption_key,
                      as_binary(get(encrypted_binary_column, 'iv')),
                      aad_column,
                      'AES-GCM',
                      as_binary(get(encrypted_binary_column, 'tag')))
    )
SELECT 'Bonjour' as original_value,
       binary_column,
       hex_decode_string(to_varchar(binary_column)) as decoded,
       encrypted_binary_column,
       decrypted_but_not_decoded,
       hex_decode_string(to_varchar(decrypted_but_not_decoded))
           as decrypted_and_decoded
    FROM binary_table;
+----------------+----------------+---------+---------------------------------------------+---------------------------+-----------------------+
| ORIGINAL_VALUE | BINARY_COLUMN  | DECODED | ENCRYPTED_BINARY_COLUMN                     | DECRYPTED_BUT_NOT_DECODED | DECRYPTED_AND_DECODED |
|----------------+----------------+---------+---------------------------------------------+---------------------------+-----------------------|
| Bonjour        | 426F6E6A6F7572 | Bonjour | {                                           | 426F6E6A6F7572            | Bonjour               |
|                |                |         |   "ciphertext": "CA2F4A383F6F55",           |                           |                       |
|                |                |         |   "iv": "416C736F4E6F745365637265",         |                           |                       |
|                |                |         |   "tag": "91F28FBC6A2FE9B213D1C44B8D75D147" |                           |                       |
|                |                |         | }                                           |                           |                       |
+----------------+----------------+---------+---------------------------------------------+---------------------------+-----------------------+

더 알아보기

• ENCRYPT
• ENCRYPT_RAW
• DECRYPT
• TRY_DECRYPT
• TRY_DECRYPT_RAW
• Snowflake 암호화 함수