0

I'm fairly new to MySQL and I've been creating test tables, etc.

Anyway, I created a new user, using

CREATE USER 'myUser'@'localhost' IDENTIFIED BY 'myPassword';

Now, when I log into this user, I can SELECT from a table I created with root earlier. Surely this should not be possible? The user has no SELECT permissions, or indeed any permissions. If (logged in as root) I do either:

SHOW GRANTS FOR 'myUser'@'localhost';

Or

SELECT * FROM information_schema.user_privileges WHERE grantee LIKE "'myUser'@%";

I can see they only have USAGE permissions. My understanding is this permission is set to indicate a user has no permissions.

So how is this user able to SELECT from my table?

If I do:

SELECT * FROM information_schema.table_privileges;

No results are returned.

What am I missing here? Are certain things like SELECT implicitly granted to new users? Do I have to explicitly revoke this?

If so, what other permissions are implicitly granted?

Ideally what I'm aiming for is a user that can do nothing except run one (or more) stored procedures that I specify with GRANT EXECUTE ON.

Interminable
  • 1,338
  • 3
  • 21
  • 52
  • It's possible you don't know exactly which user/host you're connecting as; check with `SELECT CURRENT_USER();`. – fenway Jul 27 '13 at 14:45
  • I get 'myUser@localhost' which is what I would expect. However, if things were working as I thought they were going to, shouldn't I be denied permissions to do that as well? To block ALL `SELECT` queries, including that one, and to tables? – Interminable Jul 27 '13 at 15:05
  • that's not a "true" SELECT (i.e. not accessing a table) -- the (pseudo-)grante USAGE allows you to log in to the DB server, but not much else. – fenway Jul 27 '13 at 15:10
  • Well, I'm still able to do `SELECT * FROM MyTable;` with that user as well. – Interminable Jul 27 '13 at 15:39

2 Answers2

0

During my testing, I created a schema called 'test'. Unknown to me, 'test', is actually some sort of reserved database schema name for MySQL. Anonymous users can use it, etc.

As a result, the stuff I was creating in there for testing purposes didn't have certain permission restrictions that other databases would have done.

I can't really find any information on this beyond this page: http://www.greensql.com/content/mysql-security-best-practices-hardening-mysql-tips

That page says that it comes with it, but I'm pretty sure my installation didn't have it by default.

Either way, even with deleting it and then recreating it, if it has the name 'test', it will always be accessible to anonymous users, etc.

I hope this helps someone, I spent too long puzzling over this!

Interminable
  • 1,338
  • 3
  • 21
  • 52
0

It never even occurred to me that you would be creating production tables in a test schema -- but it turns out this is documented in the MySQL refman (emphasis added):

In addition, the mysql.db table contains rows that permit all accounts to access the test database and other databases with names that start with test_. This is true even for accounts that otherwise have no special privileges such as the default anonymous accounts. This is convenient for testing but inadvisable on production servers. Administrators who want database access restricted only to accounts that have permissions granted explicitly for that purpose should remove these mysql.db table rows.

So that explains your find that "Either way, even with deleting it and then recreating it, if it has the name 'test', it will always be accessible to anonymous users, etc."

fenway
  • 416
  • 2
  • 8
  • Is this still accurate with newer versions of MySQL? I don't see entries for this in my MySQL instances – Alf47 Aug 25 '21 at 19:30