[MySQL] view 권한 및 alter view 정리 > php

mysql 외부 IP 설정 시 CREATE VIEW, SHOW VIEW 권한이 부여됨.
mysql> show grants for testid@175.118.124.221;
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for testid@175.118.124.221                                                                                                                                                          |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'testid'@'175.118.124.221' IDENTIFIED BY PASSWORD '*4E0AE8EAAE183E4454B803A602F1EF1E8F5DE9CA'                                                                        |
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, CREATE VIEW, SHOW VIEW ON `testid`.* TO 'testid'@'175.118.124.221' |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

localhost 및 다른 외부 IP설정에서 생성된 view 테이블은 외부 IP(175.118.124.221)에서는 접근 및 삭제는 가능하오나 alter view 문을 사용할 수 없음.
alter view 사용시 Access denied; you need the SUPER privilege for this operation 에러 발생.

1. localhost 에서 view 생성
mysql> create view view_table_name as select * from aa;
Query OK, 0 rows affected (0.00 sec)

2. view 테이블 확인
mysql> show create table view_table_name;
+-----------------+-----------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
| View            | Create View                                                                                                                                  | character_set_client | collation_connection |
+-----------------+-----------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
| view_table_name | CREATE ALGORITHM=UNDEFINED DEFINER=`testid`@`localhost` SQL SECURITY DEFINER VIEW `view_table_name` AS select `aa`.`t1` AS `t1` from `aa` | binary              | binary              |
+-----------------+-----------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
1 row in set (0.00 sec)

3. 175.118.124.221 에서 view table 접근 확인

mysql> check table view_table_name;
+----------------------------+-------+----------+----------+
| Table                      | Op    | Msg_type | Msg_text |
+----------------------------+-------+----------+----------+
| testid.view_table_name | check | status  | OK      |
+----------------------------+-------+----------+----------+
1 row in set (0.00 sec)

4. 175.118.124.221 에서 view 테이블 수정 시 에러 발생.
mysql> ALTER ALGORITHM=UNDEFINED DEFINER=`testid`@`localhost` SQL SECURITY DEFINER VIEW `view_table_name` AS select `aa`.`t1` AS `t1` from `aa`;
ERROR 1227 (42000): Access denied; you need the SUPER privilege for this operation

5. view 테이블 수정은 definer 로 지정된 곳에서 작업가능. 또는 view 테이블 drop 후 재생성

6. localhost 에서 alter view 가능
mysql> ALTER ALGORITHM=UNDEFINED DEFINER=`testid`@`localhost` SQL SECURITY DEFINER VIEW `view_table_name` AS select `aa`.`t1` AS `t1` from `aa`;
Query OK, 0 rows affected (0.01 sec)