자주 사용하는 액세스 관리 쿼리

이 문서에서는 SQL 사용자와 역할을 정의하고, 해당 권한 및 permission을 데이터베이스, 테이블, 행, 컬럼에 적용하는 기본 방법을 설명합니다.

자가 관리형

자가 관리형 ClickHouse를 사용 중이라면 SQL 사용자 및 역할을 참조하십시오.

이 문서에서는 SQL 사용자와 역할을 정의하고, 해당 권한을 데이터베이스(database), 테이블(table), 행(row), 컬럼(column)에 적용하는 기본 사항을 설명합니다.

​

관리자 사용자

ClickHouse Cloud 서비스에는 서비스가 생성될 때 함께 생성되는 관리자 사용자 default가 있습니다. 비밀번호는 서비스 생성 시 제공되며, 관리자 역할이 있는 ClickHouse Cloud 사용자가 재설정할 수 있습니다.

ClickHouse Cloud 서비스에 SQL 사용자를 추가로 만들면 해당 사용자에게는 SQL 사용자 이름과 비밀번호가 필요합니다. 이 사용자들에게 관리자 수준의 권한을 부여하려면 새 사용자에게 역할 default_role을 할당하십시오. 예를 들어, 사용자 clickhouse_admin을 추가하는 방법은 다음과 같습니다.

CREATE USER IF NOT EXISTS clickhouse_admin
IDENTIFIED WITH sha256_password BY 'P!@ssword42!';

GRANT default_role TO clickhouse_admin;

SQL 콘솔을 사용할 때 SQL 문은 default 사용자로 실행되지 않습니다. 대신 sql-console:${cloud_login_email}라는 이름의 사용자로 실행되며, 여기서 cloud_login_email은 현재 쿼리를 실행하는 사용자의 이메일입니다.

이렇게 자동으로 생성된 SQL 콘솔 사용자에게는 default 역할이 부여됩니다.

​

비밀번호 없는 인증

SQL 콘솔에서 사용할 수 있는 역할은 두 가지입니다. default_role과 동일한 권한을 가진 sql_console_admin과 읽기 전용 권한을 가진 sql_console_read_only입니다.

관리자 사용자는 기본적으로 sql_console_admin 역할이 할당되므로 별도 변경 사항이 없습니다. 반면 sql_console_read_only 역할을 사용하면 관리자 권한이 없는 사용자에게 임의의 인스턴스에 대해 읽기 전용 또는 전체 접근 권한을 부여할 수 있습니다. 이 접근 권한은 관리자가 구성해야 합니다. 또한 인스턴스별 요구 사항에 더 잘 맞도록 GRANT 또는 REVOKE 명령으로 역할을 조정할 수 있으며, 이러한 역할에 적용한 변경 사항은 모두 유지됩니다.

​

세분화된 접근 제어

이 접근 제어 기능은 사용자 수준의 세분화 수준으로 수동 구성할 수도 있습니다. 사용자에게 새 sql_console_* 역할을 할당하기 전에, 네임스페이스 sql-console-role:<email>에 맞는 SQL 콘솔 사용자별 데이터베이스 역할을 먼저 생성해야 합니다. 예시는 다음과 같습니다:

CREATE ROLE OR REPLACE sql-console-role:<email>;
GRANT <some grants> TO sql-console-role:<email>;

일치하는 역할이 감지되면 미리 정의된 역할 대신 해당 사용자에게 할당됩니다. 이를 통해 sql_console_sa_role 및 sql_console_pm_role과 같은 역할을 생성하고, 이를 특정 사용자에게 부여하는 등 더 복잡한 접근 제어 구성을 할 수 있습니다. 예시는 다음과 같습니다:

CREATE ROLE OR REPLACE sql_console_sa_role;
GRANT <whatever level of access> TO sql_console_sa_role;
CREATE ROLE OR REPLACE sql_console_pm_role;
GRANT <whatever level of access> TO sql_console_pm_role;
CREATE ROLE OR REPLACE `sql-console-role:[e-mail]`;
CREATE ROLE OR REPLACE `sql-console-role:[e-mail]`;
CREATE ROLE OR REPLACE `sql-console-role:[e-mail]`;
GRANT sql_console_sa_role to `sql-console-role:[e-mail]`;
GRANT sql_console_sa_role to `sql-console-role:[e-mail]`;
GRANT sql_console_pm_role to `sql-console-role:[e-mail]`;

