關於用戶端加密

本頁說明如何在 Cloud SQL 上實作用戶端加密。

總覽

用戶端加密是指在將資料寫入 Cloud SQL 之前加密資料。您可以加密 Cloud SQL 資料,只有應用程式才能解密。

如要啟用用戶端加密,請選擇下列其中一種做法:

  1. 使用儲存在 Cloud Key Management Service (Cloud KMS) 中的加密金鑰。
  2. 使用儲存在應用程式本機的加密金鑰。

本主題將說明如何使用第一個選項,這是最順暢的金鑰管理方式。我們會在 Cloud KMS 中建立加密金鑰,並使用 Google 的開放原始碼加密程式庫 Tink 實作信封加密

為何需要用戶端加密?

如要在資料欄層級保護 Cloud SQL 資料,您需要用戶端加密機制 1。假設您有一個名稱和信用卡號碼的資料表。您想授予使用者存取這個表格的權限,但不希望他們查看信用卡號碼。您可以使用用戶端加密功能加密號碼。只要使用者未獲授權存取 Cloud KMS 中的加密金鑰,就無法讀取信用卡資訊。

使用 Cloud KMS 建立金鑰

您可以在 Google Cloud Platform 中建立及管理金鑰。

Cloud KMS 支援多種金鑰類型。如要使用用戶端加密功能,您必須建立對稱式金鑰

如要授予應用程式 Cloud KMS 中的金鑰存取權,請為應用程式使用的服務帳戶指派 cloudkms.cryptoKeyEncrypterDecrypter 角色。在 gcloud 中,您可以使用下列指令執行這項操作:

gcloud kms keys add-iam-policy-binding key \
--keyring=key-ring \
--location=location \
--member=serviceAccount:service-account-name@example.domain.com \
--role=roles/cloudkms.cryptoKeyEncrypterDecrypter

雖然您可以使用 KMS 金鑰直接加密資料,但我們在此使用更彈性的解決方案,也就是信封式加密。這樣一來,我們就能加密超過 64 KB 的郵件,這是 Cloud Key Management Service API 支援的最大郵件大小。

Cloud KMS 包絡加密

在信封式加密中,KMS 金鑰會做為金鑰加密金鑰 (KEK)。也就是說,金鑰加密金鑰是用來加密資料加密金鑰 (DEK),而 DEK 則是用來加密實際資料。

在 Cloud KMS 中建立 KEK 後,如要加密每則訊息,請執行下列操作:

  • 在本機產生資料加密金鑰 (DEK)。
  • 在本機使用這個 DEK 加密訊息。
  • 呼叫 Cloud KMS,使用 KEK 加密 (包裝) DEK。
  • 儲存加密的資料與經過包裝的 DEK。

在本主題中,我們使用 Tink,而非從頭開始實作信封式加密。

Tink

Tink 是跨平台的多語言程式庫,提供高階加密 API。如要使用 Tink 的信封式加密功能加密資料,請向 Tink 提供指向 Cloud KMS 中 KEK 的金鑰 URI,以及允許 Tink 使用 KEK 的憑證。Tink 會產生 DEK、加密資料、包裝 DEK,並傳回單一密文,其中包含加密資料和包裝的 DEK。

Tink 支援 C++、Java、Go 和 Python 中的信封加密,方法是使用 AEAD API:

public interface Aead{
  byte[] encrypt(final byte[] plaintext, final byte[] associatedData)
  throws
  byte[] decrypt(final byte[] ciphertext, final byte[] associatedData)
  throws
}

除了正常的訊息/密文引數之外,加密和解密方法也支援選用的關聯資料。這個引數可用於將密文繫結至一筆資料。舉例來說,假設您有一個資料庫,其中包含 user-id 欄位和 encrypted-medical-history 欄位。在這種情況下,加密病史時,可能應使用 user-id 欄位做為相關聯的資料。確保攻擊者無法將醫療記錄從一位使用者轉移到另一位使用者。此外,執行查詢時,系統也會使用這項資訊來驗證您是否擁有正確的資料列。

範例

在本節中,我們將逐步說明使用用戶端加密的選民資訊資料庫範例程式碼。程式碼範例說明如何:

  • 建立資料庫資料表和連線集區
  • 設定 Tink 進行信封式加密
  • 使用 Tink 的信封式加密功能,透過 Cloud KMS 中的 KEK 加密及解密資料

