How can a startup go around making a scalable advanced security model as the security existing in MS CRM 2015 using services offered my Azure?

Question

Azure AD provides platforms for Authentication and Authorization. Azure SQL Database provides Row Level Security. Combining both the platforms using Azure API Service support implementing an advanced security model as the Hierarchical model in MS CRM 2015. Is it in the reality possible? Is there an existing implementation?

Answer 1

Not sure if this directly answers your question, but you can certainly use Azure AD Authentication together with Row-Level Security (RLS) on Azure SQL Database to enable different Azure AD identities to see different subsets of data, depending on who they are and what group memberships they have. Group memberships are particularly useful for enabling hierarchical access control.

A walkthrough for setting up Azure AD Authentication in Azure SQL Database is available here: https://azure.microsoft.com/en-us/documentation/articles/sql-database-aad-authentication/

Once you have users/groups in Azure SQL Database based on Azure AD identities, you can then create an RLS security policy that filters rows based on the identity of the user who is executing a query.

Here is a simple T-SQL example: Let's assume your Azure AD has many users, some of which are members of an 'Admin' group. In Azure SQL Database, we have a table 'Orders' with an 'AssignedUser' column indicating which user each row belongs to. Users should only be able to see rows assigned to them, unless they are members of the 'Admin' group, in which case they should see all rows.

-- Set up dummy table
CREATE TABLE ORDERS Orders (
    Date date,
    Item nvarchar(64),
    Price float,
    AssignedUser sysname
)

INSERT INTO Orders
    (Date, Item, Price, AssignedUser)
VALUES 
    ('2015-08-01', 'wheel', 101.94, 'AAD_User01@YourDomain.com'), 
    ('2015-08-08', 'seat', 50.45, 'AAD_User01@YourDomain.com'), 
    ('2015-08-15', 'pedal', 26.73, 'AAD_User02@YourDomain.com')
go

-- Enable RLS
CREATE SCHEMA Security
go

CREATE FUNCTION Security.orderAccessPredicate(@AssignedUser sysname)
    RETURNS TABLE
    WITH SCHEMABINDING
AS
    -- Regular users see rows assigned to them
    -- Members of 'Admins' can see all rows
    RETURN SELECT 1 AS accessResult
    WHERE SUSER_SNAME() = @AssignedUser OR IS_MEMBER('Admins') = 1
go

CREATE SECURITY POLICY Security.orderSecurityPolicy
    ADD FILTER PREDICATE Security.orderAccessPredicate(AssignedUser) ON dbo.Orders
go

-- Try connecting as various users
-- Members of Admins will see all rows
-- Other users will see only rows assigned to them
SELECT * FROM Orders
go