8.2.12 Privilege Restriction Using Partial Revokes

8.2.12 Partial Revokes를 사용한 권한 제한

partial_revokes 시스템 변수(system variable)가 활성화되어 있으면 전역적으로 적용되는 권한을 부여하는 것이 가능합니다. 구체적으로, 전역 수준에서 권한을 가진 사용자에 대해, partial_revokes를 사용하면 특정 스키마(schema)에 대해서만 해당 권한을 취소하고 다른 스키마에 대해서는 그대로 유지할 수 있습니다. 이렇게 부과된 권한 제한은 전역 권한을 가지고 있지만 특정 스키마에는 접근해서는 안 되는 계정을 관리하는 데 유용할 수 있습니다. 예를 들어, 어떤 계정이 mysql 시스템 스키마(system schema)에 있는 테이블을 제외한 모든 테이블을 수정할 수 있도록 허용하는 것이 가능합니다.

• Using Partial Revokes

• Partial Revokes Versus Explicit Schema Grants

• Disabling Partial Revokes

• Partial Revokes and Replication

참고

간결함을 위해 여기 표시된 CREATE USER 문(statement)에는 패스워드가 포함되어 있지 않습니다. 운영 환경에서는 항상 계정 패스워드를 지정해야 합니다.

Using Partial Revokes

partial_revokes 시스템 변수는 계정에 권한 제한을 걸 수 있는지를 제어합니다. 기본적으로 partial_revokes는 비활성화되어 있으며, 전역 권한을 부분적으로 취소하려는 시도는 에러를 발생시킵니다:

1mysql> CREATE USER u1;
2mysql> GRANT SELECT, INSERT ON *.* TO u1;
3mysql> REVOKE INSERT ON world.* FROM u1;
4ERROR 1141 (42000): There is no such grant defined for user 'u1' on host '%'

REVOKE 연산을 허용하려면 partial_revokes를 활성화합니다:

1SET PERSIST partial_revokes = ON;

SET PERSIST는 실행 중인 MySQL 인스턴스(instance)에 대해 값을 설정합니다. 또한 이 값을 저장하여 이후 서버(server) 재시작 시에도 그대로 유지되도록 합니다. 실행 중인 MySQL 인스턴스에 대해서만 값을 변경하고 이후 재시작에까지 이어지지 않게 하려면, PERSIST 대신 GLOBAL 키워드(keyword)를 사용하십시오. Section 15.7.6.1, “SET Syntax for Variable Assignment”를 참조하십시오.

partial_revokes가 활성화된 상태에서는, partial revoke가 성공합니다:

1mysql> REVOKE INSERT ON world.* FROM u1;
2mysql> SHOW GRANTS FOR u1;
3+------------------------------------------+
4| Grants for u1@%                          |
5+------------------------------------------+
6| GRANT SELECT, INSERT ON *.* TO `u1`@`%`  |
7| REVOKE INSERT ON `world`.* FROM `u1`@`%` |
8+------------------------------------------+

SHOW GRANTS는 출력에서 partial revoke를 REVOKE 문으로 표시합니다. 결과는 u1이 전역 SELECT 및 INSERT 권한을 가지고 있지만, world 스키마의 테이블에 대해서는 INSERT를 사용할 수 없음을 나타냅니다. 즉, u1의 world 테이블에 대한 액세스(access)는 읽기 전용입니다.

서버는 partial revokes를 통해 구현된 권한 제한을 mysql.user 시스템 테이블(system table)에 기록합니다. 어떤 계정에 partial revoke가 있으면, 그 계정의 User_attributes 컬럼(column) 값에는 Restrictions 속성(attribute)이 포함됩니다:

1mysql> SELECT User, Host, User_attributes->>'$.Restrictions'
2       FROM mysql.user WHERE User_attributes->>'$.Restrictions' <> '';
3+------+------+------------------------------------------------------+
4| User | Host | User_attributes->>'$.Restrictions'                   |
5+------+------+------------------------------------------------------+
6| u1   | %    | [{"Database": "world", "Privileges": ["INSERT"]}] |
7+------+------+------------------------------------------------------+

