<h1>Безопасность бизнес систем и баз данных</h1>
### Установка ClickHouse
```
sudo apt-get install -y apt-transport-https ca-certificates dirmngr
sudo apt-key adv --keyserver hkp://keyserver.ubuntu.com:80 --recv 8919F6BD2B48D754
echo "deb https://packages.clickhouse.com/deb stable main" | sudo tee \
/etc/apt/sources.list.d/clickhouse.list
sudo apt-get update
sudo apt-get install -y clickhouse-server clickhouse-client
sudo service clickhouse-server start
clickhouse-client
```
### Аудит
в данной субд есть 2 варианта ведения аудита
1 - system.query_log
2 - /var/log/clickhouse-server/clickhouse-server.log
Воспользуемся 1
| Id | Событие | Расположение лога | Сырое событие | Комментарий | Важность | Локализация |
| --- | ------------------------------- | ----------------- | ------------- | ----------- | -------- | ----------- |
| 1 | Удаление таблицы | system.query_log | Drop table | Нам интересно: event_time - дата и время события по UTC; query - выполненный запрос; user - пользователь БД, от имени которого выполнен запрос; tables - таблицы, которые подверглись удалению | low | Пользователь user удалил tables |
| 2 | Создание таблицы | system.query_log | create table | Нам интересно:event_time - дата и время события по UTC; query - выполненный запрос; user - пользователь БД, от имени которого выполнен запрос; tables - таблицы, которые были созданы | info | Пользователь user создал tables |
| 3 | Просмотр лога | system.query_log | log check | Нам интересно: event_time - дата и время события по UTC; query - выполненный запрос; user - пользователь БД, который выполнил запрос | low | Пользователь user посмотрел таблицу system.query_log |
| 4 | Просмотр версии clickhouse | system.query_log |select version();| Нам интересно:event_time - дата и время события по UTC; query - выполненный запрос; user - пользователь БД, который выполнил запрос; os_user - пользователь ОС | low | Пользователь user просматривал информацию о версии clickhouse|
| 5 | Просмотр всех бд |system.query_log|Show databases | Нам интересно:event_time - дата и время события по UTC; query - выполненный запрос; user - пользователь БД, который выполнил запрос; | medium | Пользователь user выполнил команду для просмотра всех баз данных |
| 6 | Просмотр колонки в таблице | system.query_log | select table | Нам интересно:event_time - дата и время события по UTC; query - выполненный запрос; user - пользователь БД, который выполнил запрос; | medium | Пользователь user посмотрел колонку columns таблицы tables |
События
```
Show databases;
type: QueryFinish
event_date: 2022-10-07
event_time: 2022-10-07 10:48:00
event_time_microseconds: 2022-10-07 10:48:00.115730
query_start_time: 2022-10-07 10:48:00
query_start_time_microseconds: 2022-10-07 10:48:00.115075
query_duration_ms: 0
read_rows: 4
read_bytes: 543
written_rows: 0
written_bytes: 0
result_rows: 4
result_bytes: 4224
memory_usage: 0
current_database: default
query: SHOW DATABASES;
formatted_query:
normalized_query_hash: 7519697629179491996
query_kind:
databases: []
tables: []
columns: []
projections: []
views: []
exception_code: 0
exception:
stack_trace:
is_initial_query: 1
user: default
query_id: 3045a2b0-8c9c-4b9b-a192-e49058ff5b40
address: ::ffff:127.0.0.1
port: 33640
initial_user: default
initial_query_id: 3045a2b0-8c9c-4b9b-a192-e49058ff5b40
initial_address: ::ffff:127.0.0.1
initial_port: 33640
initial_query_start_time: 2022-10-07 10:48:00
initial_query_start_time_microseconds: 2022-10-07 10:48:00.115075
interface: 1
is_secure: 0
os_user: ubuntu
client_hostname: ubuntu-virtual-machine
client_name: ClickHouse
client_revision: 54460
client_version_major: 22
client_version_minor: 9
client_version_patch: 3
http_method: 0
http_user_agent:
http_referer:
forwarded_for:
quota_key:
distributed_depth: 0
revision: 54466
log_comment:
thread_ids: [1318,2636]
ProfileEvents: {'Query':2,'SelectQuery':1,'NetworkSendElapsedMicroseconds':39,'NetworkSendBytes':1030,'SelectedRows':4,'SelectedBytes':543,'ContextLock':21,'RWLockAcquiredReadLocks':1,'RealTimeMicroseconds':818,'SystemTimeMicroseconds':752,'OSCPUWaitMicroseconds':10,'OSCPUVirtualTimeMicroseconds':751}
Settings: {}
used_aggregate_functions: []
used_aggregate_function_combinators: []
used_database_engines: []
used_data_type_families: []
used_dictionaries: []
used_formats: []
used_functions: []
used_storages: []
used_table_functions: []
transaction_id: (0,0,'00000000-0000-0000-0000-000000000000')
```
```
select version()
type: QueryFinish
event_date: 2022-10-07
event_time: 2022-10-07 10:43:29
event_time_microseconds: 2022-10-07 10:43:29.556366
query_start_time: 2022-10-07 10:43:29
query_start_time_microseconds: 2022-10-07 10:43:29.555481
query_duration_ms: 0
read_rows: 1
read_bytes: 1
written_rows: 0
written_bytes: 0
result_rows: 1
result_bytes: 4168
memory_usage: 9312
current_database: default
query: select version();
formatted_query:
normalized_query_hash: 9433313819049117352
query_kind: Select
databases: ['system']
tables: ['system.one']
columns: ['system.one.dummy']
projections: []
views: []
exception_code: 0
exception:
stack_trace:
is_initial_query: 1
user: default
query_id: bb34047a-49e4-4ce1-ad62-7985cc57c96b
address: ::ffff:127.0.0.1
port: 33640
initial_user: default
initial_query_id: bb34047a-49e4-4ce1-ad62-7985cc57c96b
initial_address: ::ffff:127.0.0.1
initial_port: 33640
initial_query_start_time: 2022-10-07 10:43:29
initial_query_start_time_microseconds: 2022-10-07 10:43:29.555481
interface: 1
is_secure: 0
os_user: ubuntu
client_hostname: ubuntu-virtual-machine
client_name: ClickHouse
client_revision: 54460
client_version_major: 22
client_version_minor: 9
client_version_patch: 3
http_method: 0
http_user_agent:
http_referer:
forwarded_for:
quota_key:
distributed_depth: 0
revision: 54466
log_comment:
thread_ids: [1318,2639]
ProfileEvents: {'Query':1,'SelectQuery':1,'NetworkSendElapsedMicroseconds':282,'NetworkSendBytes':856,'SelectedRows':1,'SelectedBytes':1,'ContextLock':10,'RWLockAcquiredReadLocks':1,'RealTimeMicroseconds':978,'UserTimeMicroseconds':716,'OSCPUWaitMicroseconds':258,'OSCPUVirtualTimeMicroseconds':715}
Settings: {}
used_aggregate_functions: []
used_aggregate_function_combinators: []
used_database_engines: []
used_data_type_families: []
used_dictionaries: []
used_formats: []
used_functions: ['version']
used_storages: []
used_table_functions: []
transaction_id: (0,0,'00000000-0000-0000-0000-000000000000')
```
```
Drop table
type: QueryFinish
event_date: 2022-10-07
event_time: 2022-10-07 10:37:37
event_time_microseconds: 2022-10-07 10:37:37.692657
query_start_time: 2022-10-07 10:37:37
query_start_time_microseconds: 2022-10-07 10:37:37.692429
query_duration_ms: 0
read_rows: 0
read_bytes: 0
written_rows: 0
written_bytes: 0
result_rows: 0
result_bytes: 0
memory_usage: 0
current_database: default
query: DROP TABLE t1;
formatted_query:
normalized_query_hash: 11460500957841125952
query_kind: Drop
databases: ['default']
tables: ['default.t1']
columns: []
projections: []
views: []
exception_code: 0
exception:
stack_trace:
is_initial_query: 1
user: default
query_id: 7b1e92b8-1a9f-48c7-a703-2e0f9fd091ac
address: ::ffff:127.0.0.1
port: 33640
initial_user: default
initial_query_id: 7b1e92b8-1a9f-48c7-a703-2e0f9fd091ac
initial_address: ::ffff:127.0.0.1
initial_port: 33640
initial_query_start_time: 2022-10-07 10:37:37
initial_query_start_time_microseconds: 2022-10-07 10:37:37.692429
interface: 1
is_secure: 0
os_user: ubuntu
client_hostname: ubuntu-virtual-machine
client_name: ClickHouse
client_revision: 54460
client_version_major: 22
client_version_minor: 9
client_version_patch: 3
http_method: 0
http_user_agent:
http_referer:
forwarded_for:
quota_key:
distributed_depth: 0
revision: 54466
log_comment:
thread_ids: [1318]
ProfileEvents: {'Query':1,'ContextLock':9,'RealTimeMicroseconds':262,'UserTimeMicroseconds':158,'OSCPUVirtualTimeMicroseconds':158}
Settings: {}
used_aggregate_functions: []
used_aggregate_function_combinators: []
used_database_engines: []
used_data_type_families: []
used_dictionaries: []
used_formats: []
used_functions: []
used_storages: []
used_table_functions: []
transaction_id: (0,0,'00000000-0000-0000-0000-000000000000')
```
```
select table
type: QueryFinish
event_date: 2022-10-07
event_time: 2022-10-07 10:36:12
event_time_microseconds: 2022-10-07 10:36:12.764634
query_start_time: 2022-10-07 10:36:12
query_start_time_microseconds: 2022-10-07 10:36:12.763757
query_duration_ms: 0
read_rows: 1
read_bytes: 10
written_rows: 0
written_bytes: 0
result_rows: 1
result_bytes: 4296
memory_usage: 9408
current_database: default
query: SELECT x, toTypeName(x) FROM t1;
formatted_query:
normalized_query_hash: 10189158771073254796
query_kind: Select
databases: ['default']
tables: ['default.t1']
columns: ['default.t1.x']
projections: []
views: []
exception_code: 0
exception:
stack_trace:
is_initial_query: 1
user: default
query_id: 6b694a57-9d1c-4ea4-b34e-01673b908c53
address: ::ffff:127.0.0.1
port: 33640
initial_user: default
initial_query_id: 6b694a57-9d1c-4ea4-b34e-01673b908c53
initial_address: ::ffff:127.0.0.1
initial_port: 33640
initial_query_start_time: 2022-10-07 10:36:12
initial_query_start_time_microseconds: 2022-10-07 10:36:12.763757
interface: 1
is_secure: 0
os_user: ubuntu
client_hostname: ubuntu-virtual-machine
client_name: ClickHouse
client_revision: 54460
client_version_major: 22
client_version_minor: 9
client_version_patch: 3
http_method: 0
http_user_agent:
http_referer:
forwarded_for:
quota_key:
distributed_depth: 0
revision: 54466
log_comment:
thread_ids: [1318,2638]
ProfileEvents: {'Query':1,'SelectQuery':1,'NetworkSendElapsedMicroseconds':312,'NetworkSendBytes':883,'SelectedRows':1,'SelectedBytes':10,'ContextLock':15,'RWLockAcquiredReadLocks':1,'RealTimeMicroseconds':976,'UserTimeMicroseconds':627,'OSCPUWaitMicroseconds':261,'OSCPUVirtualTimeMicroseconds':627}
Settings: {}
used_aggregate_functions: []
used_aggregate_function_combinators: []
used_database_engines: []
used_data_type_families: []
used_dictionaries: []
used_formats: []
used_functions: ['toTypeName']
used_storages: []
used_table_functions: []
transaction_id: (0,0,'00000000-0000-0000-0000-000000000000')
3 rows in set. Elapsed: 0.008 sec.
```
```
create table
type: QueryFinish
event_date: 2022-10-07
event_time: 2022-10-07 10:27:34
event_time_microseconds: 2022-10-07 10:27:34.741766
query_start_time: 2022-10-07 10:27:34
query_start_time_microseconds: 2022-10-07 10:27:34.739128
query_duration_ms: 2
read_rows: 1
read_bytes: 1
written_rows: 1
written_bytes: 10
result_rows: 1
result_bytes: 10
memory_usage: 5968
current_database: default
query: CREATE TABLE t1 (x String) ENGINE = Memory AS SELECT 1;
formatted_query:
normalized_query_hash: 9029032374217368033
query_kind: Create
databases: ['default','system']
tables: ['default.t1','system.one']
columns: ['system.one.dummy']
projections: []
views: []
exception_code: 0
exception:
stack_trace:
is_initial_query: 1
user: default
query_id: 772d57a0-4f7f-49b6-9979-32ea043d8d0e
address: ::ffff:127.0.0.1
port: 33640
initial_user: default
initial_query_id: 772d57a0-4f7f-49b6-9979-32ea043d8d0e
initial_address: ::ffff:127.0.0.1
initial_port: 33640
initial_query_start_time: 2022-10-07 10:27:34
initial_query_start_time_microseconds: 2022-10-07 10:27:34.739128
interface: 1
is_secure: 0
os_user: ubuntu
client_hostname: ubuntu-virtual-machine
client_name: ClickHouse
client_revision: 54460
client_version_major: 22
client_version_minor: 9
client_version_patch: 3
http_method: 0
http_user_agent:
http_referer:
forwarded_for:
quota_key:
distributed_depth: 0
revision: 54466
log_comment:
thread_ids: [1318,2638]
ProfileEvents: {'Query':1,'FileOpen':1,'WriteBufferFromFileDescriptorWrite':1,'WriteBufferFromFileDescriptorWriteBytes':94,'FileSync':1,'FileSyncElapsedMicroseconds':1393,'IOBufferAllocs':1,'IOBufferAllocBytes':109,'FunctionExecute':2,'DiskWriteElapsedMicroseconds':8,'NetworkSendElapsedMicroseconds':183,'NetworkSendBytes':1413,'InsertedRows':1,'InsertedBytes':10,'SelectedRows':1,'SelectedBytes':1,'ContextLock':21,'RWLockAcquiredReadLocks':3,'RealTimeMicroseconds':2747,'SystemTimeMicroseconds':1107,'SoftPageFaults':1,'OSCPUWaitMicroseconds':227,'OSCPUVirtualTimeMicroseconds':1141,'OSReadBytes':4096,'OSWriteBytes':8192,'OSWriteChars':1024}
Settings: {}
used_aggregate_functions: []
used_aggregate_function_combinators: []
used_database_engines: []
used_data_type_families: ['String']
used_dictionaries: []
used_formats: []
used_functions: ['replicate']
used_storages: ['Memory']
used_table_functions: []
transaction_id: (0,0,'00000000-0000-0000-0000-000000000000')
```
```
log check
type: QueryFinish
event_date: 2022-10-06
event_time: 2022-10-06 16:41:02
event_time_microseconds: 2022-10-06 16:41:02.661934
query_start_time: 2022-10-06 16:41:02
query_start_time_microseconds: 2022-10-06 16:41:02.580319
query_duration_ms: 81
read_rows: 21
read_bytes: 43027
written_rows: 0
written_bytes: 0
result_rows: 3
result_bytes: 23345
memory_usage: 180688
current_database: default
query: SELECT * FROM system.query_log WHERE type = 'QueryFinish' ORDER BY query_start_time DESC LIMIT 3 FORMAT Vertical;
formatted_query:
normalized_query_hash: 3491266004525281034
query_kind: Select
databases: ['system']
tables: ['system.query_log']
columns: ['system.query_log.ProfileEvents','system.query_log.Settings','system.query_log.address','system.query_log.client_hostname','system.query_log.client_name','system.query_log.client_revision','system.query_log.client_version_major','system.query_log.client_version_minor','system.query_log.client_version_patch','system.query_log.columns','system.query_log.current_database','system.query_log.databases','system.query_log.distributed_depth','system.query_log.event_date','system.query_log.event_time','system.query_log.event_time_microseconds','system.query_log.exception','system.query_log.exception_code','system.query_log.formatted_query','system.query_log.forwarded_for','system.query_log.http_method','system.query_log.http_referer','system.query_log.http_user_agent','system.query_log.initial_address','system.query_log.initial_port','system.query_log.initial_query_id','system.query_log.initial_query_start_time','system.query_log.initial_query_start_time_microseconds','system.query_log.initial_user','system.query_log.interface','system.query_log.is_initial_query','system.query_log.is_secure','system.query_log.log_comment','system.query_log.memory_usage','system.query_log.normalized_query_hash','system.query_log.os_user','system.query_log.port','system.query_log.projections','system.query_log.query','system.query_log.query_duration_ms','system.query_log.query_id','system.query_log.query_kind','system.query_log.query_start_time','system.query_log.query_start_time_microseconds','system.query_log.quota_key','system.query_log.read_bytes','system.query_log.read_rows','system.query_log.result_bytes','system.query_log.result_rows','system.query_log.revision','system.query_log.stack_trace','system.query_log.tables','system.query_log.thread_ids','system.query_log.transaction_id','system.query_log.type','system.query_log.used_aggregate_function_combinators','system.query_log.used_aggregate_functions','system.query_log.used_data_type_families','system.query_log.used_database_engines','system.query_log.used_dictionaries','system.query_log.used_formats','system.query_log.used_functions','system.query_log.used_storages','system.query_log.used_table_functions','system.query_log.user','system.query_log.views','system.query_log.written_bytes','system.query_log.written_rows']
projections: []
views: []
exception_code: 0
exception:
stack_trace:
is_initial_query: 1
user: default
query_id: 506b847c-fc7d-4ff6-881b-5d19b1a14b86
address: ::ffff:127.0.0.1
port: 53488
initial_user: default
initial_query_id: 506b847c-fc7d-4ff6-881b-5d19b1a14b86
initial_address: ::ffff:127.0.0.1
initial_port: 53488
initial_query_start_time: 2022-10-06 16:41:02
initial_query_start_time_microseconds: 2022-10-06 16:41:02.580319
interface: 1
is_secure: 0
os_user: ubuntu
client_hostname: ubuntu-virtual-machine
client_name: ClickHouse
client_revision: 54460
client_version_major: 22
client_version_minor: 9
client_version_patch: 3
http_method: 0
http_user_agent:
http_referer:
forwarded_for:
quota_key:
distributed_depth: 0
revision: 54466
log_comment:
thread_ids: [26262,27414]
ProfileEvents: {'Query':1,'SelectQuery':1,'FileOpen':2,'ReadBufferFromFileDescriptorReadBytes':12599,'ReadCompressedBytes':10407,'CompressedReadBufferBlocks':68,'CompressedReadBufferBytes':37866,'OpenedFileCacheHits':1,'OpenedFileCacheMisses':2,'IOBufferAllocs':5,'IOBufferAllocBytes':14116,'FunctionExecute':4,'MarkCacheHits':1,'MarkCacheMisses':1,'CreatedReadBufferOrdinary':3,'DiskReadElapsedMicroseconds':31,'NetworkSendElapsedMicroseconds':5461,'NetworkSendBytes':10022,'SelectedParts':1,'SelectedRanges':1,'SelectedMarks':1,'SelectedRows':21,'SelectedBytes':43027,'WaitMarksLoadMicroseconds':15537,'ContextLock':37,'RWLockAcquiredReadLocks':2,'RealTimeMicroseconds':98992,'UserTimeMicroseconds':52303,'SystemTimeMicroseconds':20549,'SoftPageFaults':4,'OSCPUWaitMicroseconds':10710,'OSCPUVirtualTimeMicroseconds':72851,'OSWriteBytes':4096,'OSReadChars':12288,'OSWriteChars':4096,'QueryProfilerRuns':1,'ThreadPoolReaderPageCacheHit':10,'ThreadPoolReaderPageCacheHitBytes':12599,'ThreadPoolReaderPageCacheHitElapsedMicroseconds':31}
Settings: {}
used_aggregate_functions: []
used_aggregate_function_combinators: []
used_database_engines: []
used_data_type_families: []
used_dictionaries: []
used_formats: []
used_functions: ['equals']
used_storages: []
used_table_functions: []
transaction_id: (0,0,'00000000-0000-0000-0000-000000000000')
3 rows in set. Elapsed: 0.054 sec.
```
Sign in with Wallet
Connect another wallet