事前準備

  1. 請按照這些操作說明建立 Cloud SQL 執行個體。請記下您建立的連線字串、資料庫使用者和資料庫密碼。

  2. 請按照這些操作說明,為應用程式建立資料庫。記下資料庫名稱。

  3. 請按照這些操作說明,為應用程式建立 KMS 金鑰。複製您建立的金鑰資源名稱。

  4. 按照這些操作說明,建立具備「Cloud SQL 用戶端」權限的服務帳戶。

  5. 按照這些操作說明,為服務帳戶新增金鑰的「Cloud KMS CryptoKey Encrypter/Decrypter」權限。

建立連線集區,並在資料庫中建立新資料表。

Java


import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import javax.sql.DataSource;

public class CloudSqlConnectionPool {

  public static DataSource createConnectionPool(String dbUser, String dbPass, String dbName,
      String instanceConnectionName) {
    HikariConfig config = new HikariConfig();
    config.setDataSourceClassName("com.microsoft.sqlserver.jdbc.SQLServerDataSource");
    config.setUsername(dbUser); // e.g. "root", "sqlserver"
    config.setPassword(dbPass); // e.g. "my-password"
    config.addDataSourceProperty("databaseName", dbName);

    // The Cloud SQL Java Connector provides SSL encryption so 
    // it should be disabled at the driver level
    config.addDataSourceProperty("encrypt", "false");

    config.addDataSourceProperty("socketFactoryClass",
        "com.google.cloud.sql.sqlserver.SocketFactory");
    config.addDataSourceProperty("socketFactoryConstructorArg", instanceConnectionName);
    DataSource pool = new HikariDataSource(config);
    return pool;
  }

  public static void createTable(DataSource pool, String tableName) throws SQLException {
    // Safely attempt to create the table schema.
    try (Connection conn = pool.getConnection()) {

      String stmt = String.format("IF NOT EXISTS("
          + "SELECT * FROM sysobjects WHERE name='%s' and xtype='U')"
          + "CREATE TABLE %s ("
          + "vote_id INT NOT NULL IDENTITY,"
          + "time_cast DATETIME NOT NULL,"
          + "team VARCHAR(6) NOT NULL,"
          + "voter_email VARBINARY(255)"
          + "PRIMARY KEY (vote_id));", tableName, tableName);
      try (PreparedStatement createTableStatement = conn.prepareStatement(stmt);) {
        createTableStatement.execute();
      }
    }
  }
}

Python

import pytds
import sqlalchemy
from sqlalchemy import Column
from sqlalchemy import DateTime
from sqlalchemy import Integer
from sqlalchemy import Table


def init_tcp_connection_engine(
    db_user: str, db_pass: str, db_name: str, db_host: str
) -> sqlalchemy.engine.base.Engine:
    # Remember - storing secrets in plaintext is potentially unsafe. Consider using
    # something like https://cloud.google.com/secret-manager/docs/overview to help keep
    # secrets secret.

    # Extract host and port from db_host
    host_args = db_host.split(":")
    db_hostname, db_port = host_args[0], int(host_args[1])

    def connect_with_pytds() -> pytds.Connection:
        return pytds.connect(
            db_hostname,  # e.g. "127.0.0.1"
            user=db_user,  # e.g. "my-database-user"
            password=db_pass,  # e.g. "my-database-password"
            database=db_name,  # e.g. "my-database-name"
            port=db_port,  # e.g. 1433
            bytes_to_unicode=False,  # disables automatic decoding of bytes
        )

    pool = sqlalchemy.create_engine(
        # This allows us to use the pytds sqlalchemy dialect, but also set the
        # bytes_to_unicode flag to False, which is not supported by the dialect
        "mssql+pytds://",
        creator=connect_with_pytds,
    )

    print("Created TCP connection pool")
    return pool


def init_db(
    db_user: str,
    db_pass: str,
    db_name: str,
    db_host: str,
    table_name: str,
) -> sqlalchemy.engine.base.Engine:
    db = init_tcp_connection_engine(db_user, db_pass, db_name, db_host)

    # Create tables (if they don't already exist)
    if not db.has_table(table_name):
        metadata = sqlalchemy.MetaData(db)
        Table(
            table_name,
            metadata,
            Column("vote_id", Integer, primary_key=True, nullable=False),
            Column("voter_email", sqlalchemy.types.VARBINARY, nullable=False),
            Column("time_cast", DateTime, nullable=False),
            Column("team", sqlalchemy.types.VARCHAR(6), nullable=False),
        )
        metadata.create_all()

    print(f"Created table {table_name} in db {db_name}")
    return db

使用 Tink 初始化信封 AEAD 基本類型。

Java


