Search code examples
mysqlstored-proceduresuser-permissions

MySQL: How can I allow user B to alter user A's procedure without giving them GLOBAL SELECT permission?

So, the initial problem was, in MySQL Workbench, I tried to Alter Procedure and got nothing. No error, just nothing happened. This turns out to be that despite having full permissions for the schema because the definer on the stored procedure is not me, I can't view it's source code.

Scenario:

Database: Bugs
Users: A, B
Permissions: 
grant all privileges on bugs.* to 'A'@'%'
grant all privileges on bugs.* to 'B'@'%'

User A creates a stored procedure

create procedure user_A_procedure ...

We now have a procedure user_A_prodecure who's definer is set to A@% with security_type set to DEFINER

mysql> SHOW PROCEDURE STATUS\G
              Db: bugs
            Name: user_A_prodecure
            Type: PROCEDURE
         Definer: A@%
        Modified: 2018-10-26 10:30:06
         Created: 2018-10-26 10:30:06
   Security_type: DEFINER
         Comment:

character_set_client: utf8 collation_connection: utf8_general_ci Database Collation: utf8_general_ci

The problem is, user B want's to be able to edit the procedure (they don't have access to the original source) but they can't. In MySQL Workbench nothing happens when they try and Alter Procedure, at command prompt they can't see the source code

mysql> SHOW CREATE PROCEDURE user_A_procedure
           Procedure: user_A_procedure
            sql_mode: 
    Create Procedure: NULL
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: utf8_general_ci

This does not return the source code as it would if it was user A executing it, instead it returns NULL.

It turns out that to use MySQL Workbench or SHOW CREATE PROCEDURE to see another definer's stored procedure, the user has to be given GLOBAL SELECT permission [1].

GLOBAL SELECT gives the user the ability to read everything in every database and is NOT a desirable option.

So my question is: How can I allow user A to see/alter user B's procedure without giving them GLOBAL SELECT permission?

References:

  1. https://dev.mysql.com/doc/refman/8.0/en/show-create-procedure.html
  2. https://dev.mysql.com/doc/refman/8.0/en/stored-programs-security.html
Solution

  • The MySQL 8.0 docs are changed what looks like it and contains a error.

    This statement is a MySQL extension. It returns the exact string that can be used to re-create the named stored procedure. A similar statement, SHOW CREATE FUNCTION, displays information about stored functions (see Section 13.7.6.8, “SHOW CREATE FUNCTION Syntax”).

    To use either statement, you must have the global SELECT privilege.

    source https://dev.mysql.com/doc/refman/8.0/en/show-create-procedure.html

    vs MySQL 5.7 docs.

    This statement is a MySQL extension. It returns the exact string that can be used to re-create the named stored procedure. A similar statement, SHOW CREATE FUNCTION, displays information about stored functions (see Section 13.7.5.8, “SHOW CREATE FUNCTION Syntax”).

    To use either statement, you must be the user named in the routine DEFINER clause or have SELECT access to the mysql.proc table. If you do not have privileges for the routine itself, the value displayed for the Create Procedure or Create Function field will be NULL.

    source https://dev.mysql.com/doc/refman/5.7/en/show-create-procedure.html