MySQL 계정 생성 및 권한설정

[MySQL에서는 계정을 생성하기 위한 순서]

1. root 계정으로 접속

2. use mysql; 쿼리문을 통해 mysql 데이터 베이스에 접속

3. user 테이블에 사용자 계정 추가

4. 사용자 계정 권한 설정

5. 수정한 계정 권한 정보를 최종 적용

[3. user 테이블에 사용자 계정 추가]

위에서 1번과 2번은 생략하겠습니다. MySQL에서는 계정을 생성하기 위해 mysql DB의 user 테이블을 이용합니다. desc user; 라는 쿼리를 통해서 user 테이블의 컬럼을 보면 아래와 같습니다.

Field	Type	Null	Key	Default
Host	char(60)	NO	PRI
User	char(16)	NO	PRI
Password	char(41)	NO
Select_priv	enum('N','Y')	NO		N
Insert_priv	enum('N','Y')	NO		N
Update_priv	enum('N','Y')	NO		N
Delete_priv	enum('N','Y')	NO		N
Create_priv	enum('N','Y')	NO		N
Drop_priv	enum('N','Y')	NO		N
Reload_priv	enum('N','Y')	NO		N
Shutdown_priv	enum('N','Y')	NO		N
Process_priv	enum('N','Y')	NO		N
File_priv	enum('N','Y')	NO		N
Grant_priv	enum('N','Y')	NO		N
References_priv	enum('N','Y')	NO		N
Index_priv	enum('N','Y')	NO		N
Alter_priv	enum('N','Y')	NO		N
Show_db_priv	enum('N','Y')	NO		N
Super_priv	enum('N','Y')	NO		N
Create_tmp_table_priv	enum('N','Y')	NO		N
Lock_tables_priv	enum('N','Y')	NO		N
Execute_priv	enum('N','Y')	NO		N
Repl_slave_priv	enum('N','Y')	NO		N
Repl_client_priv	enum('N','Y')	NO		N
Create_view_priv	enum('N','Y')	NO		N
Show_view_priv	enum('N','Y')	NO		N
Create_routine_priv	enum('N','Y')	NO		N
Alter_routine_priv	enum('N','Y')	NO		N
Create_user_priv	enum('N','Y')	NO		N
Event_priv	enum('N','Y')	NO		N
Trigger_priv	enum('N','Y')	NO		N
Create_tablespace_priv	enum('N','Y')	NO		N
ssl_type	enum('','ANY','X509','SPECIFIED')	NO
ssl_cipher	blob	NO		NULL
x509_issuer	blob	NO		NULL
x509_subject	blob	NO		NULL
max_questions	int(11) unsigned	NO		0
max_updates	int(11) unsigned	NO		0
max_connections	int(11) unsigned	NO		0
max_user_connections	int(11) unsigned	NO		0
plugin	char(64)	YES
authentication_string	text	YES		NULL
password_expired	enum('N','Y')	NO		N

위 내용을 보시면 아시겠지만 계정에 대한 정보(계정명, 비밀번호 등) 뿐만 아니라 계정에 대한 권한도 함께 있는 것을 알 수 있습니다. 따라서 계정을 생성할 때 권한을 같이 지정할 수 있고, 혹은 계정을 생성한 뒤에 Update 문으로 권한을 부여할 수 있습니다. 아래는 계정을 생성하기 위한 쿼리문입니다.

형식)

insert into user(host, user, password, ssl_cipher, x509_issuer, x509_subject) values('IP', '계정명', password('비밀번호'), '', '', '');

ex)

insert into user(host, user, password, ssl_cipher, x509_issuer, x509_subject) values('%', 'ekoon', password('ekoon'), '', '', '');

위 쿼리문을 실행하시면 계정이 추가됩니다. 위 쿼리문에서 각 필드에 대한 설명을 하면 아래와 같습니다.

[host]