​

관리자 권한 테스트

사용자 default에서 로그아웃한 후 사용자 clickhouse_admin으로 다시 로그인합니다.

다음 항목은 모두 성공해야 합니다:

SHOW GRANTS FOR clickhouse_admin;

CREATE DATABASE db1

CREATE TABLE db1.table1 (id UInt64, column1 String) ENGINE = MergeTree() ORDER BY id;

INSERT INTO db1.table1 (id, column1) VALUES (1, 'abc');

SELECT * FROM db1.table1;

DROP TABLE db1.table1;

DROP DATABASE db1;

​

비관리자 사용자

사용자에게는 필요한 권한만 부여해야 하며, 모든 사용자를 관리자 사용자로 둘 필요는 없습니다. 이 문서의 나머지 부분에서는 예시 시나리오와 이에 필요한 역할을 설명합니다.

​

준비

예시에서 사용할 다음 테이블과 사용자를 생성합니다.

​

샘플 데이터베이스, 테이블, 행 만들기

1. 테스트 데이터베이스 만들기

   CREATE DATABASE db1;
   
2. 테이블 만들기

   CREATE TABLE db1.table1 (
      id UInt64,
      column1 String,
      column2 String
   )
   ENGINE MergeTree
   ORDER BY id;
   
3. 테이블에 샘플 행 삽입하기

   INSERT INTO db1.table1
      (id, column1, column2)
   VALUES
      (1, 'A', 'abc'),
      (2, 'A', 'def'),
      (3, 'B', 'abc'),
      (4, 'B', 'def');
   
4. 테이블 확인하기

   쿼리

   SELECT *
   FROM db1.table1
   

   응답

   Query id: 475015cc-6f51-4b20-bda2-3c9c41404e49
   
   ┌─id─┬─column1─┬─column2─┐
   │  1 │ A       │ abc     │
   │  2 │ A       │ def     │
   │  3 │ B       │ abc     │
   │  4 │ B       │ def     │
   └────┴─────────┴─────────┘
   
5. column_user 만들기

   특정 컬럼에 대한 접근 제한을 보여주는 데 사용할 일반 사용자를 생성합니다:

   CREATE USER column_user IDENTIFIED BY 'password';
   
6. row_user 만들기

   특정 값을 가진 행에 대한 접근 제한을 보여주는 데 사용할 일반 사용자를 생성합니다:

   CREATE USER row_user IDENTIFIED BY 'password';
   

​

역할 생성

다음 예시에서는 다음 작업을 수행합니다.

• 컬럼 및 행 등 서로 다른 권한에 대한 역할을 생성합니다
• 역할에 권한을 부여합니다
• 각 역할에 사용자를 할당합니다

역할을 사용하면 각 사용자를 개별적으로 관리하는 대신, 특정 권한별로 사용자 그룹을 정의할 수 있습니다.

1. 이 역할에 속한 사용자가 데이터베이스 db1의 table1에서 column1만 볼 수 있도록 제한하는 역할 생성

   CREATE ROLE column1_users;
   
2. column1을 볼 수 있는 권한 설정

   GRANT SELECT(id, column1) ON db1.table1 TO column1_users;
   
3. column_user 사용자를 column1_users 역할에 추가

   GRANT column1_users TO column_user;
   
4. 이 역할에 속한 사용자가 선택된 행만 볼 수 있도록 제한하는 역할 생성. 여기서는 column1에 A가 있는 행만 볼 수 있습니다

   CREATE ROLE A_rows_users;
   
5. row_user를 A_rows_users 역할에 추가

   GRANT A_rows_users TO row_user;
   
6. column1 값이 A인 경우에만 볼 수 있도록 정책 생성

   CREATE ROW POLICY A_row_filter ON db1.table1 FOR SELECT USING column1 = 'A' TO A_rows_users;
   
7. 데이터베이스와 테이블에 대한 권한 설정

   GRANT SELECT(id, column1, column2) ON db1.table1 TO A_rows_users;
   
