권한(Privileges)

Mysql은 다른 RDBMS와 마찬가지로 계정을 통해서 DB를 접근해야 하고, 각 계정에는 스키마에 대한 권한을 부여하여 접근 관리를 할 수 있습니다.

 

Show grants for '유저명'@'Host명'; 명령어를 통해서 해당 계정이 가지고 있는 권한을 확인할 수 있습니다.

내부적으로 mysql system 스키마의 grant테이블의 정보를 불러와서 보여주는 것입니다.

 

--USER와 HOST 확인

mysql> use mysql
Database changed
mysql> select user,host from user;
+------------------+---------------+
| user             | host          |
+------------------+---------------+
| test             | %             |
| db_test          | 192.168.56.58 |
| db_test          | localhost     |
| mysql.infoschema | localhost     |
| mysql.session    | localhost     |
| mysql.sys        | localhost     |
| root             | localhost     |
+------------------+---------------+
7 rows in set (0.00 sec)


--권한 확인
mysql> show grants for test@'%';
+------------------------------------------------+
| Grants for test@%                              |
+------------------------------------------------+
| GRANT USAGE ON *.* TO `test`@`%`               |
| GRANT ALL PRIVILEGES ON `test`.* TO `test`@`%` |
+------------------------------------------------+
2 rows in set (0.00 sec)

 

아래는 Mysql에서 Grants 와 Revoke가 가능한 권한입니다. Static 권한과 Dynamic 권한으로 불리는 권한으로 나누어 지며, Static 권한은 Oracle에서도 사용하는 DML, DDL을 포함한 권한들이 존재합니다.

 

Static Privileges

