User-Level Security
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.
%UserName%
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.
%UserEmail%
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
Conditional Processing
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.
Conditional Visibility
Button visibility can be controlled by field contents using simple visibility expressions. This is very effective if you are looking for records that meet a singular condition. e.g. Owned by a specific user or group, or a specific status, or a specific age....
Button visibility can also be limited to users with specific record access (Admin, Update, Readonly)
Field Visibility
Similar to Button visibility, visibility expressions on field groups can enable or disable the visibility of entire groups of fields.
This is very effective if you are looking for records that meet a singular condition. e.g. Owned by a specific user or group, or a specific status, or a specific age....
Row Security
Row Security use SQL where clauses to allow highly granular and complex record access.
One recommendation is to create a Profile Table and lookup the user access based on 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
Table Triggers
Audit Fields are used to make the %UserName% or %UserEmail% values available to table triggers. This allows table (insert, update) triggers to respond to the current user and selectively perform the appropriate actions including rejecting the update or insert.
Table Visibility
If tables are marked as System Tables, then they will only be visible to Admin users. The only exception is when a System Table is needed to populated a dropdown field.