Oracle - 特定用户的审计跟踪

正如该主题所述，我正在寻找一种方法来跟踪特定用户的活动.可能有也可能没有 SYSDBA 或 SYSOPER 权限.

例如，人力资源部.

我想知道他登录的细节是什么，他改变的对象是什么，它们的原始值是什么，执行的SQL语句，执行的过程/函数等.

我们可以在 Oracle 11gR2 Standard/Enterprise 中设置这样的审计跟踪日志吗?

谢谢

解决方案

首先，您需要通过设置audit_trail 参数在您的数据库中启用审计，如下所示-

SQL>更改系统设置 audit_trail='OS|DB|DB,EXTENDED|XML|XML,EXTENDED';

用于审计的初始化参数

然后，您可以将用户审核为-

SQL>CONNECT sys/password AS SYSDBASQL>AUDIT ALL BY 用户名 BY ACCESS;SQL>AUDIT SELECT TABLE, UPDATE TABLE, INSERT TABLE, DELETE TABLE BY username BY ACCESS;SQL>AUDIT EXECUTE PROCEDURE BY username BY ACCESS;

审计

审计记录可以在DBA_AUDIT_TRAIL视图中找到.以下查询列出了所有与审计相关的视图.

SQL>SELECT view_name FROM dba_views WHERE view_name LIKE 'DBA%AUDIT%';

细粒度审计仅在企业版中可用.

各版本的功能可用性

As stated on the topic , I am looking for a way for us to track on the activities of the specific user. May or may not have the SYSDBA or SYSOPER privilege.

For example , HR.

I would like to know what are the details of his login , what are the objects that are changed by him , what were their original values , SQL statements executed , what procedure/functions that were executed etc.

Could we set up such audit trail log in Oracle 11gR2 Standard/Enterprise?

Thanks

解决方案

First of all you need to enable auditing in your database by setting audit_trail parameter as shown below-

SQL> alter system set audit_trail='OS|DB|DB,EXTENDED|XML|XML, EXTENDED';

Initialization Parameters Used for Auditing

Then, you can audit user as-

SQL>CONNECT sys/password AS SYSDBA

SQL> AUDIT ALL BY username BY ACCESS;
SQL> AUDIT SELECT TABLE, UPDATE TABLE, INSERT TABLE, DELETE TABLE BY username BY ACCESS;
SQL> AUDIT EXECUTE PROCEDURE BY username BY ACCESS;

AUDIT

Audit records can be found in DBA_AUDIT_TRAIL view.Following query list all audit related views.

SQL>SELECT view_name FROM dba_views WHERE view_name LIKE 'DBA%AUDIT%';

Fine-grained auditing is available in Enterprise Edition only.

Feature Availability by Edition