Privilege	Grant Table Column	Context	설명
ALL [PRIVILEGES]	Synonym for “all privileges”	Server administration	GRANT OPTION을 제외한 가능한 모든 권한을 부여
ALTER	Alter_priv	Tables	테이블의 구조를 변경할 수 있는 권한 ALTER TABLE권한은 CREATE와 INSERT 권한이 필요함
ALTER ROUTINE	Alter_routine_priv	Stored routines	stored procedure와 function같은 stored routines를 alter 또는 drop할 수 있는 권한
CREATE	Create_priv	Databases, tables, or indexes	Database(스키마)나 테이블,인덱스를 생성할 수 있는 권한
CREATE ROLE	Create_role_priv	Server administration	ROLE을 생성할 수 있는 권한
CREATE ROUTINE	Create_routine_priv	Stored routines	stored procedure와 function같은 stored routines를 생성할 수 있는 권한
CREATE TABLESPACE	Create_tablespace_priv	Server administration	테이블스페이스와 log file group에 CREATE,ALTER,DROP할 수 있는 권한
CREATE TEMPORARY TABLES	Create_tmp_table_priv	Tables	TEMPORARY TABLE을 생성할 수 있는 권한
CREATE USER	Create_user_priv	Server administration	ALTER USER, CREATE ROLE, CREATE USER, DROP ROLE, DROP USER, RENAME USER 그리고 REVOKE ALL PRIVILEGES 할 수 있는 권한
CREATE VIEW	Create_view_priv	Views	View를 생성할 수 있는 권한
DELETE	Delete_priv	Tables	테이블에 있는 row를 삭제할 수 있는 권한
DROP	Drop_priv	Databases, tables, or views	데이터베이스(스키마),테이블,뷰에 대해 삭제할 수 있는 권한 파티션 테이블을 삭제시에는 alter table ... drop partition 권한이 필요 TRUNCATE TABLE 권한이 필요
DROP ROLE	Drop_role_priv	Server administration	ROLE을 삭제할 수 있는 권한 (CREATE USER 권한은 해당 권한 포함)
EVENT	Event_priv	Databases	EVENT SCHEDULER에서 create,alter,drop 또는 이벤트를 보여주는 권한
EXECUTE	Execute_priv	Stored routines	stored procedure와 function같은 stored routines를 실행할 수 있는 권한
FILE	File_priv	File access on server host	-LOAD DATA 및 SELECT ... INTO OUTFILE을 이용해서 서버의 파일로부터 읽거나 쓸 수 있음 -MySQL 서버에 쓰기 권한이 있는 모든 디렉토리에 새 파일을 생성할 수 있음 -CREATE TABLE권한에 대해 DATA DIRECTORY 또는 INDEX DIRECTORY 테이블 옵션 사용 가능
GRANT OPTION	Grant_priv	Databases, tables, or stored routines	자신이 소유한 권한을 다른 유저에서 부여하거나 회수 할 수 있는 권한
INDEX	Index_priv	Tables	인덱스를 생성하거나 삭제할 수 있는 권한
INSERT	Insert_priv	Tables or columns	테이블에 데이터를 삽입할 수 있는 권한 ANALYZE TABLE,OPTIMIZE TABLE,REPAIR TABLE 사용시 필요한 권한
LOCK TABLES	Lock_tables_priv	Databases	명시적으로 LOCK TABLES문을 이용해서 테이블을 LOCK할 수 있는 권한 다른 세션이 LOCK한 테이블을 읽지 못하도록 하는 쓰기 LOCK사용이 포함
PROCESS	Process_priv	Server administration	서버 내에서 실행되는 쓰레드에 대한 정보에 대한 액세스를 부여하는 권한 show processlist, mysqladmin processlist
PROXY	proxies_priv	table Server administration	한 사용자가 다른 사용자로 알려지거나 사용될 수 있는 권한
REFERENCES	References_priv	Databases or tables	부모 테이블의 외래키(foreign key)제약조건을 생성할 수 있는 권한
RELOAD	Reload_priv	Server administration	-FLUSH문 사용 -FLUSH작업과 동등한 mysqladmin 명령어 사용 서버에 사용중인 메모리를 다시 로드하도록 지시 FLUSH 권한과 동의어
REPLICATION CLIENT	Repl_client_priv	Server administration	SHOW MASTER STATUS,SHOW REPLICA| SLAVE STATUS , SHOW BINARY LOGS 사용 가능
REPLICATION SLAVE	Repl_slave_priv	Server administration	SHOW REPLICAS| SHOW SLAVE HOSTS,SHOW RELAYLOG EVENTS, SHOW BINLOG EVENTS 사용가능
SELECT	Select_priv	Tables or columns	조회 권한
SHOW DATABASES	Show_db_priv	Server administration	database(스키마)의 이름을 확인할 수 있는 권한 -skip-show-database 옵션으로 해당 권한을 사용할 수 없게 할 수 있음.
SHOW VIEW	Show_view_priv	Views	SHOW CREATE VIEW를 사용가능
SHUTDOWN	Shutdown_priv	Server administration	SHUTDOWN과 RESTART를 사용가능 mysqladmin shutdown과 mysql_shutdown
SUPER	Super_priv	Server administration	강력하고 광범위한 권한 함부로 부여해서는 안됌
TRIGGER	Trigger_priv	Tables	트리거를 사용할 수 있게 하는 권한
UPDATE	Update_priv	Tables or columns	UPDATE를 사용할 수 있게 하는 권한
USAGE	Synonym for “no privileges”	Server administration	권한이 아무것도 없을때 보여지는 권한

 

Dynamic Privileges

Privilege	Context
APPLICATION_PASSWORD_ADMIN	Dual password administration
AUDIT_ADMIN	Audit log administration
BACKUP_ADMIN	Backup administration
BINLOG_ADMIN	Backup and Replication administration
BINLOG_ENCRYPTION_ADMIN	Backup and Replication administration
CLONE_ADMIN	Clone administration
CONNECTION_ADMIN	Server administration
ENCRYPTION_KEY_ADMIN	Server administration
FIREWALL_ADMIN	Firewall administration
FIREWALL_USER	Firewall administration
FLUSH_OPTIMIZER_COSTS	Server administration
FLUSH_STATUS	Server administration
FLUSH_TABLES	Server administration
FLUSH_USER_RESOURCES	Server administration
GROUP_REPLICATION_ADMIN	Replication administration
INNODB_REDO_LOG_ARCHIVE	Redo log archiving administration
NDB_STORED_USER	NDB Cluster
PERSIST_RO_VARIABLES_ADMIN	Server administration
REPLICATION_APPLIER PRIVILEGE_CHECKS_USER	for a replication channel
REPLICATION_SLAVE_ADMIN	Replication administration
RESOURCE_GROUP_ADMIN	Resource group administration
RESOURCE_GROUP_USER	Resource group administration
ROLE_ADMIN	Server administration
SESSION_VARIABLES_ADMIN	Server administration
SET_USER_ID	Server administration
SHOW_ROUTINE	Server administration
SYSTEM_USER	Server administration
SYSTEM_VARIABLES_ADMIN	Server administration
TABLE_ENCRYPTION_ADMIN	Server administration
VERSION_TOKEN_ADMIN	Server administration
XA_RECOVER_ADMIN	Server administration

 

