DZone
Thanks for visiting DZone today,
Edit Profile
  • Manage Email Subscriptions
  • How to Post to DZone
  • Article Submission Guidelines
Sign Out View Profile
  • Post an Article
  • Manage My Drafts
Over 2 million developers have joined DZone.
Log In / Join
Refcards Trend Reports Events Over 2 million developers have joined DZone. Join Today! Thanks for visiting DZone today,
Edit Profile Manage Email Subscriptions Moderation Admin Console How to Post to DZone Article Submission Guidelines
View Profile
Sign Out
Refcards
Trend Reports
Events
Zones
Culture and Methodologies Agile Career Development Methodologies Team Management
Data Engineering AI/ML Big Data Data Databases IoT
Software Design and Architecture Cloud Architecture Containers Integration Microservices Performance Security
Coding Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks
Culture and Methodologies
Agile Career Development Methodologies Team Management
Data Engineering
AI/ML Big Data Data Databases IoT
Software Design and Architecture
Cloud Architecture Containers Integration Microservices Performance Security
Coding
Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance
Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks
  1. DZone
  2. Data Engineering
  3. Databases
  4. Column Level Encryption in GCP

Column Level Encryption in GCP

Choose the right data to encrypt.

Sachin Kulkarni user avatar by
Sachin Kulkarni
·
Satyen Mishra user avatar by
Satyen Mishra
·
Mahendra Gawayi user avatar by
Mahendra Gawayi
·
Sep. 16, 19 · Tutorial
Like (4)
Save
Tweet
Share
9.66K Views

Join the DZone community and get the full member experience.

Join For Free

matrix-rain-on-macbook-screen


Column level encryption allows users to select specific information or attributes to be encrypted instead of encrypting an entire database. Some data is more sensitive than others such as personal beliefs, home addresses, etc. This information can identify a person. In order to ensure private information is protected in transit and at rest, data should be encrypted. Encryption will encode plaintext data into ciphertext.

Non-designated readers or receivers will not able to read the data without the decryption key. Even Database Administrator should not be allowed to see sensitive data belonging to others. The fact that not all data is sensitive and important, Column Level Encryption was created to allow users flexibility in choosing what sort of attributes (table columns) should be encrypted.

You may also like: Transparent Data Encryption for Databases.

Here, I am going to explain how text data that is categorized as sensitive should be encrypted in GCP managed Cloud SQL databases. Think of this data as descriptive, unstructured (no fixed format and length) in nature (e.g. home address, personal opinion, or beliefs on religious matters, etc). 

Advantages of Column Level Encryption

  1. Flexibility in data to encrypt. The application can be written to control when, where, by whom, and how data is viewed.

  2. More secure as each column can have its own unique encryption key within the database.

  3. Encryption is possible when data is active and not just “at rest.”

  4. Retrieval speed is maintained because there's less encrypted data.

Google Cloud KMS Service

Unlike Microsoft SQL server, managed the Cloud SQL service offering from GCP with MySQL and Postgres variants does not offer Column Level Encryption natively. This limitation forces us to encrypt/ decrypt each sensitive value, one by one, inside our program and then persist into a database (by encryption) column or display on an application's user interface (by decryption). 

Asymmetric keys (Asymmetric decrypt) from Google Cloud KMS can be used to encrypt and decrypt sensitive data. The public key encrypts sensitive data; whereas the private key is used to decrypt it.

To create an Asymmetric decrypt key in Cloud KMS, select the crypto algorithm that provides at least 128 bits of security. 

You need to use Cloud KMS APIs to perform crypto operations — encrypt and decrypt. 

A service account is used in Google to invoke API of Service without the user getting involved. The service account is then assigned permissions to access the resources it needs. We will create two service accounts —one with an encrypt and another with a decrypt role. Following the steps below: 

  1. Download a JSON key file for that service account.

  2. Provision that key file into your production environment.

  3. Load the credentials from the key file in your code.

cloudkms.cryptoKeyEncrypter ---role to encrypt data
cloudkms.cryptoKeyDecrypter -- role to decrypt data


Encryption Logic

