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?
-
@BalusC:Thanks for editing and making it understandable :)Paul– Paul2010-06-01 04:34:09 +00:00Commented Jun 1, 2010 at 4:34
Add a comment
|
3 Answers
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.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.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
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.
8 Comments
Konerak
And what if the credentials are for a user without modifying rights? Or only certain stored procedures are used?
Paul
What has to done between client and MySQL database. Pl mention clearly whether it is possible or not!
laanwj
If the credentials are for a user without modifying rights, the application (and stored procedures) cannot makes changes either. This is contrary to the question.
BalusC
@wump: a WAR file signifies a web application. So I bet that the client doesn't run at the same machine as the server. The only wall is the Java webapp code and the control is fully at the server side (at the developer). So unless the code is prone to SQL injections, there should be absolutely no problem and MySQL user/roles has absolutely no influence on this. It's all how you control the access to the webapp itself (by HTTP) and how the webapp is programmed.
BalusC
@Paul: you should have been more clear on that :/ Then the answer of @wump is correct. If the DB runs at the client, you cannot restrict the control in any way. It's like as attempting to restrict the control all of the DB's and servers at my laptop right here. Think logically.
|
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.