User Access controls which row the user is able to view in an exMonDM table.



An administrator can use four parameters when configuring user access:

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


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

Examples of valid User Access statements in exMonDM:

  • [__UserIdCreated] = @UserId
    The user can view rows he himself created
    [Column 1]
    [__UserIdCreated]
    Microsoft1
    Apple1
    Apple2
    A user with UserId 1 would only see the first two rows

  • [Column 1] < 2500
    The user can view rows where [Column 1] is less than 2500
    [Column 1]
    [UserId]
    10001
    30001
    50002
    The user would only see the first row

  • [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

  • [Column 1] IN ({SELECT [Col1] FROM dbo.Function(@Schema)})
    The user can view rows where [dbo].[Function] with the table's schema as parameter returns a list including the same value as [Column 1] in the exMonDM table contains
    [Column 1]
    [UserId]
    Microsoft1
    Apple1
    Apple2


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

  • [Column 1] IN ({SELECT [Col1] FROM dbo.Function(@TableName)})
    The user can view rows where [dbo].[Function] with the table's tablename as a parameter returns a list including the same value as [Column 1] in the exMonDM table contains
    [Column 1]
    [UserId]
    Microsoft1
    Apple1
    Apple2


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

  • [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]
    [Column 1]
    [UserId]
    Microsoft1
    Apple1
    Apple2
    A user with UserId 1 would see all 3 rows