OceanBase 数据库 Oracle 模式下的安全审计-OceanBase数据库使用指南

本文介绍 OceanBase 数据库 Oracle 模式安全审计的简单应用实例，更多信息详见安全审计概述。

安全审计概述

目前 OceanBase 数据库仅 Oracle 模式支持安全审计功能。

检查安全审计状态

通过 SYS 用户登录到 Oracle 租户。

#obclient -h${DB_HOST} -P2883 -uSYS@oboracle#obcluster -A -p${DB_PASSWORD}
Welcome to the OceanBase.  Commands end with ; or \g.
Your OceanBase connection id is 3221584773
Server version: OceanBase 4.2.2.0 (r100000032024010510-75c47d4be18a399e13c5309de1a81da5caf4e7c0) (Built Jan  5 2024 10:17:55)

Copyright (c) 2000, 2018, OceanBase and/or its affiliates. All rights reserved.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

查看用户 ORAAUDITOR 的账户状态。

obclient [SYS]> SELECT username, account_status FROM dba_users WHERE username = 'ORAAUDITOR';

输出结果如下：

+------------+----------------+
| USERNAME   | ACCOUNT_STATUS |
+------------+----------------+
| ORAAUDITOR | LOCKED         |
+------------+----------------+
1 row in set (0.051 sec)

查看租户所在所有 OBServer 节点上的配置项 audit_trail 信息。

obclient [SYS]> SELECT
     zone,
     scope,
     tenant_id,
     name,
     value,
     section,
     edit_level
     FROM gv$ob_parameters
     WHERE name = 'audit_trail';

输出结果如下：

+-------+--------+-----------+-------------+-------+----------+-------------------+
| ZONE  | SCOPE  | TENANT_ID | NAME        | VALUE | SECTION  | EDIT_LEVEL        |
+-------+--------+-----------+-------------+-------+----------+-------------------+
| zone1 | TENANT |      1004 | audit_trail | None  | OBSERVER | DYNAMIC_EFFECTIVE |
+-------+--------+-----------+-------------+-------+----------+-------------------+
1 row in set (0.044 sec)

说明

ORAAUDITOR 用户默认处于锁定状态，在使用审计前需要启用该用户。
audit_trail 默认为 None，不启用数据库审核。

开启安全审计

通过 SYS 用户登录到 Oracle 租户。

#obclient -h${DB_HOST} -P2883 -uSYS@oboracle#obcluster -A -p${DB_PASSWORD}
Welcome to the OceanBase.  Commands end with ; or \g.
Your OceanBase connection id is 3221584773
Server version: OceanBase 4.2.2.0 (r100000032024010510-75c47d4be18a399e13c5309de1a81da5caf4e7c0) (Built Jan  5 2024 10:17:55)

Copyright (c) 2000, 2018, OceanBase and/or its affiliates. All rights reserved.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

解锁 ORAAUDITOR 用户。

obclient [SYS]> ALTER USER ORAAUDITOR ACCOUNT UNLOCK;
Query OK, 0 rows affected (0.061 sec)

修改 ORAAUDITOR 用户的登录密码。

obclient [SYS]> ALTER USER ORAAUDITOR IDENTIFIED BY xxx;
Query OK, 0 rows affected (0.059 sec)

授予 ORAAUDITOR 用户，创建会话和访问所有视图的权限。

obclient [SYS]> GRANT CREATE SESSION, SELECT ANY DICTIONARY TO ORAAUDITOR;
Query OK, 0 rows affected (0.060 sec)

开启安全审计功能。
```
obclient [SYS]> ALTER SYSTEM SET audit_trail = 'DB,EXTENDED';
Query OK, 0 rows affected (0.041 sec)
```
通过配置项 audit_trail 开启审计功能，执行成功后立即生效。该配置项的取值如下：
说明
- NONE：关闭审计。
- OS：审计记录写本地文件。
- DB：审计记录写内部表。
- DB,EXTENDED：审计记录写内部表且记录包含执行的 SQL 语句。
管理员用户拥有最高权限，能执行很多操作，所以针对管理员有单独配置审计。租户配置项 audit_sys_operations 决定是否对管理员的操作进行审计记录。

确定用户 ORAAUDITOR 的账户状态是否已开通。

obclient [SYS]> SELECT username, account_status FROM dba_users WHERE username = 'ORAAUDITOR';

输出结果如下：

+------------+----------------+
| USERNAME   | ACCOUNT_STATUS |
+------------+----------------+
| ORAAUDITOR | OPEN           |
+------------+----------------+
1 row in set (0.008 sec)

查看租户所在所有 OBServer 节点上的配置项 audit_trail 信息。

obclient [SYS]> SELECT
     zone,
     scope,
     tenant_id,
     name,
     value,
     section,
     edit_level
     FROM gv$ob_parameters
     WHERE name = 'audit_trail';

输出结果如下：

+-------+--------+-----------+-------------+-------------+----------+-------------------+
| ZONE  | SCOPE  | TENANT_ID | NAME        | VALUE       | SECTION  | EDIT_LEVEL        |
+-------+--------+-----------+-------------+-------------+----------+-------------------+
| zone1 | TENANT |      1004 | audit_trail | DB,EXTENDED | OBSERVER | DYNAMIC_EFFECTIVE |
+-------+--------+-----------+-------------+-------------+----------+-------------------+
1 row in set (0.035 sec)

设置审计规则

通过内置的审计管理用户 ORAAUDITOR 可以配置审计规则，包括：

语句审计：对特定的操作进行审计，不指定具体对象，可以指定对具体用户或对所有用户生效。
对象审计：对特定的对象上执行的特定操作进行审计，可以指定对具体用户或对所有用户生效。

审计规则是通过 DDL 语句 audit/noaudit 语句来配置，审计规则也是一种 schema 对象。

审计用户登录

通过 SYS 用户登录到 Oracle 租户。

#obclient -h${DB_HOST} -P2883 -uSYS@oboracle#obcluster -A -p${DB_PASSWORD}
Welcome to the OceanBase.  Commands end with ; or \g.
Your OceanBase connection id is 3221584773
Server version: OceanBase 4.2.2.0 (r100000032024010510-75c47d4be18a399e13c5309de1a81da5caf4e7c0) (Built Jan  5 2024 10:17:55)

Copyright (c) 2000, 2018, OceanBase and/or its affiliates. All rights reserved.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

创建 ALVIN 和 TOM 用户。

obclient [SYS]> CREATE USER ALVIN identified by "xxx";
Query OK, 0 rows affected (0.064 sec)

obclient [SYS]> CREATE USER TOM identified by "xxxxx";
Query OK, 0 rows affected (0.051 sec)

设置 ALVIN 和 TOM 用户的权限。

obclient [SYS]> GRANT dba TO ALVIN;
Query OK, 0 rows affected (0.048 sec)

obclient [SYS]> GRANT dba TO TOM;
Query OK, 0 rows affected (0.060 sec)

通过 ALVIN 用户登录到 Oracle 租户。

#obclient -h${DB_HOST} -P2883 -uALVIN@oboracle#obcluster -A -p${DB_PASSWORD}
Welcome to the OceanBase.  Commands end with ; or \g.
Your OceanBase connection id is 3221584773
Server version: OceanBase 4.2.2.0 (r100000032024010510-75c47d4be18a399e13c5309de1a81da5caf4e7c0) (Built Jan  5 2024 10:17:55)

Copyright (c) 2000, 2018, OceanBase and/or its affiliates. All rights reserved.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

在 ALVIN 用户中创建 test 表。

obclient [ALVIN]> CREATE TABLE test(n1 INT, d1 DATE DEFAULT sysdate);
Query OK, 0 rows affected (0.142 sec)

通过 ORAAUDITOR 用户登录到 Oracle 租户。

#obclient -h${DB_HOST} -P2883 -uORAAUDITOR@oboracle#obcluster -A -p${DB_PASSWORD}
Welcome to the OceanBase.  Commands end with ; or \g.
Your OceanBase connection id is 3221584773
Server version: OceanBase 4.2.2.0 (r100000032024010510-75c47d4be18a399e13c5309de1a81da5caf4e7c0) (Built Jan  5 2024 10:17:55)

Copyright (c) 2000, 2018, OceanBase and/or its affiliates. All rights reserved.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

审计 ALVIN 用户的登录。

obclient [ORAAUDITOR]> AUDIT SESSION BY ALVIN;
Query OK, 0 rows affected (0.071 sec)

通过 ALVIN 用户登录到 Oracle 租户并退出登录。

#obclient -h${DB_HOST} -P2883 -uALVIN@oboracle#obcluster -A -p${DB_PASSWORD}
Welcome to the OceanBase.  Commands end with ; or \g.
Your OceanBase connection id is 3221584773
Server version: OceanBase 4.2.2.0 (r100000032024010510-75c47d4be18a399e13c5309de1a81da5caf4e7c0) (Built Jan  5 2024 10:17:55)

