Database Security (part 4 of 4)
Dynamic Data Masking
You set up a DDM policy in the Azure portal by selecting the DDM operation in your SQL Database configuration blade or settings blade. This feature cannot be set by using portal for Azure Synapse.
Dynamic data masking policy
- SQL users excluded from masking- A set of SQL users or AAD identities that get unmasked data in the SQL query results. Users with the administrators privileges are always excluded from masking, and view the original data without any mask.
- Masking rules- A set of rules that define the designated fields to be masked and the masking function that is used. The designated fields can be defined using a database schema name, table name, and column name.
- Masking functions- A set of methods that controls the exposure of data for different scenarios.
In the DDM blade in the portal, you can review the recommended columns for your database. All you need to do is click on Add Mask for one or more columns and then Save to apply a mask for these fields.
Transparent Data Encryption
TDE performs real time I/O encryption and decryption of the data at the page level and on the database startup, the encrypted Database Encryption Key (DEK) is decrypted and then used for decrytion and re-encryption of the database files in the SQL Server Database Engine process.
For Azure SQL Database and Azure Synapse, the TDE protector is set at the logical SQL server level and is inherited by all databases associated with that server. The term server always refers both to server and instance, unless stated differently.
Service-managed transparent data encryption
In Azure, the default setting for TDE is that the DEK is protected by a built-in server certificate. If the two databases are connected to the same server, they also share the same built-in certificate and the Microsoft can automatically rotate these certificates in compliance with the internal security policy and the root key is protected by a Microsoft internal secret store. It can also seamlessly move and manage the keys as needed for geo-replication and restores.
Customer-managed transparent data encryption- bring your own key
Always Encrypted
SQL Database Always Encrypted
Always encrypted is a feature designed to secure sensitive data, like credit numbers, or national identification numbers, stored in the Azure SQL database or SQL server databases which allows the clients to encrypt sensitive data inside client applications and never reveal encryption keys to the Database Engine.
It also makes the encryption transparent to applications and the driver encrypts the data in sensitive columns before passing the data to the Database Engine and automatically rewrites queries so that the semantics to the application are preserved. Similarly, the driver transparently decrypts data, stored in encrypted database columns, contained in query results.
Always Encrypted Features
Always Encrypted supports two types of encryption:
- Deterministic encryption- It always generates the same encrypted value for any given plain text value and allows point lookups, equality joins, grouping, as well as indexing on encrypted columns. Deterministic encryption must use a column collation using binary2 sort order for character columns.
- Randomized encryption- It uses a method that encrypts the data in a less predictable manner. It is more secure, but prevents searching, grouping, indexing, and joining on encrypted columns.
You can use deterministic encryption for the columns that will be used as search and grouping parameters such as government ID number, while randomized encryption for the data such as confidential investigation comments, which are not grouped with other records and are not used to join tables.
Always Encrypted Implementation
The initial setup of Always Encrypted in a database generally involves generating Always Encrypted Keys, creating key metadata, configuring encryption properties of selected database columns, and/or encrypting data that may already exist in the columns that need to be encrypted. It is to be noted that some of these tasks are not supported in Transact-SQL and require the use of client-side tools. You can use SQL Server Management Studio (SSMS) or PowerShell to accomplish such tasks.
Whenever you set up an encryption for a column, you can specify the information about the encryption algorithm and cryptographic keys used to protect the data in the column. A column encryption key is used to encrypt data in an encrypted column while a column master key is a key-protecting key that encrypts one or more column encryption keys.
The Database Engine stores encryption configuration for each column in database metadata and if you want to access the data stored in an encrypted column in plaintext, an application must use an Always Encrypted enabled client driver. Next, the driver contacts the key store, containing the column master key, in order to decrypt the encrypted column encryption key value and then, it uses the plaintext column encryption key to encrypt the parameter.
The driver substitutes the plaintext values of the parameters targeting encrypted columns with their encrypted values, and it sends the query to the server for processing. The server computes the result set, and for any encrypted columns included in the result set, the driver attaches the encryption metadata for the column, including the information about the encryption algorithm and the corresponding keys. Next, the driver decrypts the results and returns plaintext values to the application.
A client driver interacts with a key store, containing a column master key, using a column master key store provider, which is a client-side software component that encapsulates a key store containing the column master key. Always Encrypted capabilities, including built-in column master key store providers vary by a driver library and its version.
Comments
Post a Comment