Which permissions for MySQL Database

ringo747 · 6 Nov 2007 at 23:46

Hey guys,
Over the past while I have been playing around with MySQL databases just on my machine but I now want to try and work with some live versions on my hosting. I am using the mysql admin tool within my hosting control panel to set up users and so on. I have set up a user with 'grant all privleges'. Is this secure enough to use within PHP scripts on my website or for a public database user should I be reducing these privileges somewhat? I have googled on this but can't find the info I am looking for. Basically what I need to know is when I set up my PHP file containing the database connection details do I use a user there with full privileges or can I use a special user just for that purpose? Thanks in advance

SiriusB · 7 Nov 2007 at 01:27

For the best possible security logically you would only assign the privileges absolutely required by that particular script. So yes, create a new user just for your script and give it only the access it needs.

That way, should anything happen [someone hacks your script/SQL injection etc] the damage is prevented/limited.

Eriedor · 7 Nov 2007 at 10:24

Even better you should write stored procs and only give the script access to execute them. Then no malicious sql can be run.

SiriusB · 7 Nov 2007 at 10:37

Aren't stored procedures faster than doing it via SQL too?

EDIT: That is to say, executes faster.

ringo747 · 7 Nov 2007 at 22:33

Thanks for the info guys. I think I will have a file with database connection details and it requires a database username and password. What permissions would be required though? I will disable options like 'drop database' etc to make it more secure. So really I should have a full access account to admin the db myself, but for my website my db user should only have fewer privileges? Am i correct on this? Thanks