Mysql – How to export specific columns from a SQL table

In reality, those three user accounts are actually quite dangerous. They pose a very great threat to test databases.

Unfortunately, mysql comes with full access to test databases. How can you find them ?

Run this query:

 SELECT user,host,db from mysql.db;

Upon installation of mysql, you will see two rows that give full access to any database named 'test' or whose first 5 characters are 'test_'.

Why is this a problem ???

Try running this command:

$ mysql -u'' -Dtest

You will have connected the test database without a password.

Now, create a table and load it with a row:

CREATE TABLE mytable (a int);
INSERT INTO mytable VALUES (1);

OK, big deal. Could you double this table in size 30 times ???

INSERT INTO mytable SELECT * FROM mytable;
INSERT INTO mytable SELECT * FROM mytable;
... (30 times)
INSERT INTO mytable SELECT * FROM mytable;

What do you get ?? A table with 1,073,741,824 rows. Easily, 4GB+.

Imagine creating any table of any size. How about creating a bunch of tables in the test database and freely accessing them at will ?

The best thing you can do under these circumstances is to run this query:

DELETE FROM mysql.db;

and restart mysql. Then, those three accounts will be properly rendered inoperative.

Give it a Try !!!

UPDATE 2011-09-12 10:00 EDT

This delete:

DELETE FROM mysql.db;
FLUSH PRIVILEGES;

is just what you need for an initial installation. However, if you have users already established, You can run this instead:

DELETE FROM mysql.db WHERE db IN ('test','test_%');
FLUSH PRIVILEGES;

This will remove the specific two DB permissions.

As I mentioned in my answer, the three permissions are very dangerous for test databases. Running this DELETE will neutralize those accounts for having full rights to test databases.

I've created an open source project to create MS SQL Server database documentation.

http://jeremykdev.github.io/SqlServerDatabaseDocumentationGenerator