참고

partial revoke는 어떤 스키마에 대해서도 부과할 수 있지만, 특히 mysql 시스템 스키마에 대한 권한 제한은 일반 계정이 시스템 계정을 수정하지 못하도록 방지하는 전략의 일부로 유용합니다. Protecting System Accounts Against Manipulation by Regular Accounts를 참조하십시오.

Partial revoke 연산에는 다음 조건이 적용됩니다:

• partial revoke를 사용하여 존재하지 않는 스키마에 제한을 설정하는 것은 가능하지만, 취소되는 권한이 전역적으로 부여되어 있는 경우에만 가능합니다. 권한이 전역적으로 부여되지 않은 상태에서 존재하지 않는 스키마에 대해 해당 권한을 취소하면 에러가 발생합니다.

• partial revoke는 스키마 수준에만 적용됩니다. FILE 또는 BINLOG_ADMIN과 같이 전역적으로만 적용되는 권한이나, 테이블, 컬럼 또는 루틴(routine) 권한에 대해 partial revoke를 사용할 수는 없습니다.

• 권한 부여 시에 partial_revokes를 활성화하면, 스키마 이름에서 이스케이프되지 않은 _ 및 % SQL 와일드카드(wildcard) 문자가 \_ 및 \%로 이스케이프된 것과 마찬가지로 리터럴(literal) 문자로 MySQL에 의해 해석되도록 합니다. 이는 MySQL이 권한을 해석하는 방식을 변경하므로, partial_revokes가 활성화될 수 있는 설치 환경에서는 권한 부여에 이스케이프되지 않은 와일드카드 문자를 사용하지 않는 것이 바람직할 수 있습니다.

앞에서 언급했듯이, 스키마 수준 권한에 대한 partial revoke는 SHOW GRANTS 출력에서 REVOKE 문으로 나타납니다. 이는 SHOW GRANTS가 “일반적인” 스키마 수준 권한을 표현하는 방식과는 다릅니다:

• 부여될 때, 스키마 수준 권한은 출력에서 자체 GRANT 문으로 표현됩니다:

1mysql> CREATE USER u1;
2mysql> GRANT UPDATE ON mysql.* TO u1;
3mysql> GRANT DELETE ON world.* TO u1;
4mysql> SHOW GRANTS FOR u1;
5+---------------------------------------+
6| Grants for u1@%                       |
7+---------------------------------------+
8| GRANT USAGE ON *.* TO `u1`@`%`        |
9| GRANT UPDATE ON `mysql`.* TO `u1`@`%` |
10| GRANT DELETE ON `world`.* TO `u1`@`%` |
11+---------------------------------------+

• 취소될 때, 스키마 수준 권한은 단순히 출력에서 사라집니다. REVOKE 문으로 표시되지는 않습니다:

1mysql> REVOKE UPDATE ON mysql.* FROM u1;
2mysql> REVOKE DELETE ON world.* FROM u1;
3mysql> SHOW GRANTS FOR u1;
4+--------------------------------+
5| Grants for u1@%                |
6+--------------------------------+
7| GRANT USAGE ON *.* TO `u1`@`%` |
8+--------------------------------+

사용자가 어떤 권한을 부여할 때, grantor가 그 권한에 대해 가지고 있는 제한은, grantee가 이미 제한 없이 해당 권한을 가지고 있지 않은 한, grantee에게 상속됩니다. 다음과 같이 두 사용자가 있다고 가정합니다. 이 중 한 사용자는 전역 SELECT 권한을 가지고 있습니다:

1CREATE USER u1, u2;
2GRANT SELECT ON *.* TO u2;

관리 사용자 admin이 전역이지만 부분적으로 리보크(revoke)된 SELECT 권한을 가지고 있다고 가정해 봅니다:

