using a materialised view to track user-entity authorisation
I'm wondering if this is a used pattern, or something definitely not to do. Working with SQL Server.
We have a system where the rules about what a user can access are things like "if the user has presented a course within the last year then they have access to course materials within that same subject area."
So "rules-based" auth not role-based. Many queries are slow because of all the things they need to check to show a list of available courses, etc to any particular user.
Taking the above example, I was thinking of creating materialised views whose SQL create rows that link users to the entities they can access. So e.g. a user_coursematerial view, which contains user_id and coursematerial_id - the query for which joins users and course materials based on those rules as above.
(Edit: The idea being that the view maintains those connections itself as data changes - I assume that's what happens?)
Then every time I want to list all the course materials a user has access to, I just join the user to that view, and bam I have all the correct material_ids for that user, which is fast and the rules are all in one place.
Is this achievable and acceptable, or are there better approaches to complex permissions issues like this?