An important part of Azure SQL Data Security is being able to identify where classified and confidential resides in order to protect it and audit and control access. This is where Data Classification and Data Masking comes into play.
Hello and welcome to the final part of our three part series on Azure Data Security where we will demo Data Classification and Data Masking. In the previous posts we looked at network level security for protecting data at the perimeter. From there we explained that after the network level we need to protect the authentication layer. The authentication layer controls who has access and what level of access they have. The goal is to ensure we limit access to only the data and operations required.
In this post and video we look at what we can do to protect our data once we have secured the network and authentication layers.
Data Security Access Layers
The illustration below shows that the data classification and masking are important when the network and authentication layers have been secured.
This post and demo, look at how we leverage the Advanced Data Security feature of Azure SQL Server which includes Threat Detection, Data Classification and Data Masking. With this feature we can control how our data is classified and even obfuscated for sensitive information, to ensure only allowed identities have the correct access. Use these features to better understand who is accessing sensitive data, when they are accessing it and from where.
the video tutorial demonstrates how data columns in a SQL Server database can be classified to allow reporting and visibility of sensitive information. We will also show how this information can be managed, reported on and monitored.
Securing Data in Azure SQL – Data Classification Dashboard
Code example here for granting an Azure AD identity read access to an Azure SQL Database
CREATE USER [dbreader@lanet.co.uk] FROM EXTERNAL PROVIDER WITH DEFAULT_SCHEMA=[dbo] GO GRANT SELECT ON dbo.Customers TO "dbreader@yourdomain.co.uk" >
Data can also be classified using the “add” or “drop” T-SQL statement as shown below.
DROP sensitivity classification FROM dbo.customers.email_address
DROP sensitivity classification FROM dbo.customers.first_name
DROP sensitivity classification FROM dbo.customers.last_name
DROP sensitivity classification FROM dbo.customers.NationalInsuranceNo
DROP sensitivity classification FROM dbo.customers.SocialSecurityNo
DROP sensitivity classification FROM dbo.customers.AccountManager
Log Analytics Query
AzureDiagnostics
| WHERE Category == "SQLSecurityAuditEvents"
| WHERE data_sensitivity_information_s contains "Confidential" AND server_principal_name_s != "appuser@yourdomain.co.uk"
| project TimeGenerated, server_instance_name_s, database_name_s , statement_s, affected_rows_d , server_principal_name_s, client_ip_s ,data_sensitivity_information_s
| ORDER BY TimeGenerated DESC
We hope you found the information in this post and video useful !
YouTube Channel Tutorials
Visit our YouTube channel (and subscribe) to see our other videos in this series and more at https://LANETYouTube
Also, visit us at www.lanet.co.uk and check out our Azure Market place offering/s here http://bit.ly/LaNetSecurity
LANET is a Microsoft Gold Cloud Platform and Silver Security Partner specialising in Microsoft Azure cloud infrastructure security.
Let us know if you use this feature or plan to, and if you found this article useful.
Recent Comments