mysql create user how create new user mysql
이 자습서에서는 다양한 권한 부여 메커니즘, 암호 관리, 리소스 제한 옵션 등을 사용하여 MySQL 사용자 만들기 명령을 설명합니다.
MySQL CREATE USER 명령은 새 사용자를 생성하고 데이터베이스 / 테이블 등에 대한 세분화 된 액세스 권한을 부여하는 데 사용됩니다.
여러 사람이 액세스 수준이 다른 MySQL 서버 인스턴스를 사용합니다. 예를 들면 일부 사용자는 특정 데이터베이스에 대한 읽기 액세스 권한을 가지고 있으며, 마찬가지로 일부 사용자는 특정 데이터베이스에 대한 읽기-쓰기 액세스 권한을 가질 수 있습니다.
CREATE USER 명령은 MySQL 관리자가 MySQL 서버 인스턴스에 대한 사용자를 만들고 GRANT 쿼리를 사용하여 다른 권한을 부여하기 위해 더 자주 사용됩니다.
학습 내용 :
내 SQL CREATE USER 명령
CREATE USER 명령은 MySQL 서버 인스턴스에 새 계정을 생성하거나 추가하는 데 사용됩니다.
이 명령은 일반적으로 액세스 가능하거나 MySQL 관리자가 MySQL Server 인스턴스의 다른 사용자에 대한 액세스를 관리하는 데 사용됩니다.
CREATE USER 명령을 사용하여 사용자를 생성하는 동안 다음을 지정할 수 있습니다.
- MySQL에 연결하는 동안 사용해야하는 인증.
- 자원 제한
- 암호 관리 속성 : 암호 만료, 암호 재사용 설정.
- 계정 잠금 : 새로 생성 된 계정은 잠기거나 잠금 해제됩니다.
계정은 MySQL 시스템 테이블에서 생성됩니다. ‘mysql.user’
노트 : CREATE USER 명령을 사용하려면 사용자에게 CREATE_USER 권한이 있거나 MySQL 시스템 스키마.
가장 간단한 형식의 CREATE USER 명령 구문은 다음과 같습니다.
CREATE USER (IF NOT EXISTS) '{username}'@'{hostname}' IDENTIFIED BY '{passwordString}';선택 사항에 유의하십시오. 존재하지 않는 경우 . 이렇게하면 사용자가 이미 존재하는 경우 SQL 쿼리 결과가 경고 만 표시하고 오류는 발생하지 않습니다. 여기, '사용자 이름' 사용자가 연결할 실제 사용자 이름을 나타내며 ‘호스트 이름’ 사용자가 연결할 호스트를 나타냅니다.
참고, '호스트 이름'필드를 비워두면 호스트의 값은 모든 호스트가 지정된 사용자 이름으로 연결할 수 있도록 허용하는 '%'로 간주됩니다.
다음 구문으로 사용자를 만듭니다.
CREATE USER 'userx'@'localhost' IDENTIFIED BY 'password';위의 설명을 통해 사용자 이름이 'userx'이고 암호가 'password'인 사용자를 만들었습니다. 로컬 MySQL 인스턴스에서 사용자를 생성 할 때 호스트 이름은 localhost입니다.
mysql.users 테이블을 쿼리하여 우리가 생성 한 사용자의 항목을 확인해 보겠습니다.
SELECT * from mysql.user다음과 같은 출력이 표시됩니다.
처음 4 명의 사용자는 MySQL 설치 중에 미리 생성되고 마지막 항목은 우리가 생성 한 사용자입니다 (예 : 'userx').
이 시점에서 데이터베이스 생성 / 업데이트 / 쿼리 등의 측면에서 사용자에게 권한을 부여하지 않고 방금 사용자를 생성했다는 점에 유의해야합니다.
이 사용자로 MySQL 클라이언트로 로그인 해 보겠습니다.
터미널을 사용하여 아래 명령으로 연결할 수 있습니다.
$ /usr/local/mysql/bin/mysql -u userx -p암호를 입력하라는 메시지가 표시되면 암호를 'password'로 입력합니다.
Enter password: Welcome to the MySQL monitor. Commands end with ; or g. Your MySQL connection id is 33 Server version: 8.0.20 MySQL Community Server - GPL Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved. 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> 이제 CREATE DATABASE 키워드를 사용하여 새 데이터베이스를 생성합니다.
mysql> create database student; ERROR 1044 (42000): Access denied for user 'userx'@'localhost' to database 'student' 위에서 볼 수 있듯이 'userx'사용자가 DATABASE를 생성 할 권한이 없다는 오류 메시지가 생성됩니다. CREATE USER Command와 함께 사용할 수있는 다른 옵션을 살펴 보겠습니다.
인증 플러그인으로 사용자 생성
'인증 플러그인'은 인증 옵션을 지정하며 mysql.user 테이블의 플러그인 열에 저장됩니다. MySQL은 몇 가지 인증 플러그인을 지원합니다.
지원되는 일부 플러그인은 다음과 같습니다.
- MySQL 기본 비밀번호 해싱 : 기본 암호 해싱은 SHA1을 사용하여 mysql.users 테이블에 암호 값을 저장하는 것입니다. 이 메커니즘은 SHA1 암호 해싱보다 덜 안전하다고 간주됩니다.
- SHA2 256 암호 해싱 : 이것은 MySQL에서 사용하는 기본 인증 플러그인입니다. 즉, CREATE_USER 명령으로 인증 플러그인이 지정되지 않은 경우에도 기본적으로이 플러그인이 적용됩니다.
- LDAP를 사용한 외부 인증 : LDAP는 일반적으로 MySQL 인증을 조직의 Active Directory와 연결하는 데 사용됩니다. 예를 들면 Google SSO, OAuth 또는 Microsoft Outlook LDAP를 사용하여 인증합니다. 이를 위해서는 MySQL 측에도 LDAP 플러그인을 설치해야합니다. 자세한 내용은 여기.
>> 참조 여기 지원되는 플러그인의 전체 목록은
mysql.users 테이블에서 기본 인증 플러그인과 SHA2 인증 플러그인 및 해당 해시 값을 사용하여 사용자를 만들어 보겠습니다. 먼저 기본 인증 인 (SHA2)를 사용하여 사용자를 생성하고 mysql.user 표.
CREATE USER IF NOT EXISTS 'user-default'@'localhost' IDENTIFIED BY 'P@ssw0rd'mysql.users 테이블에서이 사용자 'user-default'에 대한 인증 플러그인을 살펴 보겠습니다.
Windows 10을위한 최고의 시스템 도구
SELECT host,user,plugin,authentication_string from mysql.user where user='user-default' 
여기 authentication_string에서 암호 문자열‘P @ ssw0rd’에 대한 SHA-256 값을 볼 수 있습니다.
이제 인증 플러그인을 사용하여 사용자를 생성 해 보겠습니다. 'MySQL 기본 비밀번호'를 입력하고 저장되는 플러그인의 가치를 확인하세요.
CREATE USER IF NOT EXISTS 'user-sha1'@'localhost' IDENTIFIED WITH mysql_native_password BY 'P@ssw0rd' SELECT host,user,plugin,authentication_string from mysql.user where user='user-sha1' 
역할이있는 사용자 생성
MySQL은 또한 새 사용자에게 미리 정의 된 역할을 할당합니다. 이러한 역할에는 이미 일부 또는 모든 데이터베이스에 대해 구성된 액세스 권한이 있습니다 (사용자 지정 가능). 예를 들면 데이터베이스에 대한 모든 권한을 할당 할 수있는 '개발자'라는 역할을 만들 수 있으며 나중에 동일한 역할을 사용하여 새 사용자에게 할당 할 수 있습니다.
예를 들어 보겠습니다.
- 개발자라는 역할 만들기
CREATE ROLE 'developer'- ‘test’라는 데이터베이스를 만듭니다.
CREATE DATABASE test- '개발자'역할에 대한 테스트 데이터베이스에 대한 권한 할당
GRANT ALL ON test.* TO 'developer'- 사용자 생성 및 개발자 역할 할당
CREATE USER IF NOT EXISTS 'user-with-role'@'localhost' DEFAULT ROLE developer;- 사용자가 테스트 데이터베이스에 테이블을 만들 수 있는지 확인합니다.
'user-with-role'이라는 사용자로 로그인을 시도하고 테스트 데이터베이스에 새 테이블을 생성 해 보겠습니다.
$ /usr/local/mysql/bin/mysql -u user-with-role Welcome to the MySQL monitor. Commands end with ; or g. Your MySQL connection id is 44 Server version: 8.0.20 MySQL Community Server - GPL Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved. 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> use test; Database changed mysql> create table test_table(name varchar(20)); Query OK, 0 rows affected (0.07 sec) SSL / TLS로 사용자 생성
SSL / TLS 옵션을 사용하여 사용자를 만들려면 클라이언트와 서버 모두에 SSL 인증서가 설치되어 있어야합니다. 기본적으로 SSL은 MySQL에 대해 구성되지 않습니다.
다음을 따르십시오 단계 MySQL 서버 인스턴스 용 SSL을 구성합니다.
SSL이 활성화 된 사용자를 생성하려면 사용자를 생성하는 동안 REQUIRE SSL 옵션을 사용할 수 있습니다.
CREATE USER 'user-with-ssl'@'localhost' REQUIRE SSL;마찬가지로 X509를 사용하여 생성하면 클라이언트 / 사용자가 유효한 X509 인증서를 가질 것으로 예상됩니다.
>> 참조 여기 X509에 대해 더 많이 이해합니다.
X509 인증서로 사용자를 만들려면 아래 쿼리를 사용하십시오.
CREATE USER 'user-with-x509'@'localhost' REQUIRE X509;암호 관리 옵션을 사용하여 사용자 생성
암호 옵션은 CREATE USER 명령을 사용하여 사용자를 만드는 동안 암호에 대한 정책을 설정하는 데 사용됩니다.
#1) 로그인시 비밀번호가 만료됩니다. 암호는 처음 사용 후 만료되며 사용자에게 암호를 변경하라는 메시지를 표시합니다.
CREATE USER IF NOT EXISTS 'test'@'localhost' IDENTIFIED BY 'Password' PASSWORD EXPIRE;#두) 고정 된 간격 후에 암호를 만료하십시오. 구성된 간격으로 암호 만료를 구성 할 수 있습니다. 예를 들면 90 일.
CREATE USER IF NOT EXISTS 'test'@'localhost' IDENTIFIED BY 'Password' PASSWORD EXPIRE INTERVAL 90 DAY;#삼) 구성된 재시도 후 암호가 잠 깁니다. 많은 경우 잘못된 재 시도를 'n'번 시도한 후 사용자 계정을 (구성된 기간 동안) 잠그는 것이 좋습니다. 아래 쿼리에서 사용자 계정은 5 번의 잘못된 시도 후 2 일 동안 잠 깁니다.
CREATE USER 'test'@'localhost' IDENTIFIED WITH caching_sha2_password BY 'Password' FAILED_LOGIN_ATTEMPTS 5 PASSWORD_LOCK_TIME 2; # 4) 새 암호는 구성된 이전 암호와 동일하지 않아야합니다.
아래 쿼리에서 사용자가 설정 한 새 비밀번호는 과거 2 개의 비밀번호와 동일하지 않아야합니다.
CREATE USER IF NOT EXISTS 'test'@'localhost' IDENTIFIED BY 'Password' PASSWORD EXPIRE PASSWORD HISTORY 2; 리소스 제한 옵션을 사용하여 사용자 생성
특히 프로덕션 MySQL 인스턴스의 경우 단일 사용자의 쿼리 / 요청으로 인해 데이터베이스가 압도되어 MySQL 서버 성능에 영향을 미칠 수있는 것을 방지하기 위해 리소스 제한이 필요합니다.
아래 3 가지 옵션 중 하나를 사용하여 리소스 제한을 설정할 수 있습니다.
# 1) MAX_QUERIES_PER_HOUR – 특정 사용자에게 시간당 허용되는 가져 오기 쿼리 수입니다.
CREATE USER 'user-with-resource-limits'@'localhost' WITH MAX_QUERIES_PER_HOUR 5사용자가 시간당 5 개 이상의 쿼리를 초과하려고하면 MySQL 서버에서 아래와 같은 오류가 발생한다고 가정합니다.
ERROR 1226 (42000): User 'user-with-resource-limits' has exceeded the 'max_questions' resource (current value: 5)# 2) MAX_UPDATES_PER_HOUR – 특정 사용자에게 시간당 허용되는 업데이트 쿼리 수.
CREATE USER 'user-with-resource-limits'@'localhost' WITH MAX_UPDATES_PER_HOUR 50# 3) MAX_CONNECTIONS_PER_HOUR – 계정이 시간당 서버에 연결할 수있는 횟수입니다.
CREATE USER 'user-with-resource-limits'@'localhost' WITH MAX_CONNECTIONS_PER_HOUR 50# 4) MAX_USER_CONNECTIONS – 지정된 사용자가 MySQL 서버 인스턴스에 동시에 연결 한 수입니다.
CREATE USER 'user-with-resource-limits'@'localhost' WITH MAX_USER_CONNECTIONS 50사용자를 생성하는 동안 위의 모든 옵션을 결합 할 수도 있습니다. MAX_QUERIES를 10으로 MAX_UPDATES를 100으로 지정하려는 경우 단일 CREATE_USER 쿼리를 다음과 같이 지정할 수 있습니다.
CREATE USER 'user-with-resource-limits'@'localhost' WITH MAX_UPDATES_PER_HOUR 100 MAX_QUERIES_PER_HOUR 10 MySQL 업데이트 및 사용자 삭제
MySQL은 두 가지 중요한 명령 인 ALTER USER 및 DROP USER를 제공하여 각각 기존 사용자를 수정하고 삭제합니다. 예제를 사용하여이 두 가지를 모두 이해하겠습니다.
사용자 변경
MySQL ALTER USER는 기존 MySQL 사용자 계정을 업데이트 / 수정하는 데 사용됩니다. 다음과 같은 용도로 사용할 수 있습니다.
- 리소스 제한 업데이트
- 비밀번호 옵션 설정
- 계정 잠금 및 잠금 해제 등
ALTER USER를 사용하여 잠긴 계정을 잠금 해제하는 예를 살펴 보겠습니다.
잠금 상태의 사용자를 생성 한 다음 ALTER USER 명령으로 잠금을 해제합니다.
CREATE USER 'test'@'localhost'I DENTIFIED BY 'Password' ACCOUNT LOCK;이제이 사용자로 로그인하면 계정 잠금 메시지가 표시됩니다 (사용자가 잠긴 계정 상태에서 생성되었으므로).
$ /usr/local/mysql/bin/mysql -u test -p Enter password: ERROR 3118 (HY000): Access denied for user 'test'@'localhost'. Account is locked. ALTER 명령으로 사용자 잠금을 해제하려면 아래 쿼리를 사용하십시오.
ALTER USER 'test'@'localhost' ACCOUNT UNLOCKMySQL 클라이언트를 사용하여 사용자로 로그인합니다.
$ /usr/local/mysql/bin/mysql -u test -p Enter password: Welcome to the MySQL monitor. Commands end with ; or g. Your MySQL connection id is 50 Server version: 8.0.20 MySQL Community Server - GPL Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved. 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> 사용자 삭제
MySQL DROP USER 명령은 하나 이상의 기존 사용자와 모든 관련 권한 및 부여를 제거합니다.
삭제중인 사용자가 삽입 한 데이터 / 생성 또는 업데이트 한 테이블은 삭제 / 삭제되지 않습니다.
누군가 존재하지 않는 사용자를 삭제하려고하면 DROP USER 명령이 오류를 발생시킵니다.
사용자를 만들고 DROP 명령을 사용하여 삭제하겠습니다.
CREATE USER 'test'@'localhost'; DROP USER 'test'@'localhost'; 자주 묻는 질문
Q # 1) MySQL의 사용자는 무엇입니까?
대답: MySQL의 사용자는 MySQL 서버 인스턴스에 로그인하고 다른 작업을 수행 할 수있는 계정 / 엔터티입니다. 사용자에게는 역할 기반 액세스 또는 하나 이상의 데이터베이스 및 / 또는 테이블에 대한 세분화 된 액세스가 할당 될 수 있습니다.
Q # 2) MySQL에 연결된 다른 사용자를 확인하는 방법은 무엇입니까?
대답: MySQL 서버 인스턴스에서 활성 사용자 세션을 보려면 아래와 같이 processlist 명령을 실행할 수 있습니다.
SHOW processlist;이 명령의 출력은 명령 실행시 연결된 활성 사용자 세션의 다른 속성을 표시합니다.
아래 샘플 출력 :
Q # 3) MySQL에서 사용자를 전환하려면 어떻게해야합니까?
대답: MySQL은 사용자 세션 개념으로 작동합니다. 지정된 사용자로 로그인하면 세션을 종료하거나 새 창에서 새 사용자에 대해 세션을 열거 나 워크 벤치와 같은 MySQL GUI 클라이언트에서 새 연결로 세션을 열 수 있습니다.
mysql 클라이언트에서 특정 사용자로 로그인하려면 아래 구문을 사용할 수 있습니다.
mysql -u {userName} -p위의 명령을 작성한 후 Enter를 누르면 {userName} 필드에 지정된 사용자의 비밀번호를 입력하라는 메시지가 표시됩니다. 암호가 확인되면 MySQL 셸 / 명령 프롬프트로 이동합니다.
별도의 사용자로 로그인하려면 exit 명령을 입력하여 현재 세션을 종료하고 다른 사용자로 다시 로그인 할 수 있습니다.
mysql> exit다른 사용자를 위해 MySQL 서버 인스턴스에 여러 연결을 설정할 수도 있습니다. 명령 프롬프트에 대한 새 탭 / 창을 열고 동일한 명령을 사용하여 다른 사용자로 로그인 할 수 있습니다.
mysql -u {userName2} -pQ # 4) MySQL 사용자를 읽기 전용으로 만드는 방법은 무엇입니까?
대답: MySQL은 데이터베이스 또는 테이블에 대한 세분화 된 액세스 권한을 부여하는 완전한 메커니즘을 제공합니다. 하나 이상의 데이터베이스 또는 테이블에 SELECT, UPDATE, INSERT 등과 같은 다양한 종류의 액세스를 제공 할 수 있습니다.
사용자에게 읽기 전용 액세스 권한 만 부여하려면 데이터베이스에 대한 SELECT 액세스 권한을 부여 할 수 있습니다 (모든 테이블에 *를 사용하거나 요구 사항에 따라 테이블 이름을 명시 적으로 언급).
아래 예를 통해이를 이해해 보겠습니다.
비밀번호가 'Password'인 'readaccess'라는 사용자를 생성합니다.
create user 'readaccess'@'localhost' IDENTIFIED BY 'Password'이제 MySQL에서 GRANT 키워드를 사용하여 'test'라는 데이터베이스의 모든 테이블에 대한 읽기 권한을 부여합니다.
grant select on test.* to 'readaccess'@'localhost'이제 액세스를 확인하기 위해 'readaccess'라는이 사용자로 로그인하고 데이터베이스 'test'내의 테이블을 쿼리 할 수 있습니다.
그러나 테스트 데이터베이스 내의 테이블에 데이터를 삽입하려고하면이 사용자에게 방금 읽기 액세스 권한을 명시 적으로 부여했기 때문에 액세스 거부 오류가 발생합니다.
Q # 5) MySQL에서 사용자를 어떻게 볼 수 있습니까?
대답: MySQL에는 MySQL 서버 인스턴스 용으로 생성 된 모든 사용자 목록이 포함 된 'mysql.user'라는 시스템 수준 테이블이 있습니다. 이 테이블에 대한 권한은 일반적으로 root 또는 admin 사용자 또는 MySQL 서버 인스턴스에 대한 인증 및 권한 부여를 관리하는 사람으로 제한됩니다.
Q # 6) MySQL 사용자의 이름과 비밀번호는 어떻게 찾나요?
대답: MySQL 사용자 및 비밀번호 매핑은 액세스가 제한된 테이블 인 'mysql.user'시스템 테이블에 저장됩니다. 암호는 사용자 생성 중에 선택한 암호화 모드에 따라 암호화 된 형식으로 저장됩니다.
그러나 누군가의 계정이 잠긴 경우 MySQL은 지정된 사용자 계정을 잠금 해제하는 데 사용할 수있는 ALTER USER라는 또 다른 명령을 제공합니다.
결론
이 자습서에서는 일반적으로 데이터베이스 관리자가 인증 및 액세스 관리에 사용하는 MySQL CREATE USER 명령에 대해 배웠습니다.
MySQL은 사전 정의 된 액세스 권한으로 새 사용자를 생성 할 수있는 강력한 인증 메커니즘과 역할 기반 할당을 많이 제공합니다. 또한 다른 권한 부여 메커니즘, 다른 암호 설정, 만료 옵션 등을 사용하여 사용자를 만드는 예도 보았습니다.