search

dotnet / efcore

EF Core is a modern object-database mapper for .NET. It supports LINQ queries, change tracking, updates, and schema migrations.
https://docs.microsoft.com/ef/
MIT License
13.77k stars 3.19k forks source link

Code First Specification for SQL Server Sensitivity Classification #20302

Open sykesjd opened 4 years ago

sykesjd commented 4 years ago

Azure SQL Advanced Data Security produces sensitivity classification recommendations for columns in an Azure SQL database with ADS turned on. Those classifications can be applied directly via Azure Portal, but whenever the database is deployed to again those classifications are wiped out.

The solution, of course, would be to add those classifications to the SQL source control for the database. If the database is auto-generated via Code First in EF Core, however - or a hybrid approach is taken where Code First generates the source controlled SQL via Schema Compare - those classifications will be overwritten via or in source control as well. Ideally, sensitivity classifications should be specified in the Code First entity builders to prevent such occurrences.

I'm open to other suggestions, but I envision a new method in the entity builder chain:

entity.Property(e => e.SensitiveField)
      .HasSensitivityClassification(SensitivityLabel.Confidential, InformationType.Financial)

Additional overloads can be specified per the T-SQL specification for sensitivity classification.

I imagine such a feature is specific to SQL server much like with UseHiLo, but I see the ForSqlServer naming scheme for such fluent API methods appears to have been deprecated, so the method name as I have suggested it above has been made similarly generic as well.

grahamauty commented 3 years ago

Is there any update to this? Has anything been implemented? All my databases are created using code-first migrations and I'm getting hassle from my security department to add sensitivity classifications. I don't want to do that on the Azure Portal, so what's the alternative in the absence of something like that outlined by @sykesjd ?

thomaslevesque commented 3 years ago

@grahamauty in the meantime you can always do it in T-SQL, no need to use the Azure portal https://docs.microsoft.com/en-us/sql/t-sql/statements/add-sensitivity-classification-transact-sql?view=sql-server-ver15

grahamauty commented 3 years ago

@thomaslevesque true. As @sykesjd pointed out though, the classifications can be wiped out by a redeployment, so to do this properly I'm going to have to put a T-SQL script in the database deployment step in the CD pipeline to ensure the classifications remain in place, and presumably that needs to classify all columns on all tables, not just the ones picked out by the recommendations.

roji commented 3 years ago

@grahamauty until this is implemented, assuming you're using migrations you should be able to use raw SQL to alter columns and set the sensitivity classification you want. When doing this, the classifications will not be wiped out by a redeployment, but will be executed as part of the migration just like any other migration operation.

ryancole commented 3 years ago

Yea this would be great as some sort of attribute or something. Or even in the schema builder code might be better.

Currently having to decide between putting this in a script or in a migration as raw sql.

stap123 commented 2 years ago

Related to #15693

bertvan commented 5 months ago

Early version of an implementation: https://github.com/roopert-digital/efcore-sensitivityclassification