oracle 如何在没有性能和可扩展性问题的情况下审计数据库活动？

How to Audit Database Activity without Performance and Scalability Issues?

提问by amitm

I have a need to do auditing all database activity regardless of whether it came from application or someone issuing some sql via other means. So the auditing must be done at the database level. The database in question is Oracle. I looked at doing it via Triggers and also via something called Fine Grained Auditing that Oracle provides. In both cases, we turned on auditing on specific tables and specific columns. However, we found that Performance really sucks when we use either of these methods.

我需要审计所有数据库活动，无论它是来自应用程序还是通过其他方式发布一些 sql 的人。所以审计必须在数据库级别进行。有问题的数据库是 Oracle。我查看了通过触发器以及 Oracle 提供的称为细粒度审计的东西来完成它。在这两种情况下，我们都开启了对特定表和特定列的审计。然而，我们发现当我们使用这两种方法中的任何一种时，性能真的很糟糕。

Since auditing is an absolute must due to regulations placed around data privacy, I am wondering what is best way to do this without significant performance degradations. If someone has Oracle specific experience with this, it will be helpful but if not just general practices around database activity auditing will be okay as well.

由于围绕数据隐私的法规，审计是绝对必须的，我想知道在不显着降低性能的情况下执行此操作的最佳方法是什么。如果有人在这方面有 Oracle 特定的经验，这会很有帮助，但如果不仅仅是围绕数据库活动审计的一般实践也可以。

采纳答案by Colin Coghill

I'm not sure if it's a mature enough approach for a production system, but I had quite a lot of success with monitoring database traffic using a network traffic sniffer.

我不确定对于生产系统来说这是否是一种足够成熟的方法，但是我在使用网络流量嗅探器监控数据库流量方面取得了很大的成功。

Send the raw data between the application and database off to another machine and decode and analyse it there.

将应用程序和数据库之间的原始数据发送到另一台机器并在那里解码和分析。

I used PostgreSQL, and decoding the traffic and turning it into a stream of database operations that could be logged was relatively straightforward. I imagine it'd work on any database where the packet format is documented though.

我使用 PostgreSQL，解码流量并将其转换为可以记录的数据库操作流相对简单。我想它可以在任何记录了数据包格式的数据库上工作。

The main point was that it put no extra load on the database itself.

主要的一点是它不会给数据库本身带来额外的负载。

Also, it was passive monitoring, it recorded all activity, but couldn't block any operations, so might not be quite what you're looking for.

此外，它是被动监控，它记录了所有活动，但无法阻止任何操作，因此可能不是您想要的。

回答by Opus

There is no need to "roll your own". Just turn on auditing:

没有必要“自己动手”。只需打开审计：

1. Set the database parameter AUDIT_TRAIL = DB.
2. Start the instance.
3. Login with SQLPlus.
4. Enter the statement

   audit all;

   This turns on auditing for many critical DDL operations, but DML and some other DDL statements are still not audited.
5. To enable auditing on these other activities, try statements like these:

   audit alter table; -- DDL audit
   audit select table, update table, insert table, delete table; -- DML audit

1. 设置数据库参数AUDIT_TRAIL = DB。
2. 启动实例。
3. 使用 SQLPlus 登录。
4. 输入语句

   audit all;

   这会开启对许多关键 DDL 操作的审计，但 DML 和其他一些 DDL 语句仍未被审计。
5. 要启用对这些其他活动的审计，请尝试以下语句：

   audit alter table; -- DDL audit
   audit select table, update table, insert table, delete table; -- DML audit

Note: All "as sysdba" activity is ALWAYS audited to the O/S. In Windows, this means the Windows event log. In UNIX, this is usually $ORACLE_HOME/rdbms/audit.

注意：所有“as sysdba”活动总是被 O/S 审计。在 Windows 中，这意味着 Windows 事件日志。在 UNIX 中，这通常是 $ORACLE_HOME/rdbms/audit。

Check out the Oracle 10g R2 Audit Chapterof the Database SQL Reference.