해당 계정이 접속가능한 IP주소입니다. 다음과 같이 입력할 수 있습니다.

 - localhost : MySQL이 설치된 서버나 PC에서 접속할 경우 localhost를 입력합니다.

 - % : 특정 아이피 구분없이 어디에서든지 접근하기 위해서 % 기호를 입력합니다.

 - 특정아이피 : 입력한 IP주소를 가지는 컴퓨터에서만 접속이 가능합니다.

※ 주의사항 ※

위 항목에서 %를 입력하였다 하더라도 자기 자신의 PC(MySQL이 설치된 PC나 서버)에서 접속이 되지 않을 수 있습니다. 따라서 %와 더불어 localhost 항목을 가지는 계정을 같이 추가하셔야 합니다.

[user]

계정명입니다. 생성하고자 하는 계정의 이름으로, 쉽게 접속하실 때 사용하는 ID라고 보시면 되겠습니다.

 

[password]

해당 계정이 사용할 비밀번호입니다. 비밀번호를 설정하거나 변경시 password() 함수를 사용해야 합니다. 비밀번호는 평문데이터가 아닌 암호화 된 데이터로 저장해야 하기 때문입니다.

[4. 사용자 계정 권한 설정]

그 다음 아래와 같이 계정 권한을 설정해야 합니다. 각 항목별로 서로 다른 권한을 가지고 있습니다. 따라서 각 개별로 권한을 설정이 가능하고, 아래의 경우는 모든 권한을 이용할 수 있도록 하는 명령어입니다.

update user set

Select_priv = 'Y',

Insert_priv = 'Y',

Update_priv = 'Y',

Delete_priv = 'Y',

Create_priv = 'Y',

Drop_priv = 'Y',

Reload_priv = 'Y',

Shutdown_priv = 'Y',

Process_priv = 'Y',

File_priv = 'Y',

Grant_priv = 'Y',

References_priv = 'Y',

Index_priv = 'Y',

Alter_priv = 'Y',

Show_db_priv = 'Y',

Super_priv = 'Y',

Create_tmp_table_priv = 'Y',

Lock_tables_priv = 'Y',

Execute_priv = 'Y',

Repl_slave_priv = 'Y',

Repl_client_priv = 'Y',

Create_view_priv = 'Y',

Show_view_priv = 'Y',

Create_routine_priv = 'Y',

Alter_routine_priv = 'Y',

Create_user_priv = 'Y',

Event_priv = 'Y',

Trigger_priv = 'Y',

Create_tablespace_priv = 'Y'

where user = '계정명';

위 SQL문은 mysql DB의 user 테이블에 있는 각 컬럼의 값을 Y로 Update 하는 SQL문입니다. 처음에 계정을 생성하게 되면 이러한 항목들이 Default로 N 값으로 저장이 됩니다. 따라서 이 처럼 Update 문을 사용하여 풀어주고자 하는 권한을 Y로 설정할 수 있습니다.

위 방법이 복잡하고 어렵다면 아래 SQL문으로 대체가 가능합니다.

GRANT ALL PRIVILEGES on *.* TO '계정명'@'localhost(혹은 특정 아이피)';

계정명과 localhost(혹은 특정 아이피) 

항목은 user 테이블에 사용자 계정을 추가하기 위해 insert 문을 실행했을때 입력한 host와 계정명을 입력하시면 됩니다.

예를 들어서

insert into user(host, user, password, ssl_cipher, x509_issuer, x509_subject) values('%', 'ekoon', password('ekoon'), '', '', '');

위 SQL문을 이용하여 사용자 계정을 추가하였다면, 아래와 같이 입력하시면 됩니다.

GRANT ALL PRIVILEGES on *.* TO 'ekoon'@'%';

[5. 수정한 계정 권한 정보를 최종 적용]

마지막으로 지금까지 설정한 정보를 MySQL에 적용시키기 위해서 아래 SQL문을 실행하시면 됩니다.

flush privileges;