MySQL Bugs: #23458: User table permissions from application database incorrect

Bug #23458	User table permissions from application database incorrect
Submitted:	19 Oct 2006 8:58	Modified:	19 Nov 2006 11:09
Reporter:	Warren Hodgkinson	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	5.0.26	OS:	Windows (Windows 2003 Server)
Assigned to:		CPU Architecture:	Any

Description:
After upgrading to Mysql 5.0.26, I get the following message : info="update `mydb`.`user` set use_last_login=now() where use_key='18' [nativecode=1227 ** Access denied; you need the SUPER privilege for this operation]"].

How to repeat:
Create a database with a table in it called user. Try to update any of its fields as a non-super user.

Suggested fix:
Temporary fix - give user super permission.

Thank you for a problem report. Please, send the exact statements that demonstrates the behaviour described. I was not able to repeat with the following:

openxs@suse:~/dbs/5.0> bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 13
Server version: 5.0.27-debug Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> grant all on test.* to user1@localhost;
Query OK, 0 rows affected (0.01 sec)

mysql> exit
Bye
openxs@suse:~/dbs/5.0> bin/mysql -uuser1 test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 15
Server version: 5.0.27-debug Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> create table user1(c1 int);
Query OK, 0 rows affected (0.01 sec)

mysql> create table user(c1 int);
ERROR 1050 (42S01): Table 'user' already exists
mysql> drop table user;
Query OK, 0 rows affected (0.00 sec)

mysql> create table user(c1 int);
Query OK, 0 rows affected (0.01 sec)

mysql> update user set c1=10;
Query OK, 0 rows affected (0.01 sec)
Rows matched: 0  Changed: 0  Warnings: 0

mysql> show grants for user1@localhost;
+---------------------------------------------------------+
| Grants for user1@localhost                              |
+---------------------------------------------------------+
| GRANT SHOW VIEW ON *.* TO 'user1'@'localhost'           |
| GRANT ALL PRIVILEGES ON `test`.* TO 'user1'@'localhost' |
+---------------------------------------------------------+
2 rows in set (0.01 sec)

Then as root:

mysql> select * from mysql.user where user='user1'\G
*************************** 1. row ***************************
                 Host: %
                 User: user1
             Password: *22A99BA288DB55E8E230679259740873101CD636
          Select_priv: N
          Insert_priv: N
          Update_priv: N
          Delete_priv: N
          Create_priv: N
            Drop_priv: N
          Reload_priv: N
        Shutdown_priv: N
         Process_priv: N
            File_priv: N
           Grant_priv: N
      References_priv: N
           Index_priv: N
           Alter_priv: N
         Show_db_priv: N
           Super_priv: N
Create_tmp_table_priv: N
     Lock_tables_priv: N
         Execute_priv: N
      Repl_slave_priv: N
     Repl_client_priv: N
     Create_view_priv: N
       Show_view_priv: Y
  Create_routine_priv: N
   Alter_routine_priv: N
     Create_user_priv: N
             ssl_type:
           ssl_cipher:
          x509_issuer:
         x509_subject:
        max_questions: 0
          max_updates: 0
      max_connections: 0
 max_user_connections: 0
*************************** 2. row ***************************
                 Host: localhost
                 User: user1
             Password:
          Select_priv: N
          Insert_priv: N
          Update_priv: N
          Delete_priv: N
          Create_priv: N
            Drop_priv: N
          Reload_priv: N
        Shutdown_priv: N
         Process_priv: N
            File_priv: N
           Grant_priv: N
      References_priv: N
           Index_priv: N
           Alter_priv: N
         Show_db_priv: N
           Super_priv: N
Create_tmp_table_priv: N
     Lock_tables_priv: N
         Execute_priv: N
      Repl_slave_priv: N
     Repl_client_priv: N
     Create_view_priv: N
       Show_view_priv: Y
  Create_routine_priv: N
   Alter_routine_priv: N
     Create_user_priv: N
             ssl_type:
           ssl_cipher:
          x509_issuer:
         x509_subject:
        max_questions: 0
          max_updates: 0
      max_connections: 0
 max_user_connections: 0
2 rows in set (0.00 sec)

So, no SUPER priviledge, and no problems...

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".