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
