[인프라 구축] MySQL 새로운 계정 만들기

 

MySQL 새로운 계정 생성하기

MySQL 데이터베이스에 접속할 때 root 계정을 사용하는 것은 일반적으로 보안상 권장되지 않는다.

MySQL에서 root 계정 대신 사용할 새로운 계정을 생성하는 것은 좋은 보안 관행이다.

 

1. MySQL에 root 계정으로 로그인하기

먼저, root 계정 또는 다른 관리자 권한을 가진 계정으로 MySQL 서버에 로그인한다.

mysql -u root -p

 

사용된 옵션 의미 보기

-u root : 사용자 이름이 root임을 나타낸다.

-p : MySQL에 로그인할 때 비밀번호를 입력하라는 프롬프트가 나타나게 한다.

 

실습

ubuntu@ip-000-000-000-000:~$ docker exec -it mysql-server bash

bash-5.1# mysql -u root -p

Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 8.4.0 MySQL Community Server - GPL

Copyright (c) 2000, 2024, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

 

2. MySQL  사용자 계정 조회하기

다음 SQL 명령어를 실행하여 현재 존재하는 사용자 계정을 조회할 수 있다.

SELECT User, Host FROM mysql.user;

• mysql 데이터베이스의 user 테이블에서 사용자 이름(User)과 호스트(Host) 정보를 가져온다.
• 결과는 현재 MySQL 서버에 존재하는 사용자 계정 목록을 보여준다.

 

위의 명령어를 나누어 실행하면 아래와 같이 된다.

mysql> use mysql;
mysql> select host, user from user;

 

실습

mysql> SELECT User, Host FROM mysql.user;
+------------------+-----------+
| User             | Host      |
+------------------+-----------+
| root             | %         |
| mysql.infoschema | localhost |
| mysql.session    | localhost |
| mysql.sys        | localhost |
| root             | localhost |
+------------------+-----------+
5 rows in set (0.00 sec)

 

3. 새로운 사용자 계정 생성하기

새로운 사용자를 생성하려면 다음과 같은 SQL 명령어를 사용하여 새 사용자 이름과 비밀번호를 설정해야한다.

새로운 사용자를 어떤 호스트에 생성할지 지정할 수 있다. localhost, 특정 호스트, 모든 호스트 크게 3가지로 구분된다.

 

1. localhost에서만 접속할 수 있도록 제한

데이터베이스 서버를 로컬 컴퓨터에서 운영할 때, localhost를 사용하여 그 서비스에 접근할 수 있다.

CREATE USER '새사용자이름'@'localhost' IDENTIFIED BY '비밀번호';

• `새사용자이름` 칸에 입력하는 이름을 가진 사용자를 생성하고,
• 해당 사용자에게 로그인시 사용할 비밀번호를 설정한다.

예를 들어, MySQL 데이터베이스 서버를 자신의 컴퓨터에 설치하고 운영 중이라면, localhost를 사용하여 해당 데이터베이스 서버에 접속할 수 있다. 이 경우, localhost는 바로 그 컴퓨터를 가리키며, 다른 컴퓨터나 외부 네트워크를 통하지 않고 직접 로컬에서 접근하는 것을 의미한다.

 

2. 특정 호스트에서만 접속할 수 있도록 제한

CREATE USER '새사용자이름'@'특정호스트' IDENTIFIED BY '비밀번호';

• 특정 호스트에서만 접속하도록 제한하고 싶다면, 해당 호스트의 IP 주소나 도메인을 입력한다.

 

2. 모든 호스트에서 접속할 수 있도록 설정

외부에서의 접속을 허용하려면 'localhost' 대신 '%'를 사용한다.

CREATE USER '새사용자이름'@'%' IDENTIFIED BY '비밀번호';

• %는 모든 주소를 나타내며, 모든 호스트에서 접속할 수 있도록 설정한다.

 

실습

새로운 사용자 계정을 생성한다.

mysql> CREATE USER 'admin'@'%' IDENTIFIED BY 'pomorod0@0517';
Query OK, 0 rows affected (0.03 sec)

 

현재 존재하는 사용자 계정을 조회한다.

mysql> SELECT User, Host FROM mysql.user;
+------------------+-----------+
| User             | Host      |
+------------------+-----------+
| admin            | %         |
| root             | %         |
| mysql.infoschema | localhost |
| mysql.session    | localhost |
| mysql.sys        | localhost |
| root             | localhost |
+------------------+-----------+
6 rows in set (0.00 sec)

 

4. 새 사용자에게 데이터베이스 권한 부여하기

새 사용자가 데이터베이스에 대해 수행할 수 있는 작업을 정의하려면 권한을 부여해야 한다.

 

1. 새 사용자가 특정 데이터베이스의 모든 테이블에 대해 모든 권한을 갖기를 원한다면 '특정 데이터베이스 이름'.*을 사용한다. 

GRANT ALL PRIVILEGES ON mydb.* TO '새사용자이름'@'localhost';

GRANT ALL PRIVILEGES ON mydb.* TO '새사용자이름'@'%';

• mydb 데이터베이스의 모든 테이블에 대해 새 사용자가 모든 작업을 수행할 수 있는 권한을 부여한다.
• 즉, 해당 사용자에게 mydb  데이터베이스의 모든 권한을 부여한다.

 

2. 새 사용자가 모든 데이터베이스의 모든 테이블에 대해 모든 권한을 갖기를 원한다면 *.*를 사용한다.

GRANT ALL PRIVILEGES ON *.* TO '새사용자이름'@'localhost';

GRANT ALL PRIVILEGES ON *.* TO '새사용자이름'@'%';

 

사용된 옵션 의미 보기

• PRIVILEGES ON : 모든 DB 권한을 준다
• GRANT SELECT, INSERT, UPDATE ON : 특정 권한(CRU)을 준다.

 

실습

admin 계정에 pomodoro 데이터베이스에 대한 모든 권한을 부여한다.

mysql> GRANT ALL PRIVILEGES ON pomorodo.* TO 'admin'@'%';
Query OK, 0 rows affected (0.00 sec)

 

5. 변경사항 적용하기

변경된 권한을 즉시 적용하기 위해 아래 명령을 실행한다.

FLUSH PRIVILEGES;

 

실습

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.01 sec)

 

6.  새 사용자 권한 확인하기

MySQL에서 특정 사용자에게 부여된 권한을 확인하는 쿼리이다.

SHOW GRANTS FOR '새사용자이름'@'hostname';

SHOW GRANTS FOR '새사용자이름'@'%';

 

실습

1. 권한 부여 이전

mysql> SHOW GRANTS FOR 'admin'@'%';
+-----------------------------------+
| Grants for admin@%                |
+-----------------------------------+
| GRANT USAGE ON *.* TO `admin`@`%` |
+-----------------------------------+
1 row in set (0.00 sec)

 

2. pomorodo DB에 대한 권한 부여 후

mysql> SHOW GRANTS FOR 'admin'@'%';
+-----------------------------------------------------+
| Grants for admin@%                                  |
+-----------------------------------------------------+
| GRANT USAGE ON *.* TO `admin`@`%`                   |
| GRANT ALL PRIVILEGES ON `pomorodo`.* TO `admin`@`%` |
+-----------------------------------------------------+
2 rows in set (0.00 sec)

 

3. 모든 권한을 부여하면

mysql> SHOW GRANTS FOR 'admin'@'%';
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for admin@%                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 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 `admin`@`%`                                                                                                                                                                                                                                                                                                                                                                                                                                        |
| GRANT ALLOW_NONEXISTENT_DEFINER,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_ANY_DEFINER,SHOW_ROUTINE,SYSTEM_USER,SYSTEM_VARIABLES_ADMIN,TABLE_ENCRYPTION_ADMIN,TELEMETRY_LOG_ADMIN,TRANSACTION_GTID_TAG,XA_RECOVER_ADMIN ON *.* TO `admin`@`%` |
| GRANT ALL PRIVILEGES ON `pomorodo`.* TO `admin`@`%`                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
3 rows in set (0.00 sec)

 

7. 새 사용자 접속 테스트

mysql -u 새사용자이름 -p

 

실습

bash-5.1# mysql -u admin -p
Enter password:

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 12
Server version: 8.4.0 MySQL Community Server - GPL

Copyright (c) 2000, 2024, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

 

8. 사용자 삭제하기

DROP USER '새사용자이름'@'localhost';

DROP USER '새사용자이름'@'%';

 

실습

mysql> DROP USER 'admin'@'%';
Query OK, 0 rows affected (0.01 sec)

 

9. 권한 회수하기

권한을 회수하려면 REVOKE 문을 사용한다.

 

1. 'admin' 사용자에게 부여한 모든 권한 회수한다.

REVOKE ALL PRIVILEGES ON *.* FROM 'admin'@'%';

 

2. 'admin' 사용자에게 부여한 'pomorodo' 데이터베이스에 대한 모든 권한 취소한다.

REVOKE ALL PRIVILEGES ON pomorodo.* FROM 'admin'@'%';

 

실습

1. 모든 권한 회수

mysql> REVOKE ALL PRIVILEGES ON *.* FROM 'admin'@'%';
Query OK, 0 rows affected (0.01 sec)

 

2. 권한 확인

mysql> SHOW GRANTS FOR 'admin'@'%';
+-----------------------------------+
| Grants for admin@%                |
+-----------------------------------+
| GRANT USAGE ON *.* TO `admin`@`%` |
+-----------------------------------+
1 row in set (0.00 sec)