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.

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.