MySQL 계정 생성 및 권한

 

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

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

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

권한 부여의 범위

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

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

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

작업별 권한 부여

SELECT: 데이터를 조회할 수 있는 권한입니다. 보고서 작성이나 데이터 분석 작업에 필요합니다. INSERT: 테이블에 새로운 데이터를 추가할 수 있는 권한입니다. UPDATE: 기존 데이터를 수정할 수 있는 권한입니다. DELETE: 데이터를 삭제할 수 있는 권한입니다. ALTER: 테이블 구조를 변경할 수 있는 권한입니다. 테이블에 컬럼을 추가하거나, 컬럼 타입을 변경할 때 필요합니다.

권한 적용

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

권한 제거

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

시나리오 1 - 사용자 계정 생성

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 

-- 문제 1 새로운 계성 tenco2 를 생성하고 각 조장이 사용하고 있는 
-- RDBMS에  tenco2 라는 계정으로 접근해 보세요 

-- 현재 나의 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';