Flexible Permissions with SQLAlchemy many-to-many relationships

29. September 2012. Tagged python, flask, sql, sqlalchemy.

Introduction

Recently I had to work out some easy and flexible system for permissions in a web project. The last project I went for a simple table, where every permission is a boolean column. I just linked the Permission class to the User class with a one-to-one relationship.

While this approach is probably the easiest, as permission checking can be done via User.permission.messages_write and will just return True or False, but it is not flexible at all. If you want to add a new permission when you are in production already, you will have to worry about migration. And why would we want to do that, if we can avoid it?

I decided to use a many-to-many relationship with sqlalchemy. While there were a lot of examples, how to set such a thing up, I was not really sure how I would use them. What I then found out, is pretty amazing: If you define your relationships properly, you can just access a simple python list with all permissions a user has.

The practical part

First, we need two classes:

1
2
3
4
5
6
7
8
class User(db.Model):
	id = db.Column(db.Integer, primary_key=True)
	nick = db.Column(db.String(14))
	password = db.Column(db.String(14))

class Permission(db.Model):
	id = db.Column(db.Integer, primary_key=True)
	name = db.Column(db.String(20))

I made the classes as simple as possibly and I am assuming that youre flask-sqlalchemy instance is named db and imported. Just adjust those classes to your needs.

For a many-to-many relationship you will need another table though, the helper table:

1
2
3
4
permissions = db.Table('permissions',
	db.Column('permission_id', db.Integer, db.ForeignKey('permission.id')),
	db.Column('user_id', db.Integer, db.ForeignKey('user.id'))
)

This table will save a user_id and a permission_id in each row, linking those together. What is missing though, is the relationship:

1
2
permissions = db.relationship('Permission', secondary=permissions, \
	lazy='dynamic', backref=db.backref('users', lazy='dynamic'))

Just add this to the User class and you are ready to go. This is basically it already. But I wanted the whole thing to be handy, so I extend the User class with a few helper functions:

1
2
3
4
5
6
7
def has_permission(self, name):
	"""Check out whether a user has a permission or not."""
	permission = Permission.query.filter_by(name=name).first()
	# if the permission does not exist or was not given to the user
	if not permission or not permission in self.permissions:
		return False
	return True

This function will just check with a permissions name, whether the user has the permission or not.

If your user instance is named user you could check for the permission messages_read with the following call: user.has_permission('messages_read').

I also have two helpers for granting permissions and revoking them:

1
2
3
4
5
6
7
8
9
10
11
def grant_permission(self, name):
	"""Grant a permission to a user."""
	permission = Permission.query.filter_by(name=name).first()
	if permission and permission in self.permissions:
		return
	if not permission:
		permission = Permission()
		permission.name = name
		db.session.add(permission)
		db.session.commit()
	self.permissions.append(permission)

This one will first check whether the given permission exists at all and the user does have it already. If it does exist and the user has it already, it returns. If the permission does not exist at all, it is created, added to the database and changes are comitted (which is necessary, because otherwise it will not get an ID and without it, it can not be linked to the user.)

Afterwards the permission will be added to the user’s permissions.

1
2
3
4
5
6
def revoke_permission(self, name):
	"""Revoke a given permission for a user."""
	permission = Permission.query.filter_by(name=name).first()
	if not permission or not permission in self.permissions:
		return
	self.permissions.remove(permission)

This one will check whether the permission exists and the user owns it, and if both is the case, it will be removed from the user permissions.

To be honest: If you get this far, permission handling could not be easier. And more flexible. This approach can also be easily be extended, to handle granular permissions.