8. 다른 역할도 계속 모든 행에 접근할 수 있도록 명시적으로 권한 부여

   CREATE ROW POLICY allow_other_users_filter 
   ON db1.table1 FOR SELECT USING 1 TO clickhouse_admin, column1_users;
   

   정책을 테이블에 연결하면 시스템은 해당 정책을 적용하며, 그 정책에 정의된 사용자와 역할만 테이블에서 작업을 수행할 수 있습니다. 그 외 사용자와 역할의 모든 작업은 거부됩니다. 제한적인 행 정책이 다른 사용자에게 적용되지 않도록 하려면, 다른 사용자와 역할에 일반적인 접근 또는 다른 형태의 접근을 허용하는 별도의 정책을 추가로 정의해야 합니다.

​

확인

​

컬럼이 제한된 사용자로 역할 권한 테스트

1. clickhouse_admin 사용자로 clickhouse client에 로그인합니다

   clickhouse-client --user clickhouse_admin --password password
   
2. 관리자 사용자로 데이터베이스, 테이블, 그리고 모든 행에 대한 접근을 확인합니다.

   SELECT *
   FROM db1.table1
   

   Query id: f5e906ea-10c6-45b0-b649-36334902d31d
   
   ┌─id─┬─column1─┬─column2─┐
   │  1 │ A       │ abc     │
   │  2 │ A       │ def     │
   │  3 │ B       │ abc     │
   │  4 │ B       │ def     │
   └────┴─────────┴─────────┘
   
3. column_user 사용자로 ClickHouse client에 로그인합니다

   clickhouse-client --user column_user --password password
   
4. 모든 컬럼으로 SELECT를 테스트합니다

   SELECT *
   FROM db1.table1
   

   Query id: 5576f4eb-7450-435c-a2d6-d6b49b7c4a23
   
   0 rows in set. Elapsed: 0.006 sec.
   
   Received exception from server (version 22.3.2):
   Code: 497. DB::Exception: Received from localhost:9000. 
   DB::Exception: column_user: Not enough privileges. 
   To execute this query it's necessary to have grant 
   SELECT(id, column1, column2) ON db1.table1. (ACCESS_DENIED)
   

   모든 컬럼을 지정했지만, 이 사용자에게는 id와 column1에만 접근 권한이 있으므로 액세스가 거부됩니다

5. 지정되었고 허용된 컬럼만 사용하는 SELECT 쿼리를 확인합니다:

   SELECT
       id,
       column1
   FROM db1.table1
   

   Query id: cef9a083-d5ce-42ff-9678-f08dc60d4bb9
   
   ┌─id─┬─column1─┐
   │  1 │ A       │
   │  2 │ A       │
   │  3 │ B       │
   │  4 │ B       │
   └────┴─────────┘
   

​

행 제한이 적용된 사용자로 역할 권한 테스트

1. row_user로 ClickHouse client에 로그인

   clickhouse-client --user row_user --password password
   
2. 조회 가능한 행 확인

   SELECT *
   FROM db1.table1
   

   Query id: a79a113c-1eca-4c3f-be6e-d034f9a220fb
   
   ┌─id─┬─column1─┬─column2─┐
   │  1 │ A       │ abc     │
   │  2 │ A       │ def     │
   └────┴─────────┴─────────┘
   

   위의 두 개 행만 반환되는지 확인하십시오. column1의 값이 B인 행은 제외되어야 합니다.

​

사용자 및 역할 수정

필요한 권한 조합에 따라 사용자에게 여러 역할을 할당할 수 있습니다. 여러 역할을 사용하는 경우 시스템은 각 역할을 조합해 권한을 결정하며, 결과적으로 역할 권한은 누적됩니다.

예를 들어 role1이 column1에 대한 SELECT만 허용하고 role2가 column1 및 column2에 대한 SELECT를 허용하면, 해당 사용자는 두 컬럼 모두에 접근할 수 있습니다.

1. 관리자 계정을 사용해 행과 컬럼 모두에 제한을 적용하고 기본 역할을 가진 새 사용자를 생성합니다

   CREATE USER row_and_column_user IDENTIFIED BY 'password' DEFAULT ROLE A_rows_users;
   
