[MDEV-11286] Client encryption Created: 2016-11-15  Updated: 2017-05-26

Status: Open
Project: MariaDB Server
Component/s: None
Fix Version/s: None

Type: Task Priority: Major
Reporter: Georg Richter Assignee: Unassigned
Resolution: Unresolved Votes: 0
Labels: None


 Description   

Client encryption

"Client encryption" is a feature to protect sensitive data such as credit card or insurance numbers stored in MariaDB server. Client encryption allows to encrypt sensitive data inside client applications and never reveal the encryption keys to MariaDB server.
Client encryption provides a separation between those who own data and those who manage data (or get in unauthorized possession of data).
Client encryption makes encryption transparent: an encryption at rest enabled MariaDB connector achieves this by encrypting and decrypting data automatically inside the client application.

Encryption types

Deterministic encryption

Deterministic encryption generate always the same encrypted value for the same plain text. This allows lookups, equality joins grouping and indexing on encrypted columns.

Randomized encryption

Randomized encryption is more secure since it uses a method to encrypt data in a less predictable manner, but it prevents searching, grouping and indexing.

Restrictions:

Encrypt at rest cannot be used

  • in string columns with non-binary collations
  • columns with default constraints
  • columns referenced by constraints
  • more ... ?!

Tasks

Server:

Syntax

Extend syntax for supporting encryption at rest:

CREATE TABLE my_table (
 
  a unsigned int not null auto_increment primary key,
 
  cc_mumber VARCHAR(200) COLLATE latin1_bin
 
            ENCRYPTED WITH (
 
              COLUMN_ENCRYPTION_KEY='my_key',
 
              ENCRYPTION_TYPE=DETERMINISTIC,
 
              ALGORITHM='AES-256-CTR'))

Protocol.

Extend column definition packet (by using first byte of filler)

lenenc_str     catalog
 
lenenc_str     schema
 
lenenc_str     table
 
lenenc_str     org_table
 
lenenc_str     name
 
lenenc_str     org_name
 
lenenc_int     length of fixed-length fields [0c]
 
2              character set
 
4              column length
 
1              type
 
2              flags
 
1              decimals
 
-----------------------------------------
 
1              encryption_type (1= deterministic, 2= randomized)
 
1              filler [00]
 
-----------------------------------------
 
if (encryption type)
 
lenenc_str     key
 
lenenc_str     crypto_algorithm_

Server Problems to solve
Storing.

possible solution

  • in saparate blob of data in frm
  • use engine attributes
Limited usage of client side encripted fields

Non deterministic encrypted fields allowed only in SELECT list.

Deterministic encrypted fields allowed in = <> and GROUP BY.

Real problem is to pass the knowledge about client side encryption via temporary tables (for derived tables, views and CTE).

Item_func, Item_sum and other should check on fix_fields() arguments, as well as preparation of GROUP_BY/ORDER BY to prevent illegal usage of client side encrypted fields

 Comments   
Comment by Sergei Petrunia [ 2016-11-17 ]

sanja, I'm wondering - is there any plan to extend information_schema database so that it shows which columns are encrypted and which aren't? I can't point at any specific case where this is required *), but it seems like a natural addition.

*) I see that information about encryption is provided in "column definition packet".. but is the client always able to use it?

Comment by Sergei Petrunia [ 2016-11-17 ]

suppose there is a table

CREATE TABLE t1 ( col1 INT, col2 INT ENCRYPTED WITH (...)) ;

and one runs a statement:

INSERT INTO t1 (col1) VALUES (12345);

The server code will insert a row with col2=0. The client will later read the row, and will attempt to decrypt it. I am not sure what 0x00 will be decrypted to (mostly likely it cause a decryption error?)
How should this scenario be handled? Should we disallow it somehow, let the client handle it, or something else?

Comment by Sergei Petrunia [ 2016-11-17 ]

A few more random questions:

  • What is the maximum size of the encrypted column? For example, if the column is col2 INT ENCRYPTED WITH (...), does the server code handle it as an arbitrary-length blob, or there is some length limit?
  • I assume the client will have to indicate to the server that it supports encryption, so that it can receive "Extended column definition packet" with info about encryption. What happens when a client connects, doesn't specify the flag, and then attempts to read or write to the encrypted table?
  • The statement

    Non deterministic encrypted fields allowed only in SELECT list.

    is not correct. You need to allow them in INSERT statements and probably in UPDATE ... SET encrypted=... as well

Comment by Sergei Golubchik [ 2017-05-26 ]

What is the "key" that is part of the column definition and the protocol packet?

Generated at Thu Feb 08 07:48:46 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.