Definer in Mysql Stored Procedures and Routines

The definer is a mysql security tactic used for executing mysql stored procedures. If you’re coming from an SQL Server world it’s similar to the schema that owns the SP. In other words when a definer is set it gives the user executing the SP the same perms as the definer in the context of the SP. This is much easier to explain by example.

Suppose you have a sp that runs a select on Table1. The definer must have select permissions to table1 while the user executing doesn’t need it. When the user is executing the sp they inherit the permissions as the account specified as the definer. This allows your application security to be setup so that application users only need to have execute perms on the SP’s and not perms to underlying objects.

Often when developing a SP this option is left blank and by default it is set to the user that created the sp. The problem with this is that when the SP gets pushed to production, the developer account doesn’t exist, and the SP won’t execute.

There are two workarounds here, the first is to create a standard user with the perms required to access the underlying objects in delivery. Also have this user in Test/Dev/Stress (Different Password then in production) and have this user as the definer. This is of course is the optimal solution, but requires quite a bit a work in developing your security infrastructure. The hack which works while you’re developing this infrastructure is to run the following statement in delivery after release.

update proc set definer='Defineruser@localhost';

This changes the definer to an appropriate user.

Determine the Number of Connections Per Server in Mysql

Coming from the Dynamic View world of sql server 2005 the show commands of mysql take me back to the old days. The primarily issue with the “Show Commands” are they don’t return a result set you can work with. As a DBA one of the most common requests is finding all the processes connected to the server and how many connections each host has. Using show processlist requires capturing this as text then writing some script to parse and aggregate. To avoid this I use the information_schema database. Although not as robust as many rdbs’s there are some good tables to get information from. In this case I like to query the processlist table.


mysql> select host, count(*) as cnt from processlist group by host;
+----------------+-----+
| host | cnt |
+----------------+-----+
| localhost:4819 | 1 |
| localhost:4833 | 1 |
+----------------+-----+
2 rows in set (0.00 sec)

However the challenge here is that concatenated with the host name is the thread id, so you still can’t get a proper count on how many connections each server has. To solve this I used the ultra cool substring_index function of mysql. Here you pass it the string to be searched, the string to be found, and the count of occurrences you want to find.

In this example I was able to group by this function.

mysql> select substring_index(host,':',1) as hostname, count(*) as cnt from processlist group by substring_index(host,':',1);
+-----------+-----+
| hostname | cnt |
+-----------+-----+
| localhost | 2 |
+-----------+-----+
1 row in set (0.02 sec)

Here’s a link to mysql’s documentation

http://dev.mysql.com/doc/refman/5.1/en/string-functions.html#function_substring-index

Pumping Out Warnings When Using Load Data From the Command Line

I’m currently writing a SQL Server SSIS package that leverages the Load data infile command of mysql, to import data. The problem I’ve been running into is that I need to find out any warnings that occur and can be reviewed. Since I’m calling the mysql executable from the command line, I’m unable to capture this data. The command currently looks like this:

C:\>mysql -uuser -ppassword --execute="load data local infile ‘c:\\test.txt'
Replace into table test Fields terminated by ',' " -hServer DB

To work around this I’ve added a second command inside the batch that leverages the “show warnings” command, and now looks like this.

C:\>mysql -uuser -ppassword --execute="load data local infile ‘c:\\test.txt'
Replace into table test Fields terminated by ','; show warnings " -hServer DB

This now pumps out the results. In my case within ssis I pump this out to a text file and email it as part of the results such as


C:\>mysql -uuser -ppassword --execute="load data local infile ‘c:\\test.txt'
Replace into table test Fields terminated by ',' " -hServer DB >c:\testout.txt