[MySQL][Security] 계정생성과 권한

MySQL 설치와 환경설정이 끝났다면 다음은 사용자들 혹은 Application들이 접속할 수 있는 환경을 만들어 주어야 합니다. 흔히 계정이라 불리는 이것을 생성할때는 많은 고민을 가지시고 설정하여야 합니다. 데이터베이스 안의 테이블에는 수많은 데이터들이 있고 이 안에는 개인정보가 있을수도 있으며 각 접속 계정정보도 있습니다. 이런 정보들을 의도치 않는 사용자가 접속해 정보를 탈취해 간다면 개인정보에 의해 처벌받을 수도 있습니다.(상용 서비스 DB일경우) 그래서 계정관리는 꼼꼼히 권한 레벨에 맞게 부여되고 사용되어져야 합니다. 그리고 네트워크단에서도 데이터 통신이 이루어질때 암호화되는지도 체크해 보셔야 합니다. 밑에 데이터가 암호화되지 않을시 데이터가 어떻게 흘러가는지 눈으로 직접 확인해 보실 수 있습니다.

shell> tcpdump -l -i eth0 -w - src or dst port 3306 | strings

 

■ 계정생성

계정을 생성하는 방법은 다음과 같습니다.

mysql > create user '사용자명'@'접속호스트IP' identified by '비밀번호';

여기서 중요한 부분이 있습니다. 바로 ‘사용자명'@'접속호스트 IP'입니다.

 

만약 다음과 같은 에러가 나오면 기본 암호 생성 정책에 맞지 않게 생성한 것입니다.

mysql ERROR 1819 (HY000): Your password does not satisfy the current policy requirements 

MySQL은 기본 암호 정책을 만들수 있는데 예를 들면 몇자리 이상, 특수문자 포함등 원하는 암호 형식을 만들 수 있습니다.

(암호 생성 정책을 만드는 방법은 곧 만들어 가져오겠습니다.!)

 

 

+ 사용자명@접속호스트 IP

접속하는 계정이 어디서든 접속이 가능하게 만들지 아니면 특정호스트에서만 접속이 되게 만들지 결정하는 것입니다. 예를 들어 접속 계정명이 myuser1이고 접속하려는 호스트가 100.100.100.100 이라면 ‘myuser1’@‘100.100.100.100’ 이라고 입력하면 됩니다. 이렇게 하면 반드시 100.100.100.100의 IP를 가진 호스트만이 접속이 가능합니다. 만약 이 myuser1을 아무곳에서나 접속하게 만들고 싶으면 ‘myuser1’@‘%’ 를 하면 됩니다. 이렇게 하면 myuser1은 어떤 호스트에서든 접속이 가능하게 됩니다. 그러나 이런 접속계정은 보안상으로 좋지 않습니다. 만약 호스트IP가 어떤 이유로 계속 변경되게 되는데(예를 들면 DHCP를 쓰는 개인PC)이때 대여폭으로 IP를 열수도 있습니다. ‘myuser1’@‘172.16.%.%’ 혹은 ‘myuser1’@‘172.16.%’를 입력하게 되면 172.16으로 시작하는 IP를 가진 호스트는 모두 접속 할 수 있게 됩니다. 둘중에 편한 것으로 입력해 주시면 됩니다.

 

■ 권한 부여 전략

보통 rdbms에는 역활 혹은 회사의 환경이나 상황에 따라 수행할 수 있는 권한을 부여합니다. 부서에 따라 권한이 달라질 수 있으며, 개발부서 안에서도 역활에 따라 개발자 권한, 관리자 권한, DBA권한등으로 나눠서 부여합니다.

또한 DBMS의 권한 레벨에 따라 나눕니다. 보통 인스턴스 레벨, 데이터베이스 레벨, 오브젝트 레벨 정도로 나눕니다. 인스턴스 레벨은 시스템을 내렸다 올리거나 데이터베이스를 생성하고 삭제하는등의 막강한 레벨을 가지고 있습니다. 데이터베이스 레벨은 데이터베이스 안의 오브젝트 즉 테이블, 프로시져, 펑션 트리거등 DB안의 오브젝트를 생성, 삭제, 변경등을 할 수 있습니다. 마찬가지로 막강한 권한이라 할 수 있습니다. 다음은 오브젝트 레벨로 특정 테이블이나 프로시져, 펑션등만 사용할 수 있습니다. 특히 테이블은 특정 컬럼만 보게 할 수도 있습니다.