2. A_rows_users 역할의 기존 권한을 제거합니다

   REVOKE SELECT(id, column1, column2) ON db1.table1 FROM A_rows_users;
   
3. A_row_users 역할이 column1만 SELECT할 수 있도록 허용합니다

   GRANT SELECT(id, column1) ON db1.table1 TO A_rows_users;
   
4. row_and_column_user로 clickhouse client에 로그인합니다

   clickhouse-client --user row_and_column_user --password password;
   
5. 모든 컬럼으로 테스트합니다:

   SELECT *
   FROM db1.table1
   

   Query id: 8cdf0ff5-e711-4cbe-bd28-3c02e52e8bc4
   
   0 rows in set. Elapsed: 0.005 sec.
   
   Received exception from server (version 22.3.2):
   Code: 497. DB::Exception: Received from localhost:9000. 
   DB::Exception: row_and_column_user: Not enough privileges. 
   To execute this query it's necessary to have grant 
   SELECT(id, column1, column2) ON db1.table1. (ACCESS_DENIED)
   
6. 허용된 컬럼만 사용해 테스트합니다:

   SELECT
       id,
       column1
   FROM db1.table1
   

   Query id: 5e30b490-507a-49e9-9778-8159799a6ed0
   
   ┌─id─┬─column1─┐
   │  1 │ A       │
   │  2 │ A       │
   └────┴─────────┘
   

​

문제 해결

권한이 서로 중첩되거나 결합되어 예상치 못한 결과가 발생하는 경우가 있습니다. 이런 경우 관리자 계정으로 다음 명령을 사용해 문제 범위를 좁힐 수 있습니다.

​

사용자에게 부여된 권한과 역할 확인

SHOW GRANTS FOR row_and_column_user

Query id: 6a73a3fe-2659-4aca-95c5-d012c138097b

┌─GRANTS FOR row_and_column_user───────────────────────────┐
│ GRANT A_rows_users, column1_users TO row_and_column_user │
└──────────────────────────────────────────────────────────┘

​

ClickHouse에서 역할 보기

SHOW ROLES

Query id: 1e21440a-18d9-4e75-8f0e-66ec9b36470a

┌─name────────────┐
│ A_rows_users    │
│ column1_users   │
└─────────────────┘

​

정책 보기

SHOW ROW POLICIES

Query id: f2c636e9-f955-4d79-8e80-af40ea227ebc

┌─name───────────────────────────────────┐
│ A_row_filter ON db1.table1             │
│ allow_other_users_filter ON db1.table1 │
└────────────────────────────────────────┘

​

정책 정의 내용과 현재 권한 보기

SHOW CREATE ROW POLICY A_row_filter ON db1.table1

Query id: 0d3b5846-95c7-4e62-9cdd-91d82b14b80b

┌─CREATE ROW POLICY A_row_filter ON db1.table1────────────────────────────────────────────────┐
│ CREATE ROW POLICY A_row_filter ON db1.table1 FOR SELECT USING column1 = 'A' TO A_rows_users │
└─────────────────────────────────────────────────────────────────────────────────────────────┘

​

역할, 정책 및 사용자 관리를 위한 예시 명령

다음 명령으로 다음 작업을 수행할 수 있습니다.

• 권한 삭제
• 정책 삭제
• 역할에서 사용자 제거
• 사용자 및 역할 삭제
  

이 명령은 관리자 사용자 또는 default 사용자로 실행하세요

​

역할에서 권한 제거하기

REVOKE SELECT(column1, id) ON db1.table1 FROM A_rows_users;

​

정책 삭제하기

DROP ROW POLICY A_row_filter ON db1.table1;

​

역할에서 사용자 제거

REVOKE A_rows_users FROM row_user;

​

역할 삭제

DROP ROLE A_rows_users;

​

사용자 삭제

DROP USER row_user;

​

요약

이 문서에서는 SQL 사용자와 역할을 생성하는 기본 사항을 설명하고, 사용자와 역할의 권한을 설정하고 수정하는 방법을 안내했습니다. 각 항목에 대한 자세한 내용은 사용자 가이드와 참고 문서를 참조하십시오.