Row Level Security
Row Level Security is managed from the Row Security tab on the Table Preferences.
Row Level Security comes after Connection Access and User Access have been applied so it can only be used to further restrict user access.
The security is applied using SQL expressions that evaluate to true or false. The expression syntax is the same as when you create a where clause. The only difference is that fields from the current table must be bracketed by {[ and ]}.
- Can Select: Determines if the user can view the record.
- Can Update: Determines if the user can update the record.
- Can Delete: Determines if the user can delete the record.
- Can Insert: Determines if the user can insert into the record.
Expression Variables
In the help at the bottom of the dialogue, you will find a list of table fields and expression values that can be used in creating the SQL expressions.
In addition to the table field names which must be bracketed by {[ and ]}, you will find the following expression values:
- {date()}: The current date without time,
- {datetime()}: The current date and time,
- %username%: The login name of the current user,
- %useremail%: The current user email if provided by the authentication service.
Examples of Valid Expressions
- %username% = 'Admin',
- {[ProductId]} > 7
- {[DiscontinuedDate]} is not null
- {[StandardCost]} between 0 and 100
- {[ProductCategoryId]} in
(select ProductCategoryId from ProductCategory where name = 'Bikes')
Row security has the same effect as Table Security in that it will automatically hide the appropriate edit buttons if the user does not have access. For more details see: Table Security
Important Notes
- Failure to properly bracket the current table's fields with {[ and ]} will result in SQL that may work in some cases but fail in others.
- If a record update would result in a row that would fail the select expression, then the select expression will temporarily be used to make the record read-only. This makes it obvious to the user that their update succeeded while preventing them from updating the record further. Once they leave the record they will be unable to return. Child records for the temporarily visible row will be hidden.