Data classification is an important process of securing data. Without it there cannot be a consistent security policy implemented. At the end of this process each target object is assigned a level the indicates its classification. This classification can cover many aspects; but for the interest of this blog entry I will focusing on data sensitivity classification.

For data sensitivity classification I would have a set list of options to choose from that indicate an escalating level of sensitivity. For example:

Public: Low level sensitivity. This data is available for all users to view.
Internal: Medium level sensitivity. This data would be privy to company personnel.
Confidential: High level sensitivity. Additional security efforts should be made to protect this data and it is only accessible by a select group of individuals.
Top Secret: Very high level sensitivity. Extra measures of security are required and very few have access to this data. Printing or e-mailing this data would be prohibited.

In the not so distant past I often wished that there was a column property available in which I could document its classification. Then I discovered extended properties. Extended properties exist for any schema object within SQL Server. They allow the developer or DBA to add custom properties that denote valuable information. It is perfect for documenting data classification information.

Extended properties can be accessed via SQL Server Data Management Studio (SSDMS) by right-clicking on the desired object (such as a column within the table) and clicking on "Properties". Inside the properties window there is a tab for "Extended properties". The name of the property and its value can be entered at that location. An alternative is to script the creation of extended properties through the use of the sys.sp_addextendedproperty procedure. This option is very helpful when there is a lot of objects to update. The syntax of adding an extended property is:

EXEC sys.sp_addextendedproperty
    @name='[Property Name]', @value='[Property Value]' ,
    @level0type='SCHEMA', @level0name='[Target schema]',
    @level1type='TABLE', @level1name='[Target table]',
    @level2type='COLUMN', @level2name='[Target column]'
GO

Once the sensitivity classification has been assigned to all of the columns in your database the process of documenting which columns should have extra security efforts made, such as encryption, becomes a much more efficient process. To query the extended properties of an object, use the system function sys.fn_addextendedproperty or the sys.extended_properties catalog view. Below is a sample query that returns all columns in the target table that are classified as "Confidential":

SELECT 
    objname
FROM
    sys.fn_listextendedproperty ('[Property Name]',
                                             'schema', '[Target schema]',  
                                             'table', '[Target table]', 
                                             'column', default)
WHERE
    value = 'Confidential'
    AND objtype = 'COLUMN'

There may be a time when the value of the extended property will need to be modified. This can also be done through SSDMS as well as through the sys.sp_updateextendedproperty procedure.

Once these extended properties are captured the user interface, stored procedures and user defined functions can programmatically utilize this information. Adding extended properties to your database objects does take a bit of additional thought and effort; but using this feature for purposes such as this will make life much easier when it moved out of the development stages and into support. It also will make the enforcement and auditing of security policies a much smoother process.