I was talking with a customer today about filtering user settings in our peer code review tool, Code Collaborator.
Security is a key concern at his company so he wanted to be able to prevent his users of Code Collaborator from entering a non-company email address in their user preferences.
Hmmm.... We don't have that feature, so he and I discussed some alternate approaches. He could create a script that periodically queries the appropriate table, with a WHERE clause that would return rows with an invalid email address. The script could then update those rows and overwrite the email address. It could even be a bit fancier - perhaps send an email to an administrator to provide notification of the policy violation.
The question becomes, however, how often should that script be run? Run it too often and you run the risk (although we haven't verified this would happen) that this external process would cause database contention, slowing down the performance of Code Collaborator (we store other bits in that table besides the user's email - in particular, their most recent activity date/time).
Run it too infrequently and you have a security hole.
In talking it over later with Brandon, he suggested a database trigger. If the database trigger facility is sophisticated enough to only fire the trigger on insert or update of the specific column that contains the email address, then this filtering could be moved to the trigger.
Code Collaborator supports Oracle, MySQL, and SQL Server databases, but this customer is using SQL Server, so the code below is only guaranteed to work with it. Additional caveats:
- Only works with Code Collaborator v5.0.5011 and higher.
- The performance impact is unknown - we think there won't be any, but we have not stress tested it.
- The user who types in an invalid email address won't get an error message - the user interface will proceed as if everything is okay. Only if the user goes back to check their preferences will they notice that the email address they typed in got ignored.
- This is just a suggestion - not a supported part of our product.
On SQL Server, installing this example trigger will prevent a user from entering an email address that does not end with @smartbear.com:
CREATE TRIGGER [emailValidationTrigger]
IF UPDATE ( user_email )
UPDATE collabuser SET user_email=''
user_id in (
SELECT inserted.user_id FROM inserted
WHERE user_email NOT LIKE '%@smartbear.com' )