Consultas comuns de gerenciamento de acesso

AutogerenciadoSe você estiver trabalhando com ClickHouse autogerenciado, consulte usuários e funções SQL.

Este artigo apresenta os conceitos básicos para definir usuários e funções SQL e aplicar esses privilégios e permissões a bancos de dados, tabelas, linhas e colunas.

Usuário admin

Os serviços do ClickHouse Cloud têm um usuário admin, default, criado quando o serviço é criado. A senha é fornecida na criação do serviço e pode ser redefinida por usuários do ClickHouse Cloud que tenham a função Admin. Quando você adiciona usuários SQL ao seu serviço do ClickHouse Cloud, eles precisarão de um nome de usuário SQL e uma senha. Se quiser que eles tenham privilégios de nível administrativo, atribua aos novos usuários a função default_role. Por exemplo, adicionando o usuário clickhouse_admin:

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

GRANT default_role TO clickhouse_admin;

Ao usar o SQL Console, suas instruções SQL não serão executadas como o usuário default. Em vez disso, elas serão executadas como um usuário chamado sql-console:${cloud_login_email}, em que cloud_login_email é o e-mail do usuário que está executando a consulta naquele momento.Esses usuários do SQL Console gerados automaticamente têm a função default.

Autenticação sem senha

Há duas funções disponíveis para o Console SQL: sql_console_admin, com permissões idênticas às de default_role, e sql_console_read_only, com permissões somente leitura. Os usuários Admin recebem a função sql_console_admin por padrão, então nada muda para eles. No entanto, a função sql_console_read_only permite conceder a usuários não Admin acesso somente leitura ou acesso total a qualquer instância. Um Admin precisa configurar esse acesso. As funções podem ser ajustadas com os comandos GRANT ou REVOKE para atender melhor aos requisitos específicos de cada instância, e todas as modificações feitas nessas funções serão mantidas.

Controle de acesso granular

Essa funcionalidade de controle de acesso também pode ser configurada manualmente com granularidade por usuário. Antes de atribuir as novas funções sql_console_* aos usuários, devem ser criadas funções de banco de dados específicos por usuário do console SQL que sigam o namespace sql-console-role:<email>. Por exemplo:

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

