[ MySQL 계정 관리 ]
[ MySQL 사용자 이름과 패스워드 ]
MySQL 계정은 사용자 이름 (username)과 사용자가 서버에 접속할 수 있는 클라이언트 호스트 또는 호스트라는 용어로 정의됩니다.
[ 사용자 확인 ]
사용자를 확인하기 위한 명령어입니다.
mysql> SHOW DATABASES;
위 명령어를 통해 자신의 데이터베이스 목록을 확인할 수 있습니다.
그 중 사용자를 확인하기 위해서는 mysql이라는 데이터베이스를 사용해야합니다.
mysql> use mysql;
mysql 데이터베이스 안에 user라는 사용자의 정보를 담은 테이블이 존재하는데 이를 조회합니다.
(‘DESC’를 사용하여 테이블 필드를 확인하고 본인이 필요한 정보를 가져오세요.)
mysql> SELECT host, user FROM user; +-----------+------------------+ | host | user | +-----------+------------------+ | localhost | mysql.infoschema | | localhost | mysql.session | | localhost | mysql.sys | | localhost | root | +-----------+------------------+
현재 4명의 사용자가 존재합니다.
이제 여기에 사용자를 추가하겠습니다.
[ 사용자 추가 ]
# 사용자 추가 mysql> CREATE user [사용자ID];
[ 사용자 패스워드 설정 ]
# 사용자(user)를 추가하면서 패스워드 설정 mysql> CREATE user 'macarong'@localhost IDENTIFIED BY '비밀번호'; mysql> CREATE user 'macarong'@'%' IDENTIFIED BY '비밀번호';
'%'는 외부 IP에서도 접근을 허용합니다.
mysql> CREATE USER testuser1; mysql> SELECT host, user FROM user; +-----------+------------------+ | host | user | +-----------+------------------+ | % | testuser1 | | localhost | mysql.infoschema | | localhost | mysql.session | | localhost | mysql.sys | | localhost | root | +-----------+------------------+
CREATE USER testuser1을 통해 testuser1을 추가하고 정보를 조회하면
기본값으로 host는 ‘%’로 어느 IP에서도 접근이 가능하게 만들어집니다.
mysql> CREATE USER 'testuser2'@'%' IDENTIFIED BY '1111'; Query OK, 0 rows affected (0.02 sec) mysql> SELECT host, user FROM user; +-----------+------------------+ | host | user | +-----------+------------------+ | % | testuser1 | | % | testuser2 | | localhost | mysql.infoschema | | localhost | mysql.session | | localhost | mysql.sys | | localhost | root | +-----------+------------------+
다른 명령어로 testuser2도 추가해보았습니다.
INSERT문을 통해 레코드를 기록하는 것 처럼, (Host, User, Password)값을 지정하여 사용자를 추가할 수도 있습니다.
[ 사용자 삭제 ]
다음 명령어를 통해 기존 사용자를 삭제할 수 있습니다.
mysql> DROP USER '사용자ID' mysql> DROP USER '사용자ID'@'localhost';
[ 사용자 권한 ]
사용자에 따라서 접근할 수 있는 DB 데이터와 사용할 수 있는 기능을 제한하는 것입니다.
사용자에게 권하을 추가하거나 제거하는 데 GRANT 와 REVOKE 명령을 사용하길 권장합니다.
IDENTIFIED BY ‘비밀번호’는 권한을 부여하면서 비밀번호까지 변경하고자 하는 경우 사용하며,
변경하고싶지 않다면 생략 가능합니다.
[ Grant (: 승인하다) ]
사용자를 생성하고, 권한을 부여하는 명령어.
#문법
GRANT [권한] ON 데이터베이스.테이블 TO '아이디'@'호스트' IDENTIFIED BY '비밀번호'
# 계정이 이미 존재하지만 'IDENTIFIED BY '비밀번호' 부분을 추가하면 비밀번호가 변경됩니다. mysql> GRANT ALL PRIVILEGES ON 데이터베이스명.테이블명 TO '아이디'@'HOST주소' IDENTIFIED BY '비밀번호'
[ 사용자 접속 제한 ]
DB 서버에 접속하는 사용자를 제한합니다.
아이디@호스트 중에서 호스트는 접속자가 사용하는 머신의 IP를 의미합니다.
IP를 특정하지 않으려면 ‘%’를 사용
user_id@123.0.0.0 - IP 123.0.0.1에서 접속한 ID user_id
user_id@% : IP 관계없이 ID가 user_id인 사용자
[ 대상의 제한 ]
사용자가 제어할 대상이 되는 데이터베이스, 테이블을 지정
를 사용하면 모든 데이터베이스, 테이블을 제어 대상으로 함. (., DATABASE.)
[ 권한(기능)의 제한 ]
사용할 수 있는 권한을 제한합니다.
| 유저 유형 | 접근 가능한 권한 |
|---|---|
| 개발자 | INSERT, UPDATE, DELETE, SELECT |
| 설계자 | ALTER, CREATE, DROP, INDEX, INSERT, UPDATE, DELETE, SELECT |
| DBA | ALL |
[ 예제 ]
예를 들어 분식집과 초밥집이 있습니다.
mysql> SHOW TABLES; +----------------+ | Tables_in_test | +----------------+ | boonsik | | JP_store | +----------------+
[ 분식집 테이블명 : boonsik ]
mysql> SELECT * FROM boonsik; +----+-----------------------+-------+ | id | food | price | +----+-----------------------+-------+ | 1 | 일반김밥 | 3000 | | 2 | 참치김밥 | 4000 | | 3 | 땡초김밥 | 4000 | | 4 | 계란김밥 | 4500 | | 5 | 떡볶이 | 3500 | | 6 | 라면 | 3000 | | 7 | 순대 | 3500 | | 8 | 모듬튀김 | 4000 | | 9 | 고구마튀김(2개) | 2000 | | 10 | 오징어튀김(2) | 2000 | | 11 | 닭꼬치 | 3000 | | 12 | 파닭꼬치 | 3500 | +----+-----------------------+-------+
[ 초밥집 테이블명 : JP_store]
mysql> SELECT * FROM JP_store; +----+------------------+-------+ | id | food | price | +----+------------------+-------+ | 1 | 모듬초밥 | 18000 | | 2 | 스페셜초밥 | 20000 | | 3 | 광어초밥(2p) | 2500 | | 4 | 연어초밥(2p) | 3000 | | 5 | 참치초밥(2p) | 5000 | | 6 | 우동 | 5000 | | 7 | 냉모밀 | 5000 | +----+------------------+-------+
testuser1에게 분식집에 가서 메뉴만 조회할 수 있도록 권한을 주고 싶습니다.
mysql> GRANT SELECT ON Test.boonsik TO testuser1@'%';
[권한]에 SELECT를 주고 분식집에 GRANT로 권한을 설정했을 경우
testuser1으로 접속 후 테이블 조회 시,
mysql> SHOW TABLES; +----------------+ | Tables_in_test | +----------------+ | boonsik | +----------------+
다음과 같이 testuser1은 분식집만 조회할 수 있게 되며,
mysql> SELECT * FROM boonsik; +----+-----------------------+-------+ | id | food | price | +----+-----------------------+-------+ | 1 | 일반김밥 | 3000 | | 2 | 참치김밥 | 4000 | | 3 | 땡초김밥 | 4000 | | 4 | 계란김밥 | 4500 | | 5 | 떡볶이 | 3500 | | 6 | 라면 | 3000 | | 7 | 순대 | 3500 | | 8 | 모듬튀김 | 4000 | | 9 | 고구마튀김(2개) | 2000 | | 10 | 오징어튀김(2) | 2000 | | 11 | 닭꼬치 | 3000 | | 12 | 파닭꼬치 | 3500 | +----+-----------------------+-------+ 12 rows in set (0.00 sec)
SELECT 명령어를 사용할 수 있습니다.
testuser1이 분식집에 메뉴를 추가하고 싶어도 SELECT 권한밖에 없으므로, INSERT와 같은 명령어 사용 시, 에러가 발생합니다.
mysql> INSERT INTO boonsik VALUES(null,'사이다',2000); ERROR 1142 (42000): INSERT command denied to user 'testuser1'@'localhost' for table 'boonsik'
ALL PREVILEGES란 모든 권한을 부여하는 것을 의미합니다.
# 사용자에게 test 데이터베이스의 모든 테이블에 대한 권한 부여 mysql> GRANT ALL PRIVILEGES ON Test.* To user_id@localhost # 사용자에게 test 데이터베이스 모든 테이블에 select, insert, update 권한 부여 mysql> GRANT SELECT, INSERT, UPDATE ON Test.* To user_id@localhost # 사용자에게 모든 데이터베이스의 모든 테이블에 권한 부여 mysql> GRANT ALL PRIVILEGES ON *.* user_id@localhost #전역 권한은 광범위한 보안 문제가 수반되므로 권한을 허용하는 경우 신중해야함. # 변경된 내용을 메모리에 반영 (권장 적용) mysql> FLUSH PRIVILEGES;
[ 사용자에게 부여된 권한 확인 ]
SHOW GRANTS FOR [user_id]@'host'
[ root 계정의 권한 ]
mysql> SHOW GRANTS FOR root@localhost; +--------------------------------------------------------------------------------------------+ | Grants for root@localhost > > > > > > > > > | +--------------------------------------------------------------------------------------------+ | GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE, CREATE ROLE, DROP ROLE ON *.* TO `root`@`localhost` WITH GRANT OPTION > > > > | | GRANT APPLICATION_PASSWORD_ADMIN,AUDIT_ABORT_EXEMPT,AUDIT_ADMIN,AUTHENTICATION_POLICY_ADMIN,BACKUP_ADMIN,BINLOG_ADMIN,BINLOG_ENCRYPTION_ADMIN,CLONE_ADMIN,CONNECTION_ADMIN,ENCRYPTION_KEY_ADMIN,FIREWALL_EXEMPT,FLUSH_OPTIMIZER_COSTS,FLUSH_STATUS,FLUSH_TABLES,FLUSH_USER_RESOURCES,GROUP_REPLICATION_ADMIN,GROUP_REPLICATION_STREAM,INNODB_REDO_LOG_ARCHIVE,INNODB_REDO_LOG_ENABLE,PASSWORDLESS_USER_ADMIN,PERSIST_RO_VARIABLES_ADMIN,REPLICATION_APPLIER,REPLICATION_SLAVE_ADMIN,RESOURCE_GROUP_ADMIN,RESOURCE_GROUP_USER,ROLE_ADMIN,SENSITIVE_VARIABLES_OBSERVER,SERVICE_CONNECTION_ADMIN,SESSION_VARIABLES_ADMIN,SET_USER_ID,SHOW_ROUTINE,SYSTEM_USER,SYSTEM_VARIABLES_ADMIN,TABLE_ENCRYPTION_ADMIN,XA_RECOVER_ADMIN ON *.* TO `root`@`localhost` WITH GRANT OPTION | | GRANT PROXY ON ``@`` TO `root`@`localhost` WITH GRANT OPTION > > > > > > > > > | +--------------------------------------------------------------------------------------------+ 3 rows in set (0.00 sec)
[ 사용자 계정의 권한 확인 ]
mysql> SHOW GRANTS FOR user_id@localhost; mysql> SHOW GRANTS FOR user_id; mysql> SHOW GRANTS FOR testuser1@'%'; +-----------------------------------------------------+ | Grants for testuser1@% | +-----------------------------------------------------+ | GRANT USAGE ON *.* TO `testuser1`@`%` | | GRANT CREATE ON `test`.* TO `testuser1`@`%` | | GRANT CREATE ON `mysql`.`test` TO `testuser1`@`%` | | GRANT SELECT ON `test`.`boonsik` TO `testuser1`@`%` | +-----------------------------------------------------+
현재 다음과 같은 권한이 주어져있습니다.
- test 데이터베이스에서 모든 테이블을 CREATE 권한이 있습니다.
- mysql 데이터베이스에서 test 테이블에 대해 CREATE 권한이 있습니다.
- test 데이터베이스에 booonsik 테이블에 대해 SELECT 권한이 있습니다.
[ 사용자 권한 해제 ]
mysql> REVOKE [권한] ON 데이터베이스.테이블 FROM [사용자명]
mysql> REVOKE CREATE ON mysql.test FROM testuser1@'%'; Query OK, 0 rows affected (0.03 sec) mysql> SHOW GRANTS FOR testuser1@'%'; +-----------------------------------------------------+ | Grants for testuser1@% | +-----------------------------------------------------+ | GRANT USAGE ON *.* TO `testuser1`@`%` | | GRANT CREATE, DROP ON `test`.* TO `testuser1`@`%` | | GRANT SELECT ON `test`.`boonsik` TO `testuser1`@`%` | +-----------------------------------------------------+ 3 rows in set (0.00 sec)
권한 해제 명령어를 통해
testuser1의 mysql 데이터베이스의 test 테이블 권한을 삭제했습니다.
[ 사용자 계정 삭제 ]
mysql> DROP USER user_id@’%’ mysql> DROP USER user_id@localhost;
mysql> DROP USER testuser2@'%' Query OK, 0 rows affected (0.01 sec) mysql> SELECT host,user FROM user; +-----------+------------------+ | host | user | +-----------+------------------+ | % | testuser1 | | localhost | mysql.infoschema | | localhost | mysql.session | | localhost | mysql.sys | | localhost | root | +-----------+------------------+
testuser2를 DROP했더니 사용자에서 testuser2 사용자가 삭제되었습니다.
[ 비밀번호 보안 ]
MySQL은 비밀번호가 일반 텍스트가 아닌 비밀번호에 해시를 씌워 보관합니다.
그렇다고 간단하게 만들면 안됩니다.
MySQL 서버에 접속 가능한 사람은 누구든지 무차별 대입공격 브루트-포스(brute-force) 방식으로 비밀번호를 알아내려고 시도할 수 있으며, MySQL은 유닉스 비밀번호에서 처럼 이런 공격을 감지하고 막아낼 방법이 없습니다.
