[데이터베이스/Database] mariadb SQL 실습 (grant, revoke, mysqldump)

먼저 tester1이라는 사용자 생성

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

 

첫번째 진행

1. mysql -u root -p에서 진행

MariaDB [mysql]> create database tester1db;

-- CREATE 권한 부여
-- tester1 사용자에게 tester1db 데이터베이스의 모든 테이블에 대해 CREATE 권한을 부여
MariaDB [mysql]>  grant create on tester1db.* to tester1@localhost;
Query OK, 0 rows affected (0.002 sec)

-- drop 권한 부여
-- tester1 사용자에게 tester1db 데이터베이스의 모든 테이블에 대해 DROP 권한을 부여
MariaDB [mysql]> grant drop on tester1db.* to tester1@localhost;
Query OK, 0 rows affected (0.001 sec)

-- CREATE 및 DROP 권한 철회
-- tester1 사용자로부터 tester1db 데이터베이스의 모든 테이블에 대해 부여했던 CREATE 및 DROP 권한을 철회
MariaDB [mysql]> revoke create, drop on tester1db.* from tester1@localhost;
Query OK, 0 rows affected (0.001 sec)

-- 모든 권한 부여
-- tester1 사용자에게 tester1db 데이터베이스의 모든 테이블에 대해 모든 권한을 부여 
-- 여기에는 SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER 등이 포함됨
MariaDB [mysql]> grant all privileges on tester1db.* to tester1@localhost;
Query OK, 0 rows affected (0.002 sec)

 

2. mysql -h localhost -u tester1 -p에서 진행 (비번 : 1234)

-- tester1@localhost 사용자가 가진 권한을 보여줌
-- tester1 사용자는 전체 서버(*.*)에 대해 기본적인 사용 권한을 가지고 있으며, 비밀번호는 해시된 형태로 저장되어 있습니다.
-- tester1@localhost 사용자에게는 두 가지 권한이 부여
-- USAGE 권한: 모든 데이터베이스의 모든 객체에 대한 사용 권한을 가집니다. 하지만 특정 데이터베이스나 테이블에 대한 권한은 부여되지 않았습니다.
-- ALL PRIVILEGES 권한: tester1db 데이터베이스의 모든 테이블 및 객체에 대한 모든 권한을 가집니다.
MariaDB [tester1db]> show grants for tester1@localhost;
+----------------------------------------------------------------------------------------------------------------+
| Grants for tester1@localhost                                                                                   |
+----------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `tester1`@`localhost` IDENTIFIED BY PASSWORD '*A4B6157319038724E3560894F7F932C8886EBFCF' |
| GRANT ALL PRIVILEGES ON `tester1db`.* TO `tester1`@`localhost`                                                 |
+----------------------------------------------------------------------------------------------------------------+
2 rows in set (0.000 sec)

MariaDB [tester1db]> use tester1db;
Database changed
MariaDB [tester1db]> create table testbl (col varchar(10));
Query OK, 0 rows affected (0.013 sec)

MariaDB [tester1db]> desc testtbl;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| col1  | varchar(10) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
1 row in set (0.010 sec)

 

 

두번째 진행

1. mysql -u root -p에서 진행

-- tester1@localhost 사용자로부터 tester1db 데이터베이스의 모든 권한을 제거
MariaDB [mysql]> revoke all on tester1db.* from tester1@localhost;
Query OK, 0 rows affected (0.004 sec)

 

2. mysql -h localhost -u tester1 -p에서 진행 (비번 : 1234)

MariaDB [tester1db]> exit
Bye

C:\WINDOWS\System32>mysql -h localhost -u tester1 -p
Enter password: ****
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 44
Server version: 11.3.2-MariaDB mariadb.org binary distribution

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

-- tester1@localhost 사용자가 *.* (모든 데이터베이스의 모든 객체)에 대해 USAGE 권한을 가짐
-- 이는 해당 사용자가 데이터베이스 및 테이블을 사용할 수 있다는 것을 의미
MariaDB [(none)]> show grants for tester1@localhost;
+----------------------------------------------------------------------------------------------------------------+
| Grants for tester1@localhost                                                                                   |
+----------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `tester1`@`localhost` IDENTIFIED BY PASSWORD '*A4B6157319038724E3560894F7F932C8886EBFCF' |
+----------------------------------------------------------------------------------------------------------------+
1 row in set (0.000 sec)

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
+--------------------+
1 row in set (0.000 sec)

 

 

세 번째 진행

1. mysql -u root -p에서 진행

MariaDB [mysql]> select user();
+----------------+
| user()         |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.000 sec)

