While still working on Path and Form component I decided to write a bit about user authorization and especially about permissions part. So what about it? Very often in smaller projects (that don’t use any framework mechanisms for this purpose) at database level I saw user’s permissions designed like this:
There is an user and a permission table connected in an user_permission relation. To check if user has given permission simple query is required:
|
1 2 3 4 |
select permission_id from permission where user_id = ${userId} and permission_id = ${permissionId} |
It could be wrapped up in some handy function like this (I use Scala and ScalikeJDBC here):
|
1 2 3 4 5 6 7 8 9 |
def hasPermission(userId:Int, permissionId: Int) = { val pid = sql""" select permission_id from user_permission where user_id = ${userId} and permission_id = ${permissionId} """.single().apply().getOrElse(-1) pid >= 0 } |
So what’s wrong with it?. Firstly it can slow down other parts of the application. If your logic requires checking permission many times (in loop for example to do something with each order line) delay can be noticeable. Of course it can be cached in some kind of user permission dictionary but that leads to code complexity increasing. Secondly and that’s more important (at least from my point of view) is adding new permission. In the code one could have permission names defined like this:
|
1 2 3 4 5 6 |
object Permission { val ADDING_CONTRACT = 1 val EDITING_CONTRACT = 2 val REMOVING_CONTRACT = 3 val EXPORTING_TO_EXCEL = 4 } |
and use it in a clean and readable way like that (of course it can be simplified more):
|
1 2 3 |
if (hasPermission(loggedUser.userId, Permission.ADDING_CONTRACT)) { ... } |
But when new permission will be required two steps are needed to take:
- Adding new permission in the database
- Adding new variable in the Permission object
And the biggest pain here is keeping permission variables in sync with values in the database. If you used serial id (auto generated id) in the permission table and regenerated its data then you cannot be sure about consistency anymore. And developer that wants to add new permission must have database access or he has to bother database administrator.
Is there better solution? I wouldn’t be writing all this if there weren’t 🙂 Instead of having permission and user_permission table let’s just add binary “permission” field in the user table. I usually make it 32 bytes long so it’s capable to store 256 permissions. Each byte keeps 8 permissions – one per bit. In PostgreSql it’s defined as bytea type:
|
1 2 3 4 5 6 7 |
CREATE TABLE user ( user_id INT PRIMARY KEY NOT NULL, user_name VARCHAR(128) NOT NULL, password VARCHAR(50), permission BYTEA(32) ); |
To set a proper bit in the permission array byte slot (n in setFlag function) must be calculated and then bit number of this slot determined (offset in example below). Byte slot is simply a permission value divided by 8 (or shifted right by 3 bits) and offset is 1 moved left by number of bits taken from the rest of permission dividing. To check if permission is set exactly the same operations are needed except the last one where and is used to check if bit is on or off.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 |
object UserAuthorization { private def setFlag(flag: Int, flags: Array[Byte]) { val n = flag >> 3 val offset = flag - (n << 3) val mask = flags(n) | (1 << offset) flags.update(n, mask.toByte) } private def hasFlag(flag: Int, flags: Array[Byte]): Boolean = { val n = flag >> 3 val offset = flag - (n << 3) (flags(n) & (1 << offset)) != 0 } def packPermissions(permissions: Seq[Int]): Array[Byte] = { val packedPermissions = Array.ofDim[Byte](32) permissions.foreach(right => setFlag(right, packedPermissions)) packedPermissions } def hasPermission(permission: Int, permissions: Array[Byte]): Boolean = { hasFlag(permission, permissions) } def hasNotification(notification: Int, notifications: Array[Byte]): Boolean = { hasFlag(notification, notifications) } } |
packPermissions function converts array of permission numbers into array of permission bits that can be stored in the database. Below is an example of loading user and its permissions (and notifications which are handled in the same way). User class also has simplified version of hasPermission where only permission number is passed.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 |
case class User( userId: Long, userName: String, token: String, permissions: Array[Byte], notifications: Array[Byte] ) { def hasPermission(permission: Int): Boolean = { UserAuthorization.hasPermission(permission, permissions) } def hasNotification(notification: Int): Boolean = { UserAuthorization.hasNotification(notification, notifications) } } object User { def find(userName: String, password: String): Option[User] = { sql""" select user_id, user_name, token, permission, notification from user where user_name = $userName and password = $password """.map(r => User( r.int("user_id"), r.string("user_name"), r.string("token"), r.bytes("permission"), r.bytes("notification") )).single().apply() } } |
I hope the code above is clear to you and you’re able to figure everything out. The advantages of this approach are:
- Space needed to keep permissions both in the database and the code (only 32 bytes per user instead of 2048 (user_id + permission_id) * 4 bytes).
- Speed of loading (it’s a no cost actually).
- Speed of access – all permissions are loaded on user authentication, no caching required.
- Permissions are defined only on the code side. There is no need to synchronize with database values. If anyone wants to add new permission he simply adds a new variable in Permission object.
- Every coder can add new permission. There is no need to have access to the database.
And that’s it. I used this solution in my two last projects and it worked very well.