1mysql> CREATE USER admin;
2mysql> GRANT SELECT ON *.* TO admin WITH GRANT OPTION;
3mysql> REVOKE SELECT ON mysql.* FROM admin;
4mysql> SHOW GRANTS FOR admin;
5+------------------------------------------------------+
6| Grants for admin@%                                   |
7+------------------------------------------------------+
8| GRANT SELECT ON *.* TO `admin`@`%` WITH GRANT OPTION |
9| REVOKE SELECT ON `mysql`.* FROM `admin`@`%`          |
10+------------------------------------------------------+

admin이 u1과 u2에게 전역적으로 SELECT를 부여하면, 두 사용자에 대한 결과는 서로 다릅니다:

• admin이 처음에는 아무 SELECT 권한도 가지지 않은 u1에게 전역적으로 SELECT를 부여하면, u1은 admin의 권한 제한을 상속합니다:

1mysql> GRANT SELECT ON *.* TO u1;
2mysql> SHOW GRANTS FOR u1;
3+------------------------------------------+
4| Grants for u1@%                          |
5+------------------------------------------+
6| GRANT SELECT ON *.* TO `u1`@`%`          |
7| REVOKE SELECT ON `mysql`.* FROM `u1`@`%` |
8+------------------------------------------+

• 반면에, u2는 이미 제한 없이 전역 SELECT 권한을 보유하고 있습니다. GRANT는 grantee의 기존 권한을 줄일 수 없고, 추가만 할 수 있으므로, admin이 u2에게 전역적으로 SELECT를 부여하더라도 u2는 admin의 제한을 상속하지 않습니다:

1mysql> GRANT SELECT ON *.* TO u2;
2mysql> SHOW GRANTS FOR u2;
3+---------------------------------+
4| Grants for u2@%                 |
5+---------------------------------+
6| GRANT SELECT ON *.* TO `u2`@`%` |
7+---------------------------------+

GRANT 문에 AS user 절이 포함되어 있는 경우, 적용되는 권한 제한은 문을 실행하는 사용자가 아니라, 해당 절에 지정된 사용자/롤(role) 조합에 대한 제한입니다. AS 절에 대한 정보는 Section 15.7.1.6, “GRANT Statement”를 참조하십시오.

어떤 계정에 새로 부여되는 권한에 대한 제한은 그 계정에 대해 이미 존재하는 제한에 추가됩니다:

1mysql> CREATE USER u1;
2mysql> GRANT SELECT, INSERT, UPDATE, DELETE ON *.* TO u1;
3mysql> REVOKE INSERT ON mysql.* FROM u1;
4mysql> SHOW GRANTS FOR u1;
5+---------------------------------------------------------+
6| Grants for u1@%                                         |
7+---------------------------------------------------------+
8| GRANT SELECT, INSERT, UPDATE, DELETE ON *.* TO `u1`@`%` |
9| REVOKE INSERT ON `mysql`.* FROM `u1`@`%`                |
10+---------------------------------------------------------+
11mysql> REVOKE DELETE, UPDATE ON db2.* FROM u1;
12mysql> SHOW GRANTS FOR u1;
13+---------------------------------------------------------+
14| Grants for u1@%                                         |
15+---------------------------------------------------------+
16| GRANT SELECT, INSERT, UPDATE, DELETE ON *.* TO `u1`@`%` |
17| REVOKE UPDATE, DELETE ON `db2`.* FROM `u1`@`%`          |
18| REVOKE INSERT ON `mysql`.* FROM `u1`@`%`                |
19+---------------------------------------------------------+

권한 제한의 집계는, 위에서 보여준 것처럼 권한이 명시적으로 부분 취소될 때뿐 아니라, 문을 실행하는 사용자 또는 AS user 절에 언급된 사용자로부터 제한을 암묵적으로 상속받을 때에도 적용됩니다.

어떤 계정에 스키마에 대한 권한 제한이 있는 경우:

