MariaDB - 데이터베이스 및 사용자 생성

1.1 데이터베이스 및 사용자 생성

MariaDB가 설치되면서 자동으로 또는 mysql_install_db라는 유틸리티에 의해서 기본적으로 몇 개의 데이터베이스와 테이블이 생성된다.

또한, 기본 데이터베이스와 함께 처음 MariaDB 서버에 접속할 수 있는 사용자 계정이 생성된다.

오늘은 기본적으로 생성되는 데이터베이스와 테이블 그리고 사용자 계정을 알아보도록 한다.

물론, 데이터베이스와 계정 생성 방법도 말이다.

 

1.1.1. MariaDB의 사용자 계정 인식과 권한

사용자 계정에 있어 다른 RDBMS와 차이가 조금 있다면 유저 이름 뿐만 아니라 유저가 접속을 시도하는 클라이언트(IP)도 인증의 일부가 된다는 것이다.

또한 이로 인해 MariaDB 서버 연결에서 문제가 발생하기로 하므로 주의하여 관리하도록 한다.

 

1.1.1.1. 사용자의 식별

MariaDB의 사용자는 다른 DBMS와는 조금 다르게 사용자의 계정뿐만 아니라 그 사용자의 접속 지점(클라이언트가 실행된 호스트명이나 도메인 또는 IP주소)도 계정의 일부가 된다. 그래서 MariaDB에서 계정을 언급할 때에는 다음과 같이 항상 아이디와 호스트를 함께 명시해야 한다. 아이디나 IP주소를 감싸는 역따옴표(`)는 MariaDB에서 식별자를 감싸주는 따옴표의 역할을 하는데 이는 종종 그냥 홑따옴표(')로도 자주 바뀌어서 사용된다. 다음의 사용자 계정은 svc_id라는 아이디로 접속할 때에만 사용될 수 있는 계정이다. 만약 사용자 계정에 다음의 계정만 등록되어 있다면 다른 컴퓨터로부터는 svc_id라는 아이디로 접속할 수 없음을 의미한다.

'svc_id'@'127.0.0.1'

 

만약 모든 외부의 클라이언트로부터 접속이 가능한 사용자 계정을 생성하려면 계정의 호스트 부분을 "%" 문자로 대체해주면 된다. 여기에서 "%" 문자는 모든 IP 또는 모든 호스트명을 의미한다.

'svc_id'@'%'

 

만약 중첩되는 계정이 여러 개가 있을 때, MariaDB서버는 인증 수행을 위해 중첩된 여러개의 계정들 중 권한이나 계정 정보에 있어서 범위가 가장 작은 것을 먼저 선택한다.

아래의 예시를 보자.

'svc_id'@'192.168.0.10' ( 이 계정의 비밀번호는 123) 'svc_id'@'%' ( 이 계정의 비밀번호는 abc)

위 두 계정 중에서 svc_id 계정으로 접속할 때 특정 client ip를 지정한 'svc_id'@'192.168.0.10' 유저 인증을 하게 된다.

 

1.1.1.2. 권한

MariaDB에서는 여러가지 권한(privileges)이 존재하는데, MariaDB 10.0 버전부터는 권한들을 Role로 생성하고 특정 사용자에게 Role을 부여하는 것도 가능하다.

 

MariaDB의 권한은 서버 전체, 데이터베이스, 테이블, 스토어드 프로그램(프로시저) 권한으로 나누어 볼 수 있다.

사용자에게 권한을 부여할 때에는 다음과 같이 GRANT 명령을 사용하는데, ON 절에는 대상 오브젝트를 명시해 주면 된다.

여기서 ON절에 명시하는 오브젝트는 다음과 같다.

 

오브젝트 범위	설명
"*.*"	대상 오브젝트가 MariaDB 서버 전체를 의미하며 이 경우 privilege_list에는 전역 권한만 사용해야 한다. 전역 권한은 잠시 후에 언급하도록 하겠다.
"db1.*"	대상 오브젝트가 특정 데이터베이스 하나를 지칭하며, privilege_list에는 전역 권한을 제외한 데이터베이스 레벨과 테이블 레벨 그리고 스토어드 프로그램(프로시저) 레벨의 권한을 명시할 수 있다.
"db1.table1"	대상 오브젝트가 특정 데이터베이스의 테이블 하나를 지칭하며, privilege_list에는 테이블 레벨의 권한만 사용할 수 있다.
"db1.stored_program1"	대상 오브젝트가 특정 데이터베이스의 스토어드 프로그램 하나를 지칭하며, privilege_list에는 스토어드 프로그램 레벨의 권한만 사용할 수 있다.

 

전역 권한 종류

권한(Privilege)	설명
CREATE USER	새로운 사용자를 생성할 수 있는 권한
FILE	LOAD DATA INFILE이나 LOAD_FILE()함수와 같이 디스크의 파일을 접근할 때 필요한 권한
GRANT OPTION	다른 사용자에게 권한을 부여할 수 있는 권한(GRANT 권한이 있더라도, 반드시 현재 사용자가 가진 권한들만 부여할 수 있다.)
PROCESS	SHOW PROCESSLIST 명령과 같이 MariaDB 서버 내의 프로세스를 조회할 수 있는 권한
RELOAD	FLUSH  ..... 명령을 실행할 수 있는 권한
REPLICATION CLIENT	SHOW MASTER STATUS나 SHOW SLAVE STATUS와 같이 복제 클라이언트에 관련된 정보를 조회할 수 있는 권한
REPLICATION SLAVE	슬레이브 MariaDB 서버가 마스터 MariaDB에 접속할 때 사용하는 사용자 계정에 필요한 권한(이 권한이 있어야 마스터 MariaDB에 발생한 변경 내역을 조회할 수 있다.)
SHOW DATABASES	데이터베이스 목록을 조회할 수 있는 권한
SHUTDOWN	MariaDB 서버를 종료할 수 있는 권한
SUPER	리눅스의 root와 같은 권한이라고 할 수는 없지만, 특정 상황에서 제한을 넘어서서 무언가를 할 수 있는 권한. 예를 들자면, read only 설정이 된 DB에서 데이터 변경을 할 수 있게 하거나 max_connections 제한으로 더 이상 커넥션을 생성할 수 없는 상황에서도 SUPER 권한을 소유한 유저는 1개의 커넥션을 더 생성할 수 있게 해주는 권한이라고 할 수 있다.   가능하면 일반 사용자나 서비스 계정에는 SUPER 권한을 부여하지 않는 것이 좋다.

 

데이터베이스 레벨 권한 종류

권한(Privilege)	설명
CREATE	새로운 데이터베이스를 생성할 수 있는 권한
CREATE ROUTINE	새로운 스토어드 프로시저나 함수를 생성할 수 있는 권한
CREATE TEMPORARY TABLES	임시 테이블을 생성할 수 있는 권한(내부 임시 테이블 생성과는 무관)
DROP	데이터베이스를 삭제할 수 있는 권한
EVENT	이벤트를 생성하고 삭제할 수 있는 권한
GRANT OPTION	데이터베이스에 대한 권한을 다른 사용자에게 권한을 부여할 수 있는 권한(현재 사용자가 가진 권한들만 부여할 수 있음)
LOCK TABLES	LOCK TABLES 명령을 이용하여 명시적으로 테이블을 잠글 수 있는 권한

 

테이블 레벨 권한 종류

권한(Privilege)	설명
ALTER	테이블 구조를 변경할 수 있는 권한
CREATE	테이블을 생성할 수 있는 권한
CREATE VIEW	뷰를 생성할 수 있는 권한
DELETE	테이블의 레코드를 삭제(DELETE)할 수 있는 권한
DROP	테이블이나 뷰를 삭제할 수 있는 권한
GRANT OPTION	테이블에 대한 권한을 다른 사용자에게 부여할 수 있는 권한
INDEX	CREATE INDEX명령으로 인덱스를 생성할 수 있는 권한(테이블을 생성 권한이 있다면 CREATE TABLE문장과 함께 인덱스를 생성하는 것이 가능)
INSERT	테이블에 레코드를 저장(INSERT)할 수 있는 권한
SELECT	테이블의 레코드를 조회할 수 있는 권한
SHOW VIEW	SHOW CREATE VIEW 명령을 이용하여 뷰의 구조를 조회할 수 있는 권한
TRIGGER	트리거 생성/삭제/실행에 대한 권한
UPDATE	테이블의 레코드를 변경(UPDATE)할 수 있는 권한

 

스토어드 프로그램 레벨 권한

권한(Privilege)	설명
ALTER ROUTINE	스토어드 프로그램(프로시저와 함수)의 내용을 변경할 수 있는 권한
EXECUTE	스토어드 프로그램(프로시저와 함수)을 실행할 수 있는 권한
GRANT OPTION	다른 사용자에게 스토어드 프로그램(프로시저와 함수)의 권한을 부여할 수 있는 권한

1.1.1.3. 권한의 부여

사용자에게 권한을 부여할 때는 GRANT 문장을 사용한다.

 

글로벌 권한

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

글로벌 권한을 부여하는 경우에는 ON절을 모든 DB의 모든 오브젝트를 지칭한다는 의미로 "*.*"을 명시해 주어야 한다.

특정DB에 대해서만 글로벌 권한을 부여할 수는 없기 때문에 글로벌 권한의 경우에는 특정DB나 특정 오브젝트를 명시할 수는 없는 것이다.

 

데이터베이스 권한

mariadb> GRANT EVENT ON *.* TO 'user'@'localhost'; mariadb> GRANT EVENT ON employees.* TO 'user'@'localhost';

DB권한은 특정DB에 대해서만 권한을 부여하거나 또는 서버에 존재하는 모든 DB에 대해서 권한을 부여하는 것이 가능하기 때문에 위의 예제와 같이 ON절에 "*.*"이나 "employees.*" 둘 다 사용 가능하다.

 

테이블 권한

## 서버에 있는 모든 데이터베이스에 소속된 오브젝트에 대해 권한 부여 mariadb> GRANT SELECT, INSERT, UPDATE, DELETE ON *.* TO 'user'@'localhost'; ## 특정DB의 오브젝트에 대해서만 권한 부여 mariadb> GRANT SELECT, INSERT, UPDATE, DELETE ON employees.* TO 'user'@'localhost'; ## 특정DB의 특정 테이블에 대해서만 권한 부여 mariadb> GRANT SELECT, INSERT, UPDATEM, DELETE ON employees.department TO 'user'@'localhost';

 

물론, 아래와 같이 UPDATE 권한 부여시에 컬럼별로 지정할 수도 있다.

mariadb> GRANT SELECT, INSERT, UPDATE (DEPT_NAME) ON EMPLOYEES.DEPARTMENT TO 'user'@'localhost';

* 주의사항 : 테이블이나 컬럼 단위까지의 권한은 잘 사용하지 않는다. 하나의 컬럼에 설정되는 상황이 있었을 때, 나머지 모든 테이블의 모든 컬럼에 대해서도 권한 체크가 발생되므로 성능저하 가능성 존재한다.

 

GRANT 명령에 의해 부여된 권한정보는 mysql 데이터베이스(MariaDB의 인증 및 딕셔너리 데이터베이스)의 user테이블과 host 테이블들에 저장된다.

 

1.1.1.4. 권한 그룹(role)

MariaDB 10.0.5 버전부터는 각각의 권한들을 사용자에게 할당하는 방식 뿐만 아니라 각각의 권한들을 하나의 권한 그룹(Role)으로 만들고 그 권한 그룹을 사용자에게 부여하는 방식을 사용할 수 있다.

role로 권한을 부여하고 나면 mysql 데이터베이스의 role 테이블에 기록된다.

## 뒤에 WITH ADMIN 옵션을 생략하면 default로 WITH ADMIN CURRENT_USER 옵션이 적용된다. mariadb> CREATE ROLE dba; mariadb> CREATE ROLE developer WITH ADMIN {CURRENT_USER | CURRENT_ROLE | user | role}

권한 그룹(role) 이름을 줄 때 PUBLIC, NONE은 예약어로 사용할 수 없다.

그리고, 아래와 같이 일반 유저에게 권한을 주듯이 role에 권한부여를 할 수 있다.

mariadb> GRANT ALL PRIVILEGES ON *.* TO dba; mariadb> GRANT SELECT, INSERT, UPDATE, DELETE ON 'db1'.* TO developer;

MariaDB에서는 안타깝게도 한 사용자가 2개 이상의 권한 그룹을 동시에 가질 수 없다.

만약 SET ROLE 명령을 실행하면 마지막으로 설정한 권한그룹(role)만 인식하게 된다.

## 현재 유저에게 ROLE 부여 mariadb> SET ROLE developer; ## 현재 적용된 ROLE 확인 mariadb> SELECT CURRENT_ROLE;

설정된 권한 그룹(role)을 제거할 때에는 다음과 같이 권한 그룹을 NONE으로 설정하면 된다.

## 현재 유저에게 부여된 ROLE 해제 mariadb> SET ROLE NONE; ## 현재 적용된 ROLE 확인 mariadb> SELECT CURRENT_ROLE;