8.2.23 SQL-Based Account Activity Auditing

8.2.23 SQL-Based Account Activity Auditing

Application은 데이터베이스 액티비티를 MySQL 계정에 연결시키는 SQL 기반 감사(auditing)를 수행하기 위해 다음 지침을 사용할 수 있습니다.

MySQL 계정은 mysql.user 시스템 테이블의 행에 해당합니다. 클라이언트가 성공적으로 연결하면, 서버는 이 테이블의 특정 행에 대해 클라이언트를 인증합니다. 이 행의 User 및 Host 컬럼 값은 계정을 고유하게 식별하며, SQL 문에서 계정 이름이 작성되는 형식인 'user_name'@'host_name' 형식에 해당합니다.

클라이언트를 인증하는 데 사용된 계정은 클라이언트가 갖는 권한을 결정합니다. 일반적으로 CURRENT_USER() 함수를 호출하여 클라이언트 사용자에 대해 이것이 어떤 계정인지 결정할 수 있습니다. 이 값은 해당 계정에 대한 user 테이블 행의 User 및 Host 컬럼으로부터 구성됩니다.

그러나 CURRENT_USER() 값이 클라이언트 사용자가 아닌 다른 계정에 해당하는 상황이 있습니다. 이는 권한 체크가 클라이언트의 계정에 기반하지 않는 컨텍스트에서 발생합니다:

• SQL SECURITY DEFINER 특성으로 정의된 저장 루틴(프로시저 및 함수)
• SQL SECURITY DEFINER 특성으로 정의된 뷰
• 트리거 및 이벤트

이러한 컨텍스트에서는 권한 체크가 DEFINER 계정에 대해 수행되며, CURRENT_USER()는 저장 루틴 또는 뷰를 호출했거나 트리거를 활성화하도록 한 클라이언트의 계정이 아니라 그 DEFINER 계정을 가리킵니다. 호출한 사용자를 결정하려면, 클라이언트가 제공한 실제 사용자 이름과 클라이언트가 연결한 호스트를 나타내는 값을 반환하는 USER() 함수를 호출할 수 있습니다. 그러나 이 값은 user 테이블의 계정과 직접적으로 대응하지 않을 수도 있는데, USER() 값에는 와일드카드가 절대 포함되지 않는 반면, 계정 값(CURRENT_USER()이 반환하는 값)은 사용자 이름 및 호스트 이름 와일드카드를 포함할 수 있기 때문입니다.

예를 들어, 공백 사용자 이름은 어떤 사용자와도 매칭되므로, ''@'localhost' 계정은 클라이언트가 로컬 호스트에서 어떤 사용자 이름으로든 익명 사용자로 연결할 수 있게 합니다. 이 경우, 클라이언트가 로컬 호스트에서 user1로 연결하면, USER()와 CURRENT_USER()는 서로 다른 값을 반환합니다:

1mysql> SELECT USER(), CURRENT_USER();
2+-----------------+----------------+
3| USER()          | CURRENT_USER() |
4+-----------------+----------------+
5| user1@localhost | @localhost     |
6+-----------------+----------------+

계정의 호스트 이름 부분에도 와일드카드를 포함할 수 있습니다. 호스트 이름에 '%' 또는 '_' 패턴 문자가 포함되거나 넷마스크 표기법을 사용하면, 이 계정은 여러 호스트에서 연결하는 클라이언트에 사용될 수 있으며, CURRENT_USER() 값만으로는 그중 어느 호스트인지 알 수 없습니다. 예를 들어, 'user2'@'%.example.com' 계정은 user2가 example.com 도메인의 어느 호스트에서든 연결할 때 사용할 수 있습니다. user2가 remote.example.com에서 연결하면, USER()와 CURRENT_USER()는 서로 다른 값을 반환합니다:

1mysql> SELECT USER(), CURRENT_USER();
2+--------------------------+---------------------+
3| USER()                   | CURRENT_USER()      |
4+--------------------------+---------------------+
5| user2@remote.example.com | user2@%.example.com |
6+--------------------------+---------------------+

Application이 사용자 감사를 위해 USER()를 반드시 호출해야 하지만(예: 트리거 내부에서 감사를 수행하는 경우), 동시에 USER() 값을 user 테이블의 계정과 연관시킬 수 있어야 한다면, User 또는 Host 컬럼에 와일드카드가 포함된 계정을 사용하지 않도록 해야 합니다. 구체적으로, User가 비어 있도록 허용하지 말아야 하며(이는 익명 사용자 계정을 생성합니다), Host 값에 패턴 문자 또는 넷마스크 표기법을 허용해서도 안 됩니다. 모든 계정은 비어 있지 않은 User 값과 리터럴 Host 값을 가져야 합니다.

이전에 든 예와 관련해서, ''@'localhost' 및 'user2'@'%.example.com' 계정은 와일드카드를 사용하지 않도록 변경해야 합니다:

1RENAME USER ''@'localhost' TO 'user1'@'localhost';
2RENAME USER 'user2'@'%.example.com' TO 'user2'@'remote.example.com';

user2가 example.com 도메인의 여러 호스트에서 연결할 수 있어야 한다면, 각 호스트마다 별도의 계정이 있어야 합니다.

CURRENT_USER() 또는 USER() 값에서 사용자 이름 또는 호스트 이름 부분을 추출하려면, SUBSTRING_INDEX() 함수를 사용하십시오:

1mysql> SELECT SUBSTRING_INDEX(CURRENT_USER(),'@',1);
2+---------------------------------------+
3| SUBSTRING_INDEX(CURRENT_USER(),'@',1) |
4+---------------------------------------+
5| user1                                 |
6+---------------------------------------+
7
8mysql> SELECT SUBSTRING_INDEX(CURRENT_USER(),'@',-1);
9+----------------------------------------+
10| SUBSTRING_INDEX(CURRENT_USER(),'@',-1) |
11+----------------------------------------+
12| localhost                              |
13+----------------------------------------+