Consultas habituales sobre gestión de acceso

AutogestionadoSi trabaja con ClickHouse autogestionado, consulte usuarios y roles de SQL.

Este artículo presenta los conceptos básicos para definir usuarios y roles de SQL, y para aplicar esos privilegios y permisos a bases de datos, tablas, filas y columnas.

Usuario Admin

Los servicios de ClickHouse Cloud tienen un usuario Admin, default, que se crea al crear el servicio. La contraseña se proporciona durante la creación del servicio y los usuarios de ClickHouse Cloud que tienen el rol Admin pueden restablecerla. Cuando agregue usuarios SQL adicionales a su servicio de ClickHouse Cloud, necesitarán un username y una password de SQL. Si desea que tengan privilegios de nivel administrativo, asigne a los nuevos usuarios el rol default_role. Por ejemplo, para agregar el usuario clickhouse_admin:

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

GRANT default_role TO clickhouse_admin;

Al usar la consola SQL, tus sentencias SQL no se ejecutarán con el usuario default. En su lugar, se ejecutarán con un usuario llamado sql-console:${cloud_login_email}, donde cloud_login_email es el correo electrónico del usuario que está ejecutando la consulta en ese momento.Estos usuarios de consola SQL generados automáticamente tienen el rol default.

Autenticación sin contraseña

Hay dos roles disponibles para la consola SQL: sql_console_admin, con permisos idénticos a default_role, y sql_console_read_only, con permisos de solo lectura. A los usuarios Admin se les asigna el rol sql_console_admin de forma predeterminada, por lo que para ellos no cambia nada. Sin embargo, el rol sql_console_read_only permite conceder a los usuarios que no son Admin acceso de solo lectura o acceso completo a cualquier instancia. Un Admin debe configurar este acceso. Los roles pueden ajustarse mediante los comandos GRANT o REVOKE para adaptarse mejor a los requisitos específicos de cada instancia, y cualquier modificación que se haga en ellos se conservará.

Control de acceso granular

Esta funcionalidad de control de acceso también puede configurarse manualmente con granularidad a nivel de usuario. Antes de asignar a los usuarios los nuevos roles sql_console_*, deben crearse roles de base de datos específicos por usuario para la consola SQL que coincidan con el espacio de nombres sql-console-role:<email>. Por ejemplo:

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

