Secure data based on the user logged into the DA server

Data Abstract
,
1

I am working on a couple of applications where the Azure database will contain records for multiple clients and I would like to be able to have the server restrict the data available to the user based on the credentials that they logged in with.

So, in general terms I would request data from the server using a dynamic where clause - such as a list of all “active” customers in the state of ohio - then have the server extend this criteria to include a clause for the company id(s) associated user. Yes, there can be more than one customer as these are service technicians for our sales branches. Some of our technicians are shared by multiple branch locations.

What would be the best way of accomplishing this?

Development Environment:
Visual Studio 2019
C#
Xamarin (UWP, Android, iOS)

3

That’s Ione of the features DA does almost out of the box. I’ll let Anton provide actual code sample tomorrow. But essentially, you can provide filters server side (say in form of a WHERE clause on a custom SQL statement), and Data Abstract will enforce those for all requests from the client, combining it with any WHERE clause sent by the client (via DA LINQ or DA SQL for example), where necessary.

Essentially, you will have something like

SELECT * FROM Orders WHERE Client = :Client AND #WHERE

as statement server side (actual syntax might differ, it’s bene a while); the :Client variable you’d provide server side, (say based from a value stored in there session when the user logged in), and DA will automatically merge the user’s filter criteria into the placeholder. so the client app can go

"SELECT OrderID, Price FROM Orders WHERE Price > $10000`

completely unaware of the filter by Client ID.

4

Hello

So, let’s assume that

  1. We need to filter requested data rows based on some criteria
  2. This criteria could be [field] = some_value as well as [field] IN (some_value1, ..., some_valueN)
  3. This criteria is not necessarily present in all Schema tables
  4. The field used for filter purposes is not necessarily exposed in the server Schema

Condition (2) restricts us from the simplest solution possible - we could just add one more parameter to each DB command right before it is sent to the database.

Also it restricts us from using the solution marc mentioned above because as far as I understand the condition can be

SELECT * FROM Orders WHERE Client = :Client AND #WHERE

as well as

SELECT * FROM Orders WHERE Client IN (:Client1, :Client2, ...) AND #WHERE

So it seems the only solution here is the CustomMacroHandler

This is a very powerful too so it should be used only if there is no other choice. If used improperly it can open a door for SQL injection attacks that can (and will) lead to data damage!

Under NO circumstances should be macro value (see below) be set from the client side

In short words CustomMacroHandler is a feature that allows to amend SQL statements sent to the DB server.

So here is what one needs to do to enable filtering:

  1. When the user logs in retrieve filter value(s) for that user
  2. Clean up the filter values. For numeric values ensure that they fit into the desired range, for string values replace all " (double quotes) occurrences with ’ (single quote) or any other value.
  3. Compose a filtering line based on these values as [field name here] IN (values here) . This SQL statement will later be injected into the data read statement. Store that statement in the session
  4. Go to your DataService and there
    4.1. Set the AllowCustomSqlMacros property to true . This property was introduced recently so it might not be present on older Data Abstract versions. In this case just skip that step
  5. Open the server Schema and go to the table you need to filter
  6. Change the statement type of this table from AutoSQL to SQL and let the Schema Modeler generate the SQL statement for you. F.e.
SELECT 	
	"Id", "OrderDate", "OrderStatus", 
	"CustomerId" 
FROM 
	"Orders" 
WHERE 
	{WHERE}
  1. Amend the generated statement by adding the filter placeholder. F.e. if the filter statement has been saved in the session under the name ClientFilter then the placeholder name will be {SESSION_ClientFilter}. F.e.
SELECT 	
	"Id", "OrderDate", "OrderStatus", 
	"CustomerId" 
FROM 
	"Orders" 
WHERE
	{SESSION_ClientFilter}
AND	{WHERE}

The {SESSION_ClientFilter} macro will be replaced with a value stored in the user session under the key "ClientFilter"

Now the client app will always receive filtered subset of data

Regards

6

Anton,

Here are the queries that I would execute in ADO.NET:

SELECT * FROM [dbo].[Company] WHERE [id] IN (SELECT [Companies] FROM [CompanyCompanies_UserUsers] WHERE [CompanyCompanies_UserUsers].[Users]=:UserId)

SELECT * FROM [dbo].[Customer] WHERE [Company] IN (SELECT [Companies] FROM [CompanyCompanies_UserUsers] WHERE [CompanyCompanies_UserUsers].[Users]=:UserId)

