Ads were blocked - no problem. But keep in mind that developing HeidiSQL, user support and hosting takes time and money. You may want to send a donation instead.

Bug + new user can see all tables without permmision

Blanca_PJ's profile image Blanca_PJ posted 1 month ago in General Permalink

Hi everyone.

I'm trying to create a new users using HeidiSQL GUI for it. I created a test database with two tables and I created a new user. This new user only have permissions to select and insert into ONLY ONE of the tables, HeidiSQL creates the user ok but when logging in with that new user, the user sees everything (other database and others tables) and can modify the table you shouldn't even be able to see.

Does anyone know what this error an if it could be solve? I have tested on several computers and I´ve reproduced the error in some yes and in others no.

Thaks in advance. Blanca

ansgar's profile image ansgar posted 1 month ago Permalink

In Tools > User manager, what privileges are checked for that user?

And what server and version is it?

Blanca_PJ's profile image Blanca_PJ posted 1 month ago Permalink

Hi Ansgar.

MariaDB 10.5 + HeidiSQL version 11.0.0.5919

The new user has´t any global privilege selected. Only one database.table has been selected with checks selected SELECT and INSERT.

Thanks in advance. Blanca

ansgar's profile image ansgar posted 1 month ago Permalink

You could update HeidiSQL to the latest build, probably there is some fix in it for the user manager. I don't recall there was something, but I tend to forget things...

Blanca_PJ's profile image Blanca_PJ posted 1 month ago Permalink

Hi again.

Thanks for the advice but after update HeidiSQL to 11.1.0.6116 it still doesn't work. I´ve created a user with permissions only to a specific table (database.table) but that user be able to see all the tables in the database.

Thanks again for your help.

ansgar's profile image ansgar posted 1 month ago Permalink

Please log in with that user in HeidiSQL, and run this command in a query tab, report back what it says:

SHOW GRANTS;

I just did the same, using HeidiSQL's user manager on MariaDB 10.3. After logging in, I can just see this special table, plus information_schema (which is normal).

Blanca_PJ's profile image Blanca_PJ posted 1 month ago Permalink

Hi Ansgar.

I have attached an image. I created the user "Test_User" that should only see the "miguelbbdd" database and the CLIENTE table, but the user is also seeing another database that he shouldn't and the user can access and modify it (DML and also DDL!).

I tried as well with commands:

GRANT USAGE ON . TO 'Test_User'@localhost IDENTIFIED BY 'Test_User'; GRANT SELECT,INSERT ON miguelbbdd.cliente to 'Test_User'@localhost; FLUSH PRIVILEGES; SHOW GRANTS FOR 'Test_User'@localhost;

And the permission problem is the same.

Thanks in advance for your help.

Blanca

1 attachment(s):
  • Bug_Heidi
ansgar's profile image ansgar posted 1 month ago Permalink

Ok, seeing information_schema is normal, that's a system schema for all users.

The test% database privileges are also documented:

https://dev.mysql.com/doc/refman/5.6/en/default-privileges.html

... 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.

You could remove these quite dangerous fallback privileges:

DELETE FROM mysql.db WHERE Db LIKE 'test%';
FLUSH PRIVILEGES;
Blanca_PJ's profile image Blanca_PJ posted 1 month ago Permalink

Hi Ansgar.

That was the problem, solved! Thank you very much for your help.

Blanca

Please login to leave a reply, or register at first.




Ads were blocked - no problem. But keep in mind that developing HeidiSQL, user support and hosting takes time and money. You may want to send a donation instead.