권한에 대해서 잘 설명해놓은 사이트가 있어 참고 차 올리도록하겠습니다.

저는 매뉴얼의 번역정도인데 이글은 쉽게 설명까지 달 되어 있네요 저도 많은 참고가 되었습니다.

 

참고 사이트

myinfrabox.tistory.com/13

[MySQL][Security] 계정생성과 권한

 

 

ALL vs SUPER 권한 차이

 

ALL 권한은 GRANT 권한을 제외한 가능한 모든 옵션을 부여하는 기능입니다. Oracle의 DBA Role과 유사한 기능을 가진 막강한 권한입니다.

 

SUPER권한은 서버환경변수를 바꿀 수 있습니다. 접속된 세션을 끊을 수 있고 각존 서버 명령어를 수행할 수 있습니다. 또한 복제 관련 명령어를 수행할 수 있으며 binlog 명령어를 수행할 수 있습니다. 글로벌 트랜잭션 캐릭터셋을 바꿀 수 있습니다.

 

즉, ALL > SUPER 권한이 포함되게 되는 것입니다.

 

테스트를 통해 확인해보겠습니다.

 

초기상태에는 모든 권한이 N으로 되어 있는것을 확인할 수 있습니다.

--초기상태

mysql> show grants for pritest;
+-------------------------------------+
| Grants for pritest@%                |
+-------------------------------------+
| GRANT USAGE ON *.* TO `pritest`@`%` |
+-------------------------------------+
1 row in set (0.00 sec)


--mysql.user 조회

mysql> select user,host,select_priv,insert_priv,update_priv,Delete_priv, Create_priv , Drop_priv , Reload_priv , Shutdown_priv , Process_priv ,
    ->  File_priv , Grant_priv , References_priv, Index_priv , Alter_priv , Show_db_priv , Super_priv , Create_tmp_table_priv , Lock_tables_priv , Execute_priv , Repl_slave_priv , Repl_client_priv , Create_view_priv ,
    ->  Show_view_priv , Create_routine_priv , Alter_routine_priv , Create_user_priv , Event_priv , Trigger_priv , Create_tablespace_priv
    ->  from mysql.user
    -> where user='pritest';
+---------+------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+
| user    | host | select_priv | insert_priv | update_priv | Delete_priv | Create_priv | Drop_priv | Reload_priv | Shutdown_priv | Process_priv | File_priv | Grant_priv | References_priv | Index_priv | Alter_priv | Show_db_priv | Super_priv | Create_tmp_table_priv | Lock_tables_priv | Execute_priv | Repl_slave_priv | Repl_client_priv | Create_view_priv | Show_view_priv | Create_routine_priv | Alter_routine_priv | Create_user_priv | Event_priv | Trigger_priv | Create_tablespace_priv |
+---------+------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+
| pritest | %    | N           | N           | N           | N           | N           | N         | N           | N             | N            | N         | N          | N               | N          | N          | N            | N          | N                     | N                | N            | N               | N                | N                | N              | N                   | N                  | N                | N          | N            | N                      |
+---------+------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+
1 row in set (0.00 sec)

 

SUPER 권한

Super_priv에 Y로 변경되었고 다 N인것을 확인할 수 있습니다.

GRANT USAGE ON *.* To 가 GRANT SUPER ON *.* TO로 변경되었습니다.

