[MySQL] MySQL 계정 생성 및 권한

MySQL에서 새 사용자 계정을 생성하고, 해당 사용자에게 데이터베이스 권한을 부여하는 과정은 데이터베이스 보안과 관리의 중요한 부분이다. 이러한 과정을 통해 각 사용자가 접근할 수 있는 데이터를 통제하고, 시스템을 보다 안전하게 유지할 수 있다.

 

계정을 생성 및 권한 부여 방법(버전에 따라 방식이 조금 달라졌다.)

1. 사용자 생성: 먼저 CREATE USER 명령을 사용하여 사용자를 생성하고 비밀번호를 설정한다.
2. 권한 부여: 사용자 생성 후, GRANT 명령을 사용하여 해당 사용자에게 권한을 부여한다.

권한 부여의 범위

전체 권한 부여: 사용자에게 모든 데이터베이스의 모든 테이블에 대한 모든 권한을 부여한다. 이는 매우 강력한 권한이며 주로 데이터베이스 관리자에게 부여된다.

특정 데이터베이스에 대한 권한 부여: 사용자에게 특정 데이터베이스에 대한 권한을 부여한다. 이 경우, 사용자는 해당 데이터베이스의 모든 테이블에 대해 지정된 권한을 갖게 된다.

특정 테이블에 대한 권한 부여: 사용자에게 특정 데이터베이스의 특정 테이블에 대한 권한만 부여할 수 있다. 이는 권한을 보다 세밀하게 제어하고자 할 때 사용된다.

작업별 권한 부여

SELECT: 데이터를 조회할 수 있는 권한이다. 보고서 작성이나 데이터 분석 작업에 필요하다.

INSERT: 테이블에 새로운 데이터를 추가할 수 있는 권한이다.

UPDATE: 기존 데이터를 수정할 수 있는 권한이다.

DELETE: 데이터를 삭제할 수 있는 권한이다.

LTER: 테이블 구조를 변경할 수 있는 권한이다. 테이블에 컬럼을 추가하거나, 컬럼 타입을 변경할 때 필요하다.

권한 적용

부여된 권한을 데이터베이스 시스템에 적용하기 위해서는 FLUSH PRIVILEGES 명령을 실행해야 한다. 이 명령은 권한 변경사항을 즉시 적용한다.

권한 제거

사용자의 권한을 제거하고 싶을 때는 REVOKE 명령을 사용한다.

시나리오 - 사용자 계정 생성
MySQL 8.0에서는 CREATE USER 명령을 사용하여 새로운 사용자 계정을 생성한다. 이 때 비밀번호와 함께 생성해야 한다.

-- 사용자 생성하기 (비밀번호 함께 설정) 

-- 사용자 계정 생성은 (원격지, 로컬 환경) 이 있다. 

-- 원격지에서 접근하는 사용자 계정 생성 
-- 루트 권한을 제외하고 비밀번호 설정에 대한 제약이 있을 수 있다. 
create user 'tenco1'@'%' identified by '1q2w3e4r5t!';  

-- localhost 에서 접근 가능한 계정 생성 
create user 'tenco1'@'localhost' identified by '1q2w3e4r5t!';

-- 사용자 계정 권한 확인 
show grants for 'tenco1'@'%';
show grants for 'tenco1'@'localhost';

-- 작업별 권한을 할당 
-- grant select, insert, update, delete on mydb2.* to 'tenco1'@'%';
grant select, update, delete on mydb2.* to 'tenco1'@'%';
grant select, update, delete on mydb2.* to 'tenco1'@'localhost';

-- root 권한으로 새로운 사용자 생성 및 권한 할당 
-- 권한 바로 적용하기(세션 종료 후에 재접속 하면 적용 됨) 
flush privileges;

-- 사용자 생성하기 (비밀번호 함께 설정) 
-- 사용자 계정 생성은 (원격지, 로컬 환경) 이 있다. 

-- 원격지에서 접근하는 사용자 계정 생성 
-- 루트 권한을 제외하고 비밀번호 설정에 대한 제약이 있을 수 있다. 
create user 'tenco1'@'%' identified by '1q2w3e4r5t!';  

-- localhost 에서 접근 가능한 계정 생성 
create user 'tenco1'@'localhost' identified by '1q2w3e4r5t!';

-- 사용자 계정 권한 확인 
show grants for 'tenco1'@'%';
show grants for 'tenco1'@'localhost';

-- 작업별 권한을 할당 
-- grant select, insert, update, delete on mydb2.* to 'tenco1'@'%';
grant select, update, delete on mydb2.* to 'tenco1'@'%';
grant select, update, delete on mydb2.* to 'tenco1'@'localhost';

-- root 권한으로 새로운 사용자 생성 및 권한 할당 
-- 권한 바로 적용하기(세션 종료 후에 재접속 하면 적용 됨) 
flush privileges;

use mydb2;
show grants for 'tenco1'@'localhost';
select * from tb_class;
insert into tb_class values(2024, 'db', '야스오');
-- update, delete 


-- 현재 나의 RDBMS에 연결되어 있는 세션을 확인하는 명령어 
SHOW PROCESSLIST;

 

보안 고려사항

• 접근 제한: 외부 네트워크에서 접근하는 경우, 사용자의 호스트 옵션을 **'%'**에서 더 구체적인 IP 범위나 호스트명으로 제한하는 것이 좋다.
• 비밀번호 정책: 강력하고 복잡한 비밀번호를 사용하고 주기적으로 변경하여 계정 보안을 강화한다.

MySQL에서 특정 사용자에게 모든 데이터베이스와 모든 테이블에 대한 권한을 할당하려면 GRANT 명령어를 사용한다.

GRANT ALL PRIVILEGES ON *.* TO 'username'@'host';

MySQL 8.0에서의 localhost와 % 구분

• MySQL은 localhost로의 접속을 특별하게 다룬다. localhost는 네트워크를 통한 접속이 아니라, MySQL 서버가 설치된 기계의 로컬 인터페이스를 통한 직접 접속을 의미한다. 이는 UNIX 소켓을 사용하여 처리되므로, IP 네트워크를 통한 접속과는 다르게 취급된다.
• 만약 사용자가 'tenco1'@'localhost'로 명시적으로 생성되지 않았다면, 'tenco1'@'%'로 설정된 권한으로 localhost에서 접속하려 할 때 문제가 발생할 수 있다.

-- localhost 에서 접근 가능한 계정 생성 
CREATE USER 'tenco1'@'localhost' IDENTIFIED BY '1q2w3e4r5t!';
-- 권한 부여 
GRANT SELECT,INSERT, UPDATE, DELETE ON green_shcool.* TO 'tenco1'@'localhost';
-- 권한 확인 
SHOW GRANTS FOR 'tenco1'@'localhost';