Mysql multiple users for security?
In server control panels, you can create multiple users for DB access
Would it make a site more secure if I had say three database users?
1)read only access used for searches and data extracting queries only
2) access to create, write, read, alter for changing DB data
3) drop, for those times when a user closes their account, deletes something they are allowed to delete etc
I know injection can't change the type of query, so if its only a SELECT, they cant insert anything, they could only select things they shouldn't, but most times you are only appending one field in a row or selecting something, and DROP isn't used often at all.
So would only having DROP access available for usage when you are actually allowing the user to drop something make it safer?
Or is this just a pointless exercise?
teammatt3 posted this at 19:07 — 13th February 2008.
He has: 2,102 posts
Joined: Sep 2003
Having different database users for different operations is a very good idea. The main reason for having user privileges is keeping the users of the database from selecting or manipulating data they shouldn't be. If you have a large organization, your PR people should be able to select salary information (and perhaps modify it), but the field workers don't need access to those tables.
In your case, I think you're the only user. I think you should create two users. One for web queries (SELECT, UPDATE, DELETE) and another user for advanced queries like modifying tables and that kind of administration.
Though it is a little more secure to have one user to select, one to delete, and one to update, it's a lot of work. And as long as you code your queries properly, you shouldn't have a problem with SQL injection. Just use mysql_real_escape_string()
Greg K posted this at 19:51 — 13th February 2008.
He has: 2,145 posts
Joined: Nov 2003
I tend to lean towards the thought that as long as you are talking about a single mySQL server and single web server, and single person maintaining it, there one general account is good.** I mean if they are somehow going to get into your server to see the access to read a record on one script called review.php, they would be wise enough to browse over to the script that lets someone new sign up and therefore write to the DB too...
Now, that being said, still set up the account with a reasonable name for possible future branching, if you get someone else programming for you or such. One you get two separate entities (persons, departments, etc) that are making code that can specify where the data is coming and going to (as far as tables and databases), then definitely start using the differnt users and permissions.
A second place where you may want to use multiple users, even it is all only you doing it, is like we have, a beta site for developing code. All the databases for the live site start with the prefix "live_" and the dev side of the domain all use "beta_" then no matter how many different databases i have, the developer site has permission to only access beta_% (% is wildcard in mySQL) This ensures no matter what oopsies I do, the live data is not touched. Also, I have class in place for setting up the database connection, which automatically has the login credentials, as well as auto prepends the "beta_" or "live_" to the database based on the page calling from the subdomain (www. gets live, beta. gets beta)
Again, good coding practices are key to any security!
-Greg
** PS. Also assuming good coding practices are in place
greg posted this at 05:34 — 14th February 2008.
He has: 1,581 posts
Joined: Nov 2005
Thanks for the replies!
Good info, I do have a mirror of sites (large ones anyway) so I can test code before rolling it out on live.
And I am often cringing when testing stuff with the DB. Often I make a dupe of the DB, but that's not always possible.
All my code is very secure. I use ereg or eregi to check for the minimum of slashes and quotes in user inputs and use all the good techniques I have read about, and also a few I make myself. In my opinion, the best security can be the homemade one, as no one else knows about it or how it works.
I do use the real_escape, I read a lot about the problems with addslashes and potential exploits. So with the coming of php6 making magic quotes obsolete, currently I check if function exists for get_magic_quotes_gpc, if yes check if its running, if yes stripslashes, so then I can use the real_escape, that way current sites will still be fine when php doesnt know what get_magic_quotes_gpc is any more.
Would be nicer if magic quotes was never made automatic on *some* servers.
*sigh
Greg K posted this at 12:54 — 14th February 2008.
He has: 2,145 posts
Joined: Nov 2003
Ok, for those who read this and said "Whaaaaaa???" (imitating Chris from Family Guy LOL) I did a little searching and found this:
http://shiflett.org/blog/2006/jan/addslashes-versus-mysql-real-escape-st...
Good thing for programs that let you do search/replaces for all files in a directory
-Greg
greg posted this at 20:24 — 14th February 2008.
He has: 1,581 posts
Joined: Nov 2005
yeah good link. Thats one of the ones I found.
As a comment says, addslashes should mainly only be used for urls
maybe other things, but importantly, never to insert into a DB when the data is from public input
It is relatively safe, not many people will be able to hack with that exploit, but the point is some can and "might", so remember kids, better safe than sorry!
Want to join the discussion? Create an account or log in if you already have one. Joining is fast, free and painless! We’ll even whisk you back here when you’ve finished.