[MySQL] MySQL 권한

 

 

1. MySQL의 기본테이블
MySQL은 처음 설치시에 'mysql'이라는 데이타베이스를 생성한다. 이 데이타베이스는 db,host,user,
tables_priv,columns_priv, function라는 기본테이블을 가지고 있다. 이 테이블들은 MySQL의 권한과
관련이 있다.

2. MySQL의 기본테이블과 권한
(1) user테이블 : 전체서버에 적용되는 권한정보를 포함하고 있다.
(2) host테이블 : 호스트전체에 대한 서버의 접근권한을 부여한다.
(3) db테이블 : 각각의 데이타베이스에 접근 권한을 설정한다.
(4) tables_priv : 테이블에 대한 접근권한을 설정한다.
(5) columns_priv : 행에 대한 접근 권한을 설정한다.
(6) function : 사용자정의 mysql함수가 등록되는 테이블이다.

3. user테이블
(1) 개요: 전체 서버에 우선적으로 적용되는 테이블고 사용자별 권한 정보를 담고 있는 테이블이다.
(2) 특징
    MySQL서버에 사용자 계정을 만들고 패스워드를 부여하는 테이블이다. user테이블의 권한과
   db테이블의 권한은 상당한 차이가 있다. user테이블에서 권한을 허가하는 것은 전체
   데이터베이스에서 모든 권한을 허가하는 것과 같다. 따라서, 기본값으로 'N'을 주어야 한다.
(3) 기본구조
    mysql> show columns from user;
   +-----------------+-----------------+------+-----+---------+-------+
   | Field           | Type            | Null | Key | Default | Extra |
   +-----------------+-----------------+------+-----+---------+-------+
   | Host            | char(60) binary |      | PRI |         |       |
   | User            | char(16) binary |      | PRI |         |       |
   | Password        | char(16) binary |      |     |         |       |
   | Select_priv     | enum('N','Y')   |      |     | N       |       |
   | Insert_priv     | enum('N','Y')   |      |     | N       |       |
   | Update_priv     | enum('N','Y')   |      |     | N       |       |
   | Delete_priv     | enum('N','Y')   |      |     | N       |       |
   | Create_priv     | enum('N','Y')   |      |     | N       |       |
   | Drop_priv       | enum('N','Y')   |      |     | N       |       |
   | Reload_priv     | enum('N','Y')   |      |     | N       |       |
   | Shutdown_priv   | enum('N','Y')   |      |     | N       |       |
   | Process_priv    | enum('N','Y')   |      |     | N       |       |
   | File_priv       | enum('N','Y')   |      |     | N       |       |
   | Grant_priv      | enum('N','Y')   |      |     | N       |       |
   | References_priv | enum('N','Y')   |      |     | N       |       |
   | Index_priv      | enum('N','Y')   |      |     | N       |       |
   | Alter_priv      | enum('N','Y')   |      |     | N       |       |
   +-----------------+-----------------+------+-----+---------+-------+
(4) user테이블의 범위 필드값
   1) Host :  호스트 이름이나 IP주소를 입력할 수 있다. 디폴트는 localhost이다. '%'라는 와일드
            값을 사용한다. '%'는 일반적인 운영체제의 '*'와 유사하다. 특정한 이름이 주어지지
            않으면 모든 이름을 포괄한다. 즉 어떠한 호스트로부터도 접근 가능합니다.
   2) User : 와일드 값을 허용하지 않는다. 모든 유저에 해당하는 공백으로 지정할 수는 있다. 연결
            하려는 목록에 공백 사용자 이름이 있다면 클라이언트에서 실제로 지정한 이름 대신에
            그 사용자는 익명 사용자, 이름이 없는 사용자로서 간주된다.
   3) Password : 공백으로 둘 수 있다. 이것은 아무 비밀번호나 사용할 수 있다는 것을 의미하는 것
                이 아니라, 사용하는 비밀번호를 지정하지 않고 연결해야 한다는 의미이다.
(5) 접근권한
   Select_priv   : select문을 수행할 수 있는 권한
   Insert_priv   : insert문을 수행할 수 있는 권한
   Update_priv   : update문을 수행할 수 있는 권한
   Delete_priv   : delete문을 수행할 수 있는 권한
   Create_priv   : create문을 수행하거나 테이블을 생성할 수 있는 권한
   Drop_priv     : drop문을 수행하거나 데이타베이스를 삭제할 수 있는 권한
   Reload_priv   : mysqladmin reload명령을 이용하여 접근정보를 다시 읽을 수 있는 권한
   Shutdown_priv : mysqladmin shutdown명령을 이용하여 서버를 종료시킬 수 있는 권한
   Process_priv  : 서버 프로세스를 관리할 수 있는 권한
   File_priv     : select into outfile과 load data infile과 같은 명령을 이용하여 파일에 읽고
                  쓸 수 있는 권한
   Grant_priv    : 자신의 권한을 남에게 부여할 수 있는 권한
   References_priv : 사용하지 않음
   Index_priv    : 인덱스를 만들거나 삭제할 수 있는 권한
   Alter_priv    : alter table문을 수행할 수 있는 권한

