This example implements connection filtering for reporting applications in multi-user environments. The application sets the current user ID in SESSION_CONTEXT. Once the database connection opens, security policies filter visible rows for the current user.
Configure the Database
- This example uses a SQL file (instnwnd.sql). Execute it to recreate the database locally. Do not forget to update appsettings.json so that the connection string works in your environment.
- Execute the script below. This script extends the database as follows:
- Creates a new schema and predicate function that uses the user ID stored in SESSION_CONTEXT to filter rows.
- Creates a security policy that adds this function as a filter predicate and a block predicate on Orders.
SQLCREATE SCHEMA Security;
GO
CREATE FUNCTION Security.fn_securitypredicate(@EmployeeId int)
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN SELECT 1 AS fn_securitypredicate_result
WHERE CAST(SESSION_CONTEXT(N'EmployeeId') AS int) = @EmployeeId;
GO
CREATE SECURITY POLICY Security.OrdersFilter
ADD FILTER PREDICATE Security.fn_securitypredicate(EmployeeId)
ON dbo.Orders,
ADD BLOCK PREDICATE Security.fn_securitypredicate(EmployeeId)
ON dbo.Orders AFTER INSERT
WITH (STATE = ON);
GO
Use the following script to clean up database resources:
SQLDROP SECURITY POLICY Security.OrdersFilter;
--DROP TABLE Orders;
DROP FUNCTION Security.fn_securitypredicate;
DROP SCHEMA Security;
Configure the IDBConnectionInterceptor Object
Create an IDBConnectionInterceptor object (RLSConnectionInterceptor.cs in this example). When the database connection opens, store the current user ID in SESSION_CONTEXT. Modify queries to the Orders table - filter data by user ID (so as to implement database behavior equivalent to connection filtering). Register RLSConnectionInterceptor as an extension in IServiceCollection.
Run the Application
When you run the application, a registration form (Login.cshtml) will appear on-screen. Select a user to generate a report with filtered data.
Files to Review
Does this example address your development requirements/objectives?
(you will be redirected to DevExpress.com to submit your response)
Example Code
WebReportInterceptors/appsettings.json
JSON{
"ASPNETCORE_ENVIRONMENT": "Development",
"Logging": {
"LogLevel": {
"Default": "Information",
"Microsoft": "Warning",
"Microsoft.Hosting.Lifetime": "Information"
}
},
"ConnectionStrings": {
"NWindConnectionString": "XpoProvider=MSSqlServer;Server=USER-NBX;Database=instnwnd;Trusted_Connection=True",
"ReportsDataConnectionString": "Filename=Data/reportsData.db"
}
}
WebReportInterceptors/Services/RLSConnectionInterceptor.cs
C#using DevExpress.DataAccess.Sql;
using System.Data;
namespace WebReport.Services {
public class RLSConnectionInterceptor : IDBConnectionInterceptor {
readonly int employeeId;
public RLSConnectionInterceptor(IUserService userService) {
employeeId = userService.GetCurrentUserId();
}
public void ConnectionOpened(string sqlDataConnectionName, IDbConnection connection) {
using(var command = connection.CreateCommand()) {
command.CommandText = $"EXEC sp_set_session_context @key = N'EmployeeId', @value = {employeeId}";
command.ExecuteNonQuery();
}
}
public void ConnectionOpening(string sqlDataConnectionName, IDbConnection connection) { }
}
}
WebReportInterceptors/Views/Account/Login.cshtml
Razor@{
ViewBag.Title = "Sign in";
}
@using WebReport.Models
@model LoginScreenModel
<form asp-controller="Account" asp-action="Login" method="post">
<div class="container">
<div class="row">
<div class="col-md-4 offset-md-4">
<div class="card text-center card bg-default mb-3">
<div class="card-header">
LOGIN
</div>
<div class="card-body">
<select asp-for="EmployeeId" class="form-control" asp-items="Model.Employees"></select>
</br>
<input type="password" class="form-control input-sm chat-input" placeholder="Password" />
</div>
<div class="card-footer text-muted">
<button type="submit">Login</button>
</div>
</div>
</div>
</div>
</div>
</form>
WebReportInterceptors/Program.cs
C#using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Threading.Tasks;
using Microsoft.AspNetCore;
using Microsoft.AspNetCore.Hosting;
using Microsoft.Extensions.Configuration;
using Microsoft.Extensions.Logging;
namespace WebReport
{
public class Program
{
public static void Main(string[] args) {
CreateWebHostBuilder(args).Build().Run();
}
public static IWebHostBuilder CreateWebHostBuilder(string[] args) {
return WebHost.CreateDefaultBuilder(args)
.UseStartup<Startup>();
}
}
}
WebReportInterceptors/Controllers/AccountController.cs
C#using Microsoft.AspNetCore.Authentication.Cookies;
using Microsoft.AspNetCore.Authentication;
using Microsoft.AspNetCore.Mvc;
using System.Globalization;
using System.Security.Claims;
using System.Security;
using System.Threading.Tasks;
using WebReport.Models;
using System.Linq;
using Microsoft.AspNetCore.Mvc.Rendering;
using Microsoft.EntityFrameworkCore;
namespace WebReport.Controllers
{
public class AccountController : Controller
{
[HttpGet]
public async Task<IActionResult> Login([FromServices] NorthwindContext dbContext)
{
return View(await GetLoginScreenModelAsync(dbContext));
}
[HttpPost]
[ValidateAntiForgeryToken]
public async Task<IActionResult> Login([FromServices] NorthwindContext dbContext, int employeeId, string returnUrl)
{
var employee = await dbContext.Employees.FindAsync(employeeId);
if (employee != null)
{
await SignIn(employee);
if (Url.IsLocalUrl(returnUrl))
{
return Redirect(returnUrl);
}
return RedirectToAction(nameof(HomeController.Index), "Home");
}
throw new SecurityException($"Employee not found by the ID: {employeeId}");
}
[HttpPost]
public async Task<IActionResult> Logout()
{
await HttpContext.SignOutAsync();
return RedirectToAction(nameof(Login));
}
async Task SignIn(Employee employee)
{
string employeeName = $"{employee.FirstName} {employee.LastName}";
var claims = new[] {
new Claim(ClaimTypes.Name, employeeName),
new Claim(ClaimTypes.NameIdentifier, employeeName),
new Claim(ClaimTypes.Sid, employee.EmployeeId.ToString(CultureInfo.InvariantCulture))
};
var identity = new ClaimsIdentity(CookieAuthenticationDefaults.AuthenticationScheme);
identity.AddClaims(claims);
var principal = new ClaimsPrincipal(identity);
await HttpContext.SignInAsync(principal, new AuthenticationProperties { IsPersistent = true });
}
async Task<LoginScreenModel> GetLoginScreenModelAsync(NorthwindContext dbContext)
{
var model = new LoginScreenModel();
model.Employees = await dbContext.Employees
.Select(x => new SelectListItem
{
Value = x.EmployeeId.ToString(CultureInfo.InvariantCulture),
Text = $"{x.FirstName} {x.LastName}"
})
.ToListAsync();
return model;
}
}
}