Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a strange problem with EMS SQL Manager.

I'm using MySQL 5.5 with linux servers. One of my developers need to edit store procedure, so I grant him:

GRANT CREATE ROUTINE, ALTER ROUTINE ON `testdb`.* TO 'testuser'@'192.168.13.11'

When he clicked edit store in EMS SQL Manager, what he got is:

SELECT command denied to user 'testuser'@'192.168.13.11' for table 'user'

I find out that EMS SQL Manager ask for select privilege in some tables, so I need to grant this user some extra permissions.

GRANT SELECT ON `mysql`.`user` TO 'testuser'@'192.168.13.11';
GRANT SELECT ON `mysql`.`db` TO 'testuser'@'192.168.13.11';
GRANT SELECT ON `mysql`.`tables_priv` TO 'testuser'@'192.168.13.11';
GRANT SELECT ON `mysql`.`columns_priv` TO 'testuser'@'192.168.13.11';
GRANT SELECT ON `mysql`.`procs_priv` TO 'testuser'@'192.168.13.11';

So my question is, WHY EMS SQL Manager need those privileges? I can create, drop procedure in mysql command line client without problem.

Update

After some investigation, I still do not understand EMS SQL Manager. This is the action EMS SQL Manager take when click the Compile button.

Executed  : 5/8/2013 3:10:40 PM
Operation : CONNECT
Result    : "OK."

Executed  : 5/8/2013 3:10:40 PM
Operation : show variables like 'character_set_results'
Result    : "OK."

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'"

Executed  : 5/8/2013 3:10:40 PM
Operation : DISCONNECT
Result    : "OK."

Executed  : 5/8/2013 3:10:41 PM
Operation : select p.`db`, p.`name`, p.`type`, p.`specific_name`,
p.`language`, p.`sql_data_access`, p.`is_deterministic`, p.`security_type`,
p.`param_list`, p.`returns`, p.`body`, p.`definer`, p.`created`,
p.`modified`, p.`sql_mode`, p.`comment` from `mysql`.`proc` as p where
p.`type` = 'PROCEDURE' and p.`db`='reportingdb' and
p.`name`='100_rpt_campaign_ip'
Result    : "OK."

Executed  : 5/8/2013 3:10:41 PM
Operation : SHOW CREATE PROCEDURE `reportingdb`.`100_rpt_campaign_ip`
Result    : "OK."

So my question, What is the Compile button trying to do? Why does it want to select mysql.user? I think that editing a stored procedure is two steps of drop and re create it, why does it want to do more?

share|improve this question
add comment

2 Answers

up vote 3 down vote accepted

The best thing you can do to understand this behaviour better is to ask the software vendor. If you don't want to grant those permissions, lodging a bug report might be appropriate.

I'm going to hazard a guess that EMS SQL Manager is trying to be too clever, and check whether you have Alter_routine_priv or Create_routine_priv so that it can return a warning or error before it tries sending your ALTER ROUTINE to the database.

Most SQL Editors run lots of queries in the background to support "IntelliSense", speed up common queries, or provide background information, and a lot of the time they just don't make any sense (some of them are downright frightening).

share|improve this answer
add comment

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.11
  • GRANT 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.% netblock
  • GRANT 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 !!!

share|improve this answer
    
Maybe you misunderstood my question a bit. I do not see the relationship between your answer to my problem. All I want to do is editing a stored procedure? Why EMS SQL Manager need those permissions? You can see my new update for more information. –  favadi May 8 '13 at 8:28
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.