MYSQL How do i restrict Update,Delete,Isert,Drop privileges from user?

Question

Hi

I am with godaddy, and i have mysql 5.0 phpadmin access.
I have created 1 database, the user for this database obviously has full access to the tables.

Ok now i use the above username/password/dbname details in db.php as follows, to give site visitors access to my database data via php pages(normal right!):

$DB_SERVER = "h50mysql53.secureserver.net";
$DB_USER = "userxxxx";
$DB_PASSWORD = "passwordxxx";
$DB_NAME = "userxxxx"; // same as user created by default by godaddy

$link = mysql_connect($DB_SERVER, $DB_USER, $DB_PASSWORD);
mysql_select_db($DB_NAME);
?>




Problem:

1. I want to revoke drop,delete,insert,update privileges from this user, so naughty visitors can't misbehave and mess about with my database as i have some that try.

Thing is i can't create another standalone user with restricted access, godaddy don't give root access in mysql either, and i can't revoke these privileges from the user that owns the database.

How can i restrict the privileges: drop,delete,insert,update in any other way?


Thanks in advance

Tovia Singer

Answer 1

Hi Tovia!

Leo has a good answer, but it depends on your server, and Godaddy is "not" what I would call the best one...

I would suggest you allow users to access the DB through a login-page on your front end website.

The users are stored in a table, and YOU give them your own access level. They then access the DB through a PhP code, where, for example, you have:

if ($userlevel > 3) $allowwrite = true else $allowwrite = false;

and so on. This will allow you to "grant access" with very fine parameters, one for each table, for each read, write, make, select and so on.

That way, on the other hand, lets YOU in control of the data-base: NO-one needs username/dbname/localhost/pwd...

It makes the code more complex, but MUCH safer!

Do not forget: They can't access Php scripts, but they KNOW the various Php Standard settings. So, if I have an address book on my PC, I will NOT call it "my addresses". I do not use any of "mydocuments", "mypictures", provided by MS for the unaware. Nor do I give my server's access to anyone, but through MY sequence of events! (and I change it regularly!)

IF you allow someone to upload (after logging-in) something to your server, load it in a "safe" directory. Then apply all possible checks provided by php. Eliminate anything that is even suspicious. Empty that directory contents' when the user leaves the site (use ).

One last word: don't put real addresses on your request! :-))

(DB_SERVER)

Answer 2

You would not be able to restrict the privileges, as I am guessing that godaddy don't give a user with GRANT privileges.



The key is to make sure that your queries are well designed so that this is not possible.



Use PHP to validate your data.



One technique is to use rawurlencode and rawurldecode of everything that you add to a database, which will stop any unwanted hacks. If you use a statement like:



SELECT * FROM users WHERE user_id = $id



then you will not have any issues.



If you want more help with your query you can contact me via my website http://www.dazines.co.uk

Answer 3

Hi Tovia,



in order to restrict access to certain db functions, you need to use a Grant statement for the naughty user(s) as follows:



grant [privilege_typpe] on [smdb.*] to [smusr@"%"] identified by ['pwrd'];



Let's explain that:

1. [priv_type] is normally one of: select,insert,update,delete,create,drop

(for a full list of priv_types and a good article on the subject see http://dev.mysql.com/doc/refman/5.0/en/grant.html)



You need to grant the restricted users only select privileges.



2. [smdb.*] is the name of your database; the * represents here all the tables in it. If you want to grant access to a specific table, replace the asterisk with the table's name.



3. [smusr] is the name of the user you want to restrict. the % represents any host.



4. [pwrd] is the password this user is using to access the db.



For instance:



GRANT select on userxxx.* to 'userxxx' @ 'h50mysql53.secureserver.net' identified by 'passwordxxx';



Alternatively, you can use the REVOKE statement. Syntax is:



REVOKE [priv_type] on [smdb.*] from [smusr];

Again, for more info look at the mysql reference manual at

http://dev.mysql.com/doc/refman/5.0/en/revoke.html



Hope this helps :)

Leo