새로운 업무가 도착했다.
업무를 받아서 서버등록후 DB를 들어갔다.
이미 개발중이던 서버였기 때문에 user가 어떤게 있는지 조회를 하려했다.
그런데...
ERROR 1356(HY000): View 'mysql.user' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them.이게 뭐지...
구글에 검색해보는데 99%가 패스워드 변경할때 이런 에러를 마주한다고 나와있었다.
이런 답변을 원한건 아닌데,,,
하지만 같은 에러여서 같이 알아보았고 결국 다른 곳에 문제가 있다는 것을 깨달아서 포스팅한다.
패스워드 변경시 에러
10.4 버전 이후로는 mysql.user테이블은 뷰로 지정이 되어있다.
그래서 root계정은 mysql.user의 정의자가 아니다.
그럼 누구냐?
=> mariadb.sys 라는 계정이 뷰, 트리거, 함수 등의 정의자이다.
update user set password=password('password') where user='root';10.4 이전까지는 이렇게 유저의 PW를 변경했었다.
하지만 이후로는 위에 말한 에러가 발생한다.
뷰테이블이기때문에 update가 아닌 set password 함수 또는 alter user와 같은 명령어를 통하여 변경을 해야한다.
set password for 'root'@'localhost' = password('password');
alter user 'root'@'localhost' identified by 'password';이 두가지 방법 중 1개를 선택하여 바꾸면 된다.
그리고 꼭!!!
flush privileges;이걸 안하면 다음 로그인시 바뀌기 전 PASSWORD로 로그인해야함.
조회했을때 에러
나는 위의 상황처럼 password변경했을때 마주한게 아니다.
select user,host from mysql.user;했는데 맨위에서 말한 에러가 발생했다.
해석하면 뷰테이블이 이걸 사용하기에는 부족한 권한인 부적절한 테이블/칼럼/함수/정의자라는거같은 느낌이다.
대충 뭔가가 부족하다는 것만 알게되어서 뭐가 문제일지 찾아보았다.
- root는 실직적인 물리적인 테이블, 오브젝트들의 정의자이다.
하지만 뷰의 정의자는 아니다. - mariadb.sys가 바로 뷰,함수,트리거 등의 정의자이다.
그래서 잘 돌아가고있는 mariadb서버의 mariadb.sys의 권한과 에러가뜬 서버와 비교를 해봤다.
show grants for 'mariadb.sys'@'localhost';- 문제없는 서버 : usage on . to ~와 select,delete on mysql.global_priv~~~존재
- 에러나는 서버 : usage만 존재했다.
이거구나,,,싶어서 바로
grant select,delete on mysql.global_priv to 'mariadb.sys'@'localhost';그러고서는 바로 mysql.user를 조회해보았다.
된다.
추가
다른 함수나 트리거 생성후 어떤 유저로 실행했을때 안된다면 그것의 definer를 확인해보는게 먼저일듯하다. 두 유저가 다 써야한다면 root로 만들고 다 쓸수있는 권한을 부여!!
결론
mysql.user는 mysql.global_priv의 데이터를 바탕으로 만들어지는 뷰.
유저를 만들었을 시 처음으로 insert되는 곳이 바로 mysql.global_priv인거라고 생각하면된다.(like mysql.user -> mysql.global_priv)
oracle, mariadb, mssql, postgre 등등 DB별로 너무 다양하니까 어렵다.
