[MYSQL / Database] Role 생성, 퍼미션 할당, 권한취소

기본 설정 

create database db01;
use db01;
create table t1(
	id int,
    name varchar(30)
    );

insert into t1 values (1,'홍길동') ,(2,'서길동');
select * from t1;

 Role 생성

CREATE ROLE [ROLENAME];

create role role1;
create role role2;
create role role3;

 

 

 ROLE 에 퍼미션 할당

grant all on db01.* to role1;
grant select on db01.* to role2;
grant select,update,delete on db01.* to role3;

퍼미션을 주어도 ROLE이 바로 적용되어지지 않는다.

 

 사용자에게 role부여 

GRANT [ROLE NAME] TO [USERID]@'HOST'

create user user1@'localhost' identified by '1234';
create user user2@'localhost' identified by '1234';
create user user3@'localhost' identified by '1234';

grant role1 to user1@localhost;
grant role2 to user2@'localhost';
grant role3 to user3@'localhost';

 

 ROLE 활성화

각각의 유저에서 롤을 활성화 해주어야 한다.

C:\Program Files\MySQL\MySQL Server 8.0\bin>mysql -u user3 -p
Enter password: ****
mysql> select current_role();
+----------------+
| current_role() |
+----------------+
|     NONE       |
+----------------+

mysql> set role all;
Query OK, 0 rows affected (0.00 sec)

mysql> select current_role();
+----------------+
| current_role() |
+----------------+
| `role3`@`%`    |
+----------------+
1 row in set (0.00 sec)

처음에는 활성화 되어있지 않지만 아래의 명령어를 적어주면 

mysql > SET ROLE ALL; 

 [결과] ROLE적용되었는지 확인해보기

 

<USER1 - ROLE1 >

ROLE1 은 DB01에서 모든권한 허용 

mysql> insert into t1 values(3,'남길동');
Query OK, 1 row affected (0.00 sec)

mysql> update t1 set name='이순신' where id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> delete from t1 where id=1;
Query OK, 1 row affected (0.00 sec)

mysql> select * from t1;
+------+--------+
| id   | name   |
+------+--------+
|    2 | 서길동 |
|    3 | 남길동 |
+------+--------+
2 rows in set (0.00 sec)

 

<USER2 -ROLE2>

SELECT만 허용

mysql> insert into t1 valuse(3,'남길동');
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near 'valus
e(3,'남길동')' at line 1
mysql> insert into t1 values(4,'북길동');
ERROR 1142 (42000): INSERT command denied to user 'user2'@'localhost' for table
't1'
mysql> update t1 set name='leesunsin' where id=1;
ERROR 1142 (42000): UPDATE command denied to user 'user2'@'localhost' for table
't1'
mysql> delete from t1 where id=1;
ERROR 1142 (42000): DELETE command denied to user 'user2'@'localhost' for table
't1'

 

<USER3-ROLE3>

select,update,delete 허용

mysql> insert into t1 values(5,'namgildong');
ERROR 1142 (42000): INSERT command denied to user
't1'
mysql> update t1 set name='leesunsin' where id=1;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0  Changed: 0  Warnings: 0

mysql> update t1 set name='leesunsin' where id=2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from t1;
+------+-----------+
| id   | name      |
+------+-----------+
|    2 | leesunsin |
|    3 | 남길동    |
+------+-----------+
2 rows in set (0.00 sec)

mysql> delete from t1 where id=2;
Query OK, 1 row affected (0.00 sec)

 ROLE 퍼미션 취소

REVOKE [권한 ] ON [DATABASE NAME].*  FROM [ROLE NAME];

DROP ROLE [ROLE NAME]

 

revoke all on db01.* from role1;
revoke select on db01.* from role2;
revoke select,update,delete on db01.* from role3;

drop role role1;
drop role role2;
drop role role3;