Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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);  
like image 883
Nick Shabarovski Avatar asked Oct 17 '22 11:10

Nick Shabarovski


1 Answers

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.

like image 162
Damien_The_Unbeliever Avatar answered Nov 03 '22 00:11

Damien_The_Unbeliever