原始网页:https://www.cockroachlabs.com/docs/stable/sql-audit-logging.html#step-1-create-sample-tables


SQL审计日志向用户提供其应用系统中SQL查询的统计信息,能够满足用户记录针对包含个人身份信息(PII)的表所有查询的需求。

本小节将展示以下内容:

TIPS: 关于输出内容格式,用户也查看ALTER TABLE ... EXPERIMENTAL_AUDIT语句的相关介绍。
WARNING: 该功能仍处于实验阶段,对应的接口和输出在后续版本可能有所变更。

Step 1: 创建测试表

测试表包含:

CREATE TABLE customers (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name STRING NOT NULL,
    address STRING NOT NULL,
    national_id INT NOT NULL,
    telephone INT NOT NULL,
    email STRING UNIQUE NOT NULL
);
CREATE TABLE orders (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    product_id INT NOT NULL,
    delivery_status STRING check (delivery_status='processing' or delivery_status='in-transit' or delivery_status='delivered') NOT NULL,
    customer_id UUID NOT NULL REFERENCES customers (id)
);

Step 2: 开启customers表的SQL审计功能

执行ALTER TABLE语句修改相关配置:

ALTER TABLE customers EXPERIMENTAL_AUDIT SET READ WRITE;

NOTE: ALTER语句应用到多个表格,可用逗号隔开。

Step 3: 填充customers表数据

执行以下命令填充数据:

INSERT INTO customers (name, address, national_id, telephone, email) VALUES (
    'Pritchard M. Cleveland',
    '23 Crooked Lane, Garden City, NY USA 11536',
    778124477,
    12125552000,
    'pritchmeister@aol.com'
);

INSERT INTO customers (name, address, national_id, telephone, email) VALUES (
    'Vainglorious K. Snerptwiddle III',
    '44 Straight Narrows, Garden City, NY USA 11536',
    899127890,
    16465552000,
    'snerp@snerpy.net'
);

查询customers表:

SELECT * FROM customers;

+--------------------------------------+----------------------------------+------------------------------------------------+-------------+-------------+-----------------------+
|                  id                  |               name               |                    address                     | national_id |  telephone  |         email         |
+--------------------------------------+----------------------------------+------------------------------------------------+-------------+-------------+-----------------------+
| 4bd266fc-0b62-4cc4-8c51-6997675884cd | Vainglorious K. Snerptwiddle III | 44 Straight Narrows, Garden City, NY USA 11536 |   899127890 | 16465552000 | snerp@snerpy.net      |
| 988f54f0-b4a5-439b-a1f7-284358633250 | Pritchard M. Cleveland           | 23 Crooked Lane, Garden City, NY USA 11536     |   778124477 | 12125552000 | pritchmeister@aol.com |
+--------------------------------------+----------------------------------+------------------------------------------------+-------------+-------------+-----------------------+
(2 rows)

Step 4: 检查审计日志

默认情况下,CockroachDB将审计日志写入cockroach-sql-audit.log文件,并将该文件存放在标准日志文件夹里。用户也可以在执行cockroach start命令启动节点的时候使用--sql-audit-dirFlag指定审计日志存储的专用文件夹。同时类似于其他日志文件,审计日志的文件大小受配置项--log-file-max-size的约束。

启动审计功能以后,针对特定表的每条SQL语句将输出一条审计日志,样例如下:

I180321 20:54:21.381565 351 sql/exec_log.go:163  [n1,client=127.0.0.1:60754,user=root] 2 exec "psql" {"customers"[76]:READWRITE} "ALTER TABLE customers EXPERIMENTAL_AUDIT SET READ WRITE" {} 4.811 0 OK
I180321 20:54:26.315985 351 sql/exec_log.go:163  [n1,client=127.0.0.1:60754,user=root] 3 exec "psql" {"customers"[76]:READWRITE} "INSERT INTO customers(\"name\", address, national_id, telephone, email) VALUES ('Pritchard M. Cleveland', '23 Crooked Lane, Garden City, NY USA 11536', 778124477, 12125552000, 'pritchmeister@aol.com')" {} 6.319 1 OK
I180321 20:54:30.080592 351 sql/exec_log.go:163  [n1,client=127.0.0.1:60754,user=root] 4 exec "psql" {"customers"[76]:READWRITE} "INSERT INTO customers(\"name\", address, national_id, telephone, email) VALUES ('Vainglorious K. Snerptwiddle III', '44 Straight Narrows, Garden City, NY USA 11536', 899127890, 16465552000, 'snerp@snerpy.net')" {} 2.809 1 OK
I180321 20:54:39.377395 351 sql/exec_log.go:163  [n1,client=127.0.0.1:60754,user=root] 5 exec "psql" {"customers"[76]:READ} "SELECT * FROM customers" {} 1.236 2 OK

