Row-Level Security: Blocking unauthorized INSERTs
Row-Level Security (RLS) for Azure SQL Database enables you to transparently filter all “get” operations (SELECT, UPDATE, DELETE) on a table according to some user-defined criteria.
UPDATE, OCTOBER 2015: In response to your feedback, RLS now also supports BLOCK PREDICATES, which enable you to prevent "set" operations (INSERT, UPDATE, DELETE) that violate your predicate. This is much simpler than the workaround described below! See New Row-Level Security functionality: Block predicates for details.
The remainder of this post is left here for reference, but you should really use block predicates instead. Enjoy!
--
Today, however, there is no built-in support for blocking “set” operations (INSERT, UPDATE) according to the same criteria, so it is possible to insert or update rows such that they will subsequently be filtered to you. In a multi-tenant middle-tier application, for instance, an RLS policy in your database can automatically filter results returned by “SELECT * FROM table,” but it cannot block the application from accidentally inserting rows for the wrong tenant. For additional protection against mistakes in application code, developers may want to implement constraints in their database so that an error is thrown if the application tries to insert rows that violate an RLS filter predicate. This post describes how to implement this blocking functionality using check and default constraints.
We’ll expand upon the example in a prior post, Building More Secure Middle-Tier Applications with Azure SQL Database using Row-Level Security. As a recap, we have a Sales table where each row has a TenantId, and upon opening a connection, our application sets the connection's CONTEXT_INFO to the TenantId of the current application user. After that, an RLS security policy automatically applies a predicate function to all queries on our Sales table to filter out results where the TenantId does not match the current value of CONTEXT_INFO.
Right now there is nothing preventing the application from errantly inserting a row with an incorrect TenantId or updating the TenantId of a visible row to a different value. For peace of mind, we’ll create a check constraint that prevents the application from accidentally inserting or updating rows to violate our filter predicate in this way:
-- Create scalar version of predicate function so it can be used in check constraints
CREATE FUNCTION rls.fn_tenantAccessPredicateScalar(@TenantId int)
RETURNS bit
AS
BEGIN
IF EXISTS(SELECT 1 FROM rls.fn_tenantAccessPredicate(@TenantId))
RETURN 1
RETURN 0
END
go
-- Add this function as a check constraint on our Sales table
ALTER TABLE Sales
WITH NOCHECK -- don't check data already in table
ADD CONSTRAINT chk_blocking_Sales -- needs a unique name
CHECK(rls.fn_tenantAccessPredicateScalar(TenantId) = 1)
go
Now if we grant our shared AppUser INSERT permissions on our Sales table and simulate inserting a row that violates the predicate function, the appropriate error will be raised:
GRANT INSERT ON Sales TO AppUser
go
EXECUTE AS USER = 'AppUser' -- simulate app user
go
EXECUTE rls.sp_setContextInfoAsTenantId 2 -- tenant 2 is logged in
go
INSERT INTO Sales (OrderId, SKU, Price, TenantId) VALUES (100, 'Movie000', 100, 1); -- fails: "The INSERT statement conflicted with CHECK constraint"
go
INSERT INTO Sales (OrderId, SKU, Price, TenantId) VALUES (101, 'Movie111', 5, 2); -- succeeds because correct TenantId
go
SELECT * FROM Sales -- now Movie001, Movie002, and Movie111
go
REVERT
go
Likewise for UPDATE, the app cannot inadvertently update the TenantId of a row to new value:
GRANT UPDATE ON Sales TO AppUser
go
EXECUTE AS USER = 'AppUser'
go
UPDATE Sales SET TenantId = 99 WHERE OrderID = 2 -- fails: "The UPDATE statement conflicted with CHECK constraint"
go
REVERT
go
Note that while our application doesn’t need to specify the current TenantId for SELECT, UPDATE, and DELETE queries (this is handled automatically via CONTEXT_INFO), right now it does need to do so for INSERTs. To make tenant-scoped INSERT operations transparent for the application just like these other operations, we can use a default constraint to automatically populate the TenantId for new rows with the current value of CONTEXT_INFO.
To do this, we’ll need to slightly modify the schema of our Sales table:
ALTER TABLE Sales
ADD CONSTRAINT df_TenantId_Sales DEFAULT CONVERT(int, CONVERT(varbinary(4), CONTEXT_INFO())) FOR TenantId
go
And now our application no longer needs to specify the TenantId when inserting rows:
EXECUTE AS USER = 'AppUser'
go
EXECUTE rls.sp_setContextInfoAsTenantId 2
go
INSERT INTO Sales (OrderId, SKU, Price) VALUES (102, 'Movie222', 5); -- don't specify TenantId
go
SELECT * FROM Sales -- Movie222 has been inserted with the current TenantId
go
REVERT
go
At this point, our application code just needs to set CONTEXT_INFO to the current TenantId after opening a connection. After that, no the application no longer needs to specify the TenantId; SELECTs, INSERTs, UPDATEs, and DELETEs will automatically apply only to the current tenant. Even if the application code does accidentally specify a bad TenantId on an INSERT or UPDATE, no rows will be inserted or updated and the database will return an error.
In sum, this post has shown how to complement existing RLS filtering functionality with check and default constraints to block unauthorized inserts and updates. Implementing these constraints provides additional safeguards to ensure that your application code doesn’t accidentally insert rows for the wrong users. We’re working to add built-in support for this blocking functionality in future iterations of RLS, so that you won’t need to maintain the check constraints yourself. We’ll be sure to post here when we have updates on that. In the meantime, if you have any questions, comments, or feedback, please let us know in the comments below.
Full demo script: https://rlssamples.codeplex.com/SourceControl/latest#RLS-Blocking-Inserts.sql