How do I restrict MySQL user from dropping, and adding tables and databases

Question

I am trying to find the SQL command to create a user that can read and write in tables found in one database, but is not allowed to do anything else.

Also if you can recommend other limits I should add to the MySQL user I would appreciate it.

The reason I am asking is because one of my tables was dropped and I have no idea how... I think it's PHP but I am not sure... Maybe someone connected via my SSH... The thing is all my mysql logs are empty and no evidence of a another user trying to do something are to be found in the other logs...

So for now I am assuming there is something wrong with my PHP... This is why I would like to limit it to only adding, removing, and dropping rows and content in the rows... But not more. So that I don't loose my table again or my other DBs.

If you have PHP admin, it should be fairly easy, to do. But, you haven't stated if you have it or not — samayo
– samayo, Commented Jul 14, 2013 at 21:26
Have you checked your code for possible susceptibility to an SQL injection attack? — user1864610
– user1864610, Commented Jul 14, 2013 at 21:27

John WH Smith · Accepted Answer · 2013-07-14 21:25:30Z

3

Create the user using CREATE USER, and specify the privileges using GRANT.

http://dev.mysql.com/doc/refman/5.1/en/grant.html (click here for a list of MySQL privileges)

If you have phpMyAdmin running, the interface will give you a complete set of privileges to check/uncheck :)

For a basic web application, you'll only need SELECT, INSERT, UPDATE and DELETE :)

If you think there was a security issue with your application, check this page to know more.

answered Jul 14, 2013 at 21:25

John WH Smith

2,7831 gold badge23 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

jnbdz Over a year ago

I did not install it [phpMyAdmin] to limit the possibility of being hacked.

John WH Smith Over a year ago

Then process MySQL queries (CREATE USER and GRANT) from the command line (mysql client). You just need to know the syntax :) You can start a mysql client with the mysql command. Use the --user flag to set a username, and -p to warn the client you'll have to enter a password afterwards.

jnbdz Over a year ago

I am using Laravel and it is supposed to protect my app... Maybe I need to configure something? Not sure I will check. I have also added access log... I did not have it configured so I have no idea if it was coming from there.

John WH Smith Over a year ago

There is the Laravel documentation about its internal security : four.laravel.com/docs/security ; I don't use this FW, therefore I can't be more useful :p

jnbdz Over a year ago

That's ok... :) This was a question on MySQL.

Collectives™ on Stack Overflow

How do I restrict MySQL user from dropping, and adding tables and databases

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related