Consultas habituales sobre gestión de acceso

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

Autogestionado

Si 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

Prefiera los roles de base de datos administrados en Cloud para administrar el acceso a la base de datos desde la consola. Los roles sql-console-role:<email> no son compatibles con las sesiones con JWT en la consola SQL.

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:[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]`;

​

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

1. Crear una base de datos de prueba

   CREATE DATABASE db1;
   
2. Crear una tabla

   CREATE TABLE db1.table1 (
      id UInt64,
      column1 String,
      column2 String
   )
   ENGINE MergeTree
   ORDER BY id;
   
3. 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');
   
4. 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     │
   └────┴─────────┴─────────┘
   
5. Crear column_user

   Cree un usuario estándar que se usará para demostrar cómo restringir el acceso a determinadas columnas:

   CREATE USER column_user IDENTIFIED BY 'password';
   
6. Crear row_user

   Cree un usuario estándar que se usará para demostrar cómo restringir el 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.

1. 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;
   
2. Establecer privilegios para permitir ver column1

   GRANT SELECT(id, column1) ON db1.table1 TO column1_users;
   
3. Añadir el usuario column_user al rol column1_users

   GRANT column1_users TO column_user;
   
4. 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;
   
5. Añadir row_user al rol A_rows_users

   GRANT A_rows_users TO row_user;
   
6. 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;
   
7. Establecer privilegios para la base de datos y la tabla

   GRANT SELECT(id, column1, column2) ON db1.table1 TO A_rows_users;
   
8. 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

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

   clickhouse-client --user clickhouse_admin --password password
   
2. 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     │
   └────┴─────────┴─────────┘
   
3. Inicie sesión en el cliente de ClickHouse con el usuario column_user

   clickhouse-client --user column_user --password password
   
4. 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

5. 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

1. Inicie sesión en el cliente de ClickHouse con row_user

   clickhouse-client --user row_user --password password
   
2. 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.

1. 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;
   
2. Elimine los privilegios previos del rol A_rows_users

   REVOKE SELECT(id, column1, column2) ON db1.table1 FROM A_rows_users;
   
3. Permita que el rol A_row_users solo haga SELECT de column1

   GRANT SELECT(id, column1) ON db1.table1 TO A_rows_users;
   
4. Inicie sesión en el cliente de ClickHouse con row_and_column_user

   clickhouse-client --user row_and_column_user --password password;
   
5. 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)
   
6. 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.