Please refer code block below to encrypt each sensitive value using Cloud KMS API client
KeyManagementServiceClient. Use Service account JSON key file created above for encrypt. 

After this, persist each encrypted value into database programmatically.

public byte[] Encrypt(string projectId, string locationId, string keyRingId, string cryptoKeyId, string plaintext)
        {
            var credential = GoogleCredential.FromFile(@<<Encrypt Service account json key file>>)
                    .CreateScoped(KeyManagementServiceClient.DefaultScopes);
            var channel = new Grpc.Core.Channel(KeyManagementServiceClient.DefaultEndpoint.ToString(),
               credential.ToChannelCredentials());
            var client = KeyManagementServiceClient.Create(channel);

            CryptoKeyName cryptoKeyName = new CryptoKeyName(projectId, locationId, keyRingId, cryptoKeyId);
            byte[] plaintextBA = System.Text.Encoding.UTF8.GetBytes(plaintext); 
            CryptoKeyPathName pathName = CryptoKeyPathName.Parse(cryptoKeyName.ToString());
            EncryptResponse result = client.Encrypt(pathName, ByteString.CopyFrom(plaintextBA));
            return result.Ciphertext.ToByteArray();
        }


Decryption Logic

Please refer code block below to decrypt each sensitive column value using Cloud KMS API client KeyManagementServiceClient. Use Service account JSON key file created for decrypt. 

This decrypted plan value is then shown on GUI to authorised users. 

Ensure that access to Service account json key file for decryption is not available to any users except the program.

public string Decrypt(string projectId, string locationId, string keyRingId, string cryptoKeyId,
             string encryptedText) 
        {
            var credential = GoogleCredential.FromFile(@<<Decrypt Service account json key file>>)
                    .CreateScoped(KeyManagementServiceClient.DefaultScopes);
            var channel = new Grpc.Core.Channel(KeyManagementServiceClient.DefaultEndpoint.ToString(), credential.ToChannelCredentials());
            var client = KeyManagementServiceClient.Create(channel);

            CryptoKeyName cryptoKeyName = new CryptoKeyName(projectId, locationId, keyRingId, cryptoKeyId);
            DecryptResponse result = client.Decrypt(cryptoKeyName, encryptedText);
            return result.Plaintext.ToStringUtf8();
        }


Bring Your own Keys (Customer Generated)

You have the option to use a Google-generated key or import your own key into Cloud HSM. For security reasons, you can manually wrap your key created outside Cloud Key Management Service and then import it to Cloud KMS. Please refer to the links below to understand this process

  • https://cloud.google.com/kms/docs/wrapping-a-key.

  • https://cloud.google.com/kms/docs/importing-a-wrapped-key.

Conclusion

This may be one approach that can be considered for GCP in order to achieve column level encryption for sensitive (unstructured) data. Databases with native support for column level encryption are the best bet from a performance perspective.

Here, you need to do a fair bit of work to manually manage encryption in the absence (as of this writing) of native support for Column Level Encryption in Cloud SQL Postgres database service. Performance may take a hit if there are many values for which crypto operations need to be performed. 


Related Articles

  • New System of Encryption of User Data in Android 5.0.
  • Encryption, Part 1: Symmetric Encryption.
Database sql Microsoft SQL Server Data (computing) Cloud

Opinions expressed by DZone contributors are their own.

Popular on DZone

  • 5 Factors When Selecting a Database
  • OpenID Connect Flows
  • Iptables Basic Commands for Novice
  • Web Application Architecture: The Latest Guide

Comments

Partner Resources

X

ABOUT US

  • About DZone
  • Send feedback
  • Careers
  • Sitemap

ADVERTISE

  • Advertise with DZone

CONTRIBUTE ON DZONE

  • Article Submission Guidelines
  • Become a Contributor
  • Visit the Writers' Zone

LEGAL

  • Terms of Service
  • Privacy Policy

CONTACT US

  • 600 Park Offices Drive
  • Suite 300
  • Durham, NC 27709
  • support@dzone.com
  • +1 (919) 678-0300

Let's be friends: