Bug #59767 CREATE INDEX requires INDEX_ACL but ignore ALTER_ACL
Submitted: 27 Jan 2011 2:01 Modified: 27 Jan 2011 15:49
Reporter: Julien Perez Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Security: Privileges Severity:S4 (Feature request)
Version:5.0.67 and up OS:Any
Assigned to: CPU Architecture:Any
Tags: create index privilege permission
Triage: Needs Triage: D5 (Feature request)

[27 Jan 2011 2:01] Julien Perez
Description:
Hi,

I encountered a problem lately with one of my users. This one was using CREATE INDEX instead of ALTER TABLE to create an index and of course this was denied because my users *only* have the ALTER privilege (ALTER_ACL which is ALTER + INSERT + CREATE).

Now I've asked around since I couldn't find a straight answer on the documentation online and it seems this CREATE INDEX is a way to offer specific rights to the users that only needs to ADD / DROP indexes.

Thats fine by me, but why does mysqld ignore the ALTER_ACL privilege if the INDEX_ACL is not set ?

Since ALTER_ACL is a total badass privilege compared to INDEX_ACL, that does not make much sense to me.

How to repeat:
use CREATE INDEX statement instead of ALTER TABLE 

Suggested fix:
Modify the sql_parse.cc to add an additional check on ALTER_ACL

[..]
  case SQLCOM_CREATE_INDEX:
    /* Fall through */
  case SQLCOM_DROP_INDEX:
[..]

    if (check_one_table_access(thd, INDEX_ACL, all_tables))
      goto error; /* purecov: inspected */ <- add a check before that

[...]
[27 Jan 2011 15:49] Valeriy Kravchuk
Verified just as described:

C:\Program Files\MySQL\MySQL Server 5.1\bin>mysql -uroot -proot -P3310 test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 29
Server version: 5.1.54-community MySQL Community Server (GPL)

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> create user ui@localhost;
Query OK, 0 rows affected (0.14 sec)

mysql> create database db2;
Query OK, 1 row affected (0.00 sec)

mysql> grant select on db2.* to ui@localhost;
Query OK, 0 rows affected (0.03 sec)

mysql> use db2
Database changed
mysql> create table t1(c1 int);
Query OK, 0 rows affected (0.09 sec)

mysql> grant alter, insert, create on db2.* to ui@localhost;
Query OK, 0 rows affected (0.00 sec)

mysql> exit
Bye

C:\Program Files\MySQL\MySQL Server 5.1\bin>mysql -uui -P3310 db2
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 31
Server version: 5.1.54-community MySQL Community Server (GPL)

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> select * from t1;
Empty set (0.00 sec)

mysql> create index i1 on t1(c1);
ERROR 1142 (42000): INDEX command denied to user 'ui'@'localhost' for table 't1'

mysql> alter table t1 add index i1(c1);
Query OK, 0 rows affected (0.27 sec)
Records: 0  Duplicates: 0  Warnings: 0

This is neither very logical, nor properly documented at http://dev.mysql.com/doc/refman/5.1/en/create-index.html