Changing Owner in MySQL Procedure

Being a DBA our regular job is related to keep production high available and secure. To secure database we majorly gives rights using IP. In best practice we are never supposed to create user from open to all network (%).

We should create user by using his/her/it connecting ip.  Command should be like below.

Grant "Required rights"  on "databasename"."object Name"  to "username"@’Connection IP’ identified by ‘ "password"’;

Routines in MySQL give additional security. User will not be able to see table name /Actual data. For access on routines (Stored procedure /Function call routines in MySQL), we need to provide some additional rights.

Execute: - To execute procedure/function

Create routine: - To Create procedure/Function

Alter routines: - To Alter routines.

With procedure additional security has been provided by using definer. A definer in MySQL is a user who creates MySQL procedure.   As user is always created by using IP/IP Range so in case any change in network ip will make routines unusable. Users will not be able to execute those procedures. To use these routines you need to change definer. You can change definer by using different ways

1.       Drop procedure and create new procedure

2.       Change definer in mysql.proc table.

Second option is easy to use. Below is the command to change definer.

Update mysql.proc set definer=’New definer’ where definer=’old definer’

Update mysql.proc  set definer=   ‘anil@54.254.24.132’ where definer= ‘anil@localhost’.

It will change definer of stored procedure and you can procedures again.

Happy Developing  J