Client - Webserver. How to add a server side 'UserId' column

[Tuytje]

I'm trying to have a client - server setup where the server is authorising clients and assigning userIds. This means that I need to add a userId to all rows coming into the server and filter the rows going out on that userId. The client itself has no knowledge of this userId.

Syncing the columns is easily done by including all columns except the userId, but how do I add the userId to the rows incoming for the server (from the client)?

As for the part where the server only sends the filtered data to the client, this probably can be done with filters, but the parameter value needs to come from the client, which has no knowledge of the userId (since that is a server thing)

Any pointers?

Thank you for the amazing library!

[vunder]

I had the similar task.
Each task got additional columns UserId.

Server size

On server side when configuring columns those colums were excluded from sync.

            var types = typeof(Entity.SyncModelBase).Assembly.GetTypes()
                .Where(x => x.IsSubclassOf(typeof(Entity.SyncModelBase)) && x.GetTypeInfo().GetCustomAttribute<Entity.TableAttribute>() != null)
                .ToArray();
            var typesInfo = types.ToDictionary(
                x => x.GetTypeInfo().GetCustomAttribute<Entity.TableAttribute>()?.Name,
                x => x);
            var syncSetup = new SyncSetup(typesInfo.Keys);
            foreach (var info in typesInfo)
            {
                var columns = info.Value.GetProperties()
                    .Where(x => x.GetCustomAttribute<Entity.IgnoreAttribute>() == null)
                    .Select(x => x.GetCustomAttribute<Entity.ColumnAttribute>()?.Name)
                    .Where(x => x != null);
                syncSetup.Tables[info.Key].Columns.AddRange(columns);
                //Creating filter by UserId
                var filter = new SetupFilter(info.Key);
                filter.AddParameter("userId", System.Data.DbType.String);
                filter.AddWhere("UserId", info.Key, "userId");
                syncSetup.Filters.Add(filter);
            }

Also here you can see that I'm filtering all data according to the userId parameter is sync request.

Client side

In general, table configuration is the same, except the thing that you need to fill correct userId value that would represent each unique user

                var types = typeof(Model.ISyncModel).Assembly.GetTypes()
                    .Where(x => x.IsSubclassOf(typeof(Model.SyncModelBase)) && x.GetTypeInfo().GetCustomAttribute<SQLite.Attributes.TableAttribute>() != null)
                    .ToArray();
                var typesInfo = types.ToDictionary(
                    x => x.GetTypeInfo().GetCustomAttribute<SQLite.Attributes.TableAttribute>()?.Name,
                    x => x);
                var syncSetup = new SyncSetup(typesInfo.Keys);
                foreach (var info in typesInfo)
                {
                    var columns = info.Value.GetProperties()
                        .Where(x => x.GetCustomAttribute<SQLite.Attributes.IgnoreAttribute>() == null)
                        .Select(x => x.GetCustomAttribute<SQLite.Attributes.ColumnAttribute>()?.Name)
                        .Where(x => x != null)
                        .ToArray();
                    syncSetup.Tables[info.Key].Columns.AddRange(columns);
                }
                // Creating an agent that will handle all the process
                var agent = new SyncAgent(clientProvider, serverOrchestrator, new SyncOptions() { UseVerboseErrors = true }, syncSetup);
                agent.Parameters.Add("userId", userId);

Important thise is an additional data converter when you're creating server orchestrator. My idea of the trick: fill UserId value before send data to server to have there all values related to each specific user. But on the client side this column would be always NULL to have data to connected to any specific ID

    var serverOrchestrator = new WebClientOrchestrator("https://localhost:6001/api/sync", null, new AuthUserIdConverter(userId));

...

    public sealed class AuthUserIdConverter : Dotmim.Sync.Serialization.IConverter
    {
        private string userId;

        public AuthUserIdConverter(string userId)
        {
            this.userId = userId;
        }

        public string Key { get; } = "UserId";

        public void AfterDeserialized(SyncRow row)
        {
            row["UserId"] = null;
        }

        public void BeforeSerialize(SyncRow row)
        {
            row["UserId"] = userId;
        }
    }

[Tuytje]

Thank you for taking the time for the detailed response, I really appreciate it.

A couple of things are not fully clear to me (probably my lack of understanding :)):

