I've heard about creating different users for different tasks, usually based on the principle of only allowing that connection the permissions to do what it needs to. I always thought that had merit, but now that I am faced with putting it into practice a few questions arose.
1. Is the idea behind this purely SQL injection security?
If this is the case, then it's likely redundant to be using a minimum-permissions user for queries using only pre-selected data.
2.Every time I switch users it has to re-initialize the connection, right? Should I be concerned about performance loss?
This could have a bearing on how I structure some scripts. I'm not actually sure, is connecting to a database more than once per load usually a good sign you're doing something wrong?
Thanks, I would be interested to know if this is common or not.
1. Is the idea behind this purely SQL injection security?
Not at all. In fact, it is often done to allow multiple developers to work in the same system without fear they will tread on each other's work. Joe can only use DB Inventory; Mary can only us CustomerInfo database, etc.
But yes, it can be used to improve online security. After all, if the web server is only allowed a certain range of operations, then even somebody who manages to inject or otherwise execute SQL can't do any damage.
2.Every time I switch users it has to re-initialize the connection, right? Should I be concerned about performance loss?
Ummm... I think you are using it wrong if you are switching users based on who logs on to the web site. Most installations have a single user account that is used by all web pages. When there are more than one web site user accounts, it's usually divided by function, not by user. And since a given web page usually only needs to be concerned with a single function (e.g., inventory control, user account management, sales reporting), that page only makes one connection to the DB using the credentials appropriate to the function of that page. IT would be very unusual to have a website where you changed connection info based on what user was logged on. (Well...maybe if you had just a few customers and you only wanted to show data for that customer? But then you would determine the connection at the time they login and use that connection for all other pages. So you still wouldn't CHANGE connections on any pages. I don't think this is necessarily a good way to set up a site, but at least I could imagine it being done.)
__________________
An optimist sees the glass as half full.
A pessimist sees the glass as half empty.
A realist drinks it no matter how much there is.
Thanks Old Pendant. Oh, I think you misunderstood the second question quite alot. When I said "users" I meant MySQL database users!
I get the idea that what I was thinking of accomplishing can be done better by a different approach. I was thinking of changing users (permissions) if a table didn't exists, so that I could create that table. In the case of a new forum section being added to a list of forum sections — which I would regularly only need to view — I could then add a table if it doesn't have a table for that section. But it makes more sense to create that table at the same time as adding a new section to the list of sections, which would probably be done in an admin panel. Maybe I just have to structure it right.
In the case of a new forum section being added to a list of forum sections — which I would regularly only need to view — I could then add a table if it doesn't have a table for that section.
Well, I for one would *NOT* add a new table just because a new forum section is added. Why? If you have a "ForumSections" table then all you have to do is add a new record to that to add a new forum section. No reason to create an entire table.
But in any case, I would think that adding a forum section--however it is done--would be the responsibility of and only allowed to be done by an administrator. And presumably it would be done via some admin-only web page. And then, again, that page needs the admin connection whereas normal pages need only a users connection. So, again, the entire page uses the same connection and there's no issue with closing a connection and opening another.
HOWEVER... Even if you did decide you needed to close the users connection and open the admin connection, that operation would presumably only be done very rarely and so who cares if you waste a few milliseconds a handful of times per day?
__________________
An optimist sees the glass as half full.
A pessimist sees the glass as half empty.
A realist drinks it no matter how much there is.
Another mis-understanding it seems. I meant more along the lines that there already was a new entry in the "ForumSections" table, done from the admin panel, and that the script could create a brand new corresponding table to hold all threads for that section if it didn't have one related to it (Without the user knowing anything different). I was just saying that it would make more sense to create such a corresponding table at the same time as adding a new entry to "ForumSections" table in admin panel. This was just an example anyway, I'm not actually doing it.
Just a few milliseconds, huh? That doesn't sounds so bad.
Once again, I don't see why you would put posts for each forum in a separate table just for that forum. There is no DATABASE reason to do so. It will work just as well--and be easier to code--if there is only one table for all posts, no matter what forum.
So your choice to create a table per forum is your choice. I wouldn't do it. But, yet, it can be done. But once again that seems to me to be something that only an admin should be able to do and so it would be done from an admin web page using an admin connection. I see no reason to need to change connections once you get to the appropriate page.
But clearly you are designing your system completely differently than I would, so ultimately it is up to you.
__________________
An optimist sees the glass as half full.
A pessimist sees the glass as half empty.
A realist drinks it no matter how much there is.
Once again, I don't see why you would put posts for each forum in a separate table just for that forum. There is no DATABASE reason to do so. It will work just as well--and be easier to code--if there is only one table for all posts, no matter what forum.
Wouldn't it make getting results quicker, if the results were in a table with 10,000 entries, instead of 100,000?
It was all just an example, trying to mock a situation where changing the permissions during runtime might be desirable. I have since realized it was a fault in how I was thinking about it, and I agree with you that a connection shouldn't need to change permissions within a runtime. I'm not even to the point of considering the structure of forums database just yet, although I had always assumed smaller databases would equal quicker queries..
Wouldn't it make getting results quicker, if the results were in a table with 10,000 entries, instead of 100,000?
Not if the table is properly indexed and the query uses the indexes. Oh, it might take 2 or 3 milliseconds less, but it could well be you lose that much in other ways by having more tables.
Also, MySQL does a really good job of CACHING when there are only a few tables involved. With too many tables, it tends to not use the cache as effectively.
__________________
An optimist sees the glass as half full.
A pessimist sees the glass as half empty.
A realist drinks it no matter how much there is.
Before you post, read our: 
Maybe I just have to structure it right.
