Store an integer for bitwise compare in a permission model using JPA 2

Question

I am using a permission model where I have a table user_permissions. This table will hold one or more columns with a certain bigint. I will use the bits of each decimal number to compare with certain permission rules (the bit location will be a permission rule and the value will be the condition of the rule active or not active).

The problem with this approach is that I have limited number of bits to work when using a number such as bigint.

What is the best column type I can use in this case that works in a cross-database environment?

The tags represent the technologies I am aiming for, so any other solution related to those technologies are appreciated.

I was thinking to use @Lob annotation to store large data, is that the best practice?

UPDATE: The user_permission table extends the user with a 1:1 relationship and have bigint fields like bin_create, bin_read, bin_update, bin_delete that will hold the binary data as decimal numbers.

To clarify the question: I am considering comparing the permissions using bitwise operators. So let's assume I have a user with the permission value 10(1010), and an action requiring 13 (1101). So 10 & 13 == 8 (1000) -> The user have one permission matching the required permissions for the action, so I could allow or deny (it is up to the application rules define which). But with this approach I have a limited number of bits to work on (lets say I increase the permissions to be considered, so the numbers will increase too). The max bigint value is ~9223372036854775807 and that gives me the binary 111111111111111111111111111111111111111111111111111111111111111 with ~60 blocks and permission possibilities per field. So What is the best column type I can use in this case that works in a cross-database environment to store a huge quantity of binary blocks and with the possibility to work with bitwise operators in java?

Answer 1

If you want to store your data in an optimal way, you have to name the target, where you want to optimize.

This is an optimal solution for MySQL (by defining BINARY(32)), you can try something similar on your favorite database:

@Column(columnDefinition = "BINARY(32)", length = 32, nullable = false)
private byte[] bits;

Sometimes with some JPA providers and databases the column definitions ends up with Lob. That's not the best solution, because reading a Lob is an external (very expensive) operation. Try to change either the provider, or the database (if you're working with pure JPA, you can try it).

Options for replacing Lobs are for example numeric columns (you can use e.g. 4 columns with 64bit width, or similar). If you want a nice solution, these container columns can be even @Embedded into your main class. But it all depends on your database.

This way you will have 256 bits (32 bytes), without any conversion and further calculation, and you will have the possibility to extend the range if you want. You have to be careful when changing the column definition, though.

Answer 2

If it's the amount of data you can fit in a field that you're concerned about, why not store the number as a varchar? To my knowledge, pretty much any database will let you go up to at least a varchar(255). If you need more than 255 digits in the number, you could encode it in base 64 to squeeze it down more. If my mental arithmetic is right, that gives you 255 characters * 6 bits per character = 1530 different bits to use. If you need more than that, I might suggest your permissions model is a little excessive.

That's assuming that you're trying to crowd the data into a smallish space in the database. Your question isn't entirely clear on what you're trying to solve for. On the other end of the spectrum, you could unpack the bits and save each bit to its own field or its own row. For example, user_permissions could be a table with two columns: user and permission, where each row is one permission granted to one user.