How to restrict user from modifying data in mysql data base?

Question

We need to deploy application(developed by Java) WAR file in client place which make use of MySql 5.0. But we would like to restrict the client (the application owner, not the webpage visitor) from modifying any data in the database. Is there any way to protect data. The client can make use of the application but they should not be able to change any value in database. How to do that?

Answer 1

Manage Role/User permissions

Create an sql user (you should already have one), which will have only SELECT permission. So it would be something like

GRANT SELECT ON db_base.* TO db_user@'localhost' IDENTIFIED BY 'db_passwd';

http://kb.mediatemple.net/questions/788/HOWTO:+GRANT+privileges+in+MySQL

http://blog.wl0.org/2010/01/managing-mysql-grants/

http://www.ntchosting.com/mysql/grant.html

Check links below for further reading

FOR MySQL

http://stackoverflow.com/questions/333620/best-practice-for-designing-user-roles-and-permission-system

http://www.databasejournal.com/features/mysql/article.php/3311731/An-introduction-to-MySQL-permissions.htm

http://www.devshed.com/c/a/MySQL/MySQL-User-Account-Management/

http://stackoverflow.com/questions/796619/cant-set-permissions-on-mysql-user

http://www.aquafold.com/d7/docs/BD5C99E4-3B55-C812-8318-6338A9A89ED9.html

FOR SQL Server.

http://www.databasejournal.com/features/mysql/article.php/3311731/An-introduction-to-MySQL-permissions.htm

http://www.mssqlcity.com/Articles/Adm/SQL70Roles.htm

http://www.sql-server-performance.com/articles/dba/object_permission_scripts_p1.aspx

http://articles.techrepublic.com.com/5100-10878_11-1061781.html

http://www.databasejournal.com/features/mssql/article.php/2246271/Managing-Users-Permissions-on-SQL-Server.htm

Answer 2

Just write the code accordingly so that the user doesn't have any chance to modify the database? I.e. the code doesn't execute any INSERT or UPDATE and/or controls the access based on a login/role.

I honestly really don't forsee any problems here, or the code must be prone to SQL injection attacks.

Update: The above answer is actually irrelevant since the question is clarified. Turning into Community Wiki.

Answer 3

This is impossible; if you deploy the application at the client, he will have the credentials and will be able to log into the MySQL database and pretent he is the application. And thus he can make any change to the database that your application can.

The only way to solve this securely is to make a tier between the client and your MySQL database, and make sure that you control this so that it is only possible to make 'legal' changes.