import com.google.crypto.tink.Aead;
import com.google.crypto.tink.KmsClient;
import com.google.crypto.tink.aead.AeadConfig;
import com.google.crypto.tink.aead.AeadKeyTemplates;
import com.google.crypto.tink.aead.KmsEnvelopeAead;
import com.google.crypto.tink.integration.gcpkms.GcpKmsClient;
import java.security.GeneralSecurityException;

public class CloudKmsEnvelopeAead {

  public static Aead get(String kmsUri) throws GeneralSecurityException {
    AeadConfig.register();

    // Create a new KMS Client
    KmsClient client = new GcpKmsClient().withDefaultCredentials();

    // Create an AEAD primitive using the Cloud KMS key
    Aead gcpAead = client.getAead(kmsUri);

    // Create an envelope AEAD primitive.
    // This key should only be used for client-side encryption to ensure authenticity and integrity
    // of data.
    return new KmsEnvelopeAead(AeadKeyTemplates.AES128_GCM, gcpAead);
  }
}

Python

import logging

import tink
from tink import aead
from tink.integration import gcpkms

logger = logging.getLogger(__name__)


def init_tink_env_aead(key_uri: str, credentials: str) -> tink.aead.KmsEnvelopeAead:
    aead.register()

    try:
        gcp_client = gcpkms.GcpKmsClient(key_uri, credentials)
        gcp_aead = gcp_client.get_aead(key_uri)
    except tink.TinkError as e:
        logger.error("Error initializing GCP client: %s", e)
        raise e

    # Create envelope AEAD primitive using AES256 GCM for encrypting the data
    # This key should only be used for client-side encryption to ensure authenticity and integrity
    # of data.
    key_template = aead.aead_key_templates.AES256_GCM
    env_aead = aead.KmsEnvelopeAead(key_template, gcp_aead)

    print(f"Created envelope AEAD Primitive using KMS URI: {key_uri}")

    return env_aead

加密資料並插入資料庫。

Java


import com.google.crypto.tink.Aead;
import java.security.GeneralSecurityException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.Date;
import javax.sql.DataSource;

public class EncryptAndInsertData {

  public static void main(String[] args) throws GeneralSecurityException, SQLException {
    // Saving credentials in environment variables is convenient, but not secure - consider a more
    // secure solution such as Cloud Secret Manager to help keep secrets safe.
    String dbUser = System.getenv("DB_USER"); // e.g. "root", "mysql"
    String dbPass = System.getenv("DB_PASS"); // e.g. "mysupersecretpassword"
    String dbName = System.getenv("DB_NAME"); // e.g. "votes_db"
    String instanceConnectionName =
        System.getenv("INSTANCE_CONNECTION_NAME"); // e.g. "project-name:region:instance-name"
    String kmsUri = System.getenv("CLOUD_KMS_URI"); // e.g. "gcp-kms://projects/...path/to/key
    // Tink uses the "gcp-kms://" prefix for paths to keys stored in Google Cloud KMS. For more
    // info on creating a KMS key and getting its path, see
    // https://cloud.google.com/kms/docs/quickstart

    String team = "TABS";
    String tableName = "votes";
    String email = "hello@example.com";

    // Initialize database connection pool and create table if it does not exist
    // See CloudSqlConnectionPool.java for setup details
    DataSource pool =
        CloudSqlConnectionPool.createConnectionPool(dbUser, dbPass, dbName, instanceConnectionName);
    CloudSqlConnectionPool.createTable(pool, tableName);

    // Initialize envelope AEAD
    // See CloudKmsEnvelopeAead.java for setup details
    Aead envAead = CloudKmsEnvelopeAead.get(kmsUri);

    encryptAndInsertData(pool, envAead, tableName, team, email);
  }

  public static void encryptAndInsertData(
      DataSource pool, Aead envAead, String tableName, String team, String email)
      throws GeneralSecurityException, SQLException {

    try (Connection conn = pool.getConnection()) {
      String stmt =
          String.format(
              "INSERT INTO %s (team, time_cast, voter_email) VALUES (?, ?, ?);", tableName);
      try (PreparedStatement voteStmt = conn.prepareStatement(stmt); ) {
        voteStmt.setString(1, team);
        voteStmt.setTimestamp(2, new Timestamp(new Date().getTime()));

        // Use the envelope AEAD primitive to encrypt the email, using the team name as
        // associated data. This binds the encryption of the email to the team name, preventing
        // associating an encrypted email in one row with a team name in another row.
        byte[] encryptedEmail = envAead.encrypt(email.getBytes(), team.getBytes());
        voteStmt.setBytes(3, encryptedEmail);

        // Finally, execute the statement. If it fails, an error will be thrown.
        voteStmt.execute();
        System.out.println(String.format("Successfully inserted row into table %s", tableName));
      }
    }
  }
}

