Security Needs
Security has always been quite controversial, but it’s mainly due to the fact that the needs vary greatly depending on your needs. In our business, we specialize in internal systems that are 100% dependent on remote web services. We focus 99% on ensuring the data itself is locked down.
Dynamic Roles and Levels of Security
Obviously there are many ways to go about this, but we often must limit rather than simply deny data requests. We may have a sales person who can see the sales from 3 different offices, while another sales person can see data from 2 of those offices and 4 others, while a manager might see all 7 plus an additional one. Rather than trying to do this at the API server, we do this in the database using a virtual table produced from a function.
here is how we might get the data from the example above:
Select s.* from Sales s inner join dbo.SecurityByOffice(@AccountId) sec on sec.OfficeId= s.OfficeId
the function dbo.SecurityByOffice(@AccountId) creates a table, on the fly, that we can then us in a join to limit the results to just the orders in the office to which he is associated. We can use that same function to pull all the clients he can look at, all the paperwork his offices have access to, all the contacts in the contacts table that his offices have access too, etc…
So, with security functions like that for SecurityByCompany you can also include multiple companies in the same database, allowing you to have an API server that can handle thousands of companies with multiple offices. And you can have SecurityByAccount, which might pull up specific security options as well as their own account. In this case, if the user wanted to see all of his own sales,the API would request this:
Select s.* from Sales s inner join dbo.SecurityByAccount(@AccountId) sec on sec.AccountId= s.AccountId
Bringing it together with WebAPI
The problem with the above code is that it is dependent upon AccountId. We don’t want the user’s AccountId to be the security! One way is to associate the account ID with an API Key, but with a client-side system, you’ve then got this API key floating about. And then we’d still need to be passing up the user’s account ID with every request. Instead, we made our own SessionId.
When you log into our systems, the database creates a new alphanumeric ID, similar to a NONCE, but rather than being regenerated for each post, it’s maintained in the database and kept in a table holding only the AccountId, the new sessionid and an expiration date. The reply to a successful login will contain this SessionId code. We keep that in the browser’s sessionStorage, or a session cookie. Then, every single request sent to the API server will have that sid included in the URL. So, getting your Sales Orders might look like this:
https://myserver.com/api/sales?sid=ued8f923j2jkd0f8sjwk2ds9
Here’s where it gets fun….
Rather than SecurityByAccount using the actual AccountId, we have it use the SessionId, so the sql from the API server to the database is this:
Select s.* from Sales s inner join dbo.SecurityByAccount(@SessionId) sec on sec.AccountId= s.AccountId
It works exactly the same, but it simply has to first look up the AccountId in the sessions table. If the person has sales, then it returns his sales. If they have no sales, it returns an empty set. If they’re not authorized or their session is expired, it returns …. and empty set. (In our case, we always would use a stored proc like, spUserSales(@sessionid) and then return a status to notify them if they are unauthorized or expired, etc.. you get the idea)
Other Advantages
One standard script we have on the API server is “CheckUser(SessionId)”, which returns the user’s account. To make a SUPER simple API really fast, we can just add that to the top of the code to make sure they are logged in. Why not use a session variable on the server: Because we might have several servers. We might be using Amazon Lamda. We might be using an Azure WebAPI host. However, we can guarantee that all the user accounts and their sessions will be in one database.
Using tools like PostMan and other api tools is easy and fast. Mocking data is simple for testing. Using CURL and other server to server tools is a snap.
Conclusion
Security, whether you do it via oAuth, or some other standard security system, will always have flaws and weaknesses. If you are securing DATA and you are using WebAPI, this has been an extremely economical solution to a complex problem.
