[MDEV-13676] Field "create Procedure" is NULL, even if the the user has role which is the definer. (SHOW CREATE PROCEDURE) Created: 2017-08-30  Updated: 2020-08-25  Resolved: 2017-10-11

Status: Closed
Project: MariaDB Server
Component/s: Authentication and Privilege System
Affects Version/s: 10.1.26, 10.2.8
Fix Version/s: 10.0.33, 10.1.29, 10.2.10, 10.3.3

Type: Bug Priority: Critical
Reporter: Richard Stracke Assignee: Vicențiu Ciorbaru
Resolution: Fixed Votes: 0
Labels: roles
Environment:

tested on official docker container

Sprint: 10.2.10

 Description   

show create procedure gives NULL in the Field "Create Procedure" if the permission is missing.

It works, if the caller is the owner of the proedure or with the SELECT right of mysql.proc

This works not, if the owner is a role.

To reproduce:

create database rtest;
 
create role db_owner_rtest;
 
grant all privileges  on rtest.*  to db_owner_rtest;
 
 
 
 
 
create user user1;
 
grant db_owner_rtest to user1;
 
 
 
set default role db_owner_rtest for user1;

Login as user 1:

use rtest;
 
 
 
set role  db_owner_rtest;
 
 
 
DELIMITER //
 
DROP PROCEDURE IF EXISTS user1_proc;
 
CREATE DEFINER=current_role() PROCEDURE user1_proc()
 
SQL SECURITY INVOKER
 
BEGIN
 
 SELECT NOW(), VERSION();
 
END;//
 
DELIMITER ;

Role db_owner_rtest is the owner and user1 has this role,but

show create procedure db_owner_rtest;

gives this output.

MariaDB [rtest]> show create procedure user1_proc;
 
+------------+-------------------------------------------------------------------------------------------+------------------+----------------------+----------------------+--------------------+
 
| Procedure  | sql_mode                                                                                  | Create Procedure | character_set_client | collation_connection | Database Collation |
 
+------------+-------------------------------------------------------------------------------------------+------------------+----------------------+----------------------+--------------------+
 
| user1_proc | STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION | NULL             | latin1               | latin1_swedish_ci    | latin1_swedish_ci  |
 
+------------+-------------------------------------------------------------------------------------------+------------------+----------------------+----------------------+--------------------+
 
1 row in set (0.00 sec)
 
 



 Comments   
Comment by Vicențiu Ciorbaru [ 2017-10-09 ]

Hi Sergei!

Can you review the patch for this bug?

http://lists.askmonty.org/pipermail/commits/2017-October/011541.html

If we don't want to support allowing indirectly granted roles to see procedure code, we can skip the additions to sql_acl.cc entirely and just leave the simple strcmp with current_role.

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