이런것들을 고려하여 회사의 팀이나 업무 부서등등의 환경을 고려하여 어떤 DBMS레벨의 어떤 권한등을 줄건지 결정해야 합니다. 매트릭스를 그려 제목엔 부서 그리고 왼쪽에 DBMS레벨, 위쪽엔 권한 종류를 쓰고 권한을 생각하다보면 알맞은 방법을 찾으실 수 있을 겁니다. 이건 하나의 참고 예제며 상황에 따라 얼마든지 바뀔수 있습니다.

 

위의 얘기를 간단히 얘기하면

instacne level > database level > object level

이렇게 표시할 수 있습니다.

인스턴스 레벨은 다시한번 강조드리지만 막강한 권한입니다. 함부로 부여되서는 안되며 가능하면 DBA를 제외한 대부분의 접속 계정은 최소한의 권한만 주는것을 원칙으로 하시는게 제일 좋습니다.

 

■ 권한 부여 및 회수 방법.

권한 부여 문법은 다음과 같습니다.

- 인스턴스 레벨 단위로 부여 방법

grant 권한명, 권한명 on *.* to '사용자명’@‘접속호스트IP'

 

- 데이터 베이스 레벨 단위로 부여 방법.

grant 권한명, 권한명 on 데이터베이스명.* to '사용자명’@‘접속호스트IP'

 

- 오브젝트 레벨로 부여 방법

grant 권한명, 권한명 on 데이터베이스명.오브젝트명 to '사용자명'@'접속호스트IP'

 

- 컬럼 단위 레벨로 부여 방법

grant 권한명(컬럼1, 컬럼2), 권한명(컬럼) on 데이터베이스명.테이블명 to '사용자명'@'접속호스트IP'

 

대표적으로 위의 4가지가 레벨별로 권한을 부여하는 방법입니다. 이중에서 반드시 주의해서 봐야 할 필드가 있습니다.

바로 [데이터베이스명.오브젝트명] 입니다.

앞에 데이터베이스명에 * 이 들어가면 모든 데이터베이스를 사용할 수 있게 됩니다. 물론 시스템 데이터베이스도 사용이 가능합니다. 그러나 특정 데이터베이스이름을 넣게 되면 그 데이터베이스안에서만 사용이 가능하게 됩니다. 즉 * 로 주게되면 인스턴스 레벨, 특정 데이터베이스를 명시하면 데이터베이스 레벨로 되게 되는 것입니다. 데이터베이스명.특정테이블명 을 주게 되면 특정데이터베이스의 특정 테이블만을 사용할 수 있게 됩니다.

 

그럼 예제를 통해 한번 알아보겠습니다.

 

ex) db1안의 모든 테이블과 오브젝트에 대해서 생성, 삭제(drop,delete), 변경, 갱신, 선택(select)할 수 있는 권한을 주세요. 호스트 IP는 100.100.100.100만 접속 할 수 있게 해주세요. 계정명은 user1입니다.

 

grant create, drop, select, insert, update, delete on db1.* to ‘user1’@‘100.100.100.100’

 

ex) db1안의 tb1테이블에 선택, 삽입, 갱신, 삭제 권한을 주세요. 호스트 IP는 172.16 대역만 접속 할 수 있게 해주세요. 계정명은 user2 입니다.

grant select, insert, update, delete on db1.tb1 to ‘user1’@‘172.16.%’

 

ex) db와 테이블, 인덱스를 생성할 수 있는 권한을 주세요. 호스트는 아무곳이나 접속할 수 있도록 해주세요. 계정명은 anyway입니다.

grant create on *.* to ‘anyway’@‘%’

 

ex) db1안에 모든 프로시저와 모든 펑션을 실행할 수 있는 권한을 주세요. 계정명은 p1이고 접속 호스트는 10.10.10.1니다.

 grant execute on db1.* to ‘anyway’@‘%’

 

ex) db1안에 특정 프로시저만 실행할 수 있는 권한을 주세요. 계정명은 p1이고 접속 호스트는 10.10.10.10입니다.

 grant execute on procedure db1.proc1 to ‘p1’@‘10.10.10.10’

 

