MySQLの権限を絞ってみた

権限設定でカラムレベルというものが存在するらしいのでどういうものか確かめてみた

root権限

mysql> create table if not exists grant_columns(
    ->     id int unsigned not null AUTO_INCREMENT,
    ->     name varchar(255) NOT NULL,
    ->     granted_name varchar(255) NOT NULL,
    ->     PRIMARY KEY (id)
    -> );
Query OK, 0 rows affected (0.02 sec)

mysql> insert into grant_columns (name, granted_name) values ('aaa', 'bbb');
Query OK, 1 row affected (0.00 sec)

mysql> create user test_user@localhost identified by 'password';
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW GRANTS FOR test_user@localhost;
+-----------------------------------------------+
| Grants for test_user@localhost                |
+-----------------------------------------------+
| GRANT USAGE ON *.* TO 'test_user'@'localhost' |
+-----------------------------------------------+
1 row in set (0.01 sec)

mysql> GRANT SHOW DATABASES ON test.* TO 'test_user'@'localhost';
ERROR 1221 (HY000): Incorrect usage of DB GRANT and GLOBAL PRIVILEGES
mysql> GRANT SHOW DATABASES ON *.* TO 'test_user'@'localhost';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> GRANT select(`granted_name`) ON test.grant_columns TO 'test_user'@'localhost';
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> SHOW GRANTS FOR test_user@localhost;
+----------------------------------------------------------------------------------+
| Grants for test_user@localhost                                                   |
+----------------------------------------------------------------------------------+
| GRANT SHOW DATABASES ON *.* TO 'test_user'@'localhost'                           |
| GRANT SELECT (granted_name) ON `test`.`grant_columns` TO 'test_user'@'localhost' |
+----------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql> 

test_user

mysql> use test;
ERROR 1044 (42000): Access denied for user 'test_user'@'localhost' to database 'test'
mysql> SHOW GRANTS FOR test_user;
ERROR 1141 (42000): There is no such grant defined for user 'test_user' on host '%'
mysql> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| grant_columns  |
+----------------+
1 row in set (0.00 sec)

mysql> desc grant_columns;
+--------------+--------------+------+-----+---------+-------+
| Field        | Type         | Null | Key | Default | Extra |
+--------------+--------------+------+-----+---------+-------+
| granted_name | varchar(255) | NO   |     | NULL    |       |
+--------------+--------------+------+-----+---------+-------+
1 row in set (0.00 sec)

mysql> select * from grant_columns;
ERROR 1143 (42000): SELECT command denied to user 'test_user'@'localhost' for column 'id' in table 'grant_columns'
mysql> select granted_name from grant_columns;
+--------------+
| granted_name |
+--------------+
| bbb     |
+--------------+
1 row in set (0.00 sec)

mysql> 

descでは他にカラム定義がないように見えるのに*を使うと怒られる(´・ω・｀) 「見せる」設定はできるけど「見せない」という設定はできないように見える。