MYSQL Access Control

Question

I want to implement column level and row level access control on data stored in my MySQL database. I am using NodeJS on my server, what's the way to go for this ? I see SAP Hana allows that but want to keep MySQL.

Answer 1

There are three approaches you could take:

1. Do it within the app
2. Do it between the app and the db, inside a db proxy
3. Do it inside the database

The first option wouldn't really qualify as row-level access control since the application logic is the one responsible for the filtering / masking. (Filtering is row-level access control whereas masking is cell-level).

The second option, using a proxy, is an approach that is increasingly being taken. There are dedicated solutions such as:

1. GreenSQL
2. Informatica DDM, and
3. Axiomatics Data Access Filter.

These solutions typically intercept the SQL traffic and modify it such that only authorized data is returned. This is called dynamic data masking. It is explained a little bit more on Wikipedia.

The third option is to use the database's native capabilities. For instance Oracle has something called Virtual Private Database (VPD) which lets you configure advanced row filtering capabilities.

In your case (MySQL), there is something called fine-grained access control (FGAC). There is a great article on the topic here. Google that term for more resources.