[mysql] 사용자 추가 및 권한

1. 사용자 생성

CREATE USER '아이디'@'localhost' identified by '비밀번호';

 

CREATE USER 'roadmap_usr'@'localhost' identified by 'roadmap';

CREATE USER 'roadmap_usr'@'%' identified by 'roadmap';

 

2. 권한 주기

grant 권한명(all privileges, select, insert, update.....) on 테이블명(kef_pms.USR , '*') to '아이디'@'localhost' identified by '비밀번호';

 

grant all privileges on kef_pms.USR to 'roadmap_usr'@'%' identified by 'roadmap';

grant all privileges on kef_pms.USR to 'roadmap_usr'@'localhost' identified by 'roadmap';

 

 

3. 권한 삭제

REVOKE 권한명 ON 테이블명 FROM '아이디'@'localhost' identified by '비밀번호';

REVOKE UPDATE, INSERT, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE VIEW, SHOW VIEW, TRIGGER ON  kef_pms.USR FROM 'roadmap_usr'@'localhost' identified by 'roadmap';

 

REVOKE UPDATE, INSERT, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE VIEW, SHOW VIEW, TRIGGER ON  kef_pms.USR FROM 'roadmap_usr'@'%' identified by 'roadmap';

 

4. 권한 조회

show grants for 'roadmap_usr'@'localhost';