[MYSQL / Database ] 사용자 권한 실습해보기 - REVOKE

<연습> MySQL서버:192.168.1.X, Host'PC:클라이언트:192.168.1.1
- MySQL서버
SQL> create database db01;
SQL> use db01;
SQL> create table t1(id int);
SQL> insert into t1 values(1);
SQL> insert into t1 values(2);

SQL> create database db02;
SQL> use db02;
SQL> create table t2(id int);
SQL> insert into t2 values(3);
SQL> insert into t2 values(4);
SQL> select * from t2;

SQL> create user user1@'%' identified by '1';
SQL> create user user2@'%' identified by '2;

SQL> grant select,update,delete,insert on db01.t1 to user1@'%';
SQL> grant select on db01.t1 to user2@'%';

SQL> grant select on db02.t2 to user1@'%';
SQL> grant select,update,delete,insert on db02.t2 to user2@'%';

SQL> flush privileges;

 

 

C:\Program Files\MySQL\MySQL Server 8.0\bin>mysql -u user1 -p -h 192.168.1.133
Enter password: *
mysql> show grants;
+--------------------------------------------------------------------+
| Grants for user1@%                                                 |
+--------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `user1`@`%`                                  |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `db01`.`t1` TO `user1`@`%` |
| GRANT SELECT ON `db02`.`t2` TO `user1`@`%`                         |
+--------------------------------------------------------------------+
3 rows in set (0.00 sec)

mysql> exit

 

C:\Program Files\MySQL\MySQL Server 8.0\bin>mysql -u user2 -p -h 192.168.1.133
Enter password: *
mysql> show grants;
+--------------------------------------------------------------------+
| Grants for user2@%                                                 |
+--------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `user2`@`%`                                  |
| GRANT SELECT ON `db01`.`t1` TO `user2`@`%`                         |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `db02`.`t2` TO `user2`@`%` |
+--------------------------------------------------------------------+
3 rows in set (0.00 sec)

mysql>

 

 REVOKE 권한취소

REVOKE [취소할 권한] ON [DATABASENAME].[TABLENAME] FROM USERID@'HOST';

 

권한 취소하기 

db01.t1 table 에서 select 이외의 권한을 취소해봅시다

[ admin server]
revoke insert,update,delete on db01.t1 from user1@'%';
flush privileges;

 

mysql> show grants;
+--------------------------------------------+
| Grants for user1@%                         |
+--------------------------------------------+
| GRANT USAGE ON *.* TO `user1`@`%`          |
| GRANT SELECT ON `db01`.`t1` TO `user1`@`%` |
| GRANT SELECT ON `db02`.`t2` TO `user1`@`%` |
+--------------------------------------------+
3 rows in set (0.00 sec)

=>결과