Cuando se detecte un rol correspondiente, se asignará al usuario en lugar de los roles predefinidos. Esto permite configuraciones de control de acceso más complejas, como crear roles como sql_console_sa_role y sql_console_pm_role y concedérselos a usuarios específicos. Por ejemplo:

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`;

Probar los privilegios de administrador

Cierre sesión con el usuario default y vuelva a iniciarla con el usuario clickhouse_admin. Todo lo siguiente debería realizarse correctamente:

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;

Usuarios no administradores

Los usuarios deben tener los privilegios necesarios, y no todos deben ser administradores. En el resto de este documento se presentan escenarios de ejemplo y los roles necesarios.

Preparación

Cree estas tablas y usuarios que se usarán en los ejemplos.

Crear una base de datos, una tabla y filas de ejemplo

Crear una base de datos de prueba

CREATE DATABASE db1;

Crear una tabla

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

Poblar la tabla con filas de ejemplo

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

Verificar la tabla

Consulta

SELECT *
FROM db1.table1

Respuesta

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

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

Crear `column_user`

Cree un usuario estándar que se utilizará para demostrar la restricción de acceso a determinadas columnas:

CREATE USER column_user IDENTIFIED BY 'password';

Crear `row_user`

Cree un usuario estándar que se utilizará para demostrar la restricción de acceso a filas con determinados valores:

CREATE USER row_user IDENTIFIED BY 'password';

Creación de roles

Con este conjunto de ejemplos:

se crearán roles con distintos privilegios, como acceso por columnas y filas
se otorgarán privilegios a los roles
se asignarán usuarios a cada rol

Los roles se utilizan para definir grupos de usuarios con determinados privilegios, en lugar de administrar cada usuario por separado.

Crear un rol para restringir a los usuarios de este rol a que solo puedan ver `column1` en la base de datos `db1` y la tabla `table1`:

CREATE ROLE column1_users;

Establecer privilegios para permitir ver `column1`

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

Añadir el usuario `column_user` al rol `column1_users`

GRANT column1_users TO column_user;

Crear un rol para restringir a los usuarios de este rol a que solo puedan ver determinadas filas; en este caso, solo las filas que contengan `A` en `column1`

CREATE ROLE A_rows_users;

Añadir `row_user` al rol `A_rows_users`

GRANT A_rows_users TO row_user;

Crear una política para permitir ver solo las filas en las que `column1` tenga el valor `A`

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

Establecer privilegios para la base de datos y la tabla

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

Otorgar permisos explícitos para que otros roles sigan teniendo acceso a todas las filas

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

Al adjuntar una política a una tabla, el sistema aplicará esa política, y solo los usuarios y roles definidos podrán realizar operaciones sobre la tabla; a todos los demás se les denegará cualquier operación. Para evitar que la política restrictiva de filas se aplique a otros usuarios, se debe definir otra política que permita a otros usuarios y roles tener acceso normal u otros tipos de acceso.

Verificación

Comprobación de los privilegios del rol con un usuario restringido por columnas

Inicie sesión en el cliente de ClickHouse con el usuario `clickhouse_admin`

clickhouse-client --user clickhouse_admin --password password

Verifique el acceso a la base de datos, la tabla y todas las filas con el usuario administrador.

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

Inicie sesión en el cliente de ClickHouse con el usuario `column_user`

clickhouse-client --user column_user --password password

Pruebe `SELECT` con todas las columnas

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)

Se deniega el acceso porque se especificaron todas las columnas y el usuario solo tiene acceso a id y column1

Verifique la consulta `SELECT` usando solo las columnas especificadas y permitidas:

SELECT
    id,
    column1
FROM db1.table1

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

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

Probar los privilegios del rol con un usuario restringido por filas

Inicie sesión en el cliente de ClickHouse con `row_user`

clickhouse-client --user row_user --password password

Ver las filas disponibles

SELECT *
FROM db1.table1

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

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

Verifique que solo se devuelvan las dos filas anteriores; deben excluirse las filas con el valor B en column1.

Modificar usuarios y roles

A los usuarios se les pueden asignar varios roles para obtener la combinación de privilegios necesaria. Al usar varios roles, el sistema los combinará para determinar los privilegios; en la práctica, los permisos de los roles serán acumulativos. Por ejemplo, si role1 permite solo hacer SELECT en column1 y role2 permite hacer SELECT en column1 y column2, entonces el usuario tendrá acceso a ambas columnas.

Con la cuenta de administrador, cree un usuario nuevo para restringir tanto por fila como por columna con roles predeterminados

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

Elimine los privilegios previos del rol `A_rows_users`

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

Permita que el rol `A_row_users` solo haga SELECT de `column1`

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

Inicie sesión en el cliente de ClickHouse con `row_and_column_user`

clickhouse-client --user row_and_column_user --password password;

Pruebe con todas las columnas:

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)

Pruebe con las columnas permitidas:

SELECT
    id,
    column1
FROM db1.table1

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

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

Resolución de problemas

A veces, los privilegios se superponen o se combinan de forma que producen resultados inesperados; los siguientes comandos pueden usarse para acotar el problema con una cuenta de administrador

Listado de grants y roles de un usuario

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

Mostrar los roles en ClickHouse

SHOW ROLES

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

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

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

Ver cómo se definió una política y cuáles son los privilegios actuales

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 ejemplo para gestionar roles, políticas y usuarios

Los siguientes comandos se pueden usar para:

eliminar privilegios
eliminar políticas
desvincular usuarios de roles
eliminar usuarios y roles

Ejecuta estos comandos con un usuario administrador o con el usuario default

Eliminar un privilegio de un rol

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

Eliminar una política

DROP ROW POLICY A_row_filter ON db1.table1;

Quitar un usuario de un rol

REVOKE A_rows_users FROM row_user;

Eliminar un rol

DROP ROLE A_rows_users;

Eliminar un usuario

DROP USER row_user;

Resumen

En este artículo se explicaron los conceptos básicos para crear usuarios y roles de SQL, y se indicaron los pasos para establecer y modificar los privilegios de usuarios y roles. Para obtener información más detallada sobre cada uno de ellos, consulte nuestras guías de usuario y la documentación de referencia.

​Usuario Admin

​Autenticación sin contraseña

​Control de acceso granular

​Probar los privilegios de administrador

​Usuarios no administradores

​Preparación

​Crear una base de datos, una tabla y filas de ejemplo

Crear una base de datos de prueba

Crear una tabla

Poblar la tabla con filas de ejemplo

Verificar la tabla

Crear column_user

Crear row_user

​Creación de roles

Crear un rol para restringir a los usuarios de este rol a que solo puedan ver column1 en la base de datos db1 y la tabla table1:

Establecer privilegios para permitir ver column1

Añadir el usuario column_user al rol column1_users

Crear un rol para restringir a los usuarios de este rol a que solo puedan ver determinadas filas; en este caso, solo las filas que contengan A en column1

Añadir row_user al rol A_rows_users

Crear una política para permitir ver solo las filas en las que column1 tenga el valor A

Establecer privilegios para la base de datos y la tabla

Otorgar permisos explícitos para que otros roles sigan teniendo acceso a todas las filas

​Verificación

​Comprobación de los privilegios del rol con un usuario restringido por columnas

Inicie sesión en el cliente de ClickHouse con el usuario clickhouse_admin

Verifique el acceso a la base de datos, la tabla y todas las filas con el usuario administrador.

Inicie sesión en el cliente de ClickHouse con el usuario column_user

Pruebe SELECT con todas las columnas

Verifique la consulta SELECT usando solo las columnas especificadas y permitidas:

​Probar los privilegios del rol con un usuario restringido por filas

Inicie sesión en el cliente de ClickHouse con row_user

Ver las filas disponibles

​Modificar usuarios y roles

Con la cuenta de administrador, cree un usuario nuevo para restringir tanto por fila como por columna con roles predeterminados

Elimine los privilegios previos del rol A_rows_users

Permita que el rol A_row_users solo haga SELECT de column1

Inicie sesión en el cliente de ClickHouse con row_and_column_user

Pruebe con todas las columnas:

Pruebe con las columnas permitidas:

​Resolución de problemas

​Listado de grants y roles de un usuario

​Mostrar los roles en ClickHouse

​Mostrar las políticas

​Ver cómo se definió una política y cuáles son los privilegios actuales

​Comandos de ejemplo para gestionar roles, políticas y usuarios

​Eliminar un privilegio de un rol

​Eliminar una política

​Quitar un usuario de un rol

​Eliminar un rol

​Eliminar un usuario

​Resumen

Usuario Admin

Autenticación sin contraseña

Control de acceso granular

Probar los privilegios de administrador

Usuarios no administradores

Preparación

Crear una base de datos, una tabla y filas de ejemplo

Crear `column_user`

Crear `row_user`

Creación de roles

Crear un rol para restringir a los usuarios de este rol a que solo puedan ver `column1` en la base de datos `db1` y la tabla `table1`:

Establecer privilegios para permitir ver `column1`

Añadir el usuario `column_user` al rol `column1_users`

Crear un rol para restringir a los usuarios de este rol a que solo puedan ver determinadas filas; en este caso, solo las filas que contengan `A` en `column1`

Añadir `row_user` al rol `A_rows_users`

Crear una política para permitir ver solo las filas en las que `column1` tenga el valor `A`

Verificación

Comprobación de los privilegios del rol con un usuario restringido por columnas

Inicie sesión en el cliente de ClickHouse con el usuario `clickhouse_admin`

Inicie sesión en el cliente de ClickHouse con el usuario `column_user`

Pruebe `SELECT` con todas las columnas

Verifique la consulta `SELECT` usando solo las columnas especificadas y permitidas:

Probar los privilegios del rol con un usuario restringido por filas

Inicie sesión en el cliente de ClickHouse con `row_user`

Modificar usuarios y roles

Elimine los privilegios previos del rol `A_rows_users`

Permita que el rol `A_row_users` solo haga SELECT de `column1`

Inicie sesión en el cliente de ClickHouse con `row_and_column_user`

Resolución de problemas

Listado de grants y roles de un usuario

Mostrar los roles en ClickHouse

Mostrar las políticas

Ver cómo se definió una política y cuáles son los privilegios actuales

Comandos de ejemplo para gestionar roles, políticas y usuarios

Eliminar un privilegio de un rol

Eliminar una política

Quitar un usuario de un rol

Eliminar un rol

Eliminar un usuario

Resumen