• 그 계정은 제한된 스키마 또는 그 안의 어떤 객체(object)에 대해서도 다른 계정에게 권한을 부여할 수 없습니다.

• 제한이 없는 다른 계정은, 제한된 스키마 또는 그 안의 객체에 대해 제한된 계정에게 권한을 부여할 수 있습니다. 제한이 없는 사용자가 다음 문을 실행한다고 가정해 봅니다:

1CREATE USER u1;
2GRANT SELECT, INSERT, UPDATE ON *.* TO u1;
3REVOKE SELECT, INSERT, UPDATE ON mysql.* FROM u1;
4GRANT SELECT ON mysql.user TO u1;          -- grant table privilege
5GRANT SELECT(Host,User) ON mysql.db TO u1; -- grant column privileges

결과적으로 생성된 계정은 제한된 스키마 내에서 제한된 작업을 수행할 수 있는 다음과 같은 권한을 갖습니다:

1mysql> SHOW GRANTS FOR u1;
2+-----------------------------------------------------------+
3| Grants for u1@%                                           |
4+-----------------------------------------------------------+
5| GRANT SELECT, INSERT, UPDATE ON *.* TO `u1`@`%`           |
6| REVOKE SELECT, INSERT, UPDATE ON `mysql`.* FROM `u1`@`%`  |
7| GRANT SELECT (`Host`, `User`) ON `mysql`.`db` TO `u1`@`%` |
8| GRANT SELECT ON `mysql`.`user` TO `u1`@`%`                |
9+-----------------------------------------------------------+

어떤 계정에 전역 권한에 대한 제한이 있는 경우, 다음 작업 중 하나를 수행하면 그 제한이 제거됩니다:

• 제한이 없는 계정이 해당 계정에 전역적으로 그 권한을 부여하는 경우.

• 스키마 수준에서 그 권한을 부여하는 경우.

• 전역적으로 그 권한을 취소하는 경우.

몇 가지 권한을 전역적으로 보유하지만 INSERT, UPDATE 및 DELETE에 제한이 있는 사용자 u1을 생각해 봅니다:

1mysql> CREATE USER u1;
2mysql> GRANT SELECT, INSERT, UPDATE, DELETE ON *.* TO u1;
3mysql> REVOKE INSERT, UPDATE, DELETE ON mysql.* FROM u1;
4mysql> SHOW GRANTS FOR u1;
5+----------------------------------------------------------+
6| Grants for u1@%                                          |
7+----------------------------------------------------------+
8| GRANT SELECT, INSERT, UPDATE, DELETE ON *.* TO `u1`@`%`  |
9| REVOKE INSERT, UPDATE, DELETE ON `mysql`.* FROM `u1`@`%` |
10+----------------------------------------------------------+

제한이 없는 계정에서 u1에게 전역적으로 어떤 권한을 부여하면 권한 제한이 제거됩니다. 예를 들어, INSERT 제한을 제거하려면 다음과 같이 합니다:

1mysql> GRANT INSERT ON *.* TO u1;
2mysql> SHOW GRANTS FOR u1;
3+---------------------------------------------------------+
4| Grants for u1@%                                         |
5+---------------------------------------------------------+
6| GRANT SELECT, INSERT, UPDATE, DELETE ON *.* TO `u1`@`%` |
7| REVOKE UPDATE, DELETE ON `mysql`.* FROM `u1`@`%`        |
8+---------------------------------------------------------+

스키마 수준에서 u1에게 권한을 부여하면 권한 제한이 제거됩니다. 예를 들어, UPDATE 제한을 제거하려면 다음과 같이 합니다:

1mysql> GRANT UPDATE ON mysql.* TO u1;
2mysql> SHOW GRANTS FOR u1;
3+---------------------------------------------------------+
4| Grants for u1@%                                         |
5+---------------------------------------------------------+
6| GRANT SELECT, INSERT, UPDATE, DELETE ON *.* TO `u1`@`%` |
7| REVOKE DELETE ON `mysql`.* FROM `u1`@`%`                |
8+---------------------------------------------------------+

