I wanted to maintain the functionality of the Groups feature, but instead of using it for report-level security I wanted to use it for data-level security. Luckily all my database records are either tied directly or via foreign key to a single parent record called "Account". I thus modified the Account table to include "group_id" as a foreign key and created a 1:1 relationship between Account and mydbr_groups by creating a Group for each Account.
All that was left was adding each User to the corresponding Group and then adding a JOIN and WHERE statement between Account and mydbr_groupsusers for each report such as:
join mydbr_groupsusers mgu on account.group_id = mgu.group_id
where mgu.user = inLogin;
This appears to achieve all of my desired data-level filtering while also keeping the Groups feature working as expected. However, I worry that I am constantly repeating this JOIN and WHERE clause in all my reports (instead of reusing the code) and that there is simply a better way to do this?
Would appreciate a confirmation that this is the correct approach, or a push in the right direction which allows for better code reuse. Even better would be native support for what I am attempting to do (I don't want to recreate an already existing wheel).