如何在没有性能和可扩展性问题的情况下审核数据库活动？
我需要审核所有数据库活动，无论它是来自应用程序还是来自其他方式发出某些 SQL 的人。 因此审计必须在数据库级别进行。 有问题的数据库是Oracle。 我考虑通过触发器以及 Oracle 提供的细粒度审计来实现这一点。 在这两种情况下，我们都开启了对特定表和特定列的审计。 然而，我们发现当我们使用这两种方法时，性能真的很糟糕。
由于围绕数据隐私的规定，审计是绝对必须的，因此我想知道在不显着性能下降的情况下执行此操作的最佳方法是什么。 如果有人在这方面有 Oracle 的具体经验，那将会很有帮助，但即使不是围绕数据库活动审计的一般实践也可以。
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.
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.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
没有必要“自己动手”。 只需开启审计：
这会打开对许多关键 DDL 操作的审计，但 DML 和其他一些 DDL 语句仍然不会审计。
注意：所有“as sysdba”活动始终会经过操作系统审核。 在 Windows 中，这意味着 Windows 事件日志。 在 UNIX 中，这通常是 $ORACLE_HOME/rdbms/audit。
查看Oracle 10g R2 审计章节。
可以在 SYS.DBA_AUDIT_TRAIL 视图中查看数据库审计跟踪。
应该指出的是，Oracle 内部审计从定义上来说将是高性能的。 它的设计正是如此，很难想象还有什么东西可以在性能上与它相媲美。 此外，Oracle 审计还具有高度的“细粒度”控制。 您可以获得您想要的精确度。 最后，可以将 SYS.AUD$ 表及其索引移动到单独的表空间，以防止填满 SYSTEM 表空间。
亲切的问候，
作品
There is no need to "roll your own". Just turn on auditing:
This turns on auditing for many critical DDL operations, but DML and some other DDL statements are still not audited.
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.
Check out the Oracle 10g R2 Audit Chapter of the Database SQL Reference.
The database audit trail can be viewed in the SYS.DBA_AUDIT_TRAIL view.
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.
Kind regards,
Opus
我不确定这对于制作来说是否足够成熟
系统，但我在监控数据库方面取得了相当大的成功
使用网络流量嗅探器的流量。
将应用程序和数据库之间的原始数据发送到另一个应用程序和数据库
机器并在那里对其进行解码和分析。
我使用 PostgreSQL，解码流量并将其转换为
可以记录的数据库操作流相对而言
直截了当。 我想它可以在任何数据包所在的数据库上工作
格式已记录。
要点是它不会给数据库本身带来额外的负载。
而且，它是被动监控，它记录了所有活动，但是
无法阻止任何操作，因此可能不完全是您正在寻找的内容。
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.
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.
如果您使用的是 Oracle，那么有一个名为 CDC（捕获数据更改）的功能，它是针对审计类型需求的性能更高效的解决方案。
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.
如果您想在目标系统上记录更改记录的副本，您可以使用 Golden Gate Software 来完成此操作，并且不会造成源端资源消耗太多。 此外，您无需对源数据库进行任何更改即可实施此解决方案。
Golden Gate 会抓取引用您感兴趣的表列表的事务的重做日志。这些更改被写入“跟踪文件”，并且可以应用于同一数据库上的不同模式，或者发送到目标系统并应用在那里（非常适合减少源系统的负载）。
将跟踪文件发送到目标系统后，您可以进行一些配置调整，您可以设置一个选项来执行审核，如果需要，您可以调用 2 个 Golden Gate 函数来获取有关事务的信息：
1) 设置 INSERTALLRECORDS Replication 参数以插入一个对源表进行的每个更改操作都会在目标表中产生新记录。 请注意，这可能会占用大量空间，但如果您需要全面审核，这可能是预料之中的。
2) 如果您的记录中尚未附加 CHANGED_BY_USERID 和 CHANGED_DATE，您可以使用目标端的 Golden Gate 函数来获取当前交易的此信息。 查看GG参考指南中的以下函数：
GGHEADER(“用户ID”)
GGHEADER("TIMESTAMP")
所以不，它不是免费的（需要通过 Oracle 许可），并且需要一些努力来启动，但可能比实施和维护自己的自定义解决方案要少得多的努力/成本，并且您拥有将数据传送到远程系统的另一个好处是，您可以保证对源数据库的影响最小。
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 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).
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:
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.
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")
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.