[마리아DB] 데이터베이스 및 사용자 계정 관리

데이터 베이스 생성

CREATE DATABASE  [데이터베이스명];

MariaDB [(none)]> create database testdb;

 

데이터 베이스 확인

SHOW DATABASES;

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| testdb             |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+

---

계정생성 및 권한설정은 mysql DB에서 관리한다. 

USE mysql;

MariaDB [(none)]> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MariaDB [mysql]>

 

사용자 계정 생성

CREATE USER '[USER]'@'[HOST]' IDENTIFIED BY '[PASSWORD]';

MariaDB [mysql]> create user 'testuser'@'%';
Query OK, 0 rows affected (0.001 sec)

 

사용자 조회

SELECT HOST, USER, PASSWORD FROM USER;

MariaDB [mysql]> select host, user from user;
+-----------+-------------+
| Host      | User        |
+-----------+-------------+
| %         | testuser    |
| localhost | mariadb.sys |
| localhost | mysql       |
| localhost | root        |
+-----------+-------------+

 

사용자 계정 삭제

DROP USER '[id]'@'[host]';

MariaDB [mysql]> drop user 'testuser'@'%';
Query OK, 0 rows affected (0.001 sec)

---

사용자 권한 설정

GRANT ALL PRIVILEGES ON [데이터베이스명].* TO '[USER]'@'[HOST]';

Global privileges(전역 권한) 

• GRANT ALL PRIVILEGES ON *.* TO 'USER'@'%';  (원격 접속)
• GRANT ALL PRIVILEGES ON *.* TO 'USE]'@'localhost';  (local접속)

MariaDB [mysql]> grant all privileges on *.* to 'testuser'@'%';
Query OK, 0 rows affected (0.000 sec)

Database privileges(데이터베이스 권한)

MariaDB [mysql]> grant all privileges on testdb.* to 'testuser'@'%';
Query OK, 0 rows affected (0.000 sec)

 

사용자 권한 조회

SHOW GRANTS FOR '[USER]'@'[HOST]';

MariaDB [mysql]> show grants for 'testuser'@'%';
+-------------------------------------------------------------------------------------------------------------+
| Grants for testuser@%                                                                                            |
+-------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO `testuser`@`%` IDENTIFIED BY PASSWORD '*250892135F3F3750B1C015F4576B16076765DAEC' |
+-------------------------------------------------------------------------------------------------------------+
1 rows in set (0.000 sec)

 

권한 초기화

REVOLE ALL ON [데이터베이스명].*  FROM  '[USER]'@'[HOST]';

MariaDB [mysql]> revoke all on testdb.* FROM 'bpo'@'%';
Query OK, 0 rows affected (0.001 sec)

 

새로고침

FLUSH PRIVILEGES;

MariaDB [mysql]> flush privileges;
Query OK, 0 rows affected (0.000 sec)