Copyright (c) 2000, 2018, OceanBase and/or its affiliates. All rights reserved.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

obclient [ALVIN]> exit
Bye

使用有权限查询 dba_audit_trail 的用户 (e.g. ORAAUDITOR) 都可登录查询审计记录。

在会话级别设置 nls_date_format 日期格式。

obclient [ORAAUDITOR]> ALTER SESSION SET nls_date_format='YYYY-MM-DD HH24:MI:SS';
Query OK, 0 rows affected (0.040 sec)

查询审计记录。

obclient [ORAAUDITOR]> SELECT
    sessionid,
    username,
    action_name,
    TIMESTAMP,
    returncode,sql_text FROM (SELECT * FROM dba_audit_trail ORDER BY timestamp DESC) WHERE ROWNUM <= 10;

输出结果如下：

+------------+----------+-------------+---------------------+------------+------------+
| SESSIONID  | USERNAME | ACTION_NAME | TIMESTAMP           | RETURNCODE | SQL_TEXT   |
+------------+----------+-------------+---------------------+------------+------------+
| 3221562787 | ALVIN    | LOGOFF      | 2024-04-09 16:04:16 |          0 | DISCONNECT |
| 3221562787 | ALVIN    | LOGON       | 2024-04-09 16:04:09 |          0 | CONNECT    |
+------------+----------+-------------+---------------------+------------+------------+
2 rows in set (0.019 sec)

查看审计规则

使用有权限查询 dba_stmt_audit_opts ，dba_obj_audit_opts 的用户 (e.g. ORAAUDITOR) 登录并查看审计规则。

查看语句级别审计的详细配置。

obclient [ORAAUDITOR]> SELECT * FROM dba_stmt_audit_opts;

输出结果如下：

+-----------+------------+--------------+-----------+-----------+
| USER_NAME | PROXY_NAME | AUDIT_OPTION | SUCCESS   | FAILURE   |
+-----------+------------+--------------+-----------+-----------+
| ALVIN     | NULL       | SESSION      | BY ACCESS | BY ACCESS |
| NULL      | NULL       | SESSION      | BY ACCESS | BY ACCESS |
+-----------+------------+--------------+-----------+-----------+
2 rows in set (0.057 sec)

查看对象级别审计的详细配置。

obclient [ORAAUDITOR]> SELECT
      owner,
      object_name,
      object_type,
      ins,
      del,
      upd FROM dba_obj_audit_opts;

输出结果如下：

+-------+-------------+-------------+------+------+------+
| OWNER | OBJECT_NAME | OBJECT_TYPE | INS  | DEL  | UPD  |
+-------+-------------+-------------+------+------+------+
| ALVIN | TEST        | TABLE       | A/A  | A/A  | A/A  |
+-------+-------------+-------------+------+------+------+
1 row in set (0.107 sec)

审计对象的操作

以审计所有用户对 ALVIN.test 表的操作为例进行审计对象的操作。

通过 ORAAUDITOR 用户登录到 Oracle 租户。

#obclient -h${DB_HOST} -P2883 -uORAAUDITOR@oboracle#obcluster -A -p${DB_PASSWORD}
Welcome to the OceanBase.  Commands end with ; or \g.
Your OceanBase connection id is 3221584773
Server version: OceanBase 4.2.2.0 (r100000032024010510-75c47d4be18a399e13c5309de1a81da5caf4e7c0) (Built Jan  5 2024 10:17:55)

Copyright (c) 2000, 2018, OceanBase and/or its affiliates. All rights reserved.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

对用户 ALVIN 的表 test 的所有 INSERT、UPDATE 和 DELETE 操作进行审计。

obclient [ORAAUDITOR]> AUDIT INSERT,UPDATE,DELETE ON ALVIN.test;
Query OK, 0 rows affected (0.103 sec)

通过 ALVIN 用户登录到 Oracle 租户。

#obclient -h${DB_HOST} -P2883 -uALVIN@oboracle#obcluster -A -p${DB_PASSWORD}
Welcome to the OceanBase.  Commands end with ; or \g.
Your OceanBase connection id is 3221584773
Server version: OceanBase 4.2.2.0 (r100000032024010510-75c47d4be18a399e13c5309de1a81da5caf4e7c0) (Built Jan  5 2024 10:17:55)

