How to Grant a User the Privilege to Change a Procedure Created by Another User in MySQL

Products

• VMware Tanzu Application Service for VMs

Issue/Introduction

This article discusses how to grant a user the privilege to alter a procedure created by another user in MySQL. 

 

Environment

Cause

Here is a scenario where a user has one user user1 on database db1, and he writes some stored procedures using user1 on the same database. Once MySQL service is unbound and bound again, user1 will be changed to user2, and user2 doesn't have the privileges to alter the existing stored procedures. The user would like to grant user2 some privileges instead of admin privileges to access the stored procedures.   

 

Resolution

1. In database1, log in as user user1 and create a procedure

# mysql -u user1 -p database1
Enter password: 

MariaDB [database1]> create table user(
 -> id mediumint(8) unsigned not null auto_increment,
 -> name char(15) not null default '',
 -> pass char(32) not null default '',
 -> note text not null,
 -> primary key (id)
 -> )engine=Innodb charset=utf8;
Query OK, 0 rows affected (0.14 sec)

MariaDB [database1]> delimiter //
MariaDB [database1]> create procedure proc_name (in parameter integer)
 -> begin
 -> if parameter=0 then
 -> select * from user order by id asc;
 -> else
 -> select * from user order by id desc;
 -> end if;
 -> end;
 -> //
Query OK, 0 rows affected, 1 warning (0.04 sec)

2. Unbind the service instance and then bind it again, or just bind the service instance to another app. You can also create another service instance key, which will let the system create a new user. We'll call it user2

3. Login as the root user and grant the permissions to the new user user2

# mysql -u root -p database1
Enter password: 

MariaDB [database1]> grant create routine on database1.* to 'user2';
Query OK, 0 rows affected (0.00 sec)

MariaDB [database1]> grant alter routine on database1.* to 'user2';
Query OK, 0 rows affected (0.00 sec)

4. Login as the new user user2 and validate the permissions

# mysql -u user2 -p database1
Enter password: 

MariaDB [database1]> ALTER procedure proc_name COMMENT 'COMMENT1';
Query OK, 0 rows affected (0.00 sec)

Impact

Please note that every time you unbind or bind a MySQL service instance, a new user will be created and it will need to be granted the permissions again.