mysql / mariadb grant 설정

mysql/mariadb 권한 설정하기

​

1. 권한확인

show grants 명을을 통해 확인할 수 있다.

​

a. 현재 접속 계정에 부여된 권한 확인

MariaDB [(none)]> show grants ;
+----------------------------------------------------------------------------------------------------------------------------------------+
| Grants for root@localhost                                                                                                              |
+----------------------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD 'xxx' WITH GRANT OPTION |
| GRANT PROXY ON ''@'%' TO 'root'@'localhost' WITH GRANT OPTION                                                                          |
+----------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
MariaDB [(none)]>

b. 다른 계정의 권한 확인 (다른계정 권한을 확인 할 수 있는 권한이 있는 유저로 해야한다.)

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

MariaDB [(none)]>

2. 권한 부여/회수

a. 유저별 권한을 부여할 수 있다.

MariaDB [(none)]> grant select, insert, update, delete on test.* to 'test_user'@'localhost' ;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]>
MariaDB [(none)]> show grants for 'test_user'@'localhost' ;
+------------------------------------------------------------------------------------------------------------------+
| Grants for test_user@localhost                                                                                   |
+------------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'test_user'@'localhost' IDENTIFIED BY PASSWORD 'xxxx'                                      |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `test`.* TO 'test_user'@'localhost'                                      |
+------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

MariaDB [(none)]>

b. 부여된 권한을 회수할 수 있다.

MariaDB [(none)]> revoke delete on test.* from 'test_user'@'localhost' ;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> show grants for 'test_user'@'localhost' ;
+------------------------------------------------------------------------------------------------------------------+
| Grants for test_user@localhost                                                                                   |
+------------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'test_user'@'localhost' IDENTIFIED BY PASSWORD '*1A9C49BF82BE878256A6E2EE578DC57E33235E4D' |
| GRANT SELECT, INSERT, UPDATE ON `test`.* TO 'test_user'@'localhost'                                              |
+------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

MariaDB [(none)]>

이상 mysql / MariaDB grant 설정을 설명하였습니다.