Copyright (c) 2000, 2018, OceanBase and/or its affiliates. All rights reserved.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

在会话级别设置 nls_date_format 日期格式。

obclient [ALVIN]> ALTER SESSION SET nls_date_format='YYYY-MM-DD HH24:MI:SS';
Query OK, 0 rows affected (0.030 sec)

查询 test 表的内容。

obclient [ALVIN]> SELECT * FROM test;
Empty set (0.033 sec)

向 test 表插入数据。

obclient [ALVIN]> INSERT INTO test(n1) VALUES(2);
Query OK, 1 row affected (0.038 sec)

提交当前事务。

obclient [ALVIN]> COMMIT;
Query OK, 0 rows affected (0.030 sec)

再次查询 test 表的内容。

obclient [ALVIN]> SELECT * FROM test;

输出结果如下：

+------+---------------------+
| N1   | D1                  |
+------+---------------------+
|    2 | 2024-04-09 16:10:17 |
+------+---------------------+
1 row in set (0.030 sec)

再次向 test 表插入数据。

obclient [ALVIN]> INSERT INTO test(n1) VALUES(3);
Query OK, 1 row affected (0.031 sec)

第三次查询 test 表的内容。

obclient [ALVIN]> SELECT * FROM test;

输出结果如下：

+------+---------------------+
| N1   | D1                  |
+------+---------------------+
|    2 | 2024-04-09 16:10:17 |
|    3 | 2024-04-09 16:10:46 |
+------+---------------------+
2 rows in set (0.030 sec)

回滚当前事务。

obclient [ALVIN]> ROLLBACK;
Query OK, 0 rows affected (0.030 sec)

第四次查询 test 表的内容。

obclient [ALVIN]>  SELECT * FROM test;

输出结果如下：

+------+---------------------+
| N1   | D1                  |
+------+---------------------+
|    2 | 2024-04-09 16:10:17 |
+------+---------------------+
1 row in set (0.030 sec)

删除 test 表的内容。

obclient [ALVIN]> DELETE FROM test WHERE n1=2;
Query OK, 1 row affected (0.033 sec)

回滚当前事务。

obclient [ALVIN]> ROLLBACK;
Query OK, 0 rows affected (0.030 sec)

通过 ORAAUDITOR 用户登录到 Oracle 租户

#obclient -h${DB_HOST} -P2883 -uORAAUDITOR@oboracle#obcluster -A -p${DB_PASSWORD}
Welcome to the OceanBase.  Commands end with ; or \g.
Your OceanBase connection id is 3221584773
Server version: OceanBase 4.2.2.0 (r100000032024010510-75c47d4be18a399e13c5309de1a81da5caf4e7c0) (Built Jan  5 2024 10:17:55)

Copyright (c) 2000, 2018, OceanBase and/or its affiliates. All rights reserved.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

使用有权限查询 dba_audit_trail 的用户 (e.g. ORAAUDITOR) 都可登录查询审计记录。

在会话级别设置 nls_date_format 日期格式。

obclient [ORAAUDITOR]> ALTER SESSION SET nls_date_format='YYYY-MM-DD HH24:MI:SS';
Query OK, 0 rows affected (0.040 sec)

查询审计记录。

obclient [ORAAUDITOR]> SELECT
    sessionid,
    username,
    action_name,
    TIMESTAMP,
    returncode,sql_text FROM (SELECT * FROM dba_audit_trail ORDER BY timestamp DESC) WHERE ROWNUM <= 10;

输出结果如下：

+------------+------------+-------------+---------------------+------------+--------------------------------+
| SESSIONID  | USERNAME   | ACTION_NAME | TIMESTAMP           | RETURNCODE | SQL_TEXT                       |
+------------+------------+-------------+---------------------+------------+--------------------------------+
| 3221607315 | ALVIN      | LOGOFF      | 2024-04-09 16:12:04 |          0 | DISCONNECT                     |
| 3221607315 | ALVIN      | DELETE      | 2024-04-09 16:11:43 |          0 | DELETE FROM test WHERE n1=2    |
| 3221607315 | ALVIN      | INSERT      | 2024-04-09 16:10:46 |          0 | INSERT INTO test(n1) VALUES(3) |
| 3221607315 | ALVIN      | INSERT      | 2024-04-09 16:10:17 |          0 | INSERT INTO test(n1) VALUES(2) |
| 3221607315 | ALVIN      | LOGON       | 2024-04-09 16:09:49 |          0 | CONNECT                        |
| 3221594946 | ALVIN      | LOGOFF      | 2024-04-09 16:08:42 |          0 | DISCONNECT                     |
| 3221594946 | ALVIN      | LOGON       | 2024-04-09 16:08:03 |          0 | CONNECT                        |
+------------+------------+-------------+---------------------+------------+--------------------------------+
7 rows in set (0.028 sec)

