I've created an API using Flask-RESTFUL package for Python 3.7.
I'd like to know what the proper approach would be for returning data to a user based on which columns he should have access to.
For example, if I have an "orders" table with (order_id, order_date, price, ebay_name, revenue), but want User A and User B to have access to different data. Let's say that on route /get_data, I return all fields, but User A should have access to all data, while User B only can see the revenue field.
My current approach:
While building the JWT token when a user logins in and authenticates, would it be acceptable to store the column names of the "orders" table in the actual token? Then, when the user goes to the /get_data route, I would basically check the column names stored in the JWT and build the MySQL query with the column names found in the token (select all_columns_in_jwt from orders). I worry that exposing the table columns in the JWT token is not the best approach.
Another idea would be to check within a user permissions table each time the /get_data route is hit.
Does anyone have a suggestion for implementing this in a more efficient way?
I would do it using user permissions but you could also create separate procedures for either each individual user or groups of users based on their roles/permissions which create/replace specific views of the tables allowing the user to see only what you want them to see. For example, upon any changes to the table (or alternatively, upon login for a user/member of a user group) you could run the procedure to generate (or replace if it already exists) a view on the table for either each user or each user group which you want to restrict access to, and then your API would select data from the view rather than directly from the table
With user permissions (this is very similar to how I implement it in my own apps) you could create a permissions table and then a table of the permissions which users possess. Then, on using an API, you could query the user's permissions and using a map which you store somewhere in your code base you could look up the columns based on a combination of the relevant user permission and the relevant table and use the resulting column set as what you select in your query
proper way to grant permissions to specific data JWT MySQL Flask-Restful Python