Simple OAuth With MongoDB & MySQL

Published

Don Omondi, Campus Discounts' founder and CTO, discusses securing applications with OAuth and shows you how to securely store authentication data using MySQL and MongoDB in this Compose Write Stuff article.

OAuth is an open standard for authorization, commonly used as a way for Internet users to authorize websites or applications to access their information on other websites but without giving them the passwords.

Eran Hammer-Lahav, former lead author and editor for the OAuth 2.0 project, gave an analogy that explains the concept extremely well:

Many luxury cars today come with a valet key. It is a special key you give the parking attendant and unlike your regular key, will not allow the car to drive more than a mile or two. Some valet keys will not open the trunk, while others will block access to your onboard cell phone address book. Regardless of what restrictions the valet key imposes, the idea is very clever. You give someone limited access to your car with a special key, while using another key to unlock everything else.

OAuth allows you, the User, to grant access to your private resources on one site (The Service Provider), to another site (The Consumer).

The most popular way to implement OAuth either follows the OAuth 1.0 protocol (released 2010) or utilizes the OAuth 2.0 framework (released 2012). Both of them, typically involve saving and querying tokens (e.g. request, access, refresh) and scopes (permissions). They might also involve signed requests, hashing, and a bunch of other things like multistep procedures to finally grant permissions to an application.

OAuth1 Flow:
OAuth1 Flow

OAuth2 Flow:
OAuth1 Flow

For tightly controlled environments or simple use cases, OAuth 1.0 and 2.0 can be overkill. Eran Hammer resigned from his position as the lead author and editor for the OAuth 2.0 project and withdrew his name from the specification citing, among other things, the complexity of the framework.

Interestingly, it’s not that complex to implement a simplified OAuth system that still keeps the core functionality intact but gives greater flexibility for certain use cases.

The Design Model

When designing a Simple OAuth system, the service provider is required to create and store entities for users, apps, app installations and app permissions. Apps are actually the consumer in the OAuth flow diagrams. App installation entities track which users installed what apps while app permissions track the actions a consumer app is allowed to perform on the user's behalf.

I'm a big fan of storing 'primary' data (e.g. users, products, restaurants) in a relational database and 'secondary' data (e.g. comments, likes, check-ins) in a nonrelational database. This way, primary data can have relationships between each other via joins while secondary data has no direct relationships with other secondary data, although comparisons can be drawn through the use of a separate analytics database.

What we'll do is store users and apps in respective MySQL tables, while app installations and permissions in respective MongoDB collections.

