mariaDB, database, user 관리

들어가며

• mariaDB 를 사용하였지만 heidi 혹은 dbeaver 등 제한적인 방식으로 사용했다.
• 오라클을 배웠을 때 JDBC 드라이브나 Connection pool을 사용했고, 그래서 Statements로 생쿼리를 날렸다. mariaDB을 사용할 때 persistence framework와 함께 썼기 때문에, 쌩쿼리를 사용할 기회가 많지 않았다.
• 리눅스에서 DB를 사용할 경우가 많은데, prompt 조건에서 익숙하게 사용하지 못한다. 보통 db tool(heidi, dbeaver)를 사용했기 때문이다.
• 그래서 이번 기회에 mariaDB를 익히고, prompt 에서 익숙해질 수 있도록 학습하는 것이 목표이다.
• {} 의 경우 입력해야 할 값을 의미한다.
• > 은 커맨드 입력을 의미한다. > 이 없는 것은 출력한 값으로 하겠다.

기본적인 문법과 데이타베이스

> mysql -u root -p
> {비밀번호}
> exit

• 위의 방식으로 로그인, 로그아웃을 한다.
• exit 으로 로그아웃하며 프로그램을 종료한다.

MariaDB[(none)]>

• 로그인을 하면 위와 같은 화면을 보여준다.
• 괄호 안은 내가 있는 데이타베이스를 의미하며, none 어떤 데이타베이스에도 속하지 않음을 의미한다.

> select database();

+------------+
| database() |
+------------+
| NULL       |
+------------+

> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
+--------------------+
> use mysql
Database changed
MariaDB [mysql]>

> select database();
+------------+
| database() |
+------------+
| mysql      |
+------------+

• 나는 show databases 와 select database() 은 다르다. 전자는 전체 데이타베이스의 목록(혹은 허가된 데이타베이스의 목록)을 의미하며 후자는 현재 내가 있는 위치를 의미했다.

데이타베이스 생성, 유저의 생성 및 권한

> create database unlocked_db;
> create database locked_db;
> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| locked_db          |
| mysql              |
| performance_schema |
| unlocked_db        |
+--------------------+

• 위와 같이 데이타베이스 두 개를 생성했다.

> create user test@'localhost';
> select host, user from mysql.user;

+-----------------+-------------+
| Host            | User        |
+-----------------+-------------+
| 127.0.0.1       | root        |
| ::1             | root        |
| localhost       | mariadb.sys |
| localhost       | root        |
| localhost       | test        |
+-----------------+-------------+

• 위와 같이 user를 생성했다.
• user를 검색할 때 반드시 mysql(데이타베이스).user(테이블) 로 검색해야 한다. 여기서 prompt 와 db tool의 호출 방식이 차이를 가진다. db tool의 경우 현재 접속한 데이타베이스 위치와 관계없이 select host, user from user로 하면 검색이 가능하지만 prompt에서는 정확하게 테이타베이스까지 입력해야 한다.
• host는 해당 아이디가 접속할 수 있는 위치를 의미한다. 와일드카드(%) 혹은 구체적인 ip(123.123.123.123) 을 사용할 수 있다.
• user는 root이 관리한다.

grant all privileges on unlocked_db.* to test@'localhost';

• grant를 통해 특정 {데이타베이스}.{테이블} 에 대한 권한을 준다. 권한의 내용은 all privileges 을 통해 전체를 줄 수 있고, select 와 같은 제한사항을 둘 수도 있다. 권한을 뺏을 때는 revoke를 사용한다.

> select user();
+----------------+
| user()         |
+----------------+
| root@localhost |
+----------------+

> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| locked_db          |
| mysql              |
| performance_schema |
| unlocked_db        |
+--------------------+

> quit
> mysql -u test

> select user();
+----------------+
| user()         |
+----------------+
| test@localhost |
+----------------+

> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| unlocked_db        |
+--------------------+

> use unlocked_db;
MariaDB [(unlocked_db)]>
Database changed

> use locked_db;
ERROR 1044 (42000): Access denied for user 'test'@'localhost' to database 'locked_db'
MariaDB [unlocked_db]>

• mysql -u test 를 입력하면 -p 와 패스워드를 입력할 필요가 없다. 처음 유저를 생성할 때 비밀번호를 입력하지 않았기 때문이다. 만약 비밀번호를 입력하고자 한다면 identified by '{비밀번호}'; 를 회원 생성 시 함께 입력하면 된다.
• 위의 내용을 보면 select user(); 가 변경 됨을 확인할 수 있다.
• show databases; 와 use locked_db; 를 통해 권한에 따른 데이타베이스에 대한 공개 및 접속 가능 여부가 결정됨을 확인할 수 있다.

참고
https://mariadb.com/kb/en/a-mariadb-primer/