Python

import datetime
import logging
import os

import sqlalchemy
import tink

from .cloud_kms_env_aead import init_tink_env_aead
from .cloud_sql_connection_pool import init_db


logger = logging.getLogger(__name__)


def main() -> None:
    db_user = os.environ["DB_USER"]  # e.g. "root", "sqlserver"
    db_pass = os.environ["DB_PASS"]  # e.g. "mysupersecretpassword"
    db_name = os.environ["DB_NAME"]  # e.g. "votes_db"

    # Set if connecting using TCP:
    db_host = os.environ["DB_HOST"]  # e.g. "127.0.0.1"

    # Set if connecting using Unix sockets:
    db_socket_dir = os.environ.get("DB_SOCKET_DIR", "/cloudsql")

    instance_connection_name = os.environ["INSTANCE_CONNECTION_NAME"]
    # e.g. "project-name:region:instance-name"

    credentials = os.environ.get("GOOGLE_APPLICATION_CREDENTIALS", "")
    key_uri = "gcp-kms://" + os.environ["GCP_KMS_URI"]
    # e.g. "gcp-kms://projects/...path/to/key
    # Tink uses the "gcp-kms://" prefix for paths to keys stored in Google
    # Cloud KMS. For more info on creating a KMS key and getting its path, see
    # https://cloud.google.com/kms/docs/quickstart

    table_name = "votes"
    team = "TABS"
    email = "hello@example.com"

    env_aead = init_tink_env_aead(key_uri, credentials)
    db = init_db(
        db_user,
        db_pass,
        db_name,
        table_name,
        instance_connection_name,
        db_socket_dir,
        db_host,
    )

    encrypt_and_insert_data(db, env_aead, table_name, team, email)


def encrypt_and_insert_data(
    db: sqlalchemy.engine.base.Engine,
    env_aead: tink.aead.KmsEnvelopeAead,
    table_name: str,
    team: str,
    email: str,
) -> None:
    time_cast = datetime.datetime.now(tz=datetime.timezone.utc)
    # Use the envelope AEAD primitive to encrypt the email, using the team name as
    # associated data. Encryption with associated data ensures authenticity
    # (who the sender is) and integrity (the data has not been tampered with) of that
    # data, but not its secrecy. (see RFC 5116 for more info)
    encrypted_email = env_aead.encrypt(email.encode(), team.encode())
    # Verify that the team is one of the allowed options
    if team != "TABS" and team != "SPACES":
        logger.error(f"Invalid team specified: {team}")
        return

    # Preparing a statement before hand can help protect against injections.
    stmt = sqlalchemy.text(
        f"INSERT INTO {table_name} (time_cast, team, voter_email)"
        " VALUES (:time_cast, :team, CONVERT(varbinary(max), :voter_email, 0))"
    )

    # Using a with statement ensures that the connection is always released
    # back into the pool at the end of statement (even if an error occurs)
    with db.connect() as conn:
        conn.execute(stmt, time_cast=time_cast, team=team, voter_email=encrypted_email)
    print(f"Vote successfully cast for '{team}' at time {time_cast}!")

查詢資料庫並解密儲存的資料。

Java


import com.google.crypto.tink.Aead;
import java.security.GeneralSecurityException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import javax.sql.DataSource;

public class QueryAndDecryptData {

  public static void main(String[] args) throws GeneralSecurityException, SQLException {
    // Saving credentials in environment variables is convenient, but not secure - consider a more
    // secure solution such as Cloud Secret Manager to help keep secrets safe.
    String dbUser = System.getenv("DB_USER"); // e.g. "root", "mysql"
    String dbPass = System.getenv("DB_PASS"); // e.g. "mysupersecretpassword"
    String dbName = System.getenv("DB_NAME"); // e.g. "votes_db"
    String instanceConnectionName =
        System.getenv("INSTANCE_CONNECTION_NAME"); // e.g. "project-name:region:instance-name"
    String kmsUri = System.getenv("CLOUD_KMS_URI"); // e.g. "gcp-kms://projects/...path/to/key
    // Tink uses the "gcp-kms://" prefix for paths to keys stored in Google Cloud KMS. For more
    // info on creating a KMS key and getting its path, see
    // https://cloud.google.com/kms/docs/quickstart

    String tableName = "votes123";

    // Initialize database connection pool and create table if it does not exist
    // See CloudSqlConnectionPool.java for setup details
    DataSource pool =
        CloudSqlConnectionPool.createConnectionPool(dbUser, dbPass, dbName, instanceConnectionName);
    CloudSqlConnectionPool.createTable(pool, tableName);

    // Initialize envelope AEAD
    // See CloudKmsEnvelopeAead.java for setup details
    Aead envAead = CloudKmsEnvelopeAead.get(kmsUri);

    // Insert row into table to test
    // See EncryptAndInsert.java for setup details
    EncryptAndInsertData.encryptAndInsertData(
        pool, envAead, tableName, "SPACES", "hello@example.com");

    queryAndDecryptData(pool, envAead, tableName);
  }

