The basics of role-based access control in SQLAlchemy

Lito Nicolai

Almost every application will need to control who can do what. What’s the best way to design that?

One good way is with “roles” — grouping permissions so that they can be assigned to users. Often this is referred to as role-based access control (RBAC). An ‘admin’ is a role, for instance, and so is a ‘moderator’. Roles map very nicely to how we talk about our systems.

To celebrate a new release of Oso, our open-source authorization library, we’ll demonstrate a few ways of modeling role-based access control in Python and SQLAlchemy. The first few examples will be in plain SQLAlchemy, with no other libraries. For more complex examples we’ll show off Oso, which gives you the building blocks for adding RBAC to your application.

We’ll be writing these examples as they would be written in a multi-tenant production system. That is to say, we have a single version of our application that will serve many “tenants”, or organization accounts. Think of GitHub, for instance — the same servers that host Oso’s GitHub account also host the Rust Programming Language’s GitHub Account. We think this will be useful to you, because nearly every SaaS app that you’ll write will use a multi-tenant model!

Our app will start with two data-models: an Organization (each tenant of our multi-tenant app) and a User (a single person’s account). This first example will associate each User to an Organization.

Organizational roles

This is a great place for your app to start — this authorization scheme is simple, easily understood, and extensible. Each User has a Role and is associated with an Organization.

We have:

  • A Users table with a column of foreign keys to the Organization they are part of. The User table also has a column that stores the name of their role.
  • An Organizations table

    Organization role model

Here’s that model expressed in SQLAlchemy.

from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import relationship
from sqlalchemy import Column, Integer, String, ForeignKey

Base = declarative_base()

class User(Base):
    __tablename__ = 'users'

    id = Column(Integer, primary_key=True)
    name = Column(String)

    organization_id = Column(Integer, ForeignKey('organizations.id'))
    organization = relationship("Organization")

    organization_role = Column(String)

class Organization(Base):
    __tablename__ = 'organizations'

    id = Column(Integer, primary_key=True)
    name = Column(String)

You will, of course, need to write a check that verifies an action the user takes is allowed by their role! We won’t cover how to do that here. (We go into detail about how to write that code in our Authorization Academy tutorials.) In most cases, that’ll be a conditional in your controller.

You’ll also need a mapping of roles to permissions. Here’s an example, but we won’t cover this in detail.

role_permissions = {
    "member": ["view"],
    "admin": ["view", "invite"],
}

Limitations of this model

In many SaaS apps, a user can be part of many organizations. The above model only allows a User to join one organization. This will directly guide our next model:

Roles in multiple organizations

We need our User to have separate roles in many Organizations. My GitHub account, for instance, can be part of both the Oso account and the Seattle Rust Meetup account. To do that, we’ll introduce an intermediate table, the OrganizationRole. This will store a User ID and associate it with a role and an Organization.

We have:

  • A User table
  • An Organization table
  • An OrganizationRole table with columns for a User id, an Organization id, and a Role.

Roles in multiple organization data model

Here’s that in SQLAlchemy:

from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import relationship
from sqlalchemy import Column, Integer, String, ForeignKey

Base = declarative_base()

class User(Base):
    __tablename__ = 'users'

    id = Column(Integer, primary_key=True)
    name = Column(String)

    organization_roles = relationship('OrganizationRole', back_populates="user")

class OrganizationRole(Base):
    __tablename__ = 'user_organization_roles'

    user_id = Column(Integer, ForeignKey('users.id'), primary_key=True)
    user = relationship("User", back_populates="organization_roles")

    organization_id = Column(Integer, ForeignKey('organizations.id'),
                             primary_key=True)
    organization = relationship("Organization")

    role = Column(String)

class Organization(Base):
    __tablename__ = 'organizations'

    id = Column(Integer, primary_key=True)
    name = Column(String)

More complex RBAC, with Oso

We’ll leave out the SQLAlchemy code for the upcoming examples, which can quickly get complex, and use Oso. In particular, we’ll use the sqlalchemy-oso library, which will manage storing roles data in your database for you.

Implied roles

To avoid duplicating permissions for every role, you’ll often want one role to inherit permissions from another role. For instance, an ‘admin’ should be able to do anything a ‘member’ can do, plus extra permissions. If we added an ‘owner’ of the organization, they should be able to do anything an ‘admin’ can do, plus extra permissions. We’d like to avoid re-typing the same permissions for every case! That’s error-prone, and requires too much care when changing permissions.

In an Oso policy file, we can declare our roles, assigning each their permissions (perms, in the code) and the roles they inherit from (implies, in the code). This policy is written in Polar, Oso’s policy language.

resource(_resource: Organization, "org", actions, roles) if
    actions = [
        "invite",
        "view"
    ] and
    roles = {
        org_admin: {
            perms: ["invite"],
            implies: ["org_member"] # org_admin implies org_member
        },
        org_member: {
            perms: ["view"]
        }
    };

allow(actor, action, resource) if
    Roles.role_allows(actor, action, resource);

Oso will handle the relevant database tables and authorization logic. You’ll have a single function, oso.is_allowed(user, action, resource), to call to check if an action is allowed.

Here, a ‘member’ can view an Organization. An ‘admin’ has all the permissions of a ‘member’, and is able to invite users to an Organization as well.

Resource hierarchies

So far, we’ve only talked about a single resource: the Organization. Your application will have resources contained in that organization. In GitHub, for example, the Organization would be our Oso GitHub account. Our GitHub account also contains many Repositories — something we haven’t addressed yet.

Rather than giving Users access to Repositories on a one-by-one basis, we’d like to give them access to all Repositories in an Organization. We need to be able to express the authorization policy, “If a person has access to an Organization, they have access to that Organization’s Repositories.” Oso can do that!

parent(repository: Repository, parent_org: Organization) if
    repository.org = parent_org;

This establishes that Organizations are parents of (i.e. may be in charge of) Repositories. From then on, any time we write a permission in the form of resource:action, Oso will resolve the hierarchy.

Concretely, if we write that an Organization 'member' role grants permissions to repo:pull:

  • A member of an Organization will have permission to pull organization’s Repositories.
  • Members of other Organizations will not have that access.

Here’s a full implementation of this hierarchy.

resource(_type: Organization, "org", actions, roles) if
    actions = [
        "invite",
        "view"
    ] and
    roles = {
        org_admin: {
            perms: ["invite"],
            implies: ["org_member"] # org_admin implies org_member
        },
        org_member: {
            perms: ["view", "repo:push", "repo:pull"]
        }

    };

resource(_type: Repository, "repo", actions, roles) if
    actions = [
        "push",
        "pull"
    ];

parent(repository: Repository, parent_org: Organization) if
    repository.org = parent_org;

allow(actor, action, resource) if
    Roles.role_allows(actor, action, resource);

We have every feature we’ve discussed in this blog post, in under 40 lines of policy code. Not bad!

Where to start with Oso

Oso roles support is still in early access. Join here and checkout our documentation on the new library!

If this is an area you’re exploring, we encourage you to join the community of developers in the Oso Slack! Our core engineering team is also in Slack and is happy to engage and answer your questions.

Want us to remind you?

We'll email you before the event with a friendly reminder.

Get involved in the Oso community

Connect on Slack

Get help from our team, and talk with hundreds of like-minded developers.
Join the Slack

Share the love

Show off the problems you're solving with Oso and how you're leading the charge.

Get Oso Swag

We're sending free Oso swag to users anywhere in the world. Seriously.
Get swag

Get updates from Oso.

We won't spam you. Ever.