MySQL 사용자 계정 추가, 변경, 자원 제한

1. MySQL에 새로운 사용자 계정 추가하기

GRANT ALL PRIVILEGES ON DB명.테이블명(or *) to '계정이름'@'호스트명' IDENTIFIED BY '암호';

mysql> GRANT ALL PRIVILEGES ON *.* TO 'monty'@'localhost'

    ->     IDENTIFIED BY 'some_pass' WITH GRANT OPTION;

mysql> GRANT ALL PRIVILEGES ON *.* TO 'monty'@'%'

    ->     IDENTIFIED BY 'some_pass' WITH GRANT OPTION;

mysql> GRANT RELOAD,PROCESS ON *.* TO 'admin'@'localhost';

mysql> GRANT USAGE ON *.* TO 'dummy'@'localhost';

GRANT 명령문을 사용해서 생성된 계정은 아래와 같은 특성을 가지게 된다:

- 생성된 계정 중에 두 개는 사용자 이름 monty와 패스워드 some_pass를 가진다. 두 계정 모두 전체 권한을 가지고 있는 슈퍼유저 (superuser) 계정이다. 하나의 계정 ('monty'@'localhost')은 로컬 호스트에서 접속을 할 경우에만 사용된다. 또 다른 계정 ('monty'@'%')은 다른 호스트에서 접속을 하기 위해 사용된다. monty라는 이름으로 어디에서든지 접속을 할 수 있기 위해서는 monty에 대해 두 개의 계정을 모두 가지고 있는 것이 필요하다는 것을 알아 두기 바란다. localhost 계정이 없다면, monty가 로컬 호스트에서 접속을 할 때 mysql_install_db가 생성한 localhost에 대한 익명 사용자 계정이 우선적으로 사용된다. 그 결과로, monty는 하나의 익명 사용자처럼 취급을 받게 된다. 이렇게 되는 이유는 익명 사용자 계정이 'monty'@'%' 계정보다 더 구체적인 (more-specific) Host 컬럼 값을 가지기 때문이며 따라서 user 테이블 정렬 순서에서 보다 앞서 나오기 때문이다.

- admin이라는 이름의 패스워드가 없는 계정이 하나 있다. 이 계정은 로컬 호스트에서 접속하기 위해서만 사용된다. 이 계정은 RELOAD 와 PROCESS 관리 권한을 가지고 있다. 이러한 권한은 admin 사용자가 mysqladmin reload, mysqladmin refresh, 그리고 mysqladmin flush-xxx 명령문 뿐만 아니라, mysqladmin processlist도 실행할 수 있도록 해 준다. 어떤 데이터 베이스에 대해서도 접근할 수 있는 권한은 승인되지 않았다. 여러분은 나중에 GRANT 명령문을 입력해서 이러한 권한을 추가할 수가 있다.

- dummy라는 이름의 패스워드가 없는 계정이 하나 있다. 이 계정은 로컬 호스트에서 접속을 할 때에만 사용된다. 아무런 권한도 승인되지 않았다. GRANT 명령문에 있는 USAGE 권한은 여러분이 아무런 권한이 없는 계정을 만들 수 있게 해 준다. 이것은 모든 글로벌 권한을 'N'으로 설정하면 된다. 이 계정에 여러분이 특정 권한을 나중에 승인할 것이라는 가정을 한다.

2. MySQL에 새로운 사용자 비밀번호 변경

mysql> SET PASSWORD FOR 'jeffrey'@'%' = PASSWORD('biscuit');

혹은

mysql> INSERT INTO user (Host,User,Password)

    -> VALUES('%','jeffrey',PASSWORD('biscuit'));

mysql> FLUSH PRIVILEGES;

flush privileges; --  서버가 FLUSH PRIVILEGES를 실행해서 그랜트 테이블을 다시 읽어 옴으로써 변경된 권한이 적용되도록 해야 한다.

글로벌 레벨(ON *.*)에서 GRANT USAGE 명령문을 사용해서 한 계정의 현재 권한에는 영향을 미치지 않고 패스워드를 할당할 수가 있다:

mysql> GRANT USAGE ON *.* TO 'jeffrey'@'%' IDENTIFIED BY 'biscuit';

3. 계정 자원 제한하기

MySQL 5.0에서는, 각 개별 계정에서 대해 아래와 같이 서버 자원을 한정할 수가 있다:

한 계정이 시간 당 입력할 수 있는 쿼리의 숫자

한 계정이 시간 당 입력할 수 있는 업데이트의 숫자

한 계정이 시간 당 접속할 수 있는 숫자

mysql> GRANT ALL ON customer.* TO 'francis'@'localhost'

    ->     IDENTIFIED BY 'frank'

    ->     WITH MAX_QUERIES_PER_HOUR 20

    ->          MAX_UPDATES_PER_HOUR 10

    ->          MAX_CONNECTIONS_PER_HOUR 5

    ->          MAX_USER_CONNECTIONS 2;

제한 타입을 WITH 구문에 모두 거론할 필요는 없으나, 제한 타입의 이름들은 일정 순서로 표시될 수는 있다. 각각의 시간당 제한치 값은 시간당 카운트 되는 정수 값이 되어야 한다. 만약에 GRANT 명령문이 WITH 구문을 가지고 있지 않다면, 제한치는 각각 디폴트 0으로 설정된다 (즉, 제한 없음). MAX_USER_CONNECTIONS의 경우, 제한치는 그 계정이 어느 한 시점에 동시에 접속을 할 수 있는 최대 값을 나타내는 정수 값이 된다. 만일 이 값이 디폴트 0으로 설정되면, max_user_connections 시스템 변수 값이 그 계정의 동시 접속 숫자가 된다.

현재 존재하는 계정의 제한치를 설정 또는 변경하기 위해서는, 글로벌 레벨 (ON *.*)에서 GRANT USAGE 명령문을 사용한다. 아래의 명령문은 francis의 쿼리 한계를 100으로 변경한다:

mysql> GRANT USAGE ON *.* TO 'francis'@'localhost'

    ->     WITH MAX_QUERIES_PER_HOUR 100;

이 명령문은 계정의 현재 권한을 변경하지 않는 상태에서 지정된 제한 값만을 수정한다.

현재의 제한 값을 제거하기 위해서는, 그 값을 0으로 설정한다. 예를 들면, francis가 한 시간에 접속할 수 있는 제한 값을 제거하기 위해서는, 아래의 명령문을 사용한다:

mysql> GRANT USAGE ON *.* TO 'francis'@'localhost'

    ->     WITH MAX_CONNECTIONS_PER_HOUR 0;

현재의 시간당 자원 사용 카운트는 모든 계정에 대해 전체적으로, 또는 지정한 계정에 대해서만 개별적으로 리셋 시킬 수가 있다:

모든 계정의 현재 카운트 값을 0으로 리셋 시키기 위해서는, FLUSH USER_RESOURCES 명령문을 사용한다. 이 카운트 값은 그랜트 테이블을 다시 읽어 와서 리셋 시킬 수도 있다 (예를 들면, FLUSH PRIVILEGES 명령문 또는 mysqladmin reload 명령어를 사용해서).

개별 계정 카운트 값은 그것을 제한 값 중의 하나로 재 승인하면 0으로 설정된다. 이렇게 하기 위해서는, 앞에서 설명한 방식으로 GRANT USAGE를 사용하고 제한 값을 그 계정이 현재 가지고 있는 값과 동일하게 지정 한다.

카운터를 리셋하더라도 MAX_USER_CONNECTIONS 제한 값에는 영향을 주지 않는다.

서버가 시작될 때 모든 카운트는 0으로 리셋 된다.

4. 사용자 삭제

drop user admin@localhost;

flush privileges;

혹은

mysql> DLETE FROM USER WHERE USER='username';

mysql> FLUSH PRIVILEGES;

5. 사용자 권한 조회

select a.Db, b.* 

from db a, user b 

where a.user=b.user and a.host=b.host

order by 1,2,3

\G;

참조 : http://www.dbguide.net/db.db?mobile&cmd=view&boardUid=146586&boardConfigUid=9&boardIdx=127