  public static void queryAndDecryptData(DataSource pool, Aead envAead, String tableName)
      throws GeneralSecurityException, SQLException {

    try (Connection conn = pool.getConnection()) {
      String stmt =
          String.format(
              "SELECT TOP(5) team, time_cast, voter_email FROM %s ORDER BY time_cast DESC;",
              tableName);
      try (PreparedStatement voteStmt = conn.prepareStatement(stmt); ) {
        ResultSet voteResults = voteStmt.executeQuery();

        System.out.println("Team\tTime Cast\tEmail");
        while (voteResults.next()) {
          String team = voteResults.getString(1);
          Timestamp timeCast = voteResults.getTimestamp(2);

          // Use the envelope AEAD primitive to encrypt the email, using the team name as
          // associated data. This binds the encryption of the email to the team name, preventing
          // associating an encrypted email in one row with a team name in another row.
          String email = new String(envAead.decrypt(voteResults.getBytes(3), team.getBytes()));

          System.out.println(String.format("%s\t%s\t%s", team, timeCast, email));
        }
      }
    }
  }
}

Python

import os

import sqlalchemy
import tink

from .cloud_kms_env_aead import init_tink_env_aead
from .cloud_sql_connection_pool import init_db
from .encrypt_and_insert_data import encrypt_and_insert_data


def main() -> None:
    db_user = os.environ["DB_USER"]  # e.g. "root", "sqlserver"
    db_pass = os.environ["DB_PASS"]  # e.g. "mysupersecretpassword"
    db_name = os.environ["DB_NAME"]  # e.g. "votes_db"

    # Set if connecting using TCP:
    db_host = os.environ["DB_HOST"]  # e.g. "127.0.0.1"

    # Set if connecting using Unix sockets:
    db_socket_dir = os.environ.get("DB_SOCKET_DIR", "/cloudsql")

    instance_connection_name = os.environ["INSTANCE_CONNECTION_NAME"]
    # e.g. "project-name:region:instance-name"

    credentials = os.environ.get("GOOGLE_APPLICATION_CREDENTIALS", "")
    key_uri = "gcp-kms://" + os.environ["GCP_KMS_URI"]
    # e.g. "gcp-kms://projects/...path/to/key
    # Tink uses the "gcp-kms://" prefix for paths to keys stored in Google
    # Cloud KMS. For more info on creating a KMS key and getting its path, see
    # https://cloud.google.com/kms/docs/quickstart

    table_name = "votes"
    team = "TABS"
    email = "hello@example.com"

    env_aead = init_tink_env_aead(key_uri, credentials)
    db = init_db(
        db_user,
        db_pass,
        db_name,
        table_name,
        instance_connection_name,
        db_socket_dir,
        db_host,
    )

    encrypt_and_insert_data(db, env_aead, table_name, team, email)
    query_and_decrypt_data(db, env_aead, table_name)


def query_and_decrypt_data(
    db: sqlalchemy.engine.base.Engine,
    env_aead: tink.aead.KmsEnvelopeAead,
    table_name: str,
) -> None:
    with db.connect() as conn:
        # Execute the query and fetch all results
        recent_votes = conn.execute(
            f"SELECT TOP(5) team, time_cast, voter_email FROM {table_name} "
            "ORDER BY time_cast DESC"
        ).fetchall()

        print("Team\tEmail\tTime Cast")

        for row in recent_votes:
            team = row[0]
            # Use the envelope AEAD primitive to decrypt the email, using the team name as
            # associated data. Encryption with associated data ensures authenticity
            # (who the sender is) and integrity (the data has not been tampered with) of that
            # data, but not its secrecy. (see RFC 5116 for more info)
            email = env_aead.decrypt(row[2], team).decode()
            time_cast = row[1]

            # Print recent votes
            print(f"{team}\t{email}\t{time_cast}")


  1. 您也可以在執行個體資料庫層級限制存取權。