DBMS : Mysql , Mariadb
특정 유저에게 특정 데이터베이스에서 특정 테이블만 권한 부여 할때 쓰는 명령어
DBNAME : ksplus
DBUSER : ksplus
DBPASS : ksplus123456&*
테이블명 : students (없으면 만들고 있으면 상관이 없다 여기선 테스트하기 위해서 생성)
mysql root 로 접속 후 데이터베이스 생성 및 테이블 생성 (기존 테이블이 있다면 테이블 생성은 필요없다)
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
MariaDB [mysql]> create database ksplus; MariaDB [mysql]> use ksplus; Database changed ### 테이블생성 MariaDB [ksplus]> create table students ( student_no varchar(10), surname varchar(20), forename varchar(20)); Query OK, 0 rows affected (0.009 sec) MariaDB [ksplus]> insert into students values ('20220101','jin','sunghoon'); Query OK, 1 row affected (0.001 sec) MariaDB [ksplus]> insert into students values ('20220103','kim','jongmoon'); Query OK, 1 row affected (0.002 sec) MariaDB [ksplus]> flush privileges; Query OK, 0 rows affected (0.001 sec) |
다시 mysql root 로 접속하여 권한 부여 ( ksplus 데이터베이스에서 students 테이블만 select,insert,update,delete 권한만 부여)
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
MariaDB [ksplus]> use mysql; Database changed MariaDB [mysql]> grant select, insert, update, delete on ksplus.students to ksplus@'%' identified by 'ksplus123456&*'; Query OK, 0 rows affected (0.005 sec) MariaDB [mysql]> flush privileges; Query OK, 0 rows affected (0.001 sec) ### 권한확인 MariaDB [mysql]> show grants for ksplus; +-------------------------------------------------------------------------------------------------------+ | Grants for ksplus@% | +-------------------------------------------------------------------------------------------------------+ | GRANT USAGE ON *.* TO `ksplus`@`%` IDENTIFIED BY PASSWORD '*CA1895BD53B2F5D4E2FE03572E2AA3ADB3F282F1' | | GRANT SELECT, INSERT, UPDATE, DELETE ON `ksplus`.`students` TO `ksplus`@`%` | +-------------------------------------------------------------------------------------------------------+ 2 rows in set (0.000 sec) |
ksplus 사용자로 접속
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
[root@localhost ~]# mysql -h 127.0.0.1 -u ksplus -p ksplus Enter password: Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 8387 Server version: 10.7.3-MariaDB MariaDB Server 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 [ksplus]> show tables; +------------------+ | Tables_in_ksplus | +------------------+ | students | +------------------+ 1 row in set (0.001 sec) |
이제 테이블을 한번 drop 삭제 해보자 / 권한이 없어서 삭제 불가
|
1 2 |
MariaDB [ksplus]> drop table students; ERROR 1142 (42000): DROP command denied to user 'ksplus'@'127.0.0.1' for table 'students' |
테이블 내용 확인 및 데이터를 입력해보자 (정상적 처리 진행됨) ( select , insert , update, delete )
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 |
### 테이블 내용 확인 MariaDB [ksplus]> select * from students; +------------+---------+----------+ | student_no | surname | forename | +------------+---------+----------+ | 20220101 | jin | sunghoon | | 20220103 | kim | jongmoon | +------------+---------+----------+ 2 rows in set (0.000 sec) ### 테이블 내용 추가 (insert) MariaDB [ksplus]> insert into students values ('20220105','hong','sua'); Query OK, 1 row affected (0.002 sec) MariaDB [ksplus]> select * from students; +------------+---------+----------+ | student_no | surname | forename | +------------+---------+----------+ | 20220101 | jin | sunghoon | | 20220103 | kim | jongmoon | | 20220105 | hong | sua | +------------+---------+----------+ 3 rows in set (0.000 sec) ### 테이블 내용 update MariaDB [ksplus]> update students set surname='cha' where student_no='20220101'; Query OK, 1 row affected (0.001 sec) Rows matched: 1 Changed: 1 Warnings: 0 ### 테이블 내용 삭제 (delete) MariaDB [ksplus]> delete from students where student_no='20220103'; Query OK, 1 row affected (0.002 sec) |