Is it common practice to store access permissions in a database table?

Question

Question

For role-based access to a RESTful API, is it common practice (and considered safe) to store access permissions in a database table? I'm talking about access permissions that would be used to restrict user access to certain endpoints, HTTP methods (GET, POST, PUT, DELETE), or limit which fields are accepted/returned in a query.

Or would access permissions more typically be stored in the application code (i.e., role-based logic compiled into the API itself)? More generically, what is the recommended way to handle custom access permissions for a RESTful API?

I'm not referring to user authentication or OAuth-based authorization on the API itself, which would be handled via standard token-based mechanisms.

Background

We're building a RESTful API in Java EE 7 that includes a role-based access control mechanism to determine which endpoints, HTTP methods, and fields are accepted/returned in a query. In our initial design, we used custom annotations read by a Container Request Filter on endpoint methods to determine a user's permission based on their role, and we used JsonViews to filter specific entity fields during (de)serialization.

This system works but we feel it's getting overly complicated and difficult to maintain. We've discussed moving this authorization information to a few database tables, most importantly one that stores table and column access permissions (PK and FK columns excerpted).

   AccessPermissions
========================
|  Column    |  Type   |
========================
| TableName  | varchar |
| ColumnName | varchar |
| HasCreate  | bit     |
| HasRead    | bit     |
| HasUpdate  | bit     |
| HasDelete  | bit     |

Each role (table not shown) would reference potentially many rows in this AccessPermissions table so that both endpoint method access and (de)serialization could be implemented by asking whether the user had an entry for the queried table and column name that allowed the requested access.

For example, a POST /endpoint1 with {"field1": "value", "field2": 23} would require the user to be assigned a role that has any rows in AccessPermissions where TableName = 'endpoint1' and HasCreate = true, and the JSON in the body of the request would deserialize only the named columns in said rows. So if the only row in AccessPermissions corresponding to this user's role was ('endpoint1', 'field1', true, false, false, false) then the POST would be allowed but part of the JSON body would not be deserialized (i.e., field2). (Also, this user would be able to POST /endpoint1 but not [GET|PUT|DELETE] /endpoint1.)

Answer 1

Generally, you want to decouple as much functionality from one another as you can. Much like you wouldn't tightly couple (or reimplement) authentication or logging inside your API / app code, you want to keep authorization decoupled.

There's actually a name for that: externalized authorization. Gartner calls it externalized authorization management (EAM). There are several ways to achieve this. First of all, your development framework probably provides you with a means to define authorization on your API. Spring Security, .NET claims are such examples.

On top of that, you have authorization models:

• RBAC (role-based access control): in RBAC, you have users, roles, and permissions (sometimes called entitlements) which you assign to resources. For instance you may have user Alice with role manager. That role manager gives her viewEndPoint1 as a permission. In a way it is very similar to what you are suggesting. Your REST framework should be able to give you tooling to achieve this e.g. via annotations. There is a nice article you can read up here.
• ABAC (attribute-based access control): in ABAC, you fully externalize authorization from the app. This is what I do day in day out so I may be a little biased. In ABAC, you have the notion of a Policy Decision Point (PDP) and a Policy Enforcement Point (PEP). The latter is an interceptor / API gateway / proxy sitting in front of your APIs. The PEP intercepts all traffic going in and out and asks the PDP whether the call is authorized. The PDP is configured with a set of attribute-based policies that state what can and cannot happen. This lets you have a fine-grained policy-based approach to authorization. You no longer need to create your custom RBAC database schema anymore.

Imagine your API is about insurance contracts and claims:

• /acme/contracts
• /acme/contracts/{contractId}
• /acme/claims
• /acme/claims/{claimId}
• /acme/customers
• /acme/customers/{custId}/contracts
• /acme/customers/{custId}/claims

And so on... Your policies could state:

• An insurance sales representative can view the contracts of those customers they related to.
• A claims processor can edit the claim of a customer in their region
• A customer can view all their contracts and claims.
• A customer can view the contracts of another customer they are the guardian for.

As you can see, the policy defines the high-level entitlements. These are derived from your business app's metadata (which you already have) such as the contract ID, the contract value, contract region, contract customer; claim ID, claim region; customer assigned sales rep...

There are a couple of frameworks that implement this type of approach. The one I work with the most is XACML (eXtensible Access Control Markup Language).

Some of the key benefits it brings to your API security is:

• fine-grained access: you can use risk, context, and relationships
• decoupled entirely from the app logic - get rid of custom code and db schemas
• scalable: you can add new scenarios when you need to. So if you have a new API in the future, you can simply add a new policy.
• auditability & accountability: you can audit policies more easily than you can code; you can prove who (tried to) access(ed) the API and whether that access was granted or denied
• filtering: you can filter the data being returned

Have a look at this report on API security (disclaimer: I work for the company who wrote that piece).