phpbb_acl_options table and auth_option field

dcz · Post by **dcz** » Fri Feb 13, 2009 9:36 am

Hello all,
I recently faced an issue with the phpbb_acl_options table and auth_option field. Nothing really due to phpBB3, but that could be dealt with better (excuse my ignorance if I'm wrong).

Here is the thing, I was playing with a mod conversion (from phpBB2), and find out that this mod did not check the phpbb_acl_options table before it will add the new auth_option in it. So, running the converter multiple times added multiple occurrences of the same auth_option values. I later find out that many mod did this, since mod author did not find out about umil at this time.

This kind of behaviour, even though not legitimate, can cause great trouble in auth settings, and are pretty hard to detect on the first time.
So I was wondering if it would not be wise to add a UNIQUE key on the auth_option field in that table, could help out to prevent such messing and as far as I understood the whole thing, it would not jeopardize any other process, since auth_option looks like meant to be unique.

Would be a cheap and powerful self defense trick on the phpbb_acl_options on which other table depends.

Good or bad guess ?

++

dcz · Post by **dcz** » Sat Feb 14, 2009 9:44 am

no one on this matter ?

EXreaction · Post by **EXreaction** » Sat Feb 14, 2009 9:22 pm

I believe we've discussed this before with some of the developers and other mod team members, but I don't really remember what the reason was for why that column isn't unique (can't think of any reason it couldn't be right now).

There are also a few bugs in the auth admin permission add function as well, though I have not been able to find out exactly what the bug is...

dcz · Post by **dcz** » Sun Feb 15, 2009 10:50 am

EXreaction wrote:(can't think of any reason it couldn't be right now).

Same for me, I could not find any legitimate use for a duplicate auth_option key.
What I'm sure of is that if a UNIQUE key on this field is possible, then it would prevent some really bad experiences to low skills phpBB user that would have tried a dummy mod adding auth. Yes it is bad to try mods without backups, but we all know many do it, and worst, some could discover the effect quite some time after they tried it. Would as well simplify support since such matter are again hard to detect if you did not actually face it once (duplicated auth_option fields).

So my point is, if it's doable to add this UNIQUE key, it's a real good thing to do, even though no legitimate use should mess up with this field, preventing potentially heavy mess up (dealing with table that depends on the auth_option ids) once and for all would be great.

I guess we now need the dev team to answer on that.

Regards,

++

dcz · Post by **dcz** » Tue Feb 17, 2009 3:40 pm

Maybe I should post this in the bug tracker, what do you think ?

EXreaction · Post by **EXreaction** » Tue Feb 17, 2009 6:01 pm

That's probably a good idea.

dcz · Post by **dcz** » Wed Feb 18, 2009 12:02 am

mm, looking at the bug tracker, there is no "data base structure" report category, and this could as well be considered as not a (phpBB) bug. It's kind of an edge case.
But before all, I do not want to stress any body here, so let's say we wait few more days for the report.

Hopefully someone will pass by.

++

EXreaction · Post by **EXreaction** » Wed Feb 18, 2009 3:29 am

Just put it under Other.

dcz · Post by **dcz** » Thu Feb 19, 2009 8:01 am

Done : http://www.phpbb.com/bugs/phpbb3/ticket ... t_id=41835

dcz · Post by **dcz** » Sat Feb 21, 2009 8:19 am

This topic must be cursed

Too bad.
I know there is nothing really urgent here, but the duplicated auth_option key matter is, IMHO, a critical one since it's really complex to fix authorisations when you'd find out that duplicated auth key where accidentally added ages ago.

Of course, I won't be tricked by this, and I fortunately discovered this on testing boards, but I'd really would not want to be the guy facing this on a large online forum.

Because even if you took precautions, and installed a mod that would do this on a local server, you could miss the matter while testing and go online with it for some time before you find out about it ... would be very bad time for the guy.

++

Lumpy Burgertushie · Post by **Lumpy Burgertushie** » Sun Feb 22, 2009 3:34 am

I know absolutely nothing about this, however, I would assume that if it was much of a problem or even much of a potential problem, that someone would have found it before now.

robert

Eelke · Post by **Eelke** » Sun Feb 22, 2009 1:09 pm

Like was said above, it is one of those border line cases. It will certainly not stop the board from functioning properly if everyone codes properly. That's a big "if", though

In general, it is a good idea to make rules, that affect your database's data, explicit within the database. In this case, apparently there is supposed to be only one database row with a particular value for the column auth_option. If duplicate entries were to end up in the database, very obscure bugs might result, because code making use of the data is assuming there will be only one row, where there might be multiple.

People writing code to insert values into that table will have to check if there already is a row with that particular value, or delete any value that might be left, before inserting new data. One approach to stopping multiple values from ending up in the table is to basically assume that everyone will code properly. This is a very decentralized approach and prone to problems. If it does go wrong, the earlier mentioned obscure bugs can be the result.

The other approach, which generally is the preferred approach, is to have the database enforcing the rules that apply to the data within, i.e. making the rules explicit that were otherwise implicit. Marking the column UNIQUE, means that the database will make sure that the data in that column will in fact be unique, and will stop duplicate rows getting into the database, even if people write code that otherwise would result in an inconsistent database. It is enormously helpful to spot problems in the code, because it raises a flag the moment the "damage is done" instead of causing obscure bugs somewhere down the line.

Of course, this is very much a theoretical presentation of the matter, maybe there are specific reasons to not use these mechanisms. For example, I wouldn't know what the performance implications would be of marking a column UNIQUE (although I would expect that it primarily has implications upon writing a database row, which in this case only happens sporadically (I think?), and that reading of the table is not impacted).

dcz · Post by **dcz** » Sun Feb 22, 2009 4:40 pm

That's the whole point about it, if there are no reason to use duplicated auth_option keys, then a UNIQUE key on that filed is just a strong and cheap self defense trick, that can save many many time to even very very few people.

Since it's just a minor tweak in the db structure, it's IMO worth it even if very few people may be affected, cause authorizations are a very critical matter.

++

Eelke · Post by **Eelke** » Mon Feb 23, 2009 8:10 am

I agree and I am very curious to see some input from the dev team.

dcz · Post by **dcz** » Wed Feb 25, 2009 7:34 pm

Me too

advertisements