| Bug #40261 | insertion of column privileges fail | ||
|---|---|---|---|
| Submitted: | 22 Oct 2008 17:55 | Modified: | 31 Oct 2008 9:52 |
| Reporter: | Peter Laursen (Basic Quality Contributor) | Email Updates: | |
| Status: | Not a Bug | Impact on me: | |
| Category: | MySQL Server: Security: Privileges | Severity: | S2 (Serious) |
| Version: | 5.0.67, 4.1, 5.0, 5.1, 6.0 bzr | OS: | Any |
| Assigned to: | Sveta Smirnova | CPU Architecture: | Any |
| Tags: | qc | ||
[22 Oct 2008 18:01]
Peter Laursen
Of course for the INSERT a non-privileged user was created in advance also with INSERT to mysql.users table like
Insert into mysql.user ( host, user, password, select_priv, insert_priv,update_priv, delete_priv, create_priv, drop_priv, reload_priv, shutdown_priv, process_priv, file_priv, grant_priv, references_priv, index_priv, alter_priv, show_db_priv, super_priv, create_tmp_table_priv, lock_tables_priv, execute_priv, repl_slave_priv, repl_client_priv, create_view_priv, show_view_priv, create_routine_priv, alter_routine_priv, create_user_priv ) values ( '%', 'randomuser', PASSWORD(''), 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N' );
Flush privileges;
[22 Oct 2008 20:11]
Sveta Smirnova
Thank you for the report. Verified as described.
[26 Oct 2008 12:35]
Peter Laursen
Now as 4.1(.22) is also affected and 4.1.x is out of active maintenaince, am I right in assuming that there will never be released updated 4.1 binaries with a fix for this?
[31 Oct 2008 0:24]
Omer Barnir
Direct SQL to the priv tables (although works) is not supported. In the above case the SQL mentioned is not equivalent to the GRANT command as it does not cover all tables affected by the GRANT
[31 Oct 2008 2:07]
Sergei Golubchik
It is supported, it's just not recommended. But, indeed, you update privilege tables incorrectly - do GRANT and see what tables are updated and how (and in particular, note how tables_priv is updated).
[31 Oct 2008 9:25]
Peter Laursen
It is a bug. At least it is a documentation bug if it is something specific for column level privilges. For instance http://dev.mysql.com/doc/refman/5.0/en/adding-users.html says "As an alternative to GRANT, you can create the same accounts directly by issuing INSERT statements and then telling the server to reload the grant tables using FLUSH PRIVILEGES". Also INSERT+FLUSH works fine with privileges on database and table level. I am almost that even column level privileges did on an earlier stage! (and btw: I can also inform you that official MySQL program MySQL Administrator uses INSERT to privileges tables +FLUSH!) I am not impressed with the depth of thinking and quality of reply here.
[31 Oct 2008 9:51]
Sergei Golubchik
Peter, you can create column grant directly, there's no issue with that. But you need to do a bit more than one insert into columns_priv table (hint - you need to update tables_priv table too). The manual never says that one insert is enough to emulate any GRANT statement :)
Description: insertion of column privileges fail with INSERT, success with GRANT How to repeat: GRANT SELECT (ab_id) on test.abstracts to 'randomuser'@'%'; show grants for 'randomuser'@'%'; select * from mysql.columns_priv; /* Host Db User Table_name Column_name Timestamp Column_priv ------ ------ ---------- ---------- ----------- ------------------- ----------- % test randomuser abstracts ab_id 2008-10-22 19:31:56 Select */ -- after dropping user insert into mysql.columns_priv(host, db, user, table_name, column_name, column_priv) values('%', 'test', 'randomuser', 'abstracts', 'ab_id', 'Select'); flush privileges; select * from mysql.columns_priv; /* Host Db User Table_name Column_name Timestamp Column_priv ------ ------ ---------- ---------- ----------- ------------------- ----------- % test randomuser abstracts ab_id 2008-10-22 19:33:41 Select */ show grants for 'randomuser'@'%'; /* Grants for randomuser@% -------------------------------------- GRANT USAGE ON *.* TO 'randomuser'@'%' */ (use what table you have!) Suggested fix: I am almost sure that this is changed behaviour. But not sure when it may have changed! And I am not able to understand why even a server restart does not make the contents of `columns_priv` column active after the INSERT statement!