ex) db1안에 특정 펑션만 실행할 수 있는 권한을 주세요. 계정명은 p1이고 접속 호스트는 10.10.10.10입니다.

 grant execute on function db1.fn1 to ‘p1’@‘10.10.10.10’

 

ex) 모든 권한을 주세요. 계정명은 p1이고 접속 호스트는 10.10.10.10입니다.(DBA권한)

grant all privileges on *.* to ‘p1’@‘10.10.10.10’

 

ex) db1에 대한 모든 권한을 주세요. 계정명은 p1이고 접속 호스트는 10.10.10.10입니다.(특정 DB에 대한 모든 권한)

grant all privileges on db1.* to ‘p1’@‘10.10.10.10’

 

- 특수한 경우

ex) db1의 tb1테이블에 select, insert, update, delete 권한을 주세요. 그리고 다른 사람에게 권한을 부여할 수 있는 권한을 주세요. 계정명은 p1이고 접속 호스트는 10.10.10.10입니다.

grant select, insert, update, delete on db1.tb1 to ‘p1’@‘10.10.10.10’ with grant option;

 

 

몇몇 예제를 통해 권한을 어떻게 부여하는지에 대해서 알아보았습니다.

 

권환 회수는 2가지만 바꾸면 됩니다.

grant 대신 revoke, to 대신 from입니다.

 

revoke select, insert, update, delete on 특정db.특정테이블 from ‘user1’@‘100.100.100.100’;

 

이미 부여된 권환에 대해서 회수를 하는 것이므로 특별히 예제를 알아보진 않겠습니다.

 

권한회수는 조심히 하셔야 합니다. 서비스중인 계정이라면 권한을 회수함으로서 서비스가 어디까지 여파가 미치는지 정확히 판단하신 후 적용하셔야 합니다. 개인계정은 다시 부여하면 되지만 특정 호스트에서 서비중인 계정은 운영에 문제를 일으키는 만큼 조심해서 하셔야 합니다.

 

 

 

■ 참고사항

권한을 부여할때 마지막 부분에 사용자명@접속호스트IP 방식으로 권한을 부여한다고 했습니다.

다음과 같은 사용자명은 같은 사용자명일까요, 다른 사용자명일까요?

‘user1’@‘100.100.100.100’;

‘user1’@‘100.100.%’;

‘user1’@‘100.%’;

정답은 모두 다른 사용자명입니다. IP가 틀리면 앞에 계정명이 같더라도 모두 다른 사용자입니다. 모두 다른 사용자이므로 필요한 권한을 모두 따로 주어야 합니다. 같은 계정명인데 왜 안되는지 의아해 하시는 분들이 가끔 계셔서 적어보았습니다.

 

■ 권한종류

권한 부여 방법을 알았으니 이제 권한 종류에 대해서 알아보겠습니다.

그전에 알아두셔야 할것이 있습니다. 처음에 말씀드렸던 레벨에 따른 권한입니다. 예를 들어 create 권한이 있습니다. 그런데 이 권한은 level에 따라 하늘과 땅차이입니다. database level의 권한이라면 table과 인덱스를 생성합니다. 그러나 instance level로 올라가면 database까지 생성이 가능하다는 것입니다. 이건 굉장히 중요한 문제입니다. 생성은 그렇다 치고 drop권한이 instance level이면 database를 삭제할 수 있다는 것입니다. 그러므로 level에 따른 범위를 생각하여 심사숙고 후 권한을 부여해야 합니다.

 

+ 권한들과 설명

권한명 : Grant table : level로 설명드립니다. 권한명은 권한 부여 명령어로 권한 부여시 입력되는 이름입니다. Grant table은 mysql.user에 보면 권한 종류들이 설정되어 있는걸 확인할 수 있습니다. 권한명과 비슷하지만 막상 보면 헷갈리는 것도 있습니다. 테이블로 권한 조회시 매핑하여 보시면 편리합니다. level은 instance, database, object 레벨을 얘기합니다.

 

- ALL [PRIVILEGES] : 없음 : instance, database

모든권한 부여입니다. DBA나 모든권한이 필요한 계정이 아닐 시 사용하시면 위험한 권한입니다.

 

