Monthly Archives: March 2015

AngularJS — Quick Role-Based Authentication

We’ve recently started on a new product at work using the AngularJS framework. One of the most useful resources when starting out was the #angularjs room on freenode. There seems to be a really large, vibrant community around AngularJS and that’s always helpful when starting from scratch on a new framework. One of the best resources in the room was David Mosher, who helped us get set up with Lineman — an awesome build framework based on Grunt — and pointed us to some of his video tutorials. Honestly, these were a great resource for getting started and I highly recommend watching them. The following tutorial is heavily inspired by the examples David provided.
Basic Routing

Before starting with authentication you’ll need to have some routes. Let’s start with a logged out state, and a logged in state:

angular.module("yourApp").config(function ($routeProvider) {

  $routeProvider.when('/login', {
    templateUrl: 'login.html',
    controller: 'LoginController'
    resolve: {
      //...
    }
  });

  $routeProvider.when('/app', {
    templateUrl: 'app.html',
    controller: 'AppController'
    resolve: {
      //...
    }
  });

});

In this simple example we have a state for logging in and another state for seeing the app once logged in. In order to limit access to authenticated users we are going to have to check their status before allowing them to view the appstate.

Adding Authentication

This is the real meat of the solution. They key is adding this new function that watches the $routeChangeStart event and reacts on it. Once this event fires, we check if the route is clean (using some fancy underscore.js). It’s easier to enumerate all the routes that don’t require rather than all the ones that do. Presumably, there are significantly more that do.

angular.module('yourApp').run(function ($rootScope, $location, AuthenticationService) {

  // enumerate routes that don't need authentication
  var routesThatDontRequireAuth = ['/login'];

  // check if current location matches route  
  var routeClean = function (route) {
    return _.find(routesThatDontRequireAuth,
      function (noAuthRoute) {
        return _.str.startsWith(route, noAuthRoute);
      });
  };

  $rootScope.$on('$routeChangeStart', function (event, next, current) {
    // if route requires auth and user is not logged in
    if (!routeClean($location.url()) && !AuthenticationService.isLoggedIn()) {
      // redirect back to login
      $location.path('/login');
    }
  });
});

As you can see it’s fairly intuitive. If the route needs to be authenticated and the user is not logged in, then redirect them back to the login page. This doesn’t necessarily have to be a redirect, this can be an API request to see if their session cookie is still active. You can build on this and make it as sophisticated as you need — but the skeleton will probably remain the same.

Handling Failed Authentication from the API

There is another possibility, of course, in which the user is not authenticated and initiates an action that results in API request. The result will be a 401 response and possibly some sort of authentication exception. Handling this 401should be done as follows (all credit goes to David Mosher on this one):

angular.module('yourApp').config(function ($httpProvider) {

  var logsOutUserOn401 = ['$q', '$location', function ($q, $location) {
    var success = function (response) {
      return response;
    };

    var error = function (response) {
      if (response.status === 401) {
        //redirect them back to login page
        $location.path('/login');

        return $q.reject(response);
      } 
      else {
        return $q.reject(response);
      }
    };

    return function (promise) {
      return promise.then(success, error);
    };
  }];

  $httpProvider.responseInterceptors.push(logsOutUserOn401);
});

As you can see, we add an interceptor to capture the promises from the $httpProvider and validate them to make sure they are not 401 errors. If they happen to be 401 then we send the user back to the login page.

Mixing in UI-Router

One of the more useful plugins for angular is ui-router. This plugin allows you to create more powerful constructs with your states by using inheritance and substate transitions. Using ui-router, our route definition would be as follows:

angular.module('yourApp').config(function ($stateProvider, $urlRouterProvider) {

  $urlRouterProvider.otherwise('/');

  $stateProvider
    .state('login', {
      url: '/login',
      templateUrl: 'login.html',
      controller: 'LoginController'
      resolve: {
        //...
      }
    })

    .state('app', {
      url: '/app', 
      templateUrl: 'app.html',
      controller: 'AppController'
      resolve: {
        //...
      }
  });
});

