[MySQL] 사용자 및 권한

 

 

사용자 및 권환 관리하기

 

 

사용자 식별

MySQL의 사용자는 다른 DMBS와는 조금 다르게 사용자의 계정뿐만 아니라 사용자의 접속지점(클라이언트가 실행된 호스트명이나 도메인 또느 IP주소)도 계정의 일부가된다. 따라서 계정을 언급할때는 아이디와 호스트를 함께 명시해야 한다.

 

다음과 같이 sql_id 라는 아이디로 접속할때만 사용할수 있는계정이다. 다른 사용자가 다음과 같은 계정만 등록돼 있다면  다른 컴퓨터에서는 sql_id 라는 아이디로 접속 할수 없다.

 

'sql_id'@'127.0.0.1'

 

다음과 같이 서버에 동일한 계정이 있을때 어떤것을 선택할까?

 

1. 'sql_id'@'192.168.0.10' (비밀번호 123)

2. 'sql_id'@% (비밀번호 0000)

 

인증을할때 범위는 가장 작은 것부터 사용하게된다 그래서 첫번째부터 인증을 하게된다. ip가 192.168.0.10 pc에서 2번째 계정으로 로그인을 하게된다면 비밀번호가 일치하지 않는다는 이유로 접속이 거절될수있다.

---

 

 

사용자 계정 관리

시스템 계정과 일반 계정

 

SYSTEM_USER 권을을 가지고 있느냐에 따라서 시스템 계정과 일반계정으로 구분된다.

시스템 계정은 서버 관리자를 위한 계정이며, 일반 계정은 응용 프로그램이나 개발자가를 위한 계정정도이다.

또한 시스템 계정은 일반 계정을 관리를 할수있지만 일반계정은 할수가없다.

 

MySQL 서버에는 3가지 계정을 가지고있다.

 

• mysql.infoschema : information_scehma 에정이된 뷰의 DEFINER로 사용되는 계정
• mysql.session : 플러그인이 서버로 접근할 때 사용되는 계정
• mysql.sys 기본으로 내장된 sys 스키마의 객체들의 DEFINER로 사용되는 계정

 

계정 생성

 

CREATE USER 'user'@'%'

IDENTIFIED WITH 'mysql_native_password' BY 'password'

REQUIRE NONE

PASSWORD EXPRIE INTERVAL 30 DAY

ACCOUNT UNLOCK

PASSWORD HISTORY DEFAULT

PASSWORD REUSE INTERVAL DEFAULT

PASSWORD REQUIRE CURRENT DEFAULT;

 

 

• IDENTIFIED : 사용자의 인증 방식과 비밀번호를 설정한다. 기본적으로 SHA-2(256비트) 알고리즘을 사용
• REQUIRE : 서버에 접속할때 암호화된 SSL/TLS 채널을 사용할지 여부를 결정한다.
• PASSWORD EXPRIE : 비밀번호 유효기간을 성정하는 옵션이다.
  • PASSOWRD EXPIRE : 계정생성과 동시에 비밀번호 만료 처리
  • PASSWORD EXPIRE NEVER : 계정 비밀번호의 만료 기간이 없음
  • PASSWORD EXPIRE DEFAULT:비밀번호의 유효기간 설정
  • PASSWORD EXPIRE INTERVAL n DAY : 비밀번호 유효기간을 오늘부터 n일자로 설정
• PASSOWD HISTORY : 한번 사용했던 비밀번호를 재사용하지 못하게 설정하는 옵션
• PASSWORD REUSE INTERVAL : 한번 사용했던 비밀번호의 재사용 금지 기간을 설정하는 옵션
• PASSWORD REQUIRE : 비밀번호가 만료되어 새로운 비밀번호를 변경할때 현재 비밀번호를 필요 하지 말지 결정하는 옵션
• ACCOUNT LOCK/UNLOCK : 계정 생성 시 도는 ALTER USER 명령을 사용해 계정 정보를 변경할 때 계정을 사용하지 못하게 잠글지 여부를 결정

 

---

 

 

비밀번호 관리

MySQL 서버에서 비밀번호의 유효성을 체크 규칙을 적용하려면 validate_password 컴포넌트를 설치해야한다.

INSTAL COMPONENT 'file://compoent_validate_password'; 통해 설치

비밀번호 정책은 3가지로 적용된다. 기본 값으로는 MEDIUM으로 설정

• LOW : 비밀번호의 길이만 검증
• MEDIUM : 비밀번호의 길이를 검증하며, 숫자와 대소문자 그리고 특수문자의 배합을 검증
• STRONG: MEDIUM 레벨의 검증을 모두 수행하며, 금칙어가 포함됬는지 검증

만약에 금칙어나 연속된 '1234','QWER;을 금지하고 싶다면 password.dictionary_file 시스템 변수에 금칙어 파일을 저장하면된다.

 

SET GLOBAL validate_password.dictionary_file='금칙어 파일';

SET GLOBAL validate_password.polic='STRONG';

 

---

 

권한(Privilege)

테이터베이스나 테이블 이외의 객체에 적용되는 권한을 글로벌 권한이라고 하며, 데이터 베이스나 테이블을 제어하는 데 필요한 권한을 객체 권한이라고 한다.

 

객체 권한은 GRANT 명령으로 권한을 부여할대 반드시 특정 객체를 명시해야 하며, 글로벌 권한을 GRANT 명령에서 특정 객체를 명시하지 말아야한다.

 

글로벌 권한 부여

GRANT SUPER ON *.* TO 'user'@'localhost';

 

글러벌 권한은 특정 DB나 테이블에 부여될수 없기 떄문에 글로벌 권한을 부여할때 GRANT 명령의 ON절에는 항상 *.*을 사용해야한다.

권한을 확인 하려면, 해당 계정에서 아래와 같은 명령어를 확인하면 확인할수 있다.

 

SHOW GRANTS FOR '계정이름'@'호스트';

 

테이블 권한

1.GRANT SELECT,INSERT,UPDATE,DELETE ON *.* TO 'user'@'localhost';

2.GRANT SELECT,INSERT,UPDATE,DELETE ON testdb.* TO 'user'@'localhost';

3.GRANT SELECT,INSERT,UPDATE,DELETE ON 'testdb','testtable'@'localhost';

 

첫번쨰, 서버의 모든 DB에 대해 권한을 부여한다.

두번쨰, 특정 DB의 오브젝트에 대해서만 권한을 부여한다.

세번째, 특정 DB의 특정 테이블에 대해서만 권한을 부여한다.

 

여러가지 레벨이나 범위로 권한을 설정하는 것이 가능하지만 테이블이나 칼럼 단위의 권한을 잘사용 하지 않는다. 칼럼 단위의 권한이 하나라도 설정되면 나머지 모든 테이블의 모든 칼럼에 대해서도 권한을 체크하기 때문에 성능에 영향을 미칠수있다.

 

---

 

역활(ROLE)

MySQL 8.0 버전 부터는 권한을 묶어서 역활(ROLE)을 사용할수 있다.

 

 

권한을 생성

CREATE ROLE role_testdb_read, role_testdb_write;

 

역활의 권한 부여

GRANT SELECT ON testdb.* TO role_testdb_read;

GRANT INSERT,UPDATE,DELETE ON testdb.* TO role_testdb_write;

 

계정 생성

CREATE USER reader@'localhost' IDENTIFIED BY 'password';

CREATE USER writer@'localhost' IDENTIFIED BY 'password';

 

계정의 권한 부여

GRANT role_testdb_read TO reader@'loaclhost';

GRANT role_testdb_read, role_testdb_write  TO writer@'loaclhost';

 

하지만 권한은 초기에는 설정이 되어있지않아서 직접 권한을 활성화 해줘야합니다.

SET ROLE 'role_testdb_rad';

권환을 확인하려면

SELECT current_role();

명령어를 통해서 확인이 가능하다.

 

역활 자동화 활성화

매번 계정의 권한을 부여하고 일일히 활성화 하는것은 매우 귀찮은 일 일것이다. 그래서 자동 활성화 명령어를 통해서 권환을 부여시 자동으로 활성화하는 명령어를 사용하면된다.

SET GLOBAL activate_all_roles_on_login_ON;

 

 

---

참고

RealMySQL8.0 1권