Implement Row Level security column in inherited tables

Question

I'm using Entity Framework with a code-first approach to generate the database. I have introduced row-Level security in SQL Server.

I want to create 3 tables where all the common properties go to ItemVersion and document related properties to DocumentVersion and form related things to FormVersion so and so. I'm supposed to introduce row level security column TenantId in all 3 tables.

interface ITenantSecurityPolicy
{
    int TenantId { get; set; }
}

public class ItemVersion : ITenantSecurityPolicy
{
    public int Id { get; set; }
    public int CreatedPersonId { get; set; }
    public string Title { get; set; }
    public string Description { get; set; }
    public int TenantId { get; set; }
}

[Table("DocumentVersions")]
public class DocumentVersion : ItemVersion
{
    [StringLength(100)]
    public string DocumentVersionNumber { get; set; }
    public int FileId { get; set; }
}

[Table("FormVersions")]
public class FormVersion : ItemVersion
{
    public string DesignerContentId { get; set; }
}

When I generate the database, it does not create the TenantId in child classes - the column TenantId is created only in the ItemVersion table.

How do I handle this situation? Are there any workaround or suggestions?

Normally, EF should create tables for the two subtypes only, including all columns of the base type. Have you any additional mappings? — Gert Arnold
– Gert Arnold, Commented Jan 5, 2018 at 12:16
Yes. I'm using Table splitting using Data Annotation. Note that DocumentVersion and FomVersion have [Table("FormVersions")] annotations. — Aruna
– Aruna, Commented Jan 5, 2018 at 12:37
Yes, I know, but the model as you show it shouldn't create an ItemVersion table. — Gert Arnold
– Gert Arnold, Commented Jan 5, 2018 at 12:42

David Browne - Microsoft · Accepted Answer · 2018-01-05 11:09:04Z

0

The simplest solution is also probably the right relational design for a multi-tenant schema: make the TenantId the leading column in the primary key.

public class ItemVersion : ITenantSecurityPolicy
{
    [Key,Column(Order=0)]
    public int TenantId { get; set; }
    [Key,Column(Order=1),DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int Id { get; set; }
    public int CreatedPersonId { get; set; }
    public string Title { get; set; }
    public string Description { get; set; }

}

answered Jan 5, 2018 at 11:09

David Browne - Microsoft

90.7k7 gold badges52 silver badges87 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Implement Row Level security column in inherited tables

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related