• About Morris Development
  • A Focus for Cloud Efficiency
    • Microsoft Azure
    • Amazon Web Services
  • What our Clients Say
  • Our Products
    • PreschoolDB
    • WebinarDB
  • Contact Us

Morris Development

Custom System Development and Integration

December 16, 2016

Security and WebAPI

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.

Article by MacGyver / Industry, Web Developer, WebApi

About MacGyver

I've worked with database systems for over 20 years, and started my own company in 2000. Almost all my business consists of internal database systems, either ERP or CRM. My programming is primarily in Angular / Microsoft C# and MS SQL.

About This Site

Morris Development has been specializing in internal database system design and integration since 1999. We provide long-term management and support of secure data systems for many businesses as well as developing the more complex code structures for ERP systems like Intellievent, Apidas, and AVMS.

This site is primarily for our developers to keep track up various technologies and updates that are used by Morris Development.

Training

Integrating Angular Microsite with .Net

Private Data Caching with Google Storage

Continuous Deployment for Production Releases?

Azure Websites – the perfect Angular host

Angular 2

  • Angular 2 Authentication
  • Angular Command Line Interface
  • Material Design for Angular
  • Using Observables in Angular 2

Mentors

  • Ben Nadel
  • Dan Wahlin
  • Deborah Kurata
  • John Papa

Staff

  • Dan Morris

Training

  • Google Development Courses
  • Microsoft Virtual Academy
  • PluralSight
  • Test Deep Links

© 2025 · Morris Development