(참고) 접근권한에서 유의할점
  (1) select_priv는 테이블에서 데이터를 가져오는 권한만을 지정한다. select 2+3;과 같은 select
     에는 해당하지 않는 권한이다.
  (2) 일반사용자에게는 file_priv권한을 주지 않는 것이 좋다. 특히 MySQL서버를 시스템의 root
     사용자로 실행중일 때는 일반 사용자에게 특히 주지 말아야 한다.
  (3) process_priv도 일반 사용자에게 권한을 주지 않는 것이 좋다.
  (4) shutdown_priv는 권한이 있는 사용자에게 MySQL서버 종료를 하게 해주므로 유의해야 한다.
  (5) 단순히 테이블에서 데이터를 가져올 목적인 사용자에게는 update, delete, drop, alter권한은
     주지 않는 것이 좋다.

4.db테이블
(1) 개요: 보통 여러 개의 데이타베이스가 존재하는 데 db테이블은 각각의 데이타베이스에 대한
          퍼미션을 설정한다.
(2) 기본구조
   mysql> show columns from db;
   +-----------------+-----------------+------+-----+---------+-------+
   | Field           | Type            | Null | Key | Default | Extra |
   +-----------------+-----------------+------+-----+---------+-------+
   | Host            | char(60) binary |      | PRI |         |       |
   | Db              | char(64) binary |      | PRI |         |       |
   | User            | char(16) binary |      | PRI |         |       |
   | Select_priv     | enum('N','Y')   |      |     | N       |       |
   | Insert_priv     | enum('N','Y')   |      |     | N       |       |
   | Update_priv     | enum('N','Y')   |      |     | N       |       |
   | Delete_priv     | enum('N','Y')   |      |     | N       |       |
   | Create_priv     | enum('N','Y')   |      |     | N       |       |
   | Drop_priv       | enum('N','Y')   |      |     | N       |       |
   | Grant_priv      | enum('N','Y')   |      |     | N       |       |
   | References_priv | enum('N','Y')   |      |     | N       |       |
   | Index_priv      | enum('N','Y')   |      |     | N       |       |
   | Alter_priv      | enum('N','Y')   |      |     | N       |       |
   +-----------------+-----------------+------+-----+---------+-------+
(3) 특징: user테이블과 매우 유사하고, 차이점은 password행 대신에 이 테이블은 db행을 갖는다.
          이 테이블은 특정한 데이타베이스에서 어떠한 사용자의 권한을 관리한다. user 테이블
          퍼미션은 전체서버에 영향을 미치기 때문에 user테이블의 권한이 db테이블의 권한보다
          우선하게 된다. 즉 user테이블에 insert권한이 있다면, 이 사용자는 전체 데이타베이스의
          db테이블에 insert권한을 가지게 된다.

(참고) 효과적인 사용자관리
user테이블에서는 퍼미션을 갖지 않는 사용자를 생성하고, 사용자db테이블에서 권한을 얻어야 효과적
이다.

5. host테이블
(1) 기본구조
   mysql> show columns from host;
   +-----------------+-----------------+------+-----+---------+-------+
   | Field           | Type            | Null | Key | Default | Extra |
   +-----------------+-----------------+------+-----+---------+-------+
   | Host            | char(60) binary |      | PRI |         |       |
   | Db              | char(64) binary |      | PRI |         |       |
   | Select_priv     | enum('N','Y')   |      |     | N       |       |
   | Insert_priv     | enum('N','Y')   |      |     | N       |       |
   | Update_priv     | enum('N','Y')   |      |     | N       |       |
   | Delete_priv     | enum('N','Y')   |      |     | N       |       |
   | Create_priv     | enum('N','Y')   |      |     | N       |       |
   | Drop_priv       | enum('N','Y')   |      |     | N       |       |
   | Grant_priv      | enum('N','Y')   |      |     | N       |       |
   | References_priv | enum('N','Y')   |      |     | N       |       |
   | Index_priv      | enum('N','Y')   |      |     | N       |       |
   | Alter_priv      | enum('N','Y')   |      |     | N       |       |
   +-----------------+-----------------+------+-----+---------+-------+
(2) 특징: host테이블은 사용자에게 호스트단위로 기본권한을 설정할 수 있도록 한다. MySQL이
          수행권한을 검사할 때 db테이블에서 사용자이름과 호스트를 검사한다. 만약 호스트필드가
          비어있는 사용자를 만나게 되면, host테이블을 검사하여 두 가지 각각의 권한들이
          중첩되는 권한을 부여하게 된다.

6. tables_priv와 columns_priv테이블
이 두개의 테이블은 db테이블이 수행하는 일들을 보다 세분화시킨 것이다. 즉, 어떠한 오퍼레이션이
먼저 db테이블을 참조하고 다음에 table_priv, 마지막으로 columns_priv를 참조하게 된다. 만약 이중
하나라도 허가가 되어 있으면 오퍼레이션이 허가된다. 이 두개의 테이블을 이용하여 사용자의 권한을
행단위까지 자세하게 세분화할 수 있다. 이 테이블의 내용은 SQL에서 grant와 revoke를 이용하여 조작
할 수 있다.

7. 권한부여의 단계
(1) MySQL은 user테이블에서 사용자가 연결해오는 호스트이름과 사용자이름을 찾는다.
(2) 명령어가 입력되면 user테이블의 정보와 일치하는 것이 존재하고 권한이 허가되어 있으면 요청
    한 작업은 바로 수행된다. 그렇지 않는 경우에는 db테이블=> tables_priv => columns_priv 순서
    로 권한을 찾는다.

출처: https://czar.tistory.com/177 [너는 개발자냐?]