mysql 사용자 추가 ( 특정IP접속허용)

>>사용자 확인

SELECT user,Host FROM mysql.user;

 

>> 권한설정

grant all privileges on *.* to ‘root’@’%’ identified by ‘root의 패스워드’;

*.* = DB.Table

‘root’@’%’ = 사용자@접속허용IP

 

>> 모든IP허용

INSERT INTO mysql.user (host,user,password) VALUES (‘%’,’root’,password(‘패스워드’));

GRANT ALL PRIVILEGES ON *.* TO ‘root’@’%’;

FLUSH PRIVILEGES;

 

>> IP대역 허용
INSERT INTO mysql.user (host,user,password) VALUES ('111.222.%','root',password('패스워드'));
GRANT ALL PRIVILEGES ON *.* TO 'root'@'111.222.%';
FLUSH PRIVILEGES;
>> 특정 IP 허용
INSERT INTO mysql.user (host,user,password) VALUES ('111.222.33.44','root',password('패스워드')); 
GRANT ALL PRIVILEGES ON *.* TO 'root'@'111.222.33.44'; 
FLUSH PRIVILEGES;
>> 원복
DELETE FROM mysql.user WHERE Host='%' AND User='root';
FLUSH PRIVILEGES;

2 Comments so far:

  1. master says:

    UPDATE user set password=password(“passwd”) where user = ‘$ID’;

  2. master says:

    사용자 계정 추가
    >use mysql;
    >insert into user(host,user,password) values(‘localhost’,’계정명’,password(‘비밀번호’));로컬접근 허용
    >insert into user(host,user,password) values(‘%’,’계정명’,password(‘비밀번호’));외부접근 허용
    >flush privileges;

    db 생성후 db에 계정연결
    >grant all privileges on DB명.* to 계정명@localhost identified by ‘비밀번호’ with grant option;
    >flush privileges;

    – 특정 사용자의 외부접근을 허용 (이미 만들어진 계정에서)
    >update user set host=’%’ where host=’localhost’ and user=’계정명’;
    >flush privileges;

Leave your Comment

Your email address will not be published. Required fields are marked *