SQL Server Column Level Security: 3 Ways to Protect Columns

What is SQL Server Column Level Security?

Column-level security (CLS) is a SQL Server security feature that lets you ensure that only specific users can see the content of specific columns in database tables. CLS has several important advantages:

Granular access control—instead of restricting an entire table, you can restrict access to specific data within it.
Restriction logic is part of the database, not managed by a separate application tier. Whenever users attempt to access the data, from any application, the database applies the same restriction.
Enables users to directly access database tables with appropriate access controls, rather than having to query the database through an external interface.
Can easily adapt to changes to database tables, whereas controls imposed at the application tier can break when the data structure changes.
Eliminates the need for views to filter out columns to unauthorized users.

Read on to see how to take advantage of column-level security in your SQL Server database.

In this article, you will learn:

Three Ways to Protect SQL Server Data at the Column Level

Three Ways to Protect SQL Server Data at the Column Level

1. Column Level Permissions

This option provides granular control over the security of your data. There is no need to execute a separate DENY or GRANT statement per column. Instead, you can name them all in your query. For example:

				
					GRANT SELECT ON mydata.table (column1, column2) TO exampleuser;
GO
DENY SELECT ON mydata.table (column3) TO exampleuser;
GO

2. Column Level Encryption

This option lets you directly encrypt data in a column. You can use this technique to granularly encrypt the data of specific columns or even parts of a column. Another advantage of this method is that it lets you use a different encryption key for each different column.

There are a few downsides and considerations to column level encryption:

This method requires code changes and can only be implemented when configuring the database.
Requires special binary columns to store the encrypted data.
Encryption keys are stored on the server—meaning that you must trust the server to use this functionality. SQL permissions can help you limit access to your keys only to trusted accounts.
Data cannot be indexed—you need to decrypt your data first before you can decrypt it.

Column level encryption encrypts data by using symmetric keys. This helps to maintain productivity, as well as ensure security by using an asymmetric key to protect each symmetric key. There is a wide range of algorithms you can use for symmetric keys. However, AES is generally considered best. You can also protect symmetric keys with master keys or passwords.

Here’s how you can implement column level encryption:

Create the database master key that encrypts your symmetric key. You can use the following command, providing the password for the encryption key in the argument.

				
					CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'mY-PssWrd;
GO

Create a secure symmetric key. First, to secure the key, you need to make a digitally-signed certificate. Use the following command, defining your symmetric cipher and binding it to the certificate.

				
					CREATE CERTIFICATE MyCertificate WITH SUBJECT = 'Column Encryption';
GO
CREATE SYMETRIC KEY MySymmetricKey
WITH ALGORITHM = AES_256
ENCRYPTION BY CERTIFICATE MyCertificate;
GO

Add a binary column to your table to store the encrypted data.
Encrypt the data in the sensitive column by opening the symmetric key and updating the table, as shown below.

				
					OPEN SYMETRIC KEY MySymmetricKey
DECRYPTION BY CERTIFICATE MyCertificate;
UPDATE SensitiveTable;
SET [EncryptedColumn] = EncryptByKey(Key_GUID('MySymmetricKey'), SensitiveColumn);
GO
select * from SensitiveTable

Finally, drop the original column data with the SQL DROP command. This is a critical step – otherwise your sensitive data remains exposed.

Related content:

3. Dynamic Data Masking (DDM)

Another way to protect column level data in SQL Server is to use dynamic data masking. However, note that this method does not protect any underlying data-at-rest. Rather, it masks the results of query results that include data from the masked column.

DDM has several important advantages. When users try to run INSERT INTO or SELECT INTO to copy data to another table, and the column is masked, they will see masked data in the target table. In addition, DDM also works when users perform import or export of the database. When a user tries to export the database, the resulting export file will be masked (unless the user has UNMASK privileges).

It is relatively easy to implement DDM. To apply the data mask on a specific column, you need to use ALTER TABLE on an existing table. Your default data mask obfuscates all the information in that column. You can also allow partial masking.

To add or edit a mask on an existing column:

Use ALTER TABLE to mask an existing column in your table. You can also use the same command to edit a mask on your column. The following example shows you how to add and edit a mask for the UserPreference column.

				
					--add masking to the UserPreference column
ALTER TABLE Data.Membership
ALTER COLUMN UserPreference ADD MASKED WITH (FUNCTION = 'partial(2,"xxxx",0)');

--change masking function on the UserPreference column
ALTER TABLE Data.Membership
ALTER COLUMN UserPreference varchar(200) MASKED WITH (FUNCTION = 'default()');

On its own, column level security does not provide adequate security for your data. Rather, it should be used as part of a data security program, along with other data security methods such as database permissions, data encryption, and row-level security.

Learn more in our detailed guide to SQL Server data masking

SQL Server Data Masking with Satori

Satori Universal Masking is using data classification in conjunction with dynamic masking. Users can define which transformations they wish to apply on any data type instead of being applied to specific columns. It works across SQL server and other data stores such as Azure Synapse, Snowflake, Postgres, Mysql and AWS Redshift.

For example, instead of defining a masking policy on each column in a SQL server table where an email address is stored, Satori users can define that once and for all by specifying how they want email addresses to be masked. This significantly reduces the burden of maintaining a masking solution.

Satori also provides a set of out-of-the-box profile templates that users can use to create their own masking profiles.

Learn more:

SQL Server Column Level Security: 3 Ways to Protect Your Columns