Blog about software development


API authorization strategy: use the DB

29 Jul 2019 - by 'Maurits van der Schee'

When building an API you may find the need to implement authorization in a generic way. Using the authorization implementation of your (relational) database is a well-documented, simple and proven strategy. The user that is used for the database connection should in this scenario depend on the authenticated user of the API (and it's authorization). This post will explain how to apply this strategy. But before we start let's take a step back and think about what it is that you may be authorizing.

What are you authorizing?

I think that you are either authorizing:

Most APIs have a lot of CRUD operations and not so many custom actions. This may be the reason that my PHP-CRUD-API project is quite popular.

How does that translate to SQL?

These two types of authorization have corresponding SQL statements in the database:

You probably don't like stored procedures (they are not very popular nowadays), but do consider that you may not need so many stored procedures and that they have certain advantages (such as data locality in the policy code).

What levels of authorization are available?

The levels of authorization that you can grant when authorizing CRUD operations are:

The level of authorization that you can grant when authorizing execution of stored procedures are:

Note that a database holds a schema that holds stored procedures, so you can authorize execution of a single, a group or all stored procedures.

How to store the authorization rules?

You may store the authorization rules in SQL and store that SQL in Git.

Note that the stored procedures are executed in the security context of the definer, but that with a little workaround you can retrieve the invoker to implement some custom logic.


If you don't want to implement all authorization as business logic in your API code then you may start using the authorization implementation in your database system as explained in this blog post. This may work out great, especially when the majority of your API exists of CRUD operations. Not convinced? Well, maybe it is not for you. If you are looking for a more versatile approach then the quite abstract and verbose eXtensible Access Control Markup Language (XACML) is worth checking out.

Enjoy programming!

PS: Liked this article? Please share it on Facebook, Twitter or LinkedIn.