Take the 2-minute tour ×
Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

I created a new user in mysql using phpmyadmin that only has the SELECT privilege. However, this user can DROP tables, delete records, change data (INSERT, UPDATE). I have flushed the privileges, logged in and out etc but no change.

Any ideas?

Many thanks.

share|improve this question
    
Can you post the output of show grants for user@host? –  Khaled Jan 17 '12 at 9:31
    
What database did you create the table in ??? test ??? –  RolandoMySQLDBA Jan 17 '12 at 21:01

2 Answers 2

OK I have some disturbing news for you. If you wrote the tables in a test database, you need to make certains changes.

In a default installation of mysql, test databases are allowed full access to anybody.

To find this out, run select user,host,db from mysql.db where db like 'test%';

mysql> select user,host,db from mysql.db where db like 'test%';
+------+------+---------+
| user | host | db      |
+------+------+---------+
|      | %    | test    |
|      | %    | test\_% |
+------+------+---------+
2 rows in set (0.00 sec)

mysql>

If you see this display when you run this query, DELETE THESE TWO ROWS IMMEDIATELY !!! (Security hole). If you don't remove them, note that anonymous users have unlimited firepower on a test database.

Please run these commands to remove that access:

DELETE FROM mysql.db WHERE db LIKE 'test%';
FLUSH PRIVILEGES;

Please note what MySQL 5.0 Certification Study Guide says on Page 498 Paragraph 6 in its bulletpoints, especially the last bulletpoint:

On Unix, MySQL comes with a mysql_secure_installation script that can perform several helpful security-related operations on your installation. The script has the following capabilities:

  • Set a password for the root accounts
  • Remove any remotely accessible root accounts.
  • Remove the anonymous user accounts. This improves security because it prevents the possibility of anyone connecting to the MySQL server as root from a remote host. The results is that anyone who wants to connect as root must first be able to log in on the server host, which provides an additional barrier against attack.
  • Remove the test database (If you remove the anonymous accounts, you might also want to remove the test database to which they have access).
share|improve this answer

Either you have given that user more privileges than just SELECT, or you have started MySQL with --skip-grant-tables option.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.