Similarly, we’ll need to make some minor changes to the authentication watch function — from watching route change, to state change.

angular.module('yourApp').run(function ($rootScope, $location, AuthenticationService) {

  // enumerate routes that don't need authentication
  var routesThatDontRequireAuth = ['/login'];

  // check if current location matches route  
  var routeClean = function (route) {
    return _.find(routesThatDontRequireAuth,
      function (noAuthRoute) {
        return _.str.startsWith(route, noAuthRoute);
      });
  };

  $rootScope.$on('$stateChangeStart', function (ev, to, toParams, from, fromParams) {
    // if route requires auth and user is not logged in
    if (!routeClean($location.url()) && !AuthenticationService.isLoggedIn()) {
      // redirect back to login
      $location.path('/login');
    }
  });
});

Fortunately, we don’t have to change the 401 intercepter and everything works just as before.

Expanding into Role-Based Authentication

The last step that you may want to take is role-based authentication. This is starting to add a bit more complexity to your app, and may not be necessary on the JS side. However, if you do need it, it’s a critical component.

The way to approach this would be have some sort of user object with roles, ideally specified in an injectable service:

angular.module('yourApp').factory('UserService', function () {

  var currentUser = null;

  var adminRoles = ['admin', 'editor'];
  var otherRoles = ['user'];

  return {
    // some code that gets and sets the user to the singleton variable...

    validateRoleAdmin: function () {
      return _.contains(adminRoles, currentUser.role);
    },

    validateRoleOther: function () {
      return _.contains(otherRoles, currentUser.role);
    }
  };
});

Then inject this UserService into the authentication watcher for further authorization, after the authentication step. Or, probably more ideally, you would want to create an AuthorizationService and separate it from the UserService (i.e. separation of concerns). It would look something like this:

angular.module('yourApp').run(function ($rootScope, $location, AuthenticationService, UserService) {

  // enumerate routes that don't need authentication
  var routesThatDontRequireAuth = ['/login'];
  var routesThatForAdmins = ['/admin'];

  // check if route does not require authentication
  var routeClean = function(route) { //... }
  // check if route requires admin priviledge
  var routeAdmin = function(route) { //... }

  $rootScope.$on('$stateChangeStart', function (ev, to, toParams, from, fromParams) {
    if (!routeClean($location.url()) && !AuthenticationService.isLoggedIn()) {
      // redirect back to login
      $location.path('/login');
    }
    else if (routeAdmin($location.url() && !UserService.validateRoleAdmin()) {
      // redirect to error page
      $location.path('/error');
    }
  });
});
Update

I have had quite a few requests for a working code sample for this post. I spent some time putting one together and you can find it here. Note that the working code is slightly different than the code above, but the main idea is the same.

http://arthur.gonigberg.com/2013/06/29/angularjs-role-based-auth/

How to select the first/least/max row per group in SQL

How to select the first/least/max row per group in SQL

Thu, Dec 7, 2006 in Databases

Here are some common SQL problems, all of which have related solutions: how do I find the most recent log entry for each program? How do I find the most popular item from each category? How do I find the top score for each player? In general, these types of “select the extreme from each group” queries can be solved with the same techniques. I’ll explain how to do that in this article, including the harder problem of selecting the top N entries, not just the top 1.

This topic is related to numbering rows, which I just wrote about (see my articles about MySQL-specific and generic techniques to assign a number to each row in a group). Therefore I’ll use nearly the same table and data as I used in those articles, with the addition of a price column:

+——–+————+——-+

| type | variety | price |

+——–+————+——-+

| apple | gala | 2.79 |

| apple | fuji | 0.24 |

| apple | limbertwig | 2.87 |

| orange | valencia | 3.59 |

| orange | navel | 9.36 |

| pear | bradford | 6.05 |

| pear | bartlett | 2.14 |

| cherry | bing | 2.55 |

| cherry | chelan | 6.33 |

+——–+————+——-+

Selecting the one maximum row from each group

Let’s say I want to select the most recent log entry for each program, or the most recent changes in an audit table, or something of the sort. This question comes up over and over on IRC channels and mailing lists. I’ll re-phrase the question in terms of fruits. I want to select the cheapest fruit from each type. Here’s the desired result:

+——–+———-+——-+

| type | variety | price |

+——–+———-+——-+

| apple | fuji | 0.24 |

| orange | valencia | 3.59 |

| pear | bartlett | 2.14 |

| cherry | bing | 2.55 |

+——–+———-+——-+

There are a few common solutions to this problem. All involve two steps: finding the desired value of price, and then selecting the rest of the row based on that.

One common solution is a so-called self-join. Step one is to group the fruits by type (apple, cherry etc) and choose the minimum price:

select type, min(price) as minprice

from fruits

group by type;

+——–+———-+

| type | minprice |

+——–+———-+

| apple | 0.24 |

| cherry | 2.55 |

| orange | 3.59 |

| pear | 2.14 |

+——–+———-+

Step two is to select the rest of the row by joining these results back to the same table. Since the first query is grouped, it needs to be put into a subquery so it can be joined against the non-grouped table:

select f.type, f.variety, f.price

from (

select type, min(price) as minprice

from fruits group by type

) as x inner join fruits as f on f.type = x.type and f.price = x.minprice;

+——–+———-+——-+

| type | variety | price |

+——–+———-+——-+

| apple | fuji | 0.24 |

| cherry | bing | 2.55 |

| orange | valencia | 3.59 |

| pear | bartlett | 2.14 |

+——–+———-+——-+

Another common way to do this is with a correlated subquery. This can be much less efficient, depending on how good your system’s query optimizer is. You might find it clearer, though.

select type, variety, price

from fruits

where price = (select min(price) from fruits as f where f.type = fruits.type);

+——–+———-+——-+

| type | variety | price |

+——–+———-+——-+

| apple | fuji | 0.24 |

| orange | valencia | 3.59 |

| pear | bartlett | 2.14 |

| cherry | bing | 2.55 |

+——–+———-+——-+

Both queries are logically equivalent, though they may not perform the same.

Select the top N rows from each group

This is a slightly harder problem to solve. Finding a single row from each group is easy with SQL’s aggregate functions (MIN(), MAX(), and so on). Finding the first several from each group is not possible with that method because aggregate functions only return a single value. Still, it’s possible to do.

Let’s say I want to select the two cheapest fruits from each type. Here’s a first try:

select type, variety, price

from fruits

where price = (select min(price) from fruits as f where f.type = fruits.type)

or price = (select min(price) from fruits as f where f.type = fruits.type

and price > (select min(price) from fruits as f2 where f2.type = fruits.type));

+——–+———-+——-+

| type | variety | price |

+——–+———-+——-+

| apple | gala | 2.79 |

| apple | fuji | 0.24 |

| orange | valencia | 3.59 |

| orange | navel | 9.36 |

| pear | bradford | 6.05 |

| pear | bartlett | 2.14 |

| cherry | bing | 2.55 |

| cherry | chelan | 6.33 |

+——–+———-+——-+

Yuck! That can be written as a self-join, but it’s just as bad (I leave it as an exercise for the reader). This gets worse as you go to higher numbers (top 3, top 4…). There are other ways to phrase the statement, but they all boil down to the same thing, and they’re all pretty unwieldy and inefficient.

There’s a better way: select the variety from each type where the variety is no more than the second-cheapest of that type.

select type, variety, price

from fruits

where (

select count(*) from fruits as f

where f.type = fruits.type and f.price <= fruits.price

) <= 2;

This is elegant, and lets you vary N without rewriting your query (a very good thing!), but it’s functionally the same as the previous query. Both are essentially a quadratic algorithm relative to the number of varieties in each type. And again, some query optimizers may not do well with this and make it quadratic with respect to the number of rows in the table overall (especially if no useful index is defined), and the server might get clobbered. Are there better ways? Can it be done with one pass through the data, instead of the many passes required by a correlated subquery? You know it can, or I wouldn’t be writing this, now would I?