DROP TABLE IF EXISTS `users`;  
CREATE TABLE `users` (  
   `id` int(11) NOT NULL,
   `username` varchar(180) CHARACTER SET utf8 COLLATE=utf8mb4_unicode_ci NOT NULL,
   `email` varchar(180) CHARACTER SET utf8 COLLATE=utf8mb4_unicode_ci NOT NULL,
   `enabled` tinyint(1) NOT NULL,
   `password` varchar(255) CHARACTER SET utf8 COLLATE=utf8mb4_unicode_ci NOT NULL,
   `roles` longtext CHARACTER SET utf8 COLLATE=utf8mb4_unicode_ci NOT NULL COMMENT
  '(DC2Type:array)',
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `apps`;

CREATE TABLE `apps` (  
 `id` int(11) NOT NULL,
 `appsecret` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
 `apphost` varchar(255) CHARACTER SET utf8 COLLATE=utf8mb4_unicode_ci DEFAULT NULL,
 `endpoint` varchar(255) CHARACTER SET utf8 COLLATE=utf8mb4_unicode_ci DEFAULT NULL,
 `title` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
 `description` longtext CHARACTER SET utf8 COLLATE=utf8mb4_unicode_ci NOT NULL,
 `appprice` decimal(14,2) NOT NULL,
 `appbalance` decimal(14,2) NOT NULL,
 `totalinstalls` int(11) NOT NULL,
 `totalcalls` int(11) NOT NULL,
 `created` datetime NOT NULL,
 `appstatus` int(11) NOT NULL,
 `category_id` int(11) DEFAULT NULL,
 `totalpermissions` int(11) NOT NULL,
 `developer_id` int(11) DEFAULT NULL,
 `appversion` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

The endpoint column is used to implement the increasingly popular webhook feature where apps can be notified of user events via HTTP POST or websockets. The apphost column is just an added security check to lock down actions to requests from a specific host only. The appprice and appbalance columns are mockups of pay-to-use apps. The appversion column is very important, as new changes to permissions and pricing are only affected when this version is bumped to the next number.

Now, let's create the app_installations collection using the Mongo Shell:

db.createCollection("app_installations")

MongoDB is schemaless, so the best way to understand the required structure would be to look at a typical document:

{
   "_id" : 123, 
   "installs" : [{
       "_id" : 0,
       "installed_by" : 5,
       "installed_on" : ISODate("2016-08-06T22:57:52.000Z"), 
       "status" : 1,
       "appversion" : 1,
       "autoupdate" : false,
       "uninstalled_on" : ISODate("2016-09-06T22:57:52.000Z")
    }, {
       "_id" : 1,
       "installed_by" : 6,
       "installed_on" : ISODate("2016-08-06T22:57:53.000Z"), 
       "status" : 0,
       "appversion" : 3,
       "autoupdate" : true
    } 
]}

The document _id is set to match the app_id, while the installs array is made up of embedded documents describing who installed the app, when the app was installed, the installation status and the app version that was installed. The status here can represent an app uninstalled status, app deprecated status or any other logical status that a developer can come up with.

Let’s create the app_versions collection:

db.createCollection("app_versions")

Sample schema:

{
    "_id" : 123, 
    "versions" : [{
        "_id" : 0
    }, {
        "_id" : 1, 
        "appprice" : 0, 
        "permissions" : [{
            "name" : "read, post and delete comments", 
            "webhook" : true
        }]
     }, {
         "_id" : 2, 
         "appprice" : 10, 
         "permissions" : [{
             "name" : "read, post and delete comments", 
             "webhook" : true
         }, {
             "name" : "read, post and delete posts",
             "webhook" : true 
         }] 
     }]
}

The document _id here is also set to match the app_id, while the versions array is made up of embedded documents describing the key components of each version of the app; here, its price and permissions. Each permission has a name and metadata stating whether the app wants to subscribe to a webhook for that permission.

Note that the first embedded doc is empty. This is deliberate as it will help us walk through the array of embedded docs as each embedded doc _id matches its index in the array. The embedded _ids also match the appversion in the apps MySQL table, which helps to convey the concept that upon each app’s creation, the version is 0 and the permissions are empty.

When an app is updated with new permissions and/or pricing, its version is bumped and the new details saved in an embedded doc. MongoDB shines here because by fetching just 1 doc, we can view all the important changes of each version of an app. It is highly unlikely that an app’s permissions and pricing will be updated tens of thousands of times in its lifetime so the current 16MB doc size limit is in no danger of being exceeded.

These two tables and two collections are all we need. Let’s see how the OAuth flow will work then.

The Design Execution

For OAuth, the user is directed to the App Integration page, once he consents and gives permission, we fetch a MongoDB doc from app_installations collection whose _id matches the app_id. Within this doc, we embed a new doc in the installs field containing details of the installation. This embedded design again plays to the strengths of a document store.

After permissions are granted, the user is redirected to a custom URL with his ID so that the consumer can store it.

Now, an app simply needs to connect to an API endpoint specifying the App ID, App Secret and the User ID to perform actions as.

curl -H "Content-Type: application/json" -H "App-Id: 123" -H "App-Secret: Ac$R@Cas!^D" -H "User-Id: 5" -X POST -d '{"article_id":"30","comment":"This was  
a very interesting blog post, help me a lot thanks!"}' http://localhost:8000/api/comment  

On receiving this request, the following database queries would take place:

So each request would result in at least three queries: one to MySQL and two to MongoDB. Of course, you can, and perhaps always should, cache these, especially the MySQL query.

This approach has some key advantages over other methods of authorization and authentication. It allows for very easy authorization and application management with no tokens. It provides better security by frequently changing the app_secret without logging out users or asking them to reauthorize the app. Its auto-update feature allows users to immediately enjoy new app features without waiting for re-authorization.

There are also some key disadvantages to this technique. Some service providers encode the app_id, user_id and permissions in one token requiring only one query whereas the Simple OAuth approach requires three. In terms of security, a single point of exposure (the app secret) in Simple OAuth is arguably less secure than a distributed system that employs access tokens such as OAuth 1.0 and OAuth 2.0. Lastly, Simple OAuth is not as adaptable and portable to numerous use cases as OAuth 1.0 and 2.0.

Summing It Up

So there you have it, a simple yet feature-rich OAuth system. It’s easy to implement and could be great for certain scenarios, for example, at my startup, Campus Discounts, we innovate quickly and would like to allow all third party applications to do so as well with an auto-updating OAuth feature backed by GraphQL to maintain backward compatibility. As a matter of fact, we are open sourcing our Simple OAuth implementation which can be found on Github.


Do you want to shed light on a favorite feature in your preferred database? Why not write about it for Write Stuff?

Don Omondi is a full-stack developer and the Founder and CTO of [Campus Discounts](https://campus-discounts.com/). Besides the typical coffee and code, he also loves old school music over a game of chess or checkers.

attribution Ben Garratt

This article is licensed with CC-BY-NC-SA 4.0 by Compose.

Conquer the Data Layer

Spend your time developing apps, not managing databases.