如何登录Oracle数据库？ [英] How to log in an Oracle database?

问题描述

我对在Oracle数据库中频繁使用哪些记录方法感兴趣。
我们的方法如下：

I am interested in what methods of logging is frequent in an Oracle database. Our method is the following:

我们为要记录的表创建一个日志表。日志表包含原始表的所有列以及一些特殊字段，包括时间戳，修改类型（插入，更新，删除），修饰符的id。原始表上的触发器为每次插入和删除创建一个日志行，为修改创建两行。

We create a log table for the table to be logged. The log table contains all the columns of the original table plus some special fields including timestamp, modification type (insert, update, delete), modifier's id. A trigger on the original table creates one log row for each insertion and deletion, and two rows for a modification. Log rows contain the data before and after the alteration of the original one.

虽然记录的状态可以使用此方法及时挖掘，但它有一些缺点：

Although state of the records can be mined back in time using this method, it has some drawbacks:

• 在原始表格中引入新栏不会自动涉及日志修改。




• 在特定过去时间的记录状态无法以简单的方式确定。


• ...

• Introduction of a new column in the original table does not automatically involves log modification.
• Log modification affects log table and trigger and it is easy to mess up.
• State of a record at a specific past time cannot be determined in a straightforward way.
• ...

还有其他可能性吗？
什么样的工具可以用来解决这个问题？

What other possibilities exist? What kind of tools can be used to solve this problem?

我只知道 log4plsql 。这个工具的优点/缺点是什么？

I only know of log4plsql. What are the pros/cons of this tool?

编辑：根据Brian的回答，我发现了以下参考，说明标准和细粒度审核。

Based on Brian's answer I have found the following reference that explains standard and fine grain auditing.

推荐答案

听起来你是在'审计'之后。 Oracle具有称为精细粒度审计（FGA）的内置功能。简而言之，您可以审核一切或具体条件。真正酷的是你可以审计选择以及交易。开始审计的简单命令：

It sounds like you are after 'auditing'. Oracle has a built-in feature called Fine Grain Auditing (FGA). In a nutshell you can audit everything or specific conditions. What is really cool is you can 'audit' selects as well as transactions. Simple command to get started with auditing:

audit UPDATE on SCOTT.EMP by access;

将其视为select语句的触发器。例如，您创建的政策：

Think of it as a 'trigger' for select statements. For example, you create policies:

begin
   dbms_fga.add_policy (
      object_schema=>'BANK',
      object_name=>'ACCOUNTS',
      policy_name=>'ACCOUNTS_ACCESS'
  );
end;

定义策略后，当用户以常用方式查询表时，如下所示：

After you have defined the policy, when a user queries the table in the usual way, as follows:

select * from bank.accounts; 

审计跟踪记录此操作。您可以通过发出以下内容查看跟踪：

the audit trail records this action. You can see the trail by issuing:

select timestamp, 
   db_user,
   os_user,
   object_schema,
   object_name,
   sql_text
from dba_fga_audit_trail;

TIMESTAMP DB_USER OS_USER OBJECT_ OBJECT_N SQL_TEXT
--------- ------- ------- ------- -------- ----------------------
22-OCT-08 BANK    ananda  BANK    ACCOUNTS select * from accounts

这篇关于如何登录Oracle数据库？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！