--SUPER권한 부여
mysql> grant super on *.* to 'pritest'@'%';
Query OK, 0 rows affected, 1 warning (0.01 sec)

--확인
mysql> select user,host,select_priv,insert_priv,update_priv,Delete_priv, Create_priv , Drop_priv , Reload_priv , Shutdown_priv , Process_priv ,
    ->  File_priv , Grant_priv , References_priv, Index_priv , Alter_priv , Show_db_priv , Super_priv , Create_tmp_table_priv , Lock_tables_priv , Execute_priv , Repl_slave_priv , Repl_client_priv , Create_view_priv ,
    ->  Show_view_priv , Create_routine_priv , Alter_routine_priv , Create_user_priv , Event_priv , Trigger_priv , Create_tablespace_priv
    ->  from mysql.user
    -> where user='pritest';
+---------+------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+
| user    | host | select_priv | insert_priv | update_priv | Delete_priv | Create_priv | Drop_priv | Reload_priv | Shutdown_priv | Process_priv | File_priv | Grant_priv | References_priv | Index_priv | Alter_priv | Show_db_priv | Super_priv | Create_tmp_table_priv | Lock_tables_priv | Execute_priv | Repl_slave_priv | Repl_client_priv | Create_view_priv | Show_view_priv | Create_routine_priv | Alter_routine_priv | Create_user_priv | Event_priv | Trigger_priv | Create_tablespace_priv |
+---------+------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+
| pritest | %    | N           | N           | N           | N           | N           | N         | N           | N             | N            | N         | N          | N               | N          | N          | N            | Y          | N                     | N                | N            | N               | N                | N                | N              | N                   | N                  | N                | N          | N            | N                      |
+---------+------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+
1 row in set (0.00 sec)

mysql> show grants for pritest;
+-------------------------------------+
| Grants for pritest@%                |
+-------------------------------------+
| GRANT SUPER ON *.* TO `pritest`@`%` |
+-------------------------------------+
1 row in set (0.00 sec)

 

ALL 권한(특정 스키마에 대해서만)

특정 스키마에 대해서만 ALL 권한부여시 show grants에는 ALL 권한이 부여된 것을 확인할 수 있습니다.

하지만, mysql.user 테이블에서는 권한 확인 시 N인 것을 확인할 수 있습니다.

--권한 회수
mysql> revoke super on *.* from 'pritest'@'%';
Query OK, 0 rows affected, 1 warning (0.01 sec)

--권한부여 (TEST스키마에 대해서만 ALL 권한)
mysql> grant all on test.* to 'pritest'@'%';
Query OK, 0 rows affected (0.01 sec)

--ALL 권한 확인
mysql> show grants for 'pritest'@'%';
+---------------------------------------------------+
| Grants for pritest@%                              |
+---------------------------------------------------+
| GRANT USAGE ON *.* TO `pritest`@`%`               |
| GRANT ALL PRIVILEGES ON `test`.* TO `pritest`@`%` |
+---------------------------------------------------+
2 rows in set (0.00 sec)


mysql> select user,host,select_priv,insert_priv,update_priv,Delete_priv, Create_priv , Drop_priv , Reload_priv , Shutdown_priv , Process_priv ,
    ->  File_priv , Grant_priv , References_priv, Index_priv , Alter_priv , Show_db_priv , Super_priv , Create_tmp_table_priv , Lock_tables_priv , Execute_priv , Repl_slave_priv , Repl_client_priv , Create_view_priv ,
    ->  Show_view_priv , Create_routine_priv , Alter_routine_priv , Create_user_priv , Event_priv , Trigger_priv , Create_tablespace_priv
    ->  from mysql.user
    -> where user='pritest';
