How to implement MySQL query fo RBAC authorization with fine grained business restrictions in role?
When implementing an authorization system like RBAC/DAC, or XACML, or the AWS IAM authorization model, I see that I can't totally decouple business logic from authorization when there are fine-grained business restrictions. We don't use ORM. When composing the query to match the role restriction, we can't make simple queries when we don't know beforehand what the restrictions are. Example:
applicant: {
posting: {
'read:any': ['*', 'status=active', '!allowed_budget']
}
},
Today, business wants applicants to have access to status=active, but tomorrow, they may wish to allow if dept=sales or if date_posting>31/2019. I can't see how to decouple business from authorization rules.
Question 1: How do you code a
SELECT * FROM posting WHERE <place_your_restriction_here>
and you don't know beforehand what the restrictions are? I can only see that I will have to limit the different types of restrictions can be added to the policy and handcraft them one by one.
Question 2: How do you apply this model when fetching lists of records? If I select one record, I can fetch it from DB, match it against policy and allow/deny if XACML says so, but what about listing millions of records? I need a mechanism in place to convert the XACML or RBAC rule to a
SELECT * FROM posting WHERE <user_filter_desired> AND <restrictions_to_apply_from_RBAC_DAC>
All the options I have seen so far are clunky, cumbersome and error prone, specially when you have to add user custom filters with role restrictions.
