Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Won't Fix
-
10.5.23, 10.5.24
Description
Step to reproduce :
Create a new database:
CREATE DATABASE test_db; |
Create two tables with foreign keys (optional but it also shows that not only primary keys are not returned):
CREATE TABLE `client` ( |
`id` int(11) NOT NULL, |
`name` varchar(25) DEFAULT NULL, |
PRIMARY KEY (`id`) |
);
|
CREATE TABLE `order` ( |
`id` int(11) NOT NULL, |
`client_id` int(11) DEFAULT NULL, |
PRIMARY KEY (`id`), |
KEY `client_id` (`client_id`), |
CONSTRAINT `fk_order_client` FOREIGN KEY (`client_id`) REFERENCES `client` (`id`) |
);
|
Create a new user with no specific privileges:
CREATE USER test; |
Connect as user test and select table constraints:
SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS where TABLE_SCHEMA = 'test_db'; |
Result:
| CONSTRAINT_CATALOG | CONSTRAINT_SCHEMA | CONSTRAINT_NAME | TABLE_SCHEMA | TABLE_NAME | CONSTRAINT_TYPE |
| def | test_db | PRIMARY | test_db | client | PRIMARY KEY |
| def | test_db | PRIMARY | test_db | order | PRIMARY KEY |
| def | test_db | fk_order_client | test_db | order | FOREIGN KEY |
Grant SELECT privilege on test_db table to the test user:
GRANT SELECT ON test_db.* to test; |
Select table constraints again:
SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS where TABLE_SCHEMA = 'test_db'; |
Result:
Empty set (0.0007 sec) |
Additional informations
I have tested all privileges and my observations is that the user need at least one of this privileges on the table to get back a result set (or none at all):
ALTER, CREATE, DELETE, DROP, INDEX, INSERT, UPDATE, REFERENCES, TRIGGER, SHOW VIEW
Attachments
Issue Links
- relates to
-
-
- Closed
-
Activity
Hi Anel,
Strange. For information, we are running the Windows build. I haven't tested on Linux yet.
I suspect this is a consequences of how privilege matching is implemented in MariaDB is it goes way back to MySQL 3.22 of the last millennium.
When you haven't granted any privileges to your test user, it gets all privileges to the test_db because the default installation creates an anonymous (wildcard) account with all privileges on test and test\​_%.
But when you have granted something to your user, then the wildcard account is no longer considered, because there's an exact match for that user and that db.
We cannot change this matching behavior, it was around for too long and an unknown number of applications depend on it.
But in 10.11 we've implemented the SQL Standard compatible GRANT TO PUBLIC (MDEV-5215) syntax and switched to it for granting access to test and test\​_%. And this wildcard-matching logic is no longer used in the default installation.
Hi,
yes this is expected behavior according to the
MDEV-32500(547dfc0e01bb) that user should have any non-select privilege on table or any of its columns.commit 547dfc0e01bb5acbe070728706afb8a116b100e2Author: Sergei Golubchik <serg@mariadb.org>Date: Thu Oct 19 17:02:37 2023 +0200MDEV-32500 Information schema leaks table names and structure to unauthorized usersstandard table KEY_COLUMN_USAGE should only show keys wherea user has some privileges on every column of the keystandard table TABLE_CONSTRAINTS should show tables wherea user has any non-SELECT privilege on the table or on any columnof the tablestandard table REFERENTIAL_CONSTRAINTS is defined in terms ofTABLE_CONSTRAINTS, so the same rule applies. If the userhas no rights to see the REFERENCED_TABLE_NAME value, it should be NULLSHOW INDEX (and STATISTICS table) is non-standard, but it seemsreasonable to use the same logic as for KEY_COLUMN_USAGE.However, your test case caught my attention and I tried to recreate the scenario when the user is created and where you get still result even though user has no privileges.
I obtained as expected (empty set):
# Create tables with root (10.5)$ docker exec -it mariadb-cont mariadb -urootServer version: 10.5.24-MariaDB-1:10.5.24+maria~ubu2004 mariadb.org binary distributionMariaDB [(none)]> SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS where TABLE_SCHEMA = 'test_db';+--------------------+-------------------+-----------------+--------------+------------+-----------------+| CONSTRAINT_CATALOG | CONSTRAINT_SCHEMA | CONSTRAINT_NAME | TABLE_SCHEMA | TABLE_NAME | CONSTRAINT_TYPE |+--------------------+-------------------+-----------------+--------------+------------+-----------------+| def | test_db | PRIMARY | test_db | order | PRIMARY KEY || def | test_db | fk_order_client | test_db | order | FOREIGN KEY || def | test_db | PRIMARY | test_db | client | PRIMARY KEY |+--------------------+-------------------+-----------------+--------------+------------+-----------------+3 rows in set (0.000 sec)MariaDB [test_db]> create user test@localhost;Query OK, 0 rows affected (0.015 sec)# With test user$ docker exec -it mariadb-cont mariadb -utestServer version: 10.5.24-MariaDB-1:10.5.24+maria~ubu2004 mariadb.org binary distributionMariaDB [(none)]> select current_user;+----------------+| current_user |+----------------+| test@localhost |+----------------+1 row in set (0.000 sec)MariaDB [(none)]> show grants for current_user;+------------------------------------------+| Grants for test@localhost |+------------------------------------------+| GRANT USAGE ON *.* TO `test`@`localhost` |+------------------------------------------+1 row in set (0.000 sec)MariaDB [(none)]> SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS where TABLE_SCHEMA = 'test_db';Empty set (0.000 sec)# Grant privilege# MariaDB [(none)]> show grants for current_user;+---------------------------------------------------+| Grants for test@localhost |+---------------------------------------------------+| GRANT USAGE ON *.* TO `test`@`localhost` || GRANT SELECT ON `test_db`.* TO `test`@`localhost` |+---------------------------------------------------+2 rows in set (0.000 sec)MariaDB [(none)]> SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS where TABLE_SCHEMA = 'test_db';Empty set (0.000 sec)# revoke grantsMariaDB [(none)]> revoke select on test_db.* from test@localhost;Query OK, 0 rows affected (0.015 sec)MariaDB [(none)]> show grants for test@localhost;+------------------------------------------+| Grants for test@localhost |+------------------------------------------+| GRANT USAGE ON *.* TO `test`@`localhost` |+------------------------------------------+1 row in set (0.000 sec)# Add non-select (like create) privilege on tableMariaDB [(none)]> grant create on test_db.* to test@localhost;Query OK, 0 rows affected (0.015 sec)MariaDB [(none)]> show grants for test@localhost;+---------------------------------------------------+| Grants for test@localhost |+---------------------------------------------------+| GRANT USAGE ON *.* TO `test`@`localhost` || GRANT CREATE ON `test_db`.* TO `test`@`localhost` |+---------------------------------------------------+2 rows in set (0.000 sec)MariaDB [(none)]> show grants for current_user;+---------------------------------------------------+| Grants for test@localhost |+---------------------------------------------------+| GRANT USAGE ON *.* TO `test`@`localhost` || GRANT CREATE ON `test_db`.* TO `test`@`localhost` |+---------------------------------------------------+2 rows in set (0.000 sec)MariaDB [(none)]> SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS where TABLE_SCHEMA = 'test_db';+--------------------+-------------------+-----------------+--------------+------------+-----------------+| CONSTRAINT_CATALOG | CONSTRAINT_SCHEMA | CONSTRAINT_NAME | TABLE_SCHEMA | TABLE_NAME | CONSTRAINT_TYPE |+--------------------+-------------------+-----------------+--------------+------------+-----------------+| def | test_db | PRIMARY | test_db | order | PRIMARY KEY || def | test_db | fk_order_client | test_db | order | FOREIGN KEY || def | test_db | PRIMARY | test_db | client | PRIMARY KEY |+--------------------+-------------------+-----------------+--------------+------------+-----------------+3 rows in set (0.000 sec)