| Bug #4824 | User with columns privileges will not take effect after restart mysqld | ||
|---|---|---|---|
| Submitted: | 30 Jul 2004 11:09 | Modified: | 9 Apr 2008 10:52 |
| Reporter: | Unreal Hshh | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server | Severity: | S1 (Critical) |
| Version: | 4.1.3 | OS: | Any (ALL) |
| Assigned to: | Sergei Golubchik | CPU Architecture: | Any |
[30 Jul 2004 11:09]
Unreal Hshh
[30 Jul 2004 11:20]
Unreal Hshh
Grant single column is no any problem. Grant multi columns will cause this error.
[30 Jul 2004 11:47]
Unreal Hshh
Already test in FreeBSD,Windows. Grant multi columns,only the first will take effect after mysqld restart
[31 Jul 2004 21:34]
MySQL Verification Team
I have tested it thoroughly and it worked like a charm for me. I restarted MySQL server several times and it just worked .... ./client/mysql mysql -e "select user, host from user" +----------+----------------------+ | user | host | +----------+----------------------+ | Sinisa | localhost | | fabackup | localhost | | foo | localhost | | root | localhost | | Sinisa | sinisa.nasamreza.org | | root | sinisa.nasamreza.org | +----------+----------------------+ [/mnt/work/mysql-4.1]$ ./client/mysql mysql -e "grant select (broj,naziv) on telcent.nazivi to fuzzy@localhost" [/mnt/work/mysql-4.1]$ ./client/mysql mysql -e "select * from tables_priv" +-----------+---------+----------+------------------------+------------------+---------------------+--------------------+-------------+ | Host | Db | User | Table_name | Grantor | Timestamp | Table_priv | Column_priv | +-----------+---------+----------+------------------------+------------------+---------------------+--------------------+-------------+ | localhost | mysql | fabackup | ibbackup_binlog_marker | Sinisa@localhost | 2004-05-18 16:01:06 | Insert,Create,Drop | | | localhost | telcent | fuzzy | nazivi | Sinisa@localhost | 2004-07-31 22:20:28 | | Select | +-----------+---------+----------+------------------------+------------------+---------------------+--------------------+-------------+ [/mnt/work/mysql-4.1]$ ./client/mysql mysql -e "select * from columns_priv" +-----------+---------+-------+------------+-------------+---------------------+-------------+ | Host | Db | User | Table_name | Column_name | Timestamp | Column_priv | +-----------+---------+-------+------------+-------------+---------------------+-------------+ | localhost | telcent | fuzzy | nazivi | broj | 2004-07-31 22:20:28 | Select | | localhost | telcent | fuzzy | nazivi | naziv | 2004-07-31 22:20:28 | Select | +-----------+---------+-------+------------+-------------+---------------------+-------------+ [/mnt/work/mysql-4.1]$ mysqladmin shutdown [/mnt/work/mysql-4.1]$ ./client/mysql -u fuzzy telcent -e "select * from nazivi" +-------+----------------------+ | BROJ | naziv | +-------+----------------------+ | 1 | jedan | | 2 | dva | | 3 | tri | | 4 | xxxxxxxxxx | | 5 | a | | 10 | | | 6 | Sinisa | | 7 | Satkica | | 8 | yyyyy | | 9 | yyyyy | | 11 | aa | | 15 | NULL | | 55 | "kkk" | | 66 | ";;;" | | 33 | erik@frontbridge.com | | 0 | a | | 34 | a | | 35 | a | | 36 | a | | 111 | Yes | | 112 | Yes | | 12 | NULL | | 40148 | NULL | +-------+----------------------+ [/mnt/work/mysql-4.1]$ mysqladmin shutdown [/mnt/work/mysql-4.1]$ ./client/mysql -u fuzzy telcent -e "select * from nazivi" +-------+----------------------+ | BROJ | naziv | +-------+----------------------+ | 1 | jedan | | 2 | dva | | 3 | tri | | 4 | xxxxxxxxxx | | 5 | a | | 10 | | | 6 | Sinisa | | 7 | Satkica | | 8 | yyyyy | | 9 | yyyyy | | 11 | aa | | 15 | NULL | | 55 | "kkk" | | 66 | ";;;" | | 33 | erik@frontbridge.com | | 0 | a | | 34 | a | | 35 | a | | 36 | a | | 111 | Yes | | 112 | Yes | | 12 | NULL | | 40148 | NULL | +-------+----------------------+ [/mnt/work/mysql-4.1]$ ./client/mysql -u fuzzy telcent -e "select * from mails" ERROR 1142 at line 1: select command denied to user 'fuzzy'@'localhost' for table 'mails' user has no privileges for table mails ...
[1 Aug 2004 13:14]
Sergei Golubchik
It may be that this bugs shows up only in specific version of privilege tables. Try to move tables from mysql database elsewhere and run mysql_install_db - to create new privilege tables. If the bug will dissapear - please attach backed up (that is old) privilege tables to this bugreport. (tar+gzipped)
[1 Aug 2004 13:18]
Sergei Golubchik
Be sure your privilege tables are not too old and do support column privileges
[1 Aug 2004 20:38]
Unreal Hshh
already try use mysql_install_db after move the old mysql database out but still get the denied error after restart i attached the db file,please check it btw: test in windows is new installation
[21 Aug 2004 5:50]
MySQL Verification Team
I tested on Windows with latest 4.1 source tree: C:\mysql\bin>mysql -uroot Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2 to server version: 4.1.4-beta-debug-log Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> grant select (a,b) on new.test to 'abc'@'localhost' identified by 'as'; Query OK, 0 rows affected (0.06 sec) mysql> exit Bye C:\mysql\bin>mysqladmin -uroot shutdown C:\mysql\bin>mysql -uabc -p Enter password: ** Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 to server version: 4.1.4-beta-debug-log Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> use new; Database changed mysql> select (a,b) from test; ERROR 1241 (21000): Operand should contain 1 column(s) mysql> select (a) from test; +---+ | a | +---+ | 1 | +---+ 1 row in set (0.01 sec) C:\mysql\bin>mysql -uroot Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 3 to server version: 4.1.4-beta-debug-log Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> show grants for 'abc'@'localhost'\G *************************** 1. row *************************** Grants for abc@localhost: GRANT USAGE ON *.* TO 'abc'@'localhost' IDENTIFIED BY PASSWORD '*82F7A212F409F2D396 76231F9599E077C10214C9' *************************** 2. row *************************** Grants for abc@localhost: GRANT SELECT (a, b) ON `new`.`test` TO 'abc'@'localhost' 2 rows in set (0.00 sec) mysql> mysql> desc test; +-------+------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+------------------+------+-----+---------+-------+ | a | int(10) unsigned | | PRI | 0 | | | b | varchar(30) | YES | | NULL | | | c | varchar(40) | YES | | NULL | | | d | varchar(60) | YES | | NULL | | +-------+------------------+------+-----+---------+-------+ 4 rows in set (0.00 sec) The grant tables are the same as packaged with release 4.1.3
[21 Aug 2004 7:37]
MySQL Verification Team
Here my test on my Slackware box: miguel@hegel:/misc/dbs/4.1$ bin/mysql -uroot Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2 to server version: 4.1.4-beta-debug-log Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> grant select (a,b) on new.test to 'abc'@'localhost' identified by 'as'; Query OK, 0 rows affected (0.01 sec) mysql> show grants for 'abc'@'localhost'\G *************************** 1. row *************************** Grants for abc@localhost: GRANT USAGE ON *.* TO 'abc'@'localhost' IDENTIFIED BY PASSWORD '*82F7A212F409F2D39676231F9599E077C10214C9' *************************** 2. row *************************** Grants for abc@localhost: GRANT SELECT (a, b) ON `new`.`test` TO 'abc'@'localhost' 2 rows in set (0.00 sec) mysql> exit Bye miguel@hegel:/misc/dbs/4.1$ ./kill_daemon Enter password: Started the daemon in another window. miguel@hegel:/misc/dbs/4.1$ bin/mysql -uabc -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 to server version: 4.1.4-beta-debug-log Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> use new; Database changed mysql> select * from test; ERROR 1143 (42000): select command denied to user 'abc'@'localhost' for column 'c' in table 'test' mysql> select (a,b) from test; ERROR 1241 (21000): Operand should contain 1 column(s) mysql> select (a) from test; Empty set (0.00 sec)
[21 Aug 2004 19:30]
MySQL Verification Team
Miguel, Please re-test. Your show grants show proper privileges: mysql> show grants for 'abc'@'localhost'\G GRANT USAGE ON *.* TO 'abc'@'localhost' IDENTIFIED BY PASSWORD ...... GRANT SELECT (a, b) ON `new`.`test` TO 'abc'@'localhost' 2 rows in set (0.00 sec) Please test it with : SELECT a,b from new.test and not SELECT (a,b) from new.test
[21 Aug 2004 23:20]
MySQL Verification Team
Sinisa in the way you said worked. select a,b from test;
[23 Aug 2004 13:41]
MySQL Verification Team
If it worked, then why is it a bug /?
[9 Apr 2008 10:52]
Susanne Ebrecht
Tested with FreeBSD 7.0 and MySQL 5.0.51a and also with 4.1.22. This is already fixed in these versions.
