Out of the box, dbFront is able to classify user access to a database as either: Admin, Update, Read-only, or No Access. These broad categories are a good start but for many environments, they are not sufficient.
This topic covers the various options dbFront provides to enable deeper User-Level security.
Identifying the User (%UserName% / %UserEmail%)
The database server does not know who is logged in so dbFront provides the two variables %UserName% and %UserEmail%, which can be used directly in Row Security, Action Buttons, Stored Procedures, Table Triggers and Visibility Expressions to allow you to manage what users can do and see.
The %UserName% is the shortest value and is always guaranteed to be set. One issue with the %UserName% is that depending upon the authentication method the %username% may be a number or object id. This is specifically true of Azure AD.
The %UserEmail% is an alternative method of identifying users if the %UserName% is not appropriate. The %UserEmail% may not be available unless specifically configured.
Action Buttons can use the value %UserName% as direct input into their actions. This includes saving the value, passing it as input to the PDF Form, including it on the URL or passing it to the Stored Procedure.
This allows for audit and conditional processing. In the clearest example, you can pass the %UserName% to a stored procedure that could lookup the current user access before processing.
NOTE: As discussed below Action Buttons can also have conditional visibility.
For Row Security, the recommendation is to create a Profile Table and lookup the user access depending upon some direct user attributes or from a related group membership table.
- Check the direct attribute [IsAdmin] on the table [UserProfile]:
(select u.IsAdmin from UserProfile u where u.UserName = %UserName%) = 'Y'
- Check if a user is a member of the 'HR' group as found in the table [UserGroups]:
(select count(1) from UserGroups where UserName = %UserName% and GroupName='HR') > 0
When used in combination with Audit Fields, table triggers can also respond to the current user and selectively perform the appropriate actions including rejecting the update or insert.
Visibility Expressions can also be used to enable or disable the visibility of field groups or buttons based on the current user.
Visibility expressions are more limited since they can't facilitate group lookups but they are very functional if you have records that are "owned" by a specific user. In that case, you can compare the record owner with the current %UserName% and hide or show an entire group of fields or buttons.