MySQL 계정 및 권한 관리

Version - 5.7

-- 계정 생성

insert into user(host, user, authentication_string, ssl_cipher, x509_issuer, x509_subject)
values('%', 'account_srv', password('StrongPassword'), '', '', '');

--권한
grant all privileges on *.* to 'account_srv'@'%' identified by 'StrongPassword' with grant option;
grant select, insert, update, delete, execute on *.* to 'account_srv'@'%' identified by 'StrongPassword' with grant option;

flush privileges;

--- 비밀 번호 변경
UPDATE mysql.user SET Password=PASSWORD('StrongPassword') WHERE User='account_srv' AND Host='%';

SET PASSWORD FOR 'account_srv'@'%' = PASSWORD('StrongPassword');

ALTER USER 'account_srv'@'%' identified by 'StrongPassword';

flush privileges;

-- 권한 확인
# show grants for 'user'@'접속위치';
show grants for 'account_srv'@'%';

-- 계정 삭제
# drop user '계정아이디'@'접속위치';
drop user 'account_srv'@'%';

-- 권한 삭제
# revoke all on DB이름.테이블 FROM '계정아이디'@'접속위치';
revoke all on *.* FROM 'account_srv'@'%';

Version - 8.0

-- 사용자 정보 조회 
SELECT user,authentication_string,plugin,host FROM mysql.user; 

-- 사용자 생성 
create user 'account_srv'@'%' identified by 'StrongPassword'; 

-- 권한 부여 
GRANT ALL PRIVILEGES ON *.* TO 'account_srv'@'%'; 
GRANT GRANT OPTION ON *.* TO 'account_srv'@'%'; 

-- 비밀번호 plugin 변경 
ALTER USER 'account_srv'@'%' IDENTIFIED WITH mysql_native_password BY 'StrongPassword';