+---------+------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+
| user    | host | select_priv | insert_priv | update_priv | Delete_priv | Create_priv | Drop_priv | Reload_priv | Shutdown_priv | Process_priv | File_priv | Grant_priv | References_priv | Index_priv | Alter_priv | Show_db_priv | Super_priv | Create_tmp_table_priv | Lock_tables_priv | Execute_priv | Repl_slave_priv | Repl_client_priv | Create_view_priv | Show_view_priv | Create_routine_priv | Alter_routine_priv | Create_user_priv | Event_priv | Trigger_priv | Create_tablespace_priv |
+---------+------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+
| pritest | %    | N           | N           | N           | N           | N           | N         | N           | N             | N            | N         | N          | N               | N          | N          | N            | N          | N                     | N                | N            | N               | N                | N                | N              | N                   | N                  | N                | N          | N            | N                      |
+---------+------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+
1 row in set (0.00 sec)

 

ALL 권한 (*.*)

 

show grants로 확인 시 특정 스키마에 대해서만 부여 할때와 다르게 권한 이름이 상세하게 다 나오는 것을 확인할 수 있으며, mysql.user 테이블 조회시 super_priv를 포함한 모든 권한이 Y로 변경된것을 확인할 수 있습니다.

--권한 회수
mysql> revoke all on test.* from 'pritest'@'%';
Query OK, 0 rows affected (0.00 sec)

--권한 부여
mysql> grant all on *.* to 'pritest'@'%';
Query OK, 0 rows affected (0.01 sec)


--확인
mysql> show grants for 'pritest'@'%';
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for pritest@%                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE, CREATE ROLE, DROP ROLE ON *.* TO `pritest`@`%`                                                                                                                                               |
| GRANT APPLICATION_PASSWORD_ADMIN,AUDIT_ADMIN,BACKUP_ADMIN,BINLOG_ADMIN,BINLOG_ENCRYPTION_ADMIN,CLONE_ADMIN,CONNECTION_ADMIN,ENCRYPTION_KEY_ADMIN,GROUP_REPLICATION_ADMIN,INNODB_REDO_LOG_ARCHIVE,INNODB_REDO_LOG_ENABLE,PERSIST_RO_VARIABLES_ADMIN,REPLICATION_APPLIER,REPLICATION_SLAVE_ADMIN,RESOURCE_GROUP_ADMIN,RESOURCE_GROUP_USER,ROLE_ADMIN,SERVICE_CONNECTION_ADMIN,SESSION_VARIABLES_ADMIN,SET_USER_ID,SHOW_ROUTINE,SYSTEM_USER,SYSTEM_VARIABLES_ADMIN,TABLE_ENCRYPTION_ADMIN,XA_RECOVER_ADMIN ON *.* TO `pritest`@`%` |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+


mysql> select user,host,select_priv,insert_priv,update_priv,Delete_priv, Create_priv , Drop_priv , Reload_priv , Shutdown_priv , Process_priv ,
    ->  File_priv , Grant_priv , References_priv, Index_priv , Alter_priv , Show_db_priv , Super_priv , Create_tmp_table_priv , Lock_tables_priv , Execute_priv , Repl_slave_priv , Repl_client_priv , Create_view_priv ,
    ->  Show_view_priv , Create_routine_priv , Alter_routine_priv , Create_user_priv , Event_priv , Trigger_priv , Create_tablespace_priv
    ->  from mysql.user
    -> where user='pritest';
+---------+------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+
| user    | host | select_priv | insert_priv | update_priv | Delete_priv | Create_priv | Drop_priv | Reload_priv | Shutdown_priv | Process_priv | File_priv | Grant_priv | References_priv | Index_priv | Alter_priv | Show_db_priv | Super_priv | Create_tmp_table_priv | Lock_tables_priv | Execute_priv | Repl_slave_priv | Repl_client_priv | Create_view_priv | Show_view_priv | Create_routine_priv | Alter_routine_priv | Create_user_priv | Event_priv | Trigger_priv | Create_tablespace_priv |
+---------+------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+
| pritest | %    | Y           | Y           | Y           | Y           | Y           | Y         | Y           | Y             | Y            | Y         | N          | Y               | Y          | Y          | Y            | Y          | Y                     | Y                | Y            | Y               | Y                | Y                | Y              | Y                   | Y                  | Y                | Y          | Y            | Y                      |
+---------+------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+
1 row in set (0.00 sec)

출처 : Mysql 8.0 Reference Manual