Database Hardening Tutorial: ClickHouse User Security and Password Encryption

Описание

We kick-off a new ClickHouse Hardening video series by examining how you can harden your ClickHouse user accounts by:
1. Setting secure passwords, and
2. Applying profile settings to user accounts through either the XML configuration files or SQL DCL(Data Control Language)

Note: We're using Altinity ClickHouse Stable Release version 20.8.12.2 on a Linux Ubuntu 20.04 computer. You can get the latest ClickHouse version here: https://docs.altinity.com/altinitystablerelease/.

Passwords in ClickHouse can be set as one of the following:
None: The worst option
Plain text: Not really better
Sha1 256 hash: Recommended
Double SHA1 hash: Applicable only when you allow connections from MySQL clients

So, how do you set a secure user password?
First off, generate a SHA256 password with the command sha256. Or depending on your operating system — sha256sum.

But remember: In ClickHouse user XML files, it’s not enough just to add in “Oh hey, use this secure password.” If the password value is set in the users.xml file, make sure you have a command to remove it.

You can now add users from the XML files or through SQL-driven commands. For the purpose of this tutorial, we're using SQL-driven commands.

ClickHouse.Tech recommends the following process when you first set up your account:
1. Use the default user and give them SQL-driven access control.
2. Create an administrative user.
3. Give that administrative user the grant to all actions. Use this account to create other user accounts with secure passwords.
4. Remove SQL-driven access control from the default user.

After creating an admin account and sha256 passwords, we're going to do the same thing via ClickHouse cluster through Kubernetes, which connects to an external Zookeeper for its synchronization.

Here, we recommend using SQL-based commands, so you don’t have many XML plaintext files scattered across your cluster, and you can use file encryption to protect the database even further.

Read the ClickHouse Hardening Guide in detail: https://docs.altinity.com/operationsguide/security/clickhouse-hardening-guide/

Chapters:
0:00 Introduction to ClickHouse User Security
1:08 Set secure passwords
5:15 Add users

#ClickHouseSecurity #UserSecurity #DatabaseSecurity #PasswordEncryption
-----------------
Watch other ClickHouse Security Hardening videos:
Database Hardening Tutorial: Set User Profiles and Restrict User Host Networks: https://youtu.be/QcyUqPg8IXI
Database Hardening Tutorial: Row Policies and User Quotas: https://youtu.be/dTxlUmL2Ma4
-----------------
Check out more ClickHouse resources here: https://altinity.com/resources/
Join Reddit community: https://www.reddit.com/r/Clickhouse/
-----------------
Learn more about Altinity!
Site: https://www.altinity.com
LinkedIn: https://www.linkedin.com/company/altinity/
Twitter: https://twitter.com/AltinityDB
Join us on Slack: https://bit.ly/34vnPLs