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 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.

Answer 2

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 3

There are two different approaches:

1) Pretty data model

One row (user in your example) can have values (here a user permission which is one bit or a boolean value) where you don't know how many values are possible, i. e. the number of values is principally unlimited. The normal approach in SQL to handle this is a child table:

You create a table (and Java class for mapping / annotations) UserPermission, which contains the user id as a foreign key, a permission id and the boolean value. User id and permission id is a unique key for this table (you can add an id as a different primary key if you like). You even can add columns for the user who gave the permission, the date when this was done etc, if you want to have some auditing, but this is not necessary.

If you want to make it more pretty, then you also create a table (and Java class) Permission, which contains the permission id, a name for the permission and perhaps other information.

This solution needs more database space than your idea with the bits in an integer, but take in mind you won't have so many users compared to other data in the data base, so the extra amount doesn't matter.

2) Fast solution:

If the solution with extra tables needs to much overhead, because your permissions are not really important, and you worry an integer can be to short, then you can use the Java type BigInteger (the type allows bit manipulations) and map it with an @Column annotation to a NUMBER or DECIMAL in the database.

Bear in mind the size of a database's NUMBER also is limited (for example Oracle allows at maximum 10^40). If this might be a problem, then you must use solution 1).

One more disadvantage of solution 2) is, you never can use an index for the permissions. (A selection of all users having a certain permission set never will go over an index.)

I always would use solution 1).