bq wrapped keyset

bq_keyset.md

BQ wrapped keyset per row

generate wrapped key per user

bq query --nouse_legacy_sql "
DECLARE kms_resource_name STRING;
SET kms_resource_name = 'gcp-kms://projects/srashid-test2/locations/us/keyRings/bqkr/cryptoKeys/k1';

SELECT
  TO_BASE64(KEYS.NEW_WRAPPED_KEYSET(kms_resource_name, 'AEAD_AES_GCM_256'))
"
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|                                                                                                                               f0_                                                                                                                                |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| CiQAxk8ywtK2G80JTyK1yYOzD/VCSoDHmju6up/HSu+oDA+6kycSlQEApq+Vk6oudT+a+60l1ohGOMQo9Rig9CIounleB3OsWXjyylhfJcfTDgDkRbTw+15GIbfvyiOVfmBuaFWP3oXA8yvKPNJCNgWseagU6bMSU7HBRymzFZQlqFij+QoKZbLfgyb3+/S0u7tjIVwUJ09ajEJ2hylINs8eC4qLcBWBUvyaS1giavTGwD2F+hoj7ysNCjGDgg== |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

encrypt some data

bq query --nouse_legacy_sql "
DECLARE kms_resource_name STRING;
DECLARE name STRING;
DECLARE ssn STRING;
DECLARE KEY BYTES;

SET kms_resource_name = 'gcp-kms://projects/srashid-test2/locations/us/keyRings/bqkr/cryptoKeys/k1';
SET KEY = (SELECT FROM_BASE64('CiQAxk8ywtK2G80JTyK1yYOzD/VCSoDHmju6up/HSu+oDA+6kycSlQEApq+Vk6oudT+a+60l1ohGOMQo9Rig9CIounleB3OsWXjyylhfJcfTDgDkRbTw+15GIbfvyiOVfmBuaFWP3oXA8yvKPNJCNgWseagU6bMSU7HBRymzFZQlqFij+QoKZbLfgyb3+/S0u7tjIVwUJ09ajEJ2hylINs8eC4qLcBWBUvyaS1giavTGwD2F+hoj7ysNCjGDgg=='));

SET name = 'sal';
SET ssn = '123456';

SELECT name, AEAD.ENCRYPT(KEYS.KEYSET_CHAIN(kms_resource_name, KEY),ssn,'')
"

+------+------------------------------------------------------+
| name |                         f0_                          |
+------+------------------------------------------------------+
| sal  | AWuj+Vp22m0DzZnci51JFy7hkPPrF6CyMLVaY7gkMttvLdun8689 |
+------+------------------------------------------------------+

decrypt

bq query --nouse_legacy_sql "
DECLARE kms_resource_name STRING;
DECLARE KEY BYTES;
DECLARE name STRING;

SET kms_resource_name = 'gcp-kms://projects/srashid-test2/locations/us/keyRings/bqkr/cryptoKeys/k1';
SET KEY = (SELECT FROM_BASE64('CiQAxk8ywtK2G80JTyK1yYOzD/VCSoDHmju6up/HSu+oDA+6kycSlQEApq+Vk6oudT+a+60l1ohGOMQo9Rig9CIounleB3OsWXjyylhfJcfTDgDkRbTw+15GIbfvyiOVfmBuaFWP3oXA8yvKPNJCNgWseagU6bMSU7HBRymzFZQlqFij+QoKZbLfgyb3+/S0u7tjIVwUJ09ajEJ2hylINs8eC4qLcBWBUvyaS1giavTGwD2F+hoj7ysNCjGDgg=='));
SET name = 'sal';

SELECT name, AEAD.DECRYPT_STRING(KEYS.KEYSET_CHAIN(kms_resource_name, KEY),FROM_BASE64('AWuj+Vp22m0DzZnci51JFy7hkPPrF6CyMLVaY7gkMttvLdun8689'),'')"

+------+--------+
| name |  f0_   |
+------+--------+
| sal  | 123456 |
+------+--------+

if the wrapped keyset is deleted, the encrypted data is not recoverable