TQ
dev.com

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:

• Create, read, update and delete (CRUD) operations on the data
• Custom actions of the API, implementing non-CRUD operations

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:

• CRUD operations on the data = insert/select/update/delete SQL statements
• Custom actions of the API = executing stored procedures

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:

• Database level access
• Table level access
• Column level access
• Row level access

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

• Database level access
• Schema level access
• Procedure level access

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.

• CRUD operations
  • GRANT rules for INSERT/SELECT/UPDATE/DELETE
  • CREATE POLICY for row level security
• Stored procedures:
  • GRANT rules for EXECUTE on PROCEDURE, SCHEMA or DATABASE

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.

Conclusion

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.