Database security

What do you consider too much

[Tommybs]

Although I'm familiar with mysql and can write a few queries, I've never really looked too much into the administration side. So many tutorials on the net and in books generally have you setup a connection string such as the following:

$dbh = mysql_connect($user,$pass,$host);

but how much is security in this area over looked?

Obviously there are a number of functions to properly sanitize data before it is entered (mysql_real_escape_string etc.) and there are the options of storing the db connection info above the document root, but how many people generally setup more then once connection string parameter?

e.g

$dbr = mysql_connect($r_user, $r_pass,$host);

In this example $r_user would be a mysql user that only has READ permissions. It would be interesting to know how many of you actually set these permissions or do you consider it overkill? Do you base each site on it's own merits or has it just become standard to not consider this?

A wordpress site for instance could have a front-end db_user that only has read access to post tables and write to comments.

Any thoughts on this and do people believe it should be more documented to users that these options exist?

[topr8]

>>It would be interesting to know how many of you actually set these permissions or do you consider it overkill?

absolutely i do, it's not overkill, it's easy to do. the 'web' user should only have permissions to do what it needs to do and for the tables it needs to do it on, so should any other user you have.

i also make a lot of use of stored procedures and functions, which though not as mature as those available for sqlServer are still useful and used correctly also add a layer of 'security'

[Tommybs]

I am familiar with Stored Procedures in sqlServer but I've never used them with mysql and php as they seem quite cumbersome to setup and configure. Are there any benefits to this above mysqli or are they in essence the same thing?

Do you notice much of a performance increase in Sprocs VS Mysqli VS inline mysql?

Do you also think that a lot more attention should be paid towards these extra layers in security for sites, or do you believe those that actually need to be aware of it are?

i.e a blog site that gets hacked might be a big blow to a user, but the security implications might not be as big compared to an ecommerce site. Then again casual users might be comfortable enough to setup mysql users but just aren't aware of the benefits..

[topr8]

i've come from sqlServer to mySQL, so it seemed natural for me to use them, the benefits for me are that i can just call the sp, if multiple pages call the same sp then if i optimise the sql later then i only have to change it once.
to me it also makes very complex queries, easier to manage.
for straightforward select queries then it certainly is simpler just to do it inline in php, however for consistency i use only sp's ... even if at times it is not the best thing to do, that way i don't have to work about permissions to specific tables, i just blanket disallow users except for being able to call sp's.

>> or do you believe those that actually need to be aware of it are?

absolutely not, they aren't. there are a great many developers working on their own, either just with their own sites or a few clients and they are not aware of the benefits of different user permissions, when i started i learnt just from books and online tutorials, just as many others do ... and i don't remember learnign much about that security stuff at all - i got lucky and wised up before a disaster struck, however you only have to read the number of threads started here every time there is a new wave of sql injection attacks doing the rounds to see how widespread the issue is and how setting simple permissions could have saved a lot of people.

[Tommybs]

I work with sprocs and sqlServer during my job and use mysql for personal sites in my spare time, having looked at sprocs for mysql, they just don't seem to be quite there yet with regards to accessing them in php. Though to be fair I haven't really given them a chance, and I guess once they are there, they're there...

I guess a lot of the limitations with users actually come from shared hosting packages. Many people use a console to setup their DB users and they're automatically granted rights for everything in the DB. It's certainly something I'm more aware of now, but then again I've got my own server so these issues are a bit more on my radar these days. It's certainly something I'm paying a lot more attention to now and I will definitely try to inform others when answering queries around here