To summarize my understanding of your question, you want: a centralized service that would handle all permissions and ideally this service would omit using the database directly without big performance degradation.
Let’s first address the type of authorization framework you may use.
The most prevalent authorization framework out there is something called RBAC or role-based access control. RBAC was formalized by NIST, the National Institute of of Standards and Technology. The challenge, in your case, with RBAC, is that it is identity-centric as in it only considers parameters of the user (role, group). You could define a role e.g. manager that would be assigned to a permission e.g.
I don’t recommend RBAC because of its limitations. However, if you must use RBAC, you could use a framework (i.e. Spring Security for Java) to abstract security and use getter methods to retrieve information about the user and display it in the UI.
To address this, NIST came up with a new model called ABAC (Attribute Based Access Control). In ABAC, you can now use more metadata / parameters. You can for instance consider:
- a user's identity, role, job title, location, department, date of birth...
- a resource's type, location, owner, value, department...
- contextual information e.g. time of day
- the action the user is attempting on the resource
All these are called attributes. Attributes are the foundation of ABAC, hence the name. You can assemble these attributes into policies. Policies are a bit like the secret sauce of ABAC. Policies can grant and deny access. For instance:
- An employee can view a record if the employee and the record are in the same region
- Deny access to reading records between 5pm and 8am.
Policies can be used to express advanced scenarios e.g.
- segregation of duty
- time-based constraints (see above)
- relationship-based access control (see above)
- delegation rules delegate Bob access to Alice's document.
There are 2 main syntaxes available to write policies:
- the Abbreviated Language for Authorization (ALFA)
- the eXtensible Access Control Markup Language (XACML)
ABAC also comes with an architecture to define how the policies will get evaluated and enforced.
The architecture contains the following components:
the Policy Enforcement Point (PEP): this is the component that secures the API / application you want to protect. The PEP intercepts the flow, analyzes it, and send an authorization request to the PDP (see below). It then receives a decision (Permit/Deny) which it enforces.
the Policy Decision Point (PDP) receives an authorization request (e.g. can Alice view record #123?) and evaluates it against the set of policies it has been configured with. It eventually reaches a decision which it sends back to the PEP. During the evaluation process, the PDP may need additional metadata e.g. a user's job title. To that effect, it can turn to policy information points (PIP)
the Policy Information Point (PIP) is the interface between the PDP and underlying data sources e.g. an LDAP, a database, a REST service which contain metadata about users, resources, or other. You can use PIPs to retrieve information the PDP may need at runtime e.g. a risk score, a record’s location, or other.
XACML can be implemented for any kind of resource, including databases.
For a database, however, you will need a proxy to intercept these requests and act as a translator. By doing this, you can implement filtering and masking as granular as you would like.
There are several open-source and commercial implementations of XACML that will allow you to handle all permissions centrally and omit using the database directly. The ease of use will depend on the implementation.
For instance, for relational databases there is Axiomatics Data Access Filter and for HADOOP there is SmartGuard.
Viewing all the permissions of a user is not part of the XACML standard but can be achieved through extending an implementation of it. Axiomatics has achieved this with Axiomatics Reverse Query (ARQ).
Full disclosure - I work for Axiomatics.
For an entire list of XACML implementations, you can check this list on Wikipedia.