mysql viewを使ってみる

今回は特定ユーザにviewのみアクセスさせtableにはアクセスできない状態にしたかったが、いざやってみると結構嵌ったので備忘録として記載しとく。
特に記載が無い場合はrootでDDLは実行しています。

検証用のDBを作成

mysql> create database hogedb;
Query OK, 1 row affected (0.00 sec)

mysql> show databases;
 +--------------------+
 | Database           |
 +--------------------+
 | information_schema |
 | hogedb             |
 | mysql              |
 | performance_schema |
 +--------------------+
4 rows in set (0.00 sec)

ユーザの作成

tableに対してもownerとしてtablehogeユーザをviewだけ参照できるユーザとしてviewhogeユーザを作成。
（viewhogeには権限は未設定。）

create database hogedb;
GRANT USAGE ON *.* TO 'tablehoge'@'localhost' IDENTIFIED BY 'tablehoge';
GRANT ALL PRIVILEGES ON `hogedb`.* TO 'tablehoge'@'localhost';
GRANT USAGE ON *.* TO 'viewhoge'@'localhost' IDENTIFIED BY 'viewhoge';

mysql> create database hogedb;
Query OK, 1 row affected (0.00 sec)

mysql> GRANT USAGE ON *.* TO 'tablehoge'@'localhost' IDENTIFIED BY 'tablehoge';
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT ALL PRIVILEGES ON `hogedb`.* TO 'tablehoge'@'localhost';
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT USAGE ON *.* TO 'viewhoge'@'localhost' IDENTIFIED BY 'viewhoge';
Query OK, 0 rows affected (0.00 sec)

tableの作成

hogedbに作成。

create table hogetb(
 id int NOT NULL AUTO_INCREMENT,
 col1 int,
 col2 int,
 PRIMARY KEY (`ID`)
) engine = innodb
;

mysql> use hogedb
Database changed
mysql> create table hogetb(
    ->  id int NOT NULL AUTO_INCREMENT,
    ->  col1 int,
    ->  col2 int,
    ->  PRIMARY KEY (`ID`)
    -> ) engine = innodb
    -> ;
Query OK, 0 rows affected (0.01 sec)

viewの作成

DEFINERには`tablehoge`@`localhost`を指定する。

CREATE ALGORITHM=UNDEFINED DEFINER=`tablehoge`@`localhost` SQL SECURITY DEFINER VIEW hogeview AS select id ,col2 from hogetb;

mysql> CREATE ALGORITHM=UNDEFINED DEFINER=`tablehoge`@`localhost` SQL SECURITY DEFINER VIEW hogeview AS select id ,col2 from hogetb;
Query OK, 0 rows affected (0.00 sec)

viewhogeにも参照権限を付与する。

GRANT SELECT ON `hogedb`.`hogeview` TO 'viewhoge'@'localhost';

mysql> GRANT SELECT ON `hogedb`.`hogeview` TO 'viewhoge'@'localhost';
Query OK, 0 rows affected (0.00 sec)

tablehogeでの確認

tablehogeでログインしtable、viewの両方アクセスできることを確認する。

[root@localhost ~]# mysql -u tablehoge -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 76
Server version: 5.6.4-m7-log MySQL Community Server (GPL)

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>
mysql>
mysql> use hogedb
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_hogedb |
 +------------------+
 | hogetb           |
 | hogeview         |
 +------------------+
2 rows in set (0.00 sec)

mysql> select * from hogetb;
Empty set (0.00 sec)

mysql> select * from hogeview;
Empty set (0.00 sec)

viewhogeでの確認

hogeviewだけ見えれば今回のミッション達成。

[root@localhost ~]# mysql -u viewhoge -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 78
Server version: 5.6.4-m7-log MySQL Community Server (GPL)

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use hogedb
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_hogedb |
 +------------------+
 | hogeview         |
 +------------------+
1 row in set (0.01 sec)

mysql> select * from hogeview;
Empty set (0.00 sec)

無事viewだけ見せれた。（DEFINERとSQL SECURITY周りはちょっと納得してないけど。）
レプリケーション時はviewhogeはmaster,slaveの両方に存在し参照したいviewについては同じ権限があればOKなはず。