[MDEV-19610] stored function with "SQL SECURITY DEFINER" DEFINER requires COLUMN PRIVILEGES Created: 2019-05-28  Updated: 2019-05-28

Status: Open
Project: MariaDB Server
Component/s: Authentication and Privilege System, Stored routines
Affects Version/s: 10.3.15
Fix Version/s: 10.3

Type: Bug Priority: Minor
Reporter: Noel Kuntze Assignee: Alexander Barkov
Resolution: Unresolved Votes: 0
Labels: None
Environment:

Arch Linux

Attachments: File unrequested_column_checking.sql    

 Description   

MariaDB server 10.3.15 requires that the DEFINER, when the stored function has SQL SECURITY DEFINER set, has privileges not just for the whole table, but the specific columns as well.
WHy is that? I expect that the user is allowed to run the SELECT over the columns because the user has the SELECT privilege for the table.

Output:
[...]
SELECT test_function('a', 'b');
mysqltest: At line 48: query 'SELECT test_function('a', 'b')' failed: 1143: SELECT command denied to user 'admin_user'@'localh
ost' for column 'column2' in table 'table2'
not ok

A test SQL script is attached.

Kind regards

Noel Kuntze

 Comments   
Comment by Elena Stepanova [ 2019-05-28 ]

Thanks for the report.
The problem is actually caused not by SELECT, but by DECLARE .. TYPE OF. Here is a simpler test case to demonstrate it:

CREATE OR REPLACE DATABASE db;
 
USE db;
 
CREATE OR REPLACE TABLE `table1` (`column1` INT);
 
CREATE OR REPLACE USER 'admin_user'@'localhost';
 
GRANT ALL ON table1 TO 'admin_user'@'localhost';
 
 
 
--delimiter //
 
CREATE OR REPLACE DEFINER='admin_user'@'localhost' FUNCTION test_function () RETURNS BOOLEAN DETERMINISTIC SQL SECURITY DEFINER
 
BEGIN
 
    DECLARE var_a TYPE OF table1.column1;
 
    RETURN TRUE;
 
END //
 
--delimiter ;
 
 
 
GRANT EXECUTE ON FUNCTION test_function TO 'admin_user'@'localhost';
 
 
 
SELECT test_function();

It also ends with 

query 'SELECT test_function()' failed: 1143: SELECT command denied to user 'admin_user'@'localhost' for column 'column1' in table 'table1'

I coulldn't find anything in documentation about grants required for using TYPE OF, assigning to bar to clarify.

Generated at Thu Feb 08 08:53:00 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.