- ALTER : Alter_priv : Tables

테이블에 대해 변경이 가능한 권한입니다. 컬럼이름, 테이블 이름, 컬럼 속성등을 변경할 때 필요합니다.

참고로 이 권한을 부여할때는 create, drop, insert 권한도 함께 부여해주시는것이 좋습니다. 예를 들어 컬럼을 컬럼을 추가하거나 삭제 한다고 가정할때, 추가는 insert이고 삭제는 drop입니다. 이런 권한들이 내부적으로 필요하게 됩니다.

 

- ALTER ROUTINE : Alter_routine_priv : Stored routines

프로시저나 펑션을 수정할 수 있는 권한입니다.

 

- CREATE : Create_priv : Databases, tables, or indexes

데이터베이스, 테이블, 인덱스를 생성할 수 있는 권한입니다.

 

- CREATE ROUTINE : Create_routine_priv : Stored routines

프로시져나 평션을 만들 수 있는 권한입니다.

 

- CREATE TABLESPACE : Create_tablespace_priv : Server administration

innodb상의 tablespace를 만들고 수정하고 삭제하고 변경할 수 있는 권한입니다.

 

- CREATE TEMPORARY TABLES : Create_tmp_table_priv : Tables

임시 테이블을 만들 수 있는 권한입니다. 접속된 해당 세션에서만 유효합니다.

 

- CREATE USER : Create_user_priv : Server administration

유저를 만들 수 있는 권한입니다. 인스턴스 레벨로 관리됩니다.

 

- CREATE VIEW : Create_view_priv : Views

뷰를 만들 수 있는 권한입니다.

 

- DELETE : Delete_priv : Tables

테이블의 데이터를 삭제할 수 있는 권한입니다.

 

- DROP : Drop_priv : Databases, tables, or views

데이터 베이스, 테이블, 뷰를 삭제할 수 있는 권한입니다. 위험한 권한 중 하나입니다.

 

- EVENT      : Event_priv : Databases

이벤트 스케쥴러를 만들고 수정하고 삭제하고 이벤트 상태를 볼수 있는 권한입니다.

 

- EXECUTE : Execute_priv : Stored routines

프로시져, 펑션을 수행할 수 있는 권한입니다.

 

- FILE : File_priv : File access on server host

외부 파일을 로드할 수 있는 권한입니다. load명령어나 select into를 통한 외부 파일을 불러올시 필요한 권한입니다. 반대로 외부로 테이블의 데이터를 외부로 내보낼때도 필요합니다.

 

- GRANT OPTION : Grant_priv : Databases, tables, or stored routines

본인 계정 이외에 다른 계정에 권한을 부여할 수 있는 권한입니다.

 

- INDEX : Index_priv : Tables

테이블에 인덱스를 생성할 수 있는 권한입니다.

 

- INSERT : Insert_priv : Tables or columns

테이블 혹은 테이블의 컬럼에 데이터를 삽입할 수 있는 권한입니다. ANALYZE TABLE, OPTIMIZE TABLE, REPAIR TABLE 명령시 이 권한이 필요합니다.

 

- LOCK TABLES : Lock_tables_priv : Databases

테이블에 록을 걸 수 있는 권한입니다.

 

- PROCESS : Process_priv : Server administration

서버의 프로세스 상태를 볼 수 있는 권한입니다.

 

- PROXY : See proxies_priv :  table,Server,administration

proxy 사용자를 구성해서 접속하는 권한입니다.

 

- REFERENCES : References_priv : Databases or tables

테이블을 생성할때 참조 제약을 걸수 있는 권한입니다.

 

- RELOAD : Reload_priv : Server administration

flush에 관련된 명령어를 수행하는 권한입니다.

flush 명령어는 flush-hosts, flush-logs, flush-privileges, flush-status, flush-tables, flush-threads같은 것들이 있는데 이때 필요한게 reload입니다.

 

- REPLICATION CLIENT : Repl_client_priv : Server administration

복제 관련 명령어들을 수행할때 쓰이는 권한입니다. 슬레이브 서버에서 마스터 서버 상태를 볼때나 슬레이브 상태에 대해 보는 명령어들로 SHOW MASTER STATUS, SHOW SLAVE STATUS, SHOW BINARY LOGS 의 명령문을 실행할때 필요한 권한입니다.

 

