Using bitwise operations in the WHERE clause

[NotionCommotion]

Instead of using Laminas or Symfony's ACL component, I ended up coming up with my own, and only providing the following to provide context:  For each entity where access control is needed, permissions for read, edit, and create can be public, owner, or restrict.  If restrict, then it will check if a user is a member of the specific entity in question, and if so, get the same read/entity/create permissions for the member.  There are also two types of users: those that belong to the tenant and those who belong to vendors who in turn belong to a given tenant.  In addition, I wish to allow each tenant to define their own sitewide permissions which are then cloned when adding a new entity which is under access control, and also something similar when adding members.

Bottom line is I have a bunch of SQL tables which have a bunch of columns which are either boolean or some string with limited enum options.  In addition, I am using doctrine's value object solution (embeddables) which givens the columns some long name (granted, sure I can fix this part).

I expect I should let it go but it is just damn ugly, so I created a class which takes a small integer stored in the DB and creates my permission object, and all works as desired.

When dealing with a single resource (read, delete, edit, etc), I have no concerns about performance as PHP only has to deal with a single record provided by the DB.  But when requesting a collection of resources, I wish SQL to do the work, and it will need to filter based on this small integer described in the previous paragraph.  Obviously, I will want to index it, but I don't know how indexing works when using a bitwise operator in the WHERE clause.

How does indexing work when using bitwise operators in the WHERE clause?

Is there anything that can be done to improve performance if an issue?  For instance:

• Locating the read permission bits on the high side of the bit string so the index reflects them more than edit/delete/etc which won't need an index.
• Add additional columns which are redundant to the single integer stored in the DB but can be used only when filtering on the ability to read.

Edited April 2, 2022 by NotionCommotion

[requinix]

What's the table schemas? What query(ies) are you initially thinking of running, and/or what results are you trying to get out of them?

[NotionCommotion]

20 hours ago, requinix said:

What's the table schemas? What query(ies) are you initially thinking of running, and/or what results are you trying to get out of them?

Currently, I implemented adding additional columns which are redundant to the single integer stored in the DB but can be used only when filtering on the ability to read.  This permission integer is follows and has contains some enums and binary flags:

class Permission
{
    public function __construct(
        private PermissionEnum $read,
        private PermissionEnum $create,
        private PermissionEnum $modify,
        private bool $restrictToOwner,
        private bool $coworkers,
    )
    {
    }
}

 

enum PermissionEnum: int
{
    case public     = 0b00;
    case owner      = 0b01;
    case restrict   = 0b10;
    case override   = 0b11;  //currently not used.
}

Schema is as follows.  As you can see, separate copies of the permission values are stored multiple places.

tenant
- id
- tenant_default_permission_prototype (integer defining multiple boolean and enum properties)
- vendor_default_permission_prototype (same as for tenant but will be used when vendor's log on)
- tenant_member_permission_prototype (integer defining multiple boolean and enum properties for any members that are added)
- vendor_member_permission_prototype (same as for tenant but will be used when vendor's log on)


project
- id

project_access_control
- id (one-to-one to project.id)
- tenant_default_permission (integer defining multiple boolean and enum properties.  Will be copied from tenant.tenant_default_permission_prototype)
- vendor_default_permission (same as for tenant but will be used when vendor's log on)
- tenant_member_permission_prototype (integer defining multiple boolean and enum properties for any members that are added)
- vendor_member_permission_prototype (same as for tenant but will be used when vendor's log on.  Will be copied from tenant.tenant_member_permission_prototype)
- tenant_read_permission (options are public, owner, or restrict.  duplicates information in tenant_default_permission.read)
- vendor_read_permission (same as for tenant but will be used when vendor's log on)

project_member
- project_access_control_id (or maybe project_id?)
- user_id
- permission (integer defining multiple boolean and enum properties for any members that are added.  Will be copied from the applicable project permission prototype)
- read_permission (options are public, owner, or restrict.  duplicates information in permission.read)

user
- id
- descriminator

tenant_user
- id (one-to-one to user.id)

vendor_user
- id (one-to-one to user.id)

A query when a tenant user logs on looks like the following when using my redundant read permissions, and will be almost the same except will use vendor_read_permission instead of tenant_read_permission.

SELECT *
FROM project p
INNER JOIN project_access_control pac ON p.access_control_id = pac.id
LEFT JOIN project_member pm ON pac.id = pm.access_control_id
WHERE p.tenant_id = 123 AND (
    pac.tenant_read_permission = 0
    OR pm.read_permission = 0
    OR (pac.tenant_read_permission = 2 AND pac.owned_by_id = 321)
    OR (pm.read_permission = 1 AND pac.owned_by_id = 321)
)

A fictional query which I want to use (or maybe I just keep with the previous) looks like the following.  Instead of filtering based on discrete columns, my integer permission columns would be used and the SQL would need to define which bit or bits to use.  Not sure if it works this way but envision an index being a bunch of numbers from small to large, and since the read operations are the ones which would value from this index, thinking the read bits should be the bits on the left (or big values or whatever they are called).

SELECT *
FROM project p
INNER JOIN project_access_control pac ON p.access_control_id = pac.id
LEFT JOIN project_member pm ON pac.id = pm.access_control_id
WHERE p.tenant_id = 123 AND (
    areBitsSet(pac.tenant_default_permission, 0, 0)
    OR areBitsSet(pm.permission, 1, 0)
    OR (areBitsSet(pac.tenant_default_permission, 2, 2) AND pac.owned_by_id = 321)
    OR (areBitsSet(pm.permission, 3, 1) AND pac.owned_by_id = 321)
)

 

[requinix]

You can't index on a function call.

Having attempted this before (a very long time ago), bitwise isn't a good answer. It seems like the data is efficient and compact but that's not what you should be striving for with a database: what you need is a good representation of the data.

Plus your PermissionEnum bits don't make sense. For example, they imply everything is public. Not that I'm exactly following along with what you're doing but everything public doesn't sound like what you want.

What's probably easier than bits is a simple table of individual IDs and permissions.

[NotionCommotion]

Thanks requnix,

Deep down, I knew what you are saying is true and that my desire to make it look pretty and compact should not be the goal.  Still it is still tempting...  Since it is complete and working, probably will give it a try for a bit and if issues, go back to a transitional approach.

While not the purpose of my post, if you have a better word than "public", I would like to hear it.  It does not mean the public in general but users who have logged on and authenticated using their username and password, but are not the owner of the resource and do not belong to the resource's group.  While not exact, pretty close to Unix permissions and maybe other or world is more appropriate...