web api secure individual access to data for roles

Question

I'm unsure if this is a programming question or a database question

I'm making a web api, I use bearer token authentication and its working really well, I can login get resources and logout, I can added users and users can add users which ill explain next.

I have 3 main roles, CompanyAdmin (users who created the account in the first place), CompanyUser (the user company admins make) and StandardUser (users the company users make)

Everyone has a row in the users table for logging in, now when a user logs in I don't want them loading up fiddler and seeing another users data by manipulating the url, I want to make sure companys cant see other companies data, users cant see other users data from the same or another company.

I'm using WebAPI MVC, SQL azure database and the website is hosted by my hosting provider. all accounts that can log into the site and access the api have a row in the users table from which only a few tables hang off it mostly for claims, roles, profile, company data etc.

Any help, pointers in the right direction would be helpful thanks

Answer 1

Getting the access to the data by simply passing the request payload means, that there is no security in the broker between the client and database. There are some ways to secure the access that are exist and actively used, one of them, for example, is the simple, basic authentication where you are setting the context by passing credentials, (dis)advantages are described on the same page above. Or using token-based authentication (more detailed walkthrough). So, please avoid the possibility to do a just pass-through the request through the Web API.

Also you may check that old but good book on creating the multitenant solution and partition your database for customers/tenants on Azure. Some titles from that are not available already (Ex.: Federations), but examples of how to partition your SQL Database and some code samples should work.