Dev + Mocha

I’m pretty weary. All this week working on what/how/where/when implementations, starting with logic “what will this object hold?”. Working on the prototype of OpenType Threads has made realize how important it is to detail a plan, it’s good but also a bit lonely hopefully when I launch the prototype I hope some developers join me along.

It is exciting in a way to get new concepts, and just yesterday I start to get a new one that I will immediately implement.

The plan is the following, probably nothing new to many seasoned programmers, but on current projects it is rarely seen, especially in PHP. We usually use a database storage, probably MySQL. MySQL provides the feature to allow multiple users to share one database, with different permissions. Quite a nice feature isn’t it? It’s hard to imagine how useful this is in programming, because well, installing PHP scripts just relies on the usual “SELECT, CREATE, INSERT, DELETE, DROP, UPDATE, ALTER”. We don’t usually THINK why MySQL users exists and many installers just tells you “Hey! Just write your database, database user, password, port if there is any and we’ll do the rest!” And that’s the worst mistake and you’ll see why:

I layered OpenType Threads administration concept in two, the master, the user. The master user has the following privileges :

Master user:

• INSERT
• DELETE
• UPDATE
• SELECT
• CREATE
• ALTER
• INDEX
• DROP

The user:

• SELECT
• INSERT
• DELETE
• UPDATE

Now, where do we usually use the “master user”? The answer lies in administration panel, imagine if you use wordpress, wordpress plug-ins usually need to CREATE/DROP/ALTER/INDEX a table, INSERT, DELETE, UPDATE, SELECT data. It’s perfect for the admin section.

Now why would I want to use two users, sharing the same database? The answer lies in SQL exploits. I won’t pretend this will take care of all injections, that depends mostly on your sanitation of inputs in your web application. It will certainly stop aggressive ones, like, for example:

- a malicious user attempts to DROP the database, everything will be lost in just one command.
- One of your administrator’s “betrays” you and attempt tries to delete tables, again the DROP command will be unavailable.

Now as we see, using a the “normal user” will certainly be helpful. Yet it leaves kind of a gap, we can’t do much with the “DELETE” command, actually we can if you rethink your application. Lets say that in your articles table you have the usual (artID, artContent, artTitle) lets add one more column (artState(ENUM(‘publish’,'delete’,'draft’))). We have enumerated three options, publish, delete and draft. The usual “publish” will just show the article to the users viewing your site.

Now, instead of using the DELETE command to delete the record, it will hide the record and add it to a “trash can” which will be ONLY be deleted when you access the administration panel so it carries the “master user”.

Issues like “what if I get my admin login stolen?”, “what if the malicious user steals the user, password of the master user?”. Well, those issues depends on the security of your web application, it has nothing to do with the TRANSACTIONS made. And of course, sanitize all inputs.

I don’t believe it hurt to try this because so far it’s just another measure to take. Infact I’m not taking the measure with just using TWO MySQL users. I’m also throwing in Foreign Keys Constraints, to stop any user deleting data that is related in another table. The introduction of Foreign keys constraints, it’s useful, but also can turn into a double edged blade if you overuse it.

Well, there is still a lot to learn :) I’m still lurking in MySQL documentation to get more ideas (but I should stop, implementing all unneeded features will just put a hole in your goal). Yet I’ll just do this one to see how it goes, it’s a gamble after all. I need to see the impact it will cause and what benefits will be gained.

2 people like this post.

Like