oracle-审计3

五、管理对象审计：

--5.1 启用对象审计

--案例1：使用select审计

--步骤1：查看启用的对象scott.emp审计选项

select owner, object_name,object_type, ins,sel from
dba_obj_audit_opts
where owner='WIN'
and object_name='ACCP';

未选定行

--步骤2：开启审计对象

sql> show user
USER 为 "SYS"

SQL> audit select on win.accp  ;

审计已成功。

--步骤3：查看启用的对象scott.emp审计选项

select owner, object_name,object_type, ins,sel from
dba_obj_audit_opts
where owner='WIN'
and object_name='ACCP';

OWNER                          OBJECT_NAME                    OBJECT_TYPE       INS   SEL
------------------------------ ------------------------------ ----------------- ----- -----
WIN                            ACCP                           TABLE             -/-   S/S

--步骤4：让用户对对象进行操作

SQL> grant select on win.accp to scott;  --授权让用户scott可以查看用户win的表accp

授权成功。

SQL> conn scott/tiger
已连接。
SQL> select * from win.accp;

       TID
----------
      1001

--步骤5：检查审计跟踪

SQL> conn sys as sysdba
输入口令:
已连接。

select username, to_char(timestamp,'yyyy:mm:dd:hh') time, action_name from dba_audit_trail
where username='SCOTT';

USERNAME                       TIME          ACTION_NAME
------------------------------ ------------- ------------------
SCOTT                          2008:10:29:02 SESSION REC

--步骤6：清空审计记录

SQL> delete  from sys.aud$  ;

--案例2：使用insert审计

--步骤1：查看启用的对象scott.emp审计选项

select owner, object_name,object_type, ins,sel from
dba_obj_audit_opts
where owner='WIN'
and object_name='ACCP';

未选定行

--步骤2：开启审计对象

SQL> audit insert on win.accp;

审计已成功。

--步骤3：查看启用的对象scott.emp审计选项

select owner, object_name,object_type, ins,sel from
dba_obj_audit_opts
where owner='WIN'
and object_name='ACCP';

OWNER                          OBJECT_NAME                    OBJECT_TYPE       INS   SEL
------------------------------ ------------------------------ ----------------- ----- -----
WIN                            ACCP                           TABLE             S/S   -/-

--步骤4：让用户对对象进行操作

SQL> grant insert on win.accp to scott;  --授权让用户scott可以查看用户win的表accp

授权成功。

SQL> conn scott/tiger
已连接。
SQL> select * from win.accp;

       SID
----------
      1001

SQL> insert into win.accp values (1002);

已创建 1 行。

SQL> select * from win.accp;

       SID
----------
      1001
      1002

--步骤5：检查审计跟踪

SQL> conn sys as sysdba
输入口令:
已连接。

select username, to_char(timestamp,'yyyy:mm:dd:hh') time, action_name from dba_audit_trail
where username='SCOTT';

USERNAME                       TIME          ACTION_NAME
------------------------------ ------------- ------------------
SCOTT                          2008:10:29:02 SESSION REC

--步骤6：清空审计记录

SQL> delete  from sys.aud$  ;

----------------------------------------------------------------------------------------
****************************************************************************************
七、管理精细审计：
****************************************************************************************
----------------------------------------------------------------------------------------

--7.1 环境准备

---7.2 案例演示：

--(1) 查询数据库中存在的FGA策略

select policy_name, object_schema ,policy_name, policy_column, enabled, audit_trail
from dba_audit_policies;

未选定行

--(2)新建FGA策略noselect

begin
dbms_fga.add_policy(object_schema=>'scott'
,object_name=>'accp'
,policy_name=>'noselect'
,audit_column=>'sid'
,enable=>false
,statement_types=>'select,update,delete'
);
end
;
/

--(3) 查询数据库中存在的FGA策略

select policy_name, object_schema ,policy_name, policy_column, enabled, audit_trail
from dba_audit_policies;

POLICY_NAME     OBJECT_SCHEMA     POLICY_NAME      POLICY_COLUMN     ENABLED    AUDIT_TRAIL
-------------------------- ------------------------------ ------------------------------ ---
NOSELECT        SCOTT             NOSELECT          SID                NO    DB+EXTENDED

-- (4) 启用FGA策略并查看数据库中存在的FGA策略
begin
dbms_fga.enable_policy(object_schema=>'scott'
,object_name=>'accp'
,policy_name=>'noselect'
);
end
;
/

select policy_name, object_schema ,policy_name, policy_column, enabled, audit_trail
from dba_audit_policies;

POLICY_NAME     OBJECT_SCHEMA     POLICY_NAME      POLICY_COLUMN     ENABLED    AUDIT_TRAIL
-------------------------- ------------------------------ ------------------------------ ---
NOSELECT        SCOTT             NOSELECT          SID               YES   DB+EXTENDED

--(5) 查看FGA策略跟踪报表
SQL> select db_user, timestamp, userhost from dba_fga_audit_trail where policy_name='NOSELECT';

未选定行

-- (6) win用户操作

SQL> conn win/passWord
已连接。
SQL> show user
USER 为 "WIN"

select * from scott.accp;

--(7) 查看FGA策略跟踪报表

SQL> select db_user, timestamp, userhost from dba_fga_audit_trail where policy_name='NOSELECT';

DB_USER                        TIMESTAMP   USERHOST
------------------------------ ----------- --------------------------------------------------------------------------------
SCOTT                          2008-10-29  WORKGROUP\NANJING

--(8) 停用FGA策略并检查数据库存在的策略

begin
dbms_fga.disable_policy(object_schema=>'scott'
,object_name=>'accp'
,policy_name=>'noselect'
);
end
;
/

select policy_name, object_schema ,policy_name, policy_column, enabled, audit_trail
from dba_audit_policies;

POLICY_NAME     OBJECT_SCHEMA     POLICY_NAME      POLICY_COLUMN     ENABLED    AUDIT_TRAIL
-------------------------- ------------------------------ ------------------------------ ---
NOSELECT        SCOTT             NOSELECT          SID               NO  DB+EXTENDED

--(9) 删除FGA策略并查看数据库是否还存在这个FGA策略

begin
dbms_fga.drop_policy(object_schema=>'scott'
,object_name=>'accp'
,policy_name=>'noselect'
);
end
;
/

select policy_name, object_schema ,policy_name, policy_column, enabled, audit_trail
from dba_audit_policies;

未选定行