Quando uma função correspondente for detectada, ela será atribuída ao usuário em vez das funções padrão. Isso permite configurações de controle de acesso mais complexas, como criar funções como sql_console_sa_role e sql_console_pm_role e concedê-las a usuários específicos. Por exemplo:

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:christoph@clickhouse.com`;
CREATE ROLE OR REPLACE `sql-console-role:jake@clickhouse.com`;
CREATE ROLE OR REPLACE `sql-console-role:zach@clickhouse.com`;
GRANT sql_console_sa_role to `sql-console-role:christoph@clickhouse.com`;
GRANT sql_console_sa_role to `sql-console-role:jake@clickhouse.com`;
GRANT sql_console_pm_role to `sql-console-role:zach@clickhouse.com`;

Teste os privilégios de administrador

Saia da sessão com o usuário default e entre novamente como o usuário clickhouse_admin. Todos estes devem funcionar:

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;

Usuários não administradores

Os usuários devem ter os privilégios necessários, e nem todos devem ser administradores. O restante deste documento apresenta cenários de exemplo e as funções necessárias.

Preparação

Crie estas tabelas e usuários para uso nos exemplos.

Criando um banco de dados, uma tabela e linhas de exemplo

Criar um banco de dados de teste

CREATE DATABASE db1;

Criar uma tabela

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

Popular a tabela com linhas de exemplo

INSERT INTO db1.table1
   (id, column1, column2)
VALUES
   (1, 'A', 'abc'),
   (2, 'A', 'def'),
   (3, 'B', 'abc'),
   (4, 'B', 'def');

Verificar a tabela

Consulta

SELECT *
FROM db1.table1

Resposta

Query id: 475015cc-6f51-4b20-bda2-3c9c41404e49

┌─id─┬─column1─┬─column2─┐
│  1 │ A       │ abc     │
│  2 │ A       │ def     │
│  3 │ B       │ abc     │
│  4 │ B       │ def     │
└────┴─────────┴─────────┘

Criar column_user

Crie um usuário comum que será usado para demonstrar a restrição de acesso a determinadas colunas:

CREATE USER column_user IDENTIFIED BY 'password';

Criar row_user

Crie um usuário comum que será usado para demonstrar a restrição de acesso a linhas com determinados valores:

CREATE USER row_user IDENTIFIED BY 'password';

Criando funções

Com este conjunto de exemplos:

serão criadas funções para diferentes privilégios, como colunas e linhas
os privilégios serão concedidos às funções
usuários serão atribuídos a cada função

As funções são usadas para definir grupos de usuários para determinados privilégios, em vez de gerenciar cada usuário separadamente.

Crie uma função para restringir os usuários dessa função a ver apenas column1 no banco de dados db1 e na tabela table1:

CREATE ROLE column1_users;

Defina privilégios para permitir a visualização de column1

GRANT SELECT(id, column1) ON db1.table1 TO column1_users;

Adicione o usuário column_user à função column1_users

GRANT column1_users TO column_user;

Crie uma função para restringir os usuários dessa função a ver apenas linhas selecionadas; neste caso, apenas linhas que contenham A em column1

CREATE ROLE A_rows_users;

Adicione row_user à função A_rows_users

GRANT A_rows_users TO row_user;

Crie uma política para permitir a visualização apenas das linhas em que column1 tenha o valor A

CREATE ROW POLICY A_row_filter ON db1.table1 FOR SELECT USING column1 = 'A' TO A_rows_users;

Defina privilégios para o banco de dados e a tabela

GRANT SELECT(id, column1, column2) ON db1.table1 TO A_rows_users;

Conceda permissões explícitas para que outras funções ainda tenham acesso a todas as linhas

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

Ao associar uma política a uma tabela, o sistema aplicará essa política, e somente os usuários e funções definidos poderão realizar operações na tabela; todos os demais terão qualquer operação negada. Para evitar que a política restritiva de linhas seja aplicada a outros usuários, outra política deve ser definida para permitir que outros usuários e funções tenham acesso normal ou outros tipos de acesso.

Verificação

Testando privilégios da função com usuário com restrição de colunas

Verifique o acesso ao banco de dados, à tabela e a todas as linhas com o usuário admin.

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     │
└────┴─────────┴─────────┘

Teste o SELECT usando todas as colunas

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)

O acesso foi negado porque todas as colunas foram especificadas, e o usuário tem acesso apenas a id e column1

Verifique a consulta SELECT apenas com as colunas especificadas e permitidas:

SELECT
    id,
    column1
FROM db1.table1

Query id: cef9a083-d5ce-42ff-9678-f08dc60d4bb9

┌─id─┬─column1─┐
│  1 │ A       │
│  2 │ A       │
│  3 │ B       │
│  4 │ B       │
└────┴─────────┘

Testando privilégios de função com usuário com restrição por linha

Veja as linhas disponíveis

SELECT *
FROM db1.table1

Query id: a79a113c-1eca-4c3f-be6e-d034f9a220fb

┌─id─┬─column1─┬─column2─┐
│  1 │ A       │ abc     │
│  2 │ A       │ def     │
└────┴─────────┴─────────┘

Verifique se apenas as duas linhas acima são retornadas; as linhas com o valor B em column1 não devem ser incluídas.

Modificando usuários e funções

Os usuários podem receber várias funções para combinar os privilégios necessários. Ao usar várias funções, o sistema combinará essas funções para determinar os privilégios; o efeito final é que as permissões das funções serão cumulativas. Por exemplo, se role1 permitir apenas SELECT em column1 e role2 permitir SELECT em column1 e column2, o usuário terá acesso a ambas as colunas.

Usando a conta de administrador, crie um novo usuário para restringir por linha e coluna com funções padrão

CREATE USER row_and_column_user IDENTIFIED BY 'password' DEFAULT ROLE A_rows_users;

Remova os privilégios anteriores da função A_rows_users

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

Permita que a função A_row_users faça SELECT apenas de column1

GRANT SELECT(id, column1) ON db1.table1 TO A_rows_users;

Teste com todas as colunas:

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)

Teste com as colunas permitidas:

SELECT
    id,
    column1
FROM db1.table1

Query id: 5e30b490-507a-49e9-9778-8159799a6ed0

┌─id─┬─column1─┐
│  1 │ A       │
│  2 │ A       │
└────┴─────────┘

Solução de problemas

Em alguns casos, os privilégios podem se sobrepor ou se combinar de forma a produzir resultados inesperados. Os comandos a seguir podem ser usados para ajudar a identificar o problema usando uma conta de administrador

Listar grants e funções de um usuário

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 │
└──────────────────────────────────────────────────────────┘

Listar funções no ClickHouse

SHOW ROLES

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

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

Exibir as políticas

SHOW ROW POLICIES

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

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

Veja como uma política foi definida e quais são os privilégios atuais

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 │
└─────────────────────────────────────────────────────────────────────────────────────────────┘

Comandos de exemplo para gerenciar funções, políticas e usuários

Os comandos a seguir podem ser usados para:

excluir privilégios
excluir políticas
desvincular usuários de funções
excluir usuários e funções

Execute estes comandos como um usuário Admin ou o usuário default

Remover um privilégio de uma função

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

Excluir uma política

DROP ROW POLICY A_row_filter ON db1.table1;

Remover um usuário de uma função

REVOKE A_rows_users FROM row_user;

Excluir uma função

DROP ROLE A_rows_users;

Excluir um usuário

DROP USER row_user;

Resumo

Este artigo apresentou os conceitos básicos para criar usuários e funções SQL e mostrou as etapas para definir e modificar privilégios de usuários e funções. Para informações mais detalhadas sobre cada um, consulte nossos guias do usuário e a documentação de referência.

​Usuário admin

​Autenticação sem senha

​Controle de acesso granular

​Teste os privilégios de administrador

​Usuários não administradores

​Preparação

​Criando um banco de dados, uma tabela e linhas de exemplo

​Criando funções

​Verificação

​Testando privilégios da função com usuário com restrição de colunas

​Testando privilégios de função com usuário com restrição por linha

​Modificando usuários e funções

​Solução de problemas

​Listar grants e funções de um usuário

​Listar funções no ClickHouse

​Exibir as políticas

​Veja como uma política foi definida e quais são os privilégios atuais

​Comandos de exemplo para gerenciar funções, políticas e usuários

​Remover um privilégio de uma função

​Excluir uma política

​Remover um usuário de uma função

​Excluir uma função

​Excluir um usuário

​Resumo

Usuário admin

Autenticação sem senha

Controle de acesso granular

Teste os privilégios de administrador

Usuários não administradores

Preparação

Criando um banco de dados, uma tabela e linhas de exemplo

Criando funções

Verificação

Testando privilégios da função com usuário com restrição de colunas

Testando privilégios de função com usuário com restrição por linha

Modificando usuários e funções

Solução de problemas

Listar grants e funções de um usuário

Listar funções no ClickHouse

Exibir as políticas

Veja como uma política foi definida e quais são os privilégios atuais

Comandos de exemplo para gerenciar funções, políticas e usuários

Remover um privilégio de uma função

Excluir uma política

Remover um usuário de uma função

Excluir uma função

Excluir um usuário

Resumo