CODEWITHSUNDEEP
mysqlsqldatabase
Paul
Paul

Reputation: 1390

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

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?

Upvotes: 1

Views: 1070

Answers (3)

BalusC
BalusC

Reputation: 1108632

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.

Upvotes: 2

hgulyan
hgulyan

Reputation: 8239

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

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/

Can't 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

https://web.archive.org/web/1/http://articles.techrepublic%2ecom%2ecom/5100-10878_11-1061781.html

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

Upvotes: 5

laanwj
laanwj

Reputation: 4377

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.

Upvotes: 3

Related Questions