Мой тест.
Версия:
mysql> SELECT VERSION();
+------------+
| VERSION() |
+------------+
| 5.5.17-cll |
+------------+
1 row in set (0.00 sec)
Подготовка теста:
mysql> CREATE USER 'test'@'%' IDENTIFIED BY 'test';
Query OK, 0 rows affected (0.00 sec)
mysql> CREATE TABLE t (id INT PRIMARY KEY, a VARCHAR(10), b INT);
Query OK, 0 rows affected (0.11 sec)
mysql> INSERT INTO t VALUES (1, 'aaa', 111), (2, 'bbb', 222);
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> GRANT SELECT (a) ON test.t TO 'test'@'%';
Query OK, 0 rows affected (0.00 sec)
mysql> SHOW GRANTS FOR 'test'@'%';
+-----------------------------------------------------------------------------------------------------+
| Grants for test@% |
+-----------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'test'@'%' IDENTIFIED BY PASSWORD '*94BDCEBE19083CE2A1F959FD02F964C7AF4CFC29' |
| GRANT SELECT (a) ON `test`.`t` TO 'test'@'%' |
+-----------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
Тест под юзером:
mysql> SHOW TABLES;
+----------------+
| Tables_in_test |
+----------------+
| t |
+----------------+
1 row in set (0.00 sec)
mysql> SELECT * FROM t;
ERROR 1143 (42000): SELECT command denied to user 'test'@'192.168.0.1' for column 'id' in table 't'
mysql> SELECT id FROM t;
ERROR 1143 (42000): SELECT command denied to user 'test'@'192.168.0.1' for column 'id' in table 't'
mysql> SELECT b FROM t;
ERROR 1143 (42000): SELECT command denied to user 'test'@'192.168.0.1' for column 'b' in table 't'
mysql> SELECT a FROM t;
+------+
| a |
+------+
| aaa |
| bbb |
+------+
2 rows in set (0.00 sec)
Проверяй себя. Исследуй схему mysql в таом порядке:
user(user, host, password),
host(host, db),
db(user, host, db),
tables_priv(user, host, db, Table_name)
columns_priv(user, host, db, Table_name, Column_name)
Не изменяй этих таблиц напрямую, но если все таки изменил, выполни FLUSH PRIVILEGES.