전역 권한을 리보크하면, 그 권한과 그에 대한 어떤 제한도 함께 제거됩니다. 예를 들어, 모든 DELETE 액세스를 제거하는 대가로 DELETE 제한을 제거하려면 다음과 같이 합니다:

1mysql> REVOKE DELETE ON *.* FROM u1;
2mysql> SHOW GRANTS FOR u1;
3+-------------------------------------------------+
4| Grants for u1@%                                 |
5+-------------------------------------------------+
6| GRANT SELECT, INSERT, UPDATE ON *.* TO `u1`@`%` |
7+-------------------------------------------------+

어떤 계정이 전역 수준과 스키마 수준 모두에서 동일한 권한을 가지고 있는 경우, partial revoke를 수행하려면 스키마 수준에서 두 번 리보크해야 합니다. 예를 들어, u1이 다음과 같은 권한을 가지고 있으며, 여기서 INSERT는 전역 및 world 스키마 모두에서 보유된다고 가정해 봅니다:

1mysql> CREATE USER u1;
2mysql> GRANT SELECT, INSERT ON *.* TO u1;
3mysql> GRANT INSERT ON world.* TO u1;
4mysql> SHOW GRANTS FOR u1;
5+-----------------------------------------+
6| Grants for u1@%                         |
7+-----------------------------------------+
8| GRANT SELECT, INSERT ON *.* TO `u1`@`%` |
9| GRANT INSERT ON `world`.* TO `u1`@`%`   |
10+-----------------------------------------+

world에 대한 INSERT를 리보크하면 스키마 수준 권한이 리보크됩니다 (SHOW GRANTS 출력에 더 이상 스키마 수준 GRANT 문이 표시되지 않습니다):

1mysql> REVOKE INSERT ON world.* FROM u1;
2mysql> SHOW GRANTS FOR u1;
3+-----------------------------------------+
4| Grants for u1@%                         |
5+-----------------------------------------+
6| GRANT SELECT, INSERT ON *.* TO `u1`@`%` |
7+-----------------------------------------+

다시 한 번 world에 대한 INSERT를 리보크하면 전역 권한에 대한 partial revoke가 수행됩니다 (SHOW GRANTS 출력에 스키마 수준 REVOKE 문이 포함됩니다):

1mysql> REVOKE INSERT ON world.* FROM u1;
2mysql> SHOW GRANTS FOR u1;
3+------------------------------------------+
4| Grants for u1@%                          |
5+------------------------------------------+
6| GRANT SELECT, INSERT ON *.* TO `u1`@`%`  |
7| REVOKE INSERT ON `world`.* FROM `u1`@`%` |
8+------------------------------------------+

Partial Revokes Versus Explicit Schema Grants

일부 스키마에는 계정 액세스를 허용하고 다른 스키마에는 허용하지 않으려면, partial revoke는 전역 권한을 부여하지 않고 스키마 수준에서 명시적으로 액세스를 부여하는 접근 방식에 대한 대안이 됩니다. 이 두 가지 접근 방식에는 서로 다른 장단점이 있습니다.

스키마 수준 권한을 부여하고 전역 권한은 부여하지 않는 경우:

• 새 스키마 추가: 해당 스키마는 기본적으로 기존 계정에는 접근 불가능합니다. 그 스키마에 접근할 수 있어야 하는 계정에 대해, DBA는 스키마 수준 액세스를 부여해야 합니다.

• 새 계정 추가: DBA는 그 계정이 액세스해야 하는 각 스키마에 대해 스키마 수준 액세스를 부여해야 합니다.

전역 권한을 partial revoke와 함께 부여하는 경우:

• 새 스키마 추가: 해당 스키마는 전역 권한을 가진 기존 계정에 대해 접근 가능합니다. 그중에서 스키마에 접근할 수 없어야 하는 계정에 대해서는, DBA가 partial revoke를 추가해야 합니다.