- REPLICATION SLAVE : Repl_slave_priv : Server administration

마스터서버에서 슬레이브의 상태를 볼때 필요한 권한입니다. SHOW SLAVE HOSTS, SHOW RELAYLOG EVENTS, SHOW BINLOG EVENTS 의 명령문을 쓸때 필요한 옵션입니다.

 

- SELECT : Select_priv : Tables or columns

테이블의 데이터를 불러올때 쓰는 sql입니다. explain을 사용하려면 필요한 권한이자 가장 기본적인 권한입니다.

 

- SHOW DATABASES : Show_db_priv : Server administration

인스턴스에 어떤 데이터베이스들이 있는지 볼 수 있는 옵션입니다. 

 

- SHOW VIEW : Show_view_priv : Views

뷰가 어떻게 만들어졌는지 볼 수 있는 권한입니다. explain을 사용할때도 역시 필요합니다.

 

- SHUTDOWN : Shutdown_priv : Server administration

외부에서 mysqladmin shutdown 명령문을 이용해 서버를 내릴 수 있는 권한입니다. C API function에서 mysql_shutdown()을 호출해서 서버를 내리리때도 이 권한이 있어야 가능합니다.

 

- SUPER      : Super_priv : Server administration

서버환경변수를 바꿀 수 있습니다.

글로벌 트랜잭션 캐릭터셋을 바꿀 수 있습니다.

복제 관련 명령어를 수행하며 복제를 시작, 중지를 할 수 있습니다.

이진 로그 명령을 수행할 수 있습니다.

접속된 세션을 끊을 수 있고, 각종 서버 명령어를 수행할 수 있습니다.

 

- TRIGGER : Trigger_priv : Tables

트리거를 생성하고 삭제하고 수행하는 권한입니다.

 

- UPDATE : Update_priv : Tables or columns

테이블의 데이터를 갱신할

 

- USAGE : Synonym for “no privileges” : Server administration

 

- Privilege : Grant Table Column : Context

 

+ 부여된 권한 확인방법.

SHOW GRANTS FOR 'user1'@'100.100.100.100';

이렇게 하면 부여된 권한을 확인해 볼 수 있습니다. 또한 이것을 이용해서 비슷한 권한을 부여해야 할 계정이 있다면 이것을 편집해서 실행시켜주시면 됩니다.

 

+ 암호를 바꾸는 방법.

두가지 방법이 있습니다.

SET PASSWORD FOR 'user1'@'100.100.100.100' = PASSWORD('password');

flush privileges

 

ALTER USER ‘사용자명’@‘접속호스트IP’ IDENTIFIED BY ‘비밀번호’;

 

+ 사용자 계정 삭제

사용자 삭제는 무척 간단합니다.

$ drop user ‘사용자명’@‘접속호스트IP’;

 

+ 기존계정 및 권한 처리

mysql의 편리하다면 편리한 점 하나가 바로 계정권한을 부여하면서 아이디 생성과 암호를 동시에 부여한다는 것입니다. 예를 들어 위에서의 예제로 설명드린다면

 

grant create, drop, select, insert, update, delete on db1.* to ‘user1’@‘100.100.100.100’ identified by ‘pw1234’

 

이런 권한 부여 명령어로 실행을 한다고 했을때 ‘user1’@‘100.100.100.100’ 의 속성을 가진 유저가 기존에 없다면 새로 생성이 됩니다. 있다면 암호가 바뀌고 권한도 현제 입력한 명령으로 권한이 추가 됩니다. 기존 권한이 있다면 무시됩니다.

 

■ 끝으로

전 개인적으로는 계정생성과 권한부여는 별도로 필요하다는게 저의 판단입니다. 만약 실수로 엉뚱한 계정이 생성되어 그 계정을 소유자가 아닌 다른 사람이 접속해서 사용하게 된다면, 또한 하필 그 권한들이 인스턴스 레벨의 권한을 가진 유저였다면 보안관련 사고가 날 수도 있기 때문입니다. 이건 시나리오 이므로 그런일이 없을수도 있지만 보안사고는 순간이기 때문에 계정생성과 권한 설젓은 반드시 별개로 수행하기를 권고드립니다.