To explain, the tables involved are:

Users: The list of users
Company: The list of branches (companies) in our organization
CompanyCompanies_UserUsers: A “link” table that links users to the branches they work with
Customer: The list of customers (for all branches)

The first statement will get me a list of branches (companies) that the user is linked to.

The second statement will get me the list of customers that are linked to the branches that the user is linked to. The customer table has a “Company” field to associate it with the branch. In other words I’m selecting all of the customers who’s “Company” is in the list of companies the tech / salesman works at.

When the user logs in to the server then I will save their UserId to the session for later use as a query parameter.

I think that the combination of having the UserId saved in the server’s session and selecting the records as illustrated in the SQL statements would resolve condition (2). Am I correct?

In regard to condition (3) and (4) it would be perfectly acceptable IMO to put the criteria in the schema as there will never be a situation where I don’t want the user filter applied to the results returned by Data Abstract. Each table that the client application will request is either (A) the Company table itself or (B) a table that has a “Company” field to link it to the parent company / branch.

SELECT * FROM [dbo].[Customer] WHERE [Company] IN (SELECT [Companies] FROM [CompanyCompanies_UserUsers] WHERE [CompanyCompanies_UserUsers].[Users]=:UserId) AND #WHERE

Are there event handlers or methods that I could utilize to retrieve UserId from the session and fill in the parameter when a data request is made?

7

Hello

Ok, here’s a sample app. It uses the PCTrade SQLite database shipped with Data Abstract, so you should be able to build and run it without any changes.

DAFilter.zip (126.8 KB)

The client app here provides 3 buttons that allow to

  • Load some data as user 1
  • Load the same data as user 2
  • A attempt to cheat and override filters

Despite the data displayed in for 2 users is completely different, it is loaded using the same method:

	private void GetData(string username)
	{
		var dm = new DataModule();

		dm.LogOn(username, username);

		var data = from o in dm.DataAdapter.GetTable<Orders>(new[] {new DataParameter("ClientID", "")}) select o;

		this.dataGridView.AutoGenerateColumns = true;
		this.dataGridView.DataSource = data.ToList();
	}

Note the DataParameter passed from client to the server. Client cannot set its value. All it can is just to create a placeholder for this parameter.

Server side contains several points of interest.

1st is the Schema itself. SQL statement for the Orders table there looks like

SELECT 	
	"Id", "OrderDate", "OrderStatus", 
	"CustomerId" 
FROM 
	"Orders" 
WHERE 
	"CustomerID" IN (SELECT "Id" FROM Customers WHERE "Id" = :ClientID)
AND	{WHERE}

:ClientID is defined in the Schema as a parameter for this table

2nd is the LoginService. There a set of hardcoded values is set for the filter:

		public override bool LoginEx(String loginString)
		{
			...

			// Store filter value
			if (loginParameters.Username == "alpha")
			{
				this.Session["ClientID"] = "{6a71e27c-5e20-469d-8956-5b2a4aae6be1}";
			}
			else
			{
				this.Session["ClientID"] = "{b25b3cf8-f77b-48c1-8404-43e904a5b863}";
			}

			...
		}

3rd is the place where this filter is actually applied:

DataService, event handler for the BeforeGetData event:

		private void DataService_BeforeGetData(RemObjects.DataAbstract.Server.DataAbstractService sender, RemObjects.DataAbstract.Server.DataAbstractServiceGetDataEventArgs e)
		{
			var filerClientId = (string)this.Session["ClientID"];

			foreach (var tableRequest in e.TableRequestInfoArray)
			{
				if (tableRequest == null)
				{
					continue;
				}

				foreach (var parameter in tableRequest.Parameters)
				{
					if (string.Equals(parameter.Name, "ClientID", StringComparison.OrdinalIgnoreCase))
					{
						parameter.Value = filerClientId;
					}
				}
			}
		}

Code here is very straightforward:

For each received request:
If there is a parameter named ClientID (name check is not case-sensitive) then assign filter value to this parameter.

As you can see the server doesn’t care about value of this parameter provided by the client. It just sets a value it considers as a correct one.

The testcase also provides a handler for the BeforeExecutingGetDataReader event. This event allows to investigate the Db Command right before it is sent to the underlying database. Try to run the sample and then look at the debug pane to make sure that the correct filter value has been used.

Regards

1 Like
8

Anton,

I was able to apply this to our application and get filtered companies and customers.

Thank you!!

1 Like