Flexible Permissions with SQLAlchemy many-to-many relationships
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
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:
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.