눈부시도록 아름다운 그래서 바라볼 수 없는

mysql view

study/DB 2013. 4. 9. 11:54
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) 


출처 : http://blog.boyo.kr/entry/mysql-view-권한-및-alter-view-정리


Posted by 휘루걸음

댓글을 달아 주세요