NOTE: 关于审计日志的输出内容格式,详见ALTER TABLE ... EXPERIMENTAL_AUDIT语句相关介绍。

Step 5: 填充orders表数据

orders表与customers表不同,没有任何个人身份信息,只包含产品ID和交易状态。

使用CREATE SEQUENCE语句为orders表填充测试数据:

CREATE SEQUENCE product_ids_asc START 1 INCREMENT 1;

#使用product_ids_asc序列生成数据
INSERT INTO orders (product_id, delivery_status, customer_id) VALUES (
    nextval('product_ids_asc'),
    'processing',
    (SELECT id FROM customers WHERE name ~ 'Cleve')
);

查询orders表:

SELECT * FROM orders ORDER BY product_id;

+--------------------------------------+------------+-----------------+--------------------------------------+
|                  id                  | product_id | delivery_status |             customer_id              |
+--------------------------------------+------------+-----------------+--------------------------------------+
| 6e85c390-3bbf-48da-9c2f-a73a0ab9c2ce |          1 | processing      | df053c68-fcb0-4a80-ad25-fef9d3b408ca |
| e93cdaee-d5eb-428c-bc1b-a7367f334f99 |          2 | processing      | df053c68-fcb0-4a80-ad25-fef9d3b408ca |
| f05a1b0f-5847-424d-b8c8-07faa6b6e46b |          3 | processing      | df053c68-fcb0-4a80-ad25-fef9d3b408ca |
| 86f619d6-9f18-4c84-8ead-68cd07a1ee37 |          4 | processing      | df053c68-fcb0-4a80-ad25-fef9d3b408ca |
| 882c0fc8-64e7-4fab-959d-a4ff74f170c0 |          5 | processing      | df053c68-fcb0-4a80-ad25-fef9d3b408ca |
+--------------------------------------+------------+-----------------+--------------------------------------+
(5 rows)

Step 6: 检查审计日志

Step 5当中针对orders表执行了查询customers表的INSERT语句,则在审计日志中将输出:

I180321 21:01:59.677273 351 sql/exec_log.go:163  [n1,client=127.0.0.1:60754,user=root] 7 exec "psql" {"customers"[76]:READ, "customers"[76]:READ} "INSERT INTO orders(product_id, delivery_status, customer_id) VALUES (nextval('product_ids_asc'), 'processing', (SELECT id FROM customers WHERE \"name\" ~ 'Cleve'))" {} 5.183 1 OK
I180321 21:04:07.497555 351 sql/exec_log.go:163  [n1,client=127.0.0.1:60754,user=root] 8 exec "psql" {"customers"[76]:READ, "customers"[76]:READ} "INSERT INTO orders(product_id, delivery_status, customer_id) VALUES (nextval('product_ids_asc'), 'processing', (SELECT id FROM customers WHERE \"name\" ~ 'Cleve'))" {} 5.219 1 OK
I180321 21:04:08.730379 351 sql/exec_log.go:163  [n1,client=127.0.0.1:60754,user=root] 9 exec "psql" {"customers"[76]:READ, "customers"[76]:READ} "INSERT INTO orders(product_id, delivery_status, customer_id) VALUES (nextval('product_ids_asc'), 'processing', (SELECT id FROM customers WHERE \"name\" ~ 'Cleve'))" {} 5.392 1 OK

NOTE: 关于审计日志的输出内容格式,详见ALTER TABLE ... EXPERIMENTAL_AUDIT语句相关介绍。