• 새 계정 추가: DBA는 전역 권한을 부여하고, 제한할 각 스키마에 대해 partial revoke를 추가해야 합니다.

명시적 스키마 수준 grant를 사용하는 접근 방식은 소수의 스키마에만 액세스가 제한되는 계정에 대해 더 편리합니다. partial revoke를 사용하는 접근 방식은 대부분의 스키마에 대해 광범위하게 액세스를 허용하되 소수의 스키마만 제외해야 하는 계정에 대해 더 편리합니다.

Disabling Partial Revokes

일단 활성화되면, 어떤 계정에라도 권한 제한이 존재하는 경우 partial_revokes를 비활성화할 수 없습니다. 그러한 계정이 하나라도 있으면, partial_revokes를 비활성화하려는 시도는 실패합니다:

• startup 시점에 partial_revokes를 비활성화하려는 시도에 대해서는, 서버가 에러 메시지를 로그(log)로 남기고 partial_revokes를 활성화한 채로 둡니다.

• runtime에 partial_revokes를 비활성화하려는 시도에 대해서는, 에러가 발생하고 partial_revokes 값은 변경되지 않습니다.

제한이 존재하는 상태에서 partial_revokes를 비활성화하려면, 먼저 그 제한을 제거해야 합니다:

1. Partial revoke가 있는 계정을 확인합니다:

1SELECT User, Host, User_attributes->>'$.Restrictions'
2FROM mysql.user WHERE User_attributes->>'$.Restrictions' <> '';

2. 각 계정에 대해, 그 계정의 권한 제한을 제거합니다. 예를 들어, 이전 단계에서 계정 u1이 다음과 같은 제한을 가지고 있는 것으로 표시되었다고 가정해 봅니다:

1[{"Database": "world", "Privileges": ["INSERT", "DELETE"]\

제한 제거는 여러 가지 방식으로 수행할 수 있습니다:

• 전역적으로 제한 없이 권한을 부여합니다:

  1GRANT INSERT, DELETE ON *.* TO u1;

• 스키마 수준에서 권한을 부여합니다:

  1GRANT INSERT, DELETE ON world.* TO u1;

• (더 이상 필요하지 않다고 가정하고) 전역적으로 권한을 리보크합니다:

  1REVOKE INSERT, DELETE ON *.* FROM u1;

• (더 이상 필요하지 않다고 가정하고) 계정 자체를 제거합니다:

  1DROP USER u1;

모든 권한 제한이 제거된 후에는, partial revoke를 비활성화하는 것이 가능합니다:

1SET PERSIST partial_revokes = OFF;

Partial Revokes and Replication

레플리케이션(replication) 시나리오에서, 어떤 호스트(host)에서라도 partial_revokes가 활성화되어 있으면, 모든 호스트에서 활성화되어야 합니다. 그렇지 않으면, 전역 권한을 부분적으로 리보크하는 REVOKE 문이 레플리케이션이 발생하는 모든 호스트에서 동일한 효과를 내지 못하여, 레플리케이션 불일치 또는 에러가 발생할 수 있습니다.

partial_revokes가 활성화되면, GRANT 문에 대해, 그 문을 발행한 현재 사용자와 그 사용자의 현재 활성 롤을 포함하는 확장된 구문(syntax)이 바이너리 로그(binary log)에 기록됩니다. 이와 같이 기록된 사용자 또는 롤이 레플리카(replica)에 존재하지 않으면, 레플리케이션 어플라이어 스레드(replication applier thread)는 에러와 함께 GRANT 문에서 멈춥니다. 레플리케이션 소스(source) 서버에서 GRANT 문을 발행하거나 발행할 가능성이 있는 모든 사용자 계정이 레플리카에도 존재하고, 소스에서와 동일한 롤 집합을 가지고 있는지 확인하십시오.