exMonDM
      Back to home
      1. Help Center
      2. Administration
      3. exMonDM

      User Access Filter

      Created by: Gunnar Tómas Gunnarsson

      In exMonDM, the feature User Access controls which row (or rows) the user is able to view in an exMonDM table. This can be particularly useful when you need to protect sensitive information or restrict access between different departments.



       

      An administrator can use four parameters when configuring user access:

      • @UserId - UserId of the current user
      • @Schema - Schema of the table
      • @TableName - Database name of the table
      • @DBName - Schema and database name of the table

      Specific user access can also be determined from a different table or function by using curly braces (e.g. { }) around the select statement.

      Worked Examples

      Here are some examples of valid User Access statements in exMonDM

      Example 1

      1-1

      [__UserIdCreated] = @UserId

      The user can view rows created

      In this example, the user with UserId 1 would only see the first two rows.

      Example 2

      2-1

      [Column 1] < 2500

      This restricts user access to only view rows where [Column 1] is less than 2500.In this example, the user with UserID 1 would only see the first row.

      Example 3

      [Column 1] IN ({SELECT [Col1] FROM dbo.Table WHERE UserId=@UserId})

      The user can view rows where [UserId] in [dbo].[Table] is equal to the user's UserId and [Column 1] in exMonDM table and [Col1] in [dbo].[Table] are equal.

      Example 4

      4-1

      The user can view rows where [dbo].[Function] with the table's schema as a parameter returns a list including the same value as [Column 1] in the exMonDM table contains.

      [Column 1] IN ({SELECT [Col1] FROM dbo.Function(@Schema)})

      In this example, If [dbo].[Function] would return 'Microsoft', the user would only see the first row.

      Example 5

      5-1

      The user can view rows where [dbo].[Function] with the table's name as a parameter returns a list including the same value as [Column 1] in the exMonDM table contains

      [Column 1] IN ({SELECT [Col1] FROM dbo.Function(@TableName)})

      In this example, If [dbo].[Function] would return 'Microsoft', and the user would only see the first row.

      Example 6

      6-1

      [Column 1] IN ({SELECT [Column 1] FROM @DBName WHERE [UserId] = @UserId GROUP BY [Column 1]})

      The user can view all rows where the UserId of the user is equal to the [UserId] column of a row with [Column 1].

      In this example, a user with UserId 1 would see all 3 rows

       

      Gunnar is the author of this solution article.