When someone has the clearance to call a stored procedure, the grants for the user who called the stored procedure must be checked.
Here is the description of mysql.proc:
+----------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------+-----+---------------------+-----------------------------+
| Field | Type | Null | Key | Default | Extra |
+----------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------+-----+---------------------+-----------------------------+
| db | char(64) | NO | PRI | | |
| name | char(64) | NO | PRI | | |
| type | enum('FUNCTION','PROCEDURE') | NO | PRI | NULL | |
| specific_name | char(64) | NO | | | |
| language | enum('SQL') | NO | | SQL | |
| sql_data_access | enum('CONTAINS_SQL','NO_SQL','READS_SQL_DATA','MODIFIES_SQL_DATA') | NO | | CONTAINS_SQL | |
| is_deterministic | enum('YES','NO') | NO | | NO | |
| security_type | enum('INVOKER','DEFINER') | NO | | DEFINER | |
| param_list | blob | NO | | NULL | |
| returns | longblob | NO | | NULL | |
| body | longblob | NO | | NULL | |
| definer | char(77) | NO | | | |
| created | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
| modified | timestamp | NO | | 0000-00-00 00:00:00 | |
| sql_mode | set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','NOT_USED','ONLY_FULL_GROUP_BY','NO_UNSIGNED_SUBTRACTION','NO_DIR_IN_CREATE','POSTGRESQL','ORACLE','MSSQL','DB2','MAXDB','NO_KEY_OPTIONS','NO_TABLE_OPTIONS','NO_FIELD_OPTIONS','MYSQL323','MYSQL40','ANSI','NO_AUTO_VALUE_ON_ZERO','NO_BACKSLASH_ESCAPES','STRICT_TRANS_TABLES','STRICT_ALL_TABLES','NO_ZERO_IN_DATE','NO_ZERO_DATE','INVALID_DATES','ERROR_FOR_DIVISION_BY_ZERO','TRADITIONAL','NO_AUTO_CREATE_USER','HIGH_NOT_PRECEDENCE','NO_ENGINE_SUBSTITUTION','PAD_CHAR_TO_FULL_LENGTH') | NO | | | |
| comment | char(64) | NO | | | |
| character_set_client | char(32) | YES | | NULL | |
| collation_connection | char(32) | YES | | NULL | |
| db_collation | char(32) | YES | | NULL | |
| body_utf8 | longblob | YES | | NULL | |
+----------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------+-----+---------------------+-----------------------------+
20 rows in set (0.02 sec)
Notice column #8 : security_type. Its data type is enum('INVOKER','DEFINER').
Also, notice column #12 : definer
What do these settings tell me about the stored procedure's usage?
INVOKER : If you call the stored procedure, the authenticated user must have all the necessary grants before accessing the databases, tables and columns referenced in the stored procedure. For example, suppose root@localhost created a stored procedure called GetData and sets security_type as INVOKER. If user testuser@'192.168.13.11' calls GetData, mysqld will look at every database, table and column in the stored procedure and verify that testuser@'192.168.13.11' has the necessary grants to fully execute every SQL command in the stored procedure.DEFINER : If you call the stored procedure, the authenticated user effectively 'borrows' the grants of the user who created the stored procedure. The creator is defined in definer. For example, suppose root@localhost created a stored procedure called GetData and sets security_type as DEFINER. If user testuser@'192.168.13.11' calls GetData, mysqld will assume root@localhost is the caller of GetData. All checks for grants will quickly pass and GetData runs as if root@localhost called it. It's as if mysqld becomes a stored procedure proxy once it sees DEFINER.
Compare the aforementioned info to the MySQL Documentation on Creating Procedures
The SQL SECURITY characteristic can be DEFINER or INVOKER to specify the security context; that is, whether the routine executes using the privileges of the account named in the routine DEFINER clause or the user who invokes it. This account must have permission to access the database with which the routine is associated. The default value is DEFINER. The user who invokes the routine must have the EXECUTE privilege for it, as must the DEFINER account if the routine executes in definer security context.
The DEFINER clause specifies the MySQL account to be used when checking access privileges at routine execution time for routines that have the SQL SECURITY DEFINER characteristic.
This rigorous check is performed quickly because all the grant tables are loaded in RAM upon mysqld's startup. Obviously, a stored procedure with security_type INVOKER is more strict in allowing an authenticated user access to queries within the code.
This rigorous check is performed quickly because all the grant tables are loaded in RAM upon mysqld's startup. Obviously, a stored procedure with security_type INVOKER is more strict in allowing an authenticated user access to queries within the code.
UPDATE 2013-05-08 11:29 EDT
Please look at the EMS output
Executed : 5/8/2013 3:10:40 PM
Operation : SELECT * FROM mysql.user ORDER BY User, Host
Result : "SELECT command denied to user 'ems'@'192.168.13.11' for table 'user'"
Who is trying to connect? ems'@'192.168.13.11'
Does this user exist? Try running the following:
SELECT USER(),CURRENT_USER();
What do these functions give you:
- USER() reports how you attempted to authenticate in MySQL
- CURRENT_USER() reports how you were allowed to authenticate in MySQL
The second function is what you want to give privileges to.
In your particular case, my guess is that ems'@'192.168.13.11' does not exist and you would need to run one of the following:
GRANT CREATE ROUTINE, ALTER ROUTINE ON *.* TO 'ems'@'192.168.13.11';
GRANT CREATE ROUTINE, ALTER ROUTINE ON *.* TO 'ems'@'192.168.13.%';
GRANT CREATE ROUTINE, ALTER ROUTINE ON *.* TO 'ems'@'%'; -- usually not recommended
What will these commands do?
GRANT CREATE ROUTINE, ALTER ROUTINE ON *.* TO 'ems'@'192.168.13.11'; will allow the ems access to editing all stored procedures if and only ems logged into MySQL from 192.168.13.11GRANT CREATE ROUTINE, ALTER ROUTINE ON *.* TO 'ems'@'192.168.13.%'; will allow the ems access to editing all stored procedures if and only ems logged into MySQL from 192.168.13.% netblockGRANT CREATE ROUTINE, ALTER ROUTINE ON *.* TO 'ems'@'%'; will allow the ems access to editing all stored procedures if and only ems logged into MySQL from anywhere. This one is normally not recommended since this allows ems remote connections and if port 3306 is world accessible.
Now look back at your original GRANT
GRANT CREATE ROUTINE, ALTER ROUTINE ON `testdb`.* TO 'testuser'@'192.168.13.11';
User testuser can only access the testdb if and only if testuser is authenticating from '192.168.13.11'. If you want testuser to access other another user's stored procedures, thenyou need to run this:
GRANT CREATE ROUTINE, ALTER ROUTINE ON *.* TO 'testuser'@'192.168.13.11';
Please, Give it a Try !!!