通过 TOM 用户登录到 Oracle 租户。

#obclient -h${DB_HOST} -P2883 -uTOM@oboracle#obcluster -A -p${DB_PASSWORD}
Welcome to the OceanBase.  Commands end with ; or \g.
Your OceanBase connection id is 3221584773
Server version: OceanBase 4.2.2.0 (r100000032024010510-75c47d4be18a399e13c5309de1a81da5caf4e7c0) (Built Jan  5 2024 10:17:55)

Copyright (c) 2000, 2018, OceanBase and/or its affiliates. All rights reserved.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

在会话级别设置 nls_date_format 日期格式。\

obclient [TOM]> ALTER SESSION SET nls_date_format='YYYY-MM-DD HH24:MI:SS';
Query OK, 0 rows affected (0.039 sec)

在 TOM 用户下，查询 ALVIN 用户中的 test 表的内容。

obclient [TOM]>  SELECT * FROM ALVIN.test;

输出结果如下：

+------+---------------------+
 N1   | D1                  |
+------+---------------------+
|    2 | 2024-04-09 16:10:17 |
+------+---------------------+
1 row in set (0.041 sec)

在 TOM 用户下，向 ALVIN 用户中的 test 表插入数据。

obclient [TOM]> INSERT INTO alvin.test(n1) VALUES(9);
Query OK, 1 row affected (0.041 sec)

在 TOM 用户下，提交当前事务。

obclient [TOM]> commit;
Query OK, 0 rows affected (0.040 sec)

退出 TOM 用户。
```
obclient [TOM]> exit
Bye
```

通过 ORAAUDITOR 用户登录到 Oracle 租户

#obclient -h${DB_HOST} -P2883 -uORAAUDITOR@oboracle#obcluster -A -p${DB_PASSWORD}
Welcome to the OceanBase.  Commands end with ; or \g.
Your OceanBase connection id is 3221584773
Server version: OceanBase 4.2.2.0 (r100000032024010510-75c47d4be18a399e13c5309de1a81da5caf4e7c0) (Built Jan  5 2024 10:17:55)

Copyright (c) 2000, 2018, OceanBase and/or its affiliates. All rights reserved.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

使用有权限查询 dba_audit_trail 的用户 (e.g. ORAAUDITOR) 登录，可以查询到 TOM 用户对 ALVIN 用户下的 test 表的操作记录。

在会话级别设置 nls_date_format 日期格式。

obclient [ORAAUDITOR]> ALTER SESSION SET nls_date_format='YYYY-MM-DD HH24:MI:SS';
Query OK, 0 rows affected (0.038 sec)

查询审计记录。

obclient [ORAAUDITOR]> SELECT
    sessionid,
    username,
    action_name,
    TIMESTAMP,
    returncode,sql_text FROM (SELECT * FROM dba_audit_trail ORDER BY timestamp DESC) WHERE ROWNUM <= 10;

输出结果如下：

+------------+------------+-------------+---------------------+------------+--------------------------------------+
| SESSIONID  | USERNAME   | ACTION_NAME | TIMESTAMP           | RETURNCODE | SQL_TEXT                             |
+------------+------------+-------------+---------------------+------------+--------------------------------------+
| 3221654755 | TOM        | LOGOFF      | 2024-04-09 16:17:24 |          0 | DISCONNECT                           |
| 3221654755 | TOM        | INSERT      | 2024-04-09 16:17:03 |          0 | INSERT INTO alvin.test(n1) VALUES(9) |
| 3221654755 | TOM        | LOGON       | 2024-04-09 16:16:34 |          0 | CONNECT                              |
| 3221607315 | ALVIN      | LOGOFF      | 2024-04-09 16:12:04 |          0 | DISCONNECT                           |
| 3221607315 | ALVIN      | DELETE      | 2024-04-09 16:11:43 |          0 | DELETE FROM test WHERE n1=2          |
+------------+------------+-------------+---------------------+------------+--------------------------------------+
5 rows in set (0.039 sec)

适用版本

OceanBase 数据库 V4.2.x 版本。