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.
-
Standard way is to use a procedure and call it from your application with the necessary parameters.Mihai– Mihai2015-06-08 17:09:03 +00:00Commented Jun 8, 2015 at 17:09
Add a comment
|
1 Answer
There are three approaches you could take:
- Do it within the app
- Do it between the app and the db, inside a db proxy
- 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:
- GreenSQL
- Informatica DDM, and
- 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.
3 Comments
Vineet
David, all these options seem to be paid and expensive. Are there any open source options ?
David Brossard
Isn't MySQL FGAC free?
