CODEWITHSUNDEEP
sqlsql-serverdatabasemulti-tenant
Nick Shabarovski
Nick Shabarovski

Reputation: 105

What is a difference between BEFORE and AFTER dml operations ? (Row-Level Security)

I've created the Secure Policy for my Ms Sql Server database. And I'm trying to figure out a difference between BEFORE and AFTER dml operations. Also it would be great to know what is a reason to use it. I couldn't find any clear explenations of my topic.

CREATE SECURITY POLICY [Security].DealershipsCarsFilter 
    ADD FILTER PREDICATE [Security].fn_securitypredicate(DealershipId)   
        ON dbo.[DealershipsCars],  
    ADD BLOCK PREDICATE [Security].fn_securitypredicate(DealershipId)   
        ON dbo.[DealershipsCars] AFTER INSERT
    WITH (STATE = ON);

Upvotes: 5

Views: 196

Answers (1)

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239646

For an UPDATE, you might specify different policies for BEFORE and AFTER, to allow you to evaluate the old values in the row before the update is applied and the new values in the row after the update is applied. Or you may wish the same policy to be applied in both situations. The choice is yours.

For INSERT or DELETE, you can only choose AFTER or BEFORE, respectively, because the rows don't exist in the other state.

Which is a long way of saying, before and after here don't have some deep technical meaning, adopting instead their plain English meanings.

Upvotes: 3

Related Questions