• The userId variable in the client line agent.Parameters.Add('userId", userId). what is it set to? As I understand this should be 'null', always. Correct?

• The last part is also not fully clear to me. The client would have no knowledge of his userId, this is information that is only known by the server. How would the client set this value via the value converter since it is setting 'userId' as a variable. Or is this pure to have a conversion happening so that the server will use the same value converter (since the client dictates the conersion used)?

• Does this also mean that the client need a UserId column in the database, although empty (DBNull)?

Thanks!

[Tuytje]

@vunder, @Mimetis
Thank you for your previous posts, it helped me a great deal. Some things don't click yet, I hope you don't mind expanding on those:

• The server defines a parameter & where clause for every table where we need to filter on the UserId (thank you for the link you provided) like so:

var filter = new SetupFilter(table);
filter.AddParameter("userId", System.Data.DbType.String, true);
filter.AddWhere("UserId", table, "userId", "SCHEMA");`

• We can now add code in the controller post to fill that parameter (that comes in empty from the client) like so:

orchestrator.OnDatabaseChangesSelecting(x =>
 {
    var parameters = x.Context.Parameters;
    if (parameters != null)
    {
        var p = parameters["userId"];
        if (p != null)
            p.Value = "[UserId_Known_By_Server]";
    }
});

This should make sure the client only gets the changes meant for him, correct?

• Making sure the client doesn't get the userId is achieved by the custom ValueConverter (AuthUserIdConverter). The 'BeforeSerialize' is for making sure the UserId column is set to null before going to the client. I think the 'AfterDeserialized' is for the server setting the correct userId (since it is known only by the server). This userId is passed in the constructor when adding it to the webserver options, but this is a single instance for the whole webserver. How can the UserId be known at this point? So I'm not sure how to proceed with this ValueConverter on the server side.
  On the client side this same ValueConverter is known, but just to make sure it get's triggered by the server. On the client side it doesn't really do anything, since the server already set 'null' for the UserId value, correct?

[vunder]

@Tuytje

1. If you had added filter to setup then you do not need other code (if you'll use my sample). Because id-value for filtering is passing from request parameters that is filled on the client side.
2. Converter on server side does nothing. It must present, otherwise you'll get sync exception (this is correct behavior if you have some data converter on the one side, then you must have the same on the other side to match data).
3. My mistake that I did not specify that AuthUserIdConverter is differs on client and on server. On client we need it keep database values without UserId value, but fill its value once we're launching sync. On server-side this converter would do nothing.
   Client-side converter

    public sealed class AuthUserIdConverter : Dotmim.Sync.Serialization.IConverter
    {
        private string userId;

        public AuthUserIdConverter(string userId)
        {
            this.userId = userId;
        }

        public string Key { get; } = "UserId";

        public void AfterDeserialized(SyncRow row)
        {
            row["UserId"] = null;
        }

        public void BeforeSerialize(SyncRow row)
        {
            row["UserId"] = userId;
        }
    }

Server-side converter

    public sealed class AuthUserIdConverter : IConverter
    {
        public string Key { get; } = "UserId";

        public void AfterDeserialized(SyncRow row)
        {
        }

        public void BeforeSerialize(SyncRow row)
        {
        }
    }

Client-side converter initialize once we've got auth-key that should have unique UserId value.

[Tuytje]

@vunder

Thank you for expanding on my issue. I was indeed under the presumption that the server was responsible for setting the UserId in this case. Which didn't match with your solution.

Do you check on the server that the supplied UserId is correct, that it is indeed the logged in user?
I had another issue with the schema which was blocking my progress. The filter itself didn't 'take' because somehow it was ignoring my schema in it. I'll open a new topic for this since that is a separate thing

[vunder]

@Tuytje Currently - I do not check.
I'm using MS OAuth for authentication/authorization. UserId value is formed based on user's MS profile, it is a part of JWT. This information is passed with the request in Authorization header. With the standard auth flow I'm checking requests

            services.AddAuthentication(options =>
            {
                options.DefaultScheme = JwtBearerDefaults.AuthenticationScheme;
                options.DefaultAuthenticateScheme = JwtBearerDefaults.AuthenticationScheme;
                options.DefaultChallengeScheme = JwtBearerDefaults.AuthenticationScheme;

            })
                .AddJwtBearer(options =>
                {
                    options.Audience = Configuration["Authentication:ClientId"];
                    options.Authority = Configuration["Authentication:Authority"];
                    options.TokenValidationParameters = new TokenValidationParameters
                    {
                        ValidateAudience = false,
                        ValidateIssuer = false,
                        ValidateIssuerSigningKey = false
                    };
                });

[Mimetis]

Hey @Tuytje , @vunder

Since it's not an issue, I'm converting this Q&A to a discussion 👍

Regarding your particular case, I guess using an IConverter should be a good solution, even if I guess we can find more options

[Tuytje]

It seems that I was not able to find more options :)

[vunder]

@Tuytje

• agent.Parameters.Add("userId", userId) adds request parameter. If you'll look on server-side configuration, you'll find filtering setup with this parameter. Filtering+parameter value affects data filtering while sync
• client is "knows" its ID-value only, but do not store it in local database, because value converter AuthUserIdConverter while sync removes it
• UserId column must be in database. Otherwise, sync is not possible (some limitations of the current approach). But in client's local database this column would и always null. While on server it would have values of client ids

[needledrag]

Hi,

I hope you all don't mind me jumping in on this discussion as my questions were very similar to the original.

I was wondering if it's possible to setup a filter on a table where one of the parameter values (sent from the client) isn't present in the Web API request? I'm using Jwt tokens for authentication so I don't currently send any user info (user Id) in the Web API parameters, instead on the server I use the token authentication to identify my user and currently use their internal Id (in the SQL server db) to query user specific records.

Is it possible to add a parameter value into the request (that maps to a parameter setup on the filter on the server) after it comes in on the server rather than from the incoming parameters sent by the client?

Or can it be done in the AfterDeserialized event on a converter on the server for that parameter field as described earlier in this post?

So for example my parameter MyParam is sent from client with nothing in and on the server the converter adds it in the AfterDeserialized event, is that the way to do it?

I hope that makes some sense, reading my question it sounds a little convoluted..

Thank you for your time.

Regards

[Tuytje]

I was looking into this as well, but could find a way to do it when it wasn't coming from the client. I have no issue with the id coming from the client since, in my case, it doesn't matter.
I will look into confirming the logged in userid with the given user id, to make sure the client didn't tamper with it. Might be a way forward?

[needledrag]

I think you can use the AfterDeserialized event on the server, you have access to the incoming rows and in that event you can change the id feomy the client. So you could send blanks on the client and change it to the authorized user id (or whatever is you need) in that event, and then on the way out the server you can add or remove any user id values too. I think that's how it could work?--Sent from my Android phone with GMX Mail. Please excuse my brevity.On 21/01/2021, 12:40 Tuytje <notifications@github.com> wrote: I was looking into this as well, but could find a way to do it when it wasn't coming from the client. I have no issue with the id coming from the client since, in my case, it doesn't matter. I will look into confirming the logged in userid with the given user id, to make sure the client didn't tamper with it. Might be a way forward? —You are receiving this because you commented.Reply to this email directly, view it on GitHub, or unsubscribe.

[Mimetis]

Hey guys, please have a look here : #391 (comment)

[Tuytje]

I'm indeed talking about setting the UserId (The columns comes from the client as an empty column) on the rows that come into the server (so changes made on the client). I have tried with the converter, but I don't have access to the HttpContext on that point. Only the row is known:

public void AfterDeserialized(SyncRow row)
{
}

Because of this I don't think I can set the row on this point.

The new OnHttpGettingChanges would be usable since you get all changes in a single collection and you can loop over the changes like this:

orchestrator.OnHttpGettingChanges(args =>
{
       foreach (var table in args.Request.Changes.Tables)
       {
             foreach (var row in table.Rows )
             {
                    row[???] = userId;
             }
       }
});

Problem is the row variable is an array of objects, with all values for that row, but it is accessable by index, not by column name, so I have no idea what column index I need to update is. I take it that this information can be found somewhere, but I have not found it yet.

[Mimetis]

Can you use this interceptor instead ? OnTableChangesBatchApplying() ?

// Intercept a table changes applying with a particular state [Upsert] or [Deleted]
// The rows included in the args.Changes table will be applied right after.
agent.LocalOrchestrator.OnTableChangesBatchApplying(args =>
{
    Console.WriteLine($"-------- Applying changes {args.State} to table {args.Changes.GetFullName()} ...");

    if (args.Changes == null || args.Changes.Rows.Count == 0)
        return;

    foreach (var row in args.Changes.Rows)
        Console.WriteLine(row);
});

args.Changes is a SyncTable where you can set any column by name like args.Changes["UserId"].

You even has the DbCommand that will be executed through args.Command, if needed.

https://dotmimsync.readthedocs.io/Interceptors.html?highlight=ontablechangesbatchapplying#intecepting-applying-changes

Make sense ?

[Tuytje]

Makes total sense. Thank you.

Follow up question: I also want to remove the UserId from the rows when the server selects the changes to be sent to the client (so that the user is totally oblivious to its userid). Would 'OnTableChangesSelected' be a good method to hook into for this?

[Mimetis]

indeed ;)

[Tuytje]

wonderful :)

Thanks for the speedy replies!