查看数据库 SQL 参考的Oracle 10g R2 审计章节。

The database audit trail can be viewed in the SYS.DBA_AUDIT_TRAIL view.

可以在 SYS.DBA_AUDIT_TRAIL 视图中查看数据库审计跟踪。

It should be pointed out that the internal Oracle auditing will be high-performance by definition. It is designed to be exactly that, and it is very hard to imagine anything else rivaling it for performance. Also, there is a high degree of "fine-grained" control of Oracle auditing. You can get it just as precise as you want it. Finally, the SYS.AUD$ table along with its indexes can be moved to a separate tablespace to prevent filling up the SYSTEM tablespace.

应该指出的是，Oracle 内部审计按照定义将是高性能的。它的设计正是如此，很难想象有其他任何东西可以与它的性能相媲美。此外，还有对 Oracle 审计的高度“细粒度”控制。您可以随心所欲地获得它。最后，SYS.AUD$ 表及其索引可以移动到一个单独的表空间，以防止填满 SYSTEM 表空间。

Kind regards, Opus

亲切的问候，作品

回答by David Mann

If you want to record copies of changed records on a target system you can do this with Golden Gate Software and not incur much in the way of source side resource drain. Also you don't have to make any changes to the source database to implement this solution.

如果您想在目标系统上记录更改记录的副本，您可以使用 Golden Gate Software 执行此操作，并且不会导致源端资源消耗。此外，您无需对源数据库进行任何更改即可实施此解决方案。

Golden Gate scrapes the redo logs for transactions referring to a list of tables you are interested in. These changes are written to a 'Trail File' and can be applied to a different schema on the same database, or shipped to a target system and applied there (ideal for reducing load on your source system).

Golden Gate 会根据您感兴趣的表列表抓取事务的重做日志。这些更改被写入“跟踪文件”，可以应用于同一数据库上的不同模式，或发送到目标系统并应用那里（非常适合减少源系统的负载）。

Once you get the trail file to the target system there are some configuration tweaks you can set an option to perform auditing and if needed you can invoke 2 Golden Gate functions to get info about the transaction:

将跟踪文件发送到目标系统后，您可以进行一些配置调整，您可以设置一个选项来执行审计，如果需要，您可以调用 2 个金门函数来获取有关交易的信息：

1) Set the INSERTALLRECORDS Replication parameter to insert a new record in the target table for every change operation made to the source table. Beware this can eat up a lot of space, but if you need comprehensive auditing this is probably expected.

1) 设置 INSERTALLRECORDS Replication 参数，为对源表所做的每个更改操作在目标表中插入一条新记录。请注意，这会占用大量空间，但如果您需要全面审核，这可能是意料之中的。

2) If you don't already have a CHANGED_BY_USERID and CHANGED_DATE attached to your records, you can use the Golden Gate functions on the target side to get this info for the current transaction. Check out the following functions in the GG Reference Guide: GGHEADER("USERID") GGHEADER("TIMESTAMP")

2) 如果您还没有将 CHANGED_BY_USERID 和 CHANGED_DATE 附加到您的记录，您可以使用目标端的金门函数来获取当前交易的此信息。查看 GG 参考指南中的以下函数： GGHEADER("USERID") GGHEADER("TIMESTAMP")

So no its not free (requires Licensing through Oracle), and will require some effort to spin up, but probably a lot less effort/cost than implementing and maintaining a custom solution rolling your own, and you have the added benefit of shipping the data to a remote system so you can guarantee minimal impact on your source database.

所以不，它不是免费的（需要通过 Oracle 获得许可），并且需要一些努力来启动，但可能比实施和维护您自己的自定义解决方案要少得多的努力/成本，并且您还有传输数据的额外好处到远程系统，这样您就可以保证对源数据库的影响最小。

回答by ag112

if you are using oracle then there is feature called CDC(Capture data change) which is more performance efficient solution for audit kind of requirements.

如果您使用的是 oracle，那么有一个称为 CDC（捕获数据更改）的功能，它是针对审计类要求的更高效的解决方案。