Details
-
Bug
-
Status: Confirmed (View Workflow)
-
Minor
-
Resolution: Unresolved
-
10.11, 11.4, 11.8, 12.3, 13.1, 12.3.3
-
None
-
Linux x86_64
Description
I get a visible foreign-key row with REFERENCED_TABLE_NAME set to NULL when the effective user has global SELECT and schema-level SELECT, REFERENCES privileges. The query therefore exposes the constraint but hides the referenced table even though the user has REFERENCES on the schema.
Run the following in one MariaDB client session as an administrative account. The SQL SECURITY DEFINER procedure makes the restricted account the effective identity for the information-schema query while allowing the procedure to be invoked from the setup session.
DROP DATABASE IF EXISTS library; |
DROP USER IF EXISTS 'reader'@'localhost'; |
CREATE DATABASE library; |
|
|
CREATE TABLE library.author ( |
id INT PRIMARY KEY |
) ENGINE=InnoDB;
|
|
|
CREATE TABLE library.book ( |
id INT PRIMARY KEY, |
author_id INT, |
CONSTRAINT book_author_fk |
FOREIGN KEY (author_id) |
REFERENCES library.author(id) |
) ENGINE=InnoDB;
|
|
|
CREATE USER 'reader'@'localhost'; |
GRANT SELECT ON *.* TO 'reader'@'localhost'; |
GRANT SELECT, REFERENCES ON library.* |
TO 'reader'@'localhost'; |
|
|
DELIMITER //
|
CREATE DEFINER='reader'@'localhost' |
PROCEDURE library.inspect_fk() |
SQL SECURITY DEFINER
|
BEGIN
|
SELECT CURRENT_USER() AS effective_user, |
CONSTRAINT_NAME, TABLE_NAME, REFERENCED_TABLE_NAME
|
FROM information_schema.REFERENTIAL_CONSTRAINTS |
WHERE CONSTRAINT_SCHEMA='library'; |
END// |
DELIMITER ;
|
|
|
GRANT EXECUTE ON PROCEDURE library.inspect_fk |
TO 'reader'@'localhost'; |
|
|
CALL library.inspect_fk();
|
|
|
DROP DATABASE library; |
DROP USER 'reader'@'localhost'; |
On MariaDB 12.3.3, the call returns:
effective_user CONSTRAINT_NAME TABLE_NAME REFERENCED_TABLE_NAME
|
reader@localhost book_author_fk book NULL
|
The effective identity is reader@localhost, and the foreign-key row itself is visible. Because that account has REFERENCES on every table in library, REFERENCED_TABLE_NAME should contain author rather than NULL.
This occurs when foreign-key metadata is read under an identity that combines global SELECT with schema-level REFERENCES privileges.