MariaDB [mysql]> use tester1db;
Database changed
MariaDB [tester1db]> create table emp as select * from sample.emp;
Query OK, 12 rows affected (0.017 sec)
Records: 12  Duplicates: 0  Warnings: 0

MariaDB [tester1db]> desc emp;
+----------+--------------+------+-----+---------+-------+
| Field    | Type         | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+-------+
| empno    | int(11)      | NO   |     | NULL    |       |
| ename    | varchar(10)  | YES  |     | NULL    |       |
| job      | varchar(9)   | YES  |     | NULL    |       |
| mgr      | int(11)      | YES  |     | NULL    |       |
| hiredate | date         | YES  |     | NULL    |       |
| sal      | decimal(7,2) | YES  |     | NULL    |       |
| comm     | decimal(7,2) | YES  |     | NULL    |       |
| deptno   | int(11)      | YES  |     | NULL    |       |
+----------+--------------+------+-----+---------+-------+
8 rows in set (0.012 sec)

-- emp 테이블의 처음 3개의 행 확인
MariaDB [tester1db]> select * from emp limit 3;
+-------+-------+----------+------+------------+---------+--------+--------+
| empno | ename | job      | mgr  | hiredate   | sal     | comm   | deptno |
+-------+-------+----------+------+------------+---------+--------+--------+
|  7369 | SMITH | CLERK    | 7902 | 1980-12-17 |  800.00 |   NULL |     20 |
|  7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 |     30 |
|  7521 | WARD  | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 |     30 |
+-------+-------+----------+------+------------+---------+--------+--------+
3 rows in set (0.000 sec)

-- tester1@localhost 사용자에게 tester1db.emp 테이블에 대한 SELECT 권한을 부여
-- 해당 권한을 추가하면 사용자는 테이블의 내용을 조회 가능
MariaDB [tester1db]> grant select on tester1db.emp to tester1@localhost;
Query OK, 0 rows affected (0.004 sec)

 

2. mysql -h localhost -u tester1 -p에서 진행 (비번 : 1234)

MariaDB [(none)]> exit
Bye

C:\WINDOWS\System32>mysql -h localhost -u tester1 -p
Enter password: ****
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 45
Server version: 11.3.2-MariaDB mariadb.org binary distribution

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| tester1db          |
+--------------------+
2 rows in set (0.000 sec)

MariaDB [(none)]> use tester1db;
Database changed
MariaDB [tester1db]> desc emp;
+----------+--------------+------+-----+---------+-------+
| Field    | Type         | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+-------+
| empno    | int(11)      | NO   |     | NULL    |       |
| ename    | varchar(10)  | YES  |     | NULL    |       |
| job      | varchar(9)   | YES  |     | NULL    |       |
| mgr      | int(11)      | YES  |     | NULL    |       |
| hiredate | date         | YES  |     | NULL    |       |
| sal      | decimal(7,2) | YES  |     | NULL    |       |
| comm     | decimal(7,2) | YES  |     | NULL    |       |
| deptno   | int(11)      | YES  |     | NULL    |       |
+----------+--------------+------+-----+---------+-------+
8 rows in set (0.010 sec)

MariaDB [tester1db]> select * from emp limit 3;
+-------+-------+----------+------+------------+---------+--------+--------+
| empno | ename | job      | mgr  | hiredate   | sal     | comm   | deptno |
+-------+-------+----------+------+------------+---------+--------+--------+
|  7369 | SMITH | CLERK    | 7902 | 1980-12-17 |  800.00 |   NULL |     20 |
|  7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 |     30 |
|  7521 | WARD  | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 |     30 |
+-------+-------+----------+------+------------+---------+--------+--------+
3 rows in set (0.000 sec)

-- select 권한만 부여했으므로 insert, update, delete 권한은 모두 거부됨(denied)
MariaDB [tester1db]> insert into emp values (8000, '홍길동', 'MANAGER', 7782, now(), 3000, 1000, 10);
ERROR 1142 (42000): INSERT command denied to user 'tester1'@'localhost' for table `tester1db`.`emp`
MariaDB [tester1db]> show grants for tester1@localhost;
+----------------------------------------------------------------------------------------------------------------+
| Grants for tester1@localhost                                                                                   |
+----------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `tester1`@`localhost` IDENTIFIED BY PASSWORD '*A4B6157319038724E3560894F7F932C8886EBFCF' |
| GRANT SELECT ON `tester1db`.`emp` TO `tester1`@`localhost`                                                     |
+----------------------------------------------------------------------------------------------------------------+
2 rows in set (0.000 sec)

MariaDB [tester1db]> update emp set deptno=20 where empno=7934;
ERROR 1142 (42000): UPDATE command denied to user 'tester1'@'localhost' for table `tester1db`.`emp`
MariaDB [tester1db]> delete from emp where empno=7934;
ERROR 1142 (42000): DELETE command denied to user 'tester1'@'localhost' for table `tester1db`.`emp`

 

네 번째 진행

1. mysql -u root -p에서 진행

MariaDB [tester1db]> grant insert on tester1db.emp to tester1@localhost;
Query OK, 0 rows affected (0.003 sec)

MariaDB [tester1db]> grant delete on tester1db.emp to tester1@localhost;
Query OK, 0 rows affected (0.001 sec)

MariaDB [tester1db]> grant update on tester1db.emp to tester1@localhost;
Query OK, 0 rows affected (0.001 sec)

 

2. mysql -h localhost -u tester1 -p에서 진행 (비번 : 1234)

MariaDB [tester1db]> show grants for tester1@localhost;
+----------------------------------------------------------------------------------------------------------------+
| Grants for tester1@localhost                                                                                   |
+----------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `tester1`@`localhost` IDENTIFIED BY PASSWORD '*A4B6157319038724E3560894F7F932C8886EBFCF' |
| GRANT SELECT, INSERT ON `tester1db`.`emp` TO `tester1`@`localhost`                                             |
+----------------------------------------------------------------------------------------------------------------+
2 rows in set (0.000 sec)

MariaDB [tester1db]> insert into emp values (8000, '홍길동', 'MANAGER', 7782, now(), 3000, 1000, 10);
Query OK, 1 row affected, 1 warning (0.001 sec)

MariaDB [tester1db]> show grants for tester1@localhost;
+----------------------------------------------------------------------------------------------------------------+
| Grants for tester1@localhost                                                                                   |
+----------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `tester1`@`localhost` IDENTIFIED BY PASSWORD '*A4B6157319038724E3560894F7F932C8886EBFCF' |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `tester1db`.`emp` TO `tester1`@`localhost`                             |
+----------------------------------------------------------------------------------------------------------------+
2 rows in set (0.000 sec)

MariaDB [tester1db]> update emp set deptno=20 where empno=7934;
Query OK, 1 row affected (0.002 sec)
Rows matched: 1  Changed: 1  Warnings: 0

MariaDB [tester1db]> delete from emp where empno=7934;
Query OK, 1 row affected (0.021 sec)

 

문제풀이

1. tester2 사용자 생성

C:\WINDOWS\System32>mysql -u root -p
Enter password: ******
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 46
Server version: 11.3.2-MariaDB mariadb.org binary distribution

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> create user 'tester2'@'localhost' identified by '1234';
Query OK, 0 rows affected (0.003 sec)

 

2. tester2db 데이터베이스 생성

MariaDB [(none)]> create database tester2db;
Query OK, 1 row affected (0.001 sec)

 

3. tester2 사용자에게 tester2db 데이터베이스에 대한 사용권한 부여 및 확인

MariaDB [(none)]> grant all privileges on tester2db.* to 'tester2'@'localhost';
Query OK, 0 rows affected (0.003 sec)

MariaDB [(none)]> show grants for 'tester2'@'localhost';
+----------------------------------------------------------------------------------------------------------------+
| Grants for tester2@localhost                                                                                   |
+----------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `tester2`@`localhost` IDENTIFIED BY PASSWORD '*A4B6157319038724E3560894F7F932C8886EBFCF' |
| GRANT ALL PRIVILEGES ON `tester2db`.* TO `tester2`@`localhost`                                                 |
+----------------------------------------------------------------------------------------------------------------+
2 rows in set (0.000 sec)

 

4. tester2 사용자가 tester2db 데이터베이스에 테이블 생성 테스트

C:\WINDOWS\System32>mysql -u tester2 -p
Enter password: ****
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 47
Server version: 11.3.2-MariaDB mariadb.org binary distribution

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> use tester2db;
Database changed
MariaDB [tester2db]> create table example_table (
    -> id int auto_increment primary key,
    -> name varchar(50),
    -> age int
    -> );
Query OK, 0 rows affected (0.014 sec)

 

5. tester2 사용자에게서 tester2db 데이터베이스에 대한 사용권한 제거 및 확인

MariaDB [(none)]> show grants for 'tester2'@'localhost';
+----------------------------------------------------------------------------------------------------------------+
| Grants for tester2@localhost                                                                                   |
+----------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `tester2`@`localhost` IDENTIFIED BY PASSWORD '*A4B6157319038724E3560894F7F932C8886EBFCF' |
| GRANT ALL PRIVILEGES ON `tester2db`.* TO `tester2`@`localhost`                                                 |
+----------------------------------------------------------------------------------------------------------------+
2 rows in set (0.000 sec)

MariaDB [(none)]> revoke all privileges on tester2db.* from 'tester2'@'localhost';
Query OK, 0 rows affected (0.002 sec)

MariaDB [(none)]> show grants for 'tester2'@'localhost';
+----------------------------------------------------------------------------------------------------------------+
| Grants for tester2@localhost                                                                                   |
+----------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `tester2`@`localhost` IDENTIFIED BY PASSWORD '*A4B6157319038724E3560894F7F932C8886EBFCF' |
+----------------------------------------------------------------------------------------------------------------+
1 row in set (0.000 sec)

 

mysqldump

백업 ... 데이터베이스 -> 외부 추출

리스토어 ... 외부 추출 -> 데이터베이스

 

1. 백업 - 데이터파일의 위치

 

1. 바이너리 

              데이터베이스 정지 상태 ...

2. 텍스트 파일

              덤프

 

Setting environment for MariaDB 11.3 (x64)

C:\WINDOWS\System32>mysqldump
Usage: mysqldump [OPTIONS] database [tables]
OR     mysqldump [OPTIONS] --databases DB1 [DB2 DB3...]
OR     mysqldump [OPTIONS] --all-databases
OR     mysqldump [OPTIONS] --system=[SYSTEMOPTIONS]]
For more options, use mysqldump --help

C:\WINDOWS\System32>cd c:/mariadb

c:\mariadb>dir
 C 드라이브의 볼륨에는 이름이 없습니다.
 볼륨 일련 번호: 88AC-F1C9

 c:\mariadb 디렉터리

2024-05-21  오전 10:22    <DIR>          .
2024-05-21  오전 10:22    <DIR>          ..
2024-05-21  오전 10:43               143 constraint.sql
2024-05-17  오후 12:20                70 ex01.sql
2024-05-17  오후 04:33                70 ex02.sql
2024-05-17  오후 12:26                55 ex03.sql
2024-05-17  오후 12:30               148 ex04.sql
2024-05-17  오후 12:34               291 ex05.sql
2024-05-17  오후 02:12               184 ex06.sql
2024-05-17  오후 02:21               267 ex07.sql
2024-05-20  오전 10:48               292 ex09.sql
2024-05-17  오후 12:41               204 exquiz.sql
              10개 파일               1,724 바이트
               2개 디렉터리  162,460,499,968 바이트 남음

-- mysqldump 명령어를 사용하여 sample 데이터베이스의 구조와 데이터를 파일에 백업
c:\mariadb>mysqldump -u root -p sample > sample.sql
Enter password: ******

c:\mariadb>mysqldump -u root -p sample emp > emp.sql
Enter password: ******

c:\mariadb>mysql -u root -p sample
Enter password: ******
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 50
Server version: 11.3.2-MariaDB mariadb.org binary distribution

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [sample]> show tables;
+------------------+
| Tables_in_sample |
+------------------+
| dept             |
| dept_i           |
| emp              |
| emp_vu_20        |
| salgrade         |
+------------------+
5 rows in set (0.000 sec)

MariaDB [sample]> drop table dept;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails
MariaDB [sample]> drop table dept_i;
Query OK, 0 rows affected (0.011 sec)

MariaDB [sample]> drop table emp;
Query OK, 0 rows affected (0.014 sec)

MariaDB [sample]> drop table emp_vu_20;
ERROR 1965 (42S02): 'sample.emp_vu_20' is a view
MariaDB [sample]> drop view emp_vu_20;
Query OK, 0 rows affected (0.009 sec)

MariaDB [sample]> drop table salgrade;
Query OK, 0 rows affected (0.012 sec)

MariaDB [sample]> show tables;
+------------------+
| Tables_in_sample |
+------------------+
| dept             |
+------------------+
1 row in set (0.000 sec)

MariaDB [sample]> drop table dept;
Query OK, 0 rows affected (0.012 sec)

MariaDB [sample]> exit
Bye

-- 백업 파일을 데이터베이스에 복원
c:\mariadb>mysql -u root -p sample < sample.sql
Enter password: ******

c:\mariadb>mysql -u root -p sample
Enter password: ******
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 52
Server version: 11.3.2-MariaDB mariadb.org binary distribution

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [sample]> show tables;
+------------------+
| Tables_in_sample |
+------------------+
| dept             |
| dept_i           |
| emp              |
| emp_vu_20        |
| salgrade         |
+------------------+
5 rows in set (0.000 sec)

MariaDB [sample]>