一、标准化审计：
1.系统权限审计
（1）.开启数据库标准审计，使用db_extended级别
SQL> alter system set audit_trail='DB_EXTENDED' scope=spfile
;
（2）.清空标准化审计所使用的数据字典表，并把该表移动到users表空间
SQL> alter table aud$ move tablespace users;
（3）.给hr用户授予delete any table权限
SQL> grant delete any table to scott;
（4）.在scott用户下创建实验表e，要求根据 emp表进行创建
（5）.要求在会话级别审计hr用户的系统权限delete any table
SQL> audit delete any table by hi by session;
（6）.使用hr用户，删除scott用户e表的记录，并检查审计结果。
SQL> select username，timestamp，ses_actions,obj_name from dba_audit_trail;
（7）.关闭hr用户系统权限delete any table的审计。
noaudit delete any table by hi
2.对象权限审计
（1）.清空标准化审计所使用的数据字典表
（2）.审计scott用户在e表的上的insert操作
SQL> audit update,select,insert on scott.e by session;
（3）.使用scott用户在e表中插入数据
（4）.检查审计结果
select username,timestamp,ses_actions,object_name,action_name
from dba_audit_trail;
（5）.关闭该审计
noaudit select,update,insert on scott.e
3.语句级审计
（1）.清空标准化审计所使用的数据字典表
（2）.审计序列
（3）.使用hr用户，创建序列，要求名称为seq_hr_audit，起始值为1，增量为1，不循环，不缓存，没有最大值最小值。
（4）.检查审计结果
（5）.删除该序列
（6）.检查审计结果
（7）.关闭该审计
4.登录审计
（1）清空标准化审计所使用的数据字典表
truncate table aud$
（2）审计用户登录，要求能够记录登录失败以及用户账户锁定情况
audit session
（3）给SCOTT用户创建专用的PROFILE，名称为P1，要求该PROFILE限制登录失败次数为5次，其它限制均与DEFAULT的PROFILE相同。
alter profile p1 limit FAILED_LOGIN_ATTEMPTS 5
（4）切换SCOTT用户的PROFILE为P1
alter user scott profile p1
（5）尝试使用错误密码登陆SCOTT 5次，直到账户被锁定
（6）检查审计结果，统计SCOTT用户登录成功与失败的次数
SQL> select returncode,count(*) from dba_audit_trail group by returncode;
RETURNCODE COUNT(*)
---------- ----------
28000 1 --锁定的数量
1017 6 失败的数量
0 96 成功的数量
（7）关闭该审计
audit session
二.SYSDBA的审计
1.打开SYSDBA审计的附加记录选项（提示，修改某个参数）
show parameter audit_sys_operations
2.找到存放SYSDBA审计结果的目录
show paremeter audi_file_dest
/u01/app/oracle/admin/orcl/adump
3.查询当前会话的SPID值，找到相关的审计文件
select spid from v$process where addr=(select paddr from v$session
where sid=(select sid from v$mystat where rownum=1))
4.使用SYSDBA查询参数，查询一些表或者视图
/u01/app/oracle/admin/orcl/adump
5.检查该SYSDBA会话的审计结果
/u01/app/oracle/admin/orcl/adump
[oracle@oracle adump]$ ls
orcl_s000_22527_1.aud orcl_s001_22529_1.aud
[oracle@oracle adump]$ more orcl_s000_22527_1.aud
6.关闭SYSDBA审计的附加记录选项
alter system set audit_sys_operations=fales scope=spfile
startup force
三.细粒度审计
1.确认细粒度审计使用的数据字典基表，查询该表所在的表空间，将该表移动至USERS表空间
select count(*) from fga_log$;
2.清空细粒度审计使用的数据字典基表
truncate table fga_log$
3.实施细粒度审计，
使用HR创建实验表emps，根据employees表创建
要求审计HR 用户在emps表上的delete操作
使用HR用户在emps表上执行一些delete操作
检查审计结果
禁用该审计策略
清空细粒度审计使用的数据字典基表
[oracle@oracle ~]$ vim dbms_fga.add_policy.sql
begin
dbms_fga.add_policy(
object_schema =>'scott',
object_name =>'emp',
audit_condition=>'empno=7788',
audit_column =>'sal,comm',
enable =true,
statement_type =>'select,update');
end;
SQL>get dbms_fga.add_policy.sql
begin
dbms_fga.add_policy(
object_schema =>'scott',
object_name =>'emp',
audit_condition=>'empno=7788',
audit_column =>'sal,comm',
enable =true,
statement_type =>'select,update');
end;
/
SQL>conn scott/tiger
SQL> select sal,comm from emp where empno=7788;
SAL COMM
---------- ----------
100
100
SQL> update emp set sal=1 where empno=7788;
已更新2行。
SQL> update emp set sal=1,comm=1 where empno=7788;
SQL>conn / as sysdba
SQL> select count(*) from fga_log$;
COUNT(*)
----------
3
SQL>desc dba_fga_audit_trail
SQL>select to_char(timestamp,'yyyy-mm-ddhh24:mi:ss'), db_user,os_user,object_schema,object_name,sql_text from dba_fga_audit_trail;
TO_CHAR(TIMESTAMP,' DB_USER OS_USER OBJECT_SCH OBJECT_NAM
------------------- ---------- ---------- ---------- ----------
SQL_TEXT
------------------------------------------------------------
2016-05-14 11:27:37 SCOTT oracle SCOTT EMP
select sal,comm from emp where empno=7788
2016-05-14 11:27:58 SCOTT oracle SCOTT EMP
update emp set sal=1 where empno=7788
2016-05-14 11:28:13 SCOTT oracle SCOTT EMP
update emp set sal=1,comm=1 where empno=7788
SQL> desc dbms_fga
PROCEDURE ADD_POLICY
参数名称 类型 输入/输出默认值?
------------------------------ ----------------------- ------ --------
OBJECT_SCHEMA VARCHAR2 IN DEFAULT
OBJECT_NAME VARCHAR2 IN
POLICY_NAME VARCHAR2 IN
AUDIT_CONDITION VARCHAR2 IN DEFAULT
AUDIT_COLUMN VARCHAR2 IN DEFAULT
HANDLER_SCHEMA VARCHAR2 IN DEFAULT
HANDLER_MODULE VARCHAR2 IN DEFAULT
ENABLE BOOLEAN IN DEFAULT
STATEMENT_TYPES VARCHAR2 IN DEFAULT
AUDIT_TRAIL BINARY_INTEGER IN DEFAULT
AUDIT_COLUMN_OPTS BINARY_INTEGER IN DEFAULT
POLICY_OWNER VARCHAR2 IN DEFAULT
PROCEDURE DISABLE_POLICY
参数名称 类型 输入/输出默认值?
------------------------------ ----------------------- ------ --------
OBJECT_SCHEMA VARCHAR2 IN DEFAULT
OBJECT_NAME VARCHAR2 IN
POLICY_NAME VARCHAR2 IN
PROCEDURE DROP_POLICY
参数名称 类型 输入/输出默认值?
------------------------------ ----------------------- ------ --------
OBJECT_SCHEMA VARCHAR2 IN DEFAULT
OBJECT_NAME VARCHAR2 IN
POLICY_NAME VARCHAR2 IN
PROCEDURE ENABLE_POLICY
参数名称 类型 输入/输出默认值?
------------------------------ ----------------------- ------ --------
OBJECT_SCHEMA VARCHAR2 IN DEFAULT
OBJECT_NAME VARCHAR2 IN
POLICY_NAME VARCHAR2 IN
ENABLE BOOLEAN IN DEFAULT
SQL> begin
2 dbms_fga.disable_policy(
3 object_schema =>'scott',
4 object_name =>'emp',
5 policy_name =>'audit_emp');
6 end;
7 /
SQL> select object_name,object_schema,policy_name,enabled from dba_audit_policies;
OBJECT_NAM OBJECT_SCH POLICY_NAME ENA
---------- ---------- ------------------------------ ---
EMP SCOTT AUDIT_EMP NO
SQL>truncate table fga_log$;
4.实施细粒度审计
要求审计HR用户在emps表上，与部门80相关的update及delete操作
使用HR用户在emps表上执行一些DML操作
检查审计结果
禁用该审计策略
清空细粒度审计使用的数据字典基表
SQL> begin
2 dbms_fga.add_policy(
3 object_schema =>'scott',
4 object_name =>'emp',
5 policy_name =>'audit_emp1',
6 audit_condition =>'deptno=20',
7 enable =>true,
8 statement_types =>'update,delete');
9 end;
10 /
SQL> select timestamp,object_schema,object_name,sql_text from dba_fga_audit_trail;
TIMESTAMP OBJECT_SCHEMA OBJECT_NAME SQL_TEXT
14-5月 -16 SCOTT EMP
update emp set sal=300 where deptno=20
SQL> begin
2 dbms_fga.disable_policy(
3 object_schema =>'scott',
4 object_name =>'emp',
5 policy_name =>'audit_emp1');
6 end;
7 /
PL/SQL 过程已成功完成。
SQL> select object_schema,object_name,policy_name,policy_owner,enabled from dba_audit_policies;
OBJECT_SCHEMA OBJECT_NAM POLICY_NAME POLICY_OWN ENABLED
------------------------------ ---------- ------------------------------ ---------- ----------
SCOTT EMP AUDIT_EMP SYS NO
SCOTT EMP AUDIT_EMP1 SYS NO
SQL> truncate table fga_log$;
表被截断。
SQL> select object_name,object_schema,sql_text,timestamp from dba_fga_audit_trail;
未选定行
5.实施细粒度审计
要求审计HR用户在emps表上，与职务'IT_PROG'相关的salary、commission_pct字段上的select,update,insert操作
使用HR用户在emps表上执行一些DML操作
检查审计结果
禁用该审计策略
清空细粒度审计使用的数据字典基表
SQL> ed
已写入 file afiedt.buf
1 begin
2 dbms_fga.add_policy(
3 object_schema =>'scott',
4 object_name =>'emp',
5 policy_name =>'audit_emp2',
6 audit_condition =>'deptno=10',
7 audit_column =>'job,ename',
8 enable =>true,
9 statement_types =>'select,update,insert');
10* end;
11 /
PL/SQL 过程已成功完成。
SQL> define _EDITOR='vi';
SQL> ed
已写入 file afiedt.buf
1 begin
2 dbms_fga.disable_policy(
3 object_schema =>'scott',
4 object_name =>'emp',
5 policy_name =>'audit_emp2');
6* end;
7 /
PL/SQL 过程已成功完成。
SQL> select object_schema,object_name,policy_name,policy_owner,enabled from dba_audit_policies;
OBJECT_SCHEMA OBJECT_NAM POLICY_NAME POLICY_OWN ENABLED
------------------------------ ---------- ------------------------------ ---------- ----------
SCOTT EMP AUDIT_EMP SYS NO
SCOTT EMP AUDIT_EMP1 SYS NO
SCOTT EMP AUDIT_EMP2 SYS NO
SQL> !oerr ora 28106
28106, 00000, "input value for argument #%s is not valid"
// *Cause: Input values for the argument is missing or invalid.
// *Action: Correct the input values.
6.删除上述三个细粒度审计中定义的审计策略
SQL> select object_schema,object_name,policy_name,policy_owner,enabled from dba_audit_policies;
OBJECT_SCHEMA OBJECT_NAM POLICY_NAME POLICY_OWN ENABLED
------------------------------ ---------- ------------------------------ ---------- ----------
SCOTT EMP AUDIT_EMP SYS NO
SCOTT EMP AUDIT_EMP1 SYS NO
SCOTT EMP AUDIT_EMP2 SYS NO
SQL> begin
2 dbms_fga.drop_policy(
3 object_schema =>'scott',
4 object_name =>'emp',
5 policy_name =>'audit_emp');
6 end;
7 /
PL/SQL 过程已成功完成。
SQL> begin
2 dbms_fga.drop_policy(
3 object_schema =>'scott',
4 object_name =>'emp',
5 policy_name =>'audit_emp1');
6 end;
7 /
SQL> begin
2 dbms_fga.drop_policy(
3 object_schema =>'scott',
4 object_name =>'emp',
5 policy_name =>'audit_emp2');
6 end;
7 /
PL/SQL 过程已成功完成。
SQL> select object_schema,object_name,policy_name,policy_owner,enabled from dba_audit_policies;
未选定行
SQL> desc dbms_fga;
PROCEDURE ADD_POLICY
参数名称 类型 输入/输出默认值?
------------------------------ ----------------------- ------ --------
OBJECT_SCHEMA VARCHAR2 IN DEFAULT
OBJECT_NAME VARCHAR2 IN
POLICY_NAME VARCHAR2 IN
AUDIT_CONDITION VARCHAR2 IN DEFAULT
AUDIT_COLUMN VARCHAR2 IN DEFAULT
HANDLER_SCHEMA VARCHAR2 IN DEFAULT
HANDLER_MODULE VARCHAR2 IN DEFAULT
ENABLE BOOLEAN IN DEFAULT
STATEMENT_TYPES VARCHAR2 IN DEFAULT
AUDIT_TRAIL BINARY_INTEGER IN DEFAULT
AUDIT_COLUMN_OPTS BINARY_INTEGER IN DEFAULT
POLICY_OWNER VARCHAR2 IN DEFAULT
PROCEDURE DISABLE_POLICY
参数名称 类型 输入/输出默认值?
------------------------------ ----------------------- ------ --------
OBJECT_SCHEMA VARCHAR2 IN DEFAULT
OBJECT_NAME VARCHAR2 IN
POLICY_NAME VARCHAR2 IN
PROCEDURE DROP_POLICY
参数名称 类型 输入/输出默认值?
------------------------------ ----------------------- ------ --------
OBJECT_SCHEMA VARCHAR2 IN DEFAULT
OBJECT_NAME VARCHAR2 IN
POLICY_NAME VARCHAR2 IN
PROCEDURE ENABLE_POLICY
参数名称 类型 输入/输出默认值?
------------------------------ ----------------------- ------ --------
OBJECT_SCHEMA VARCHAR2 IN DEFAULT
OBJECT_NAME VARCHAR2 IN
POLICY_NAME VARCHAR2 IN
ENABLE BOOLEAN IN DEFAULT
SQL> get dbms_fga.add_policy.sql
1 begin
2 dbms_fga.add_policy (
3 object_schema =>'scott',
4 object_name =>'emp',
5 policy_name =>'audit_emp',
6 audit_condition=>'empno=7788',
7 audit_column =>'sal,comm',
8 enable =>true,
9 statement_types =>'select,update');
10* end;
11 /
PL/SQL 过程已成功完成。
SQL> begin
2 dbms_fga.disable_policy(
3 object_schema =>'scott',
4 object_name =>'emp',
5 policy_name =>'audit_emp');
6 end;
7 /
PL/SQL 过程已成功完成。
SQL> select object_schema,object_name,policy_name,policy_owner,enabled from dba_audit_policies;
OBJECT_SCHEMA OBJECT_NAME
------------------------------ ------------------------------
POLICY_NAME POLICY_OWNER ENA
------------------------------ ------------------------------ ---
SCOTT EMP
AUDIT_EMP SYS NO
SQL> begin
2 dbms_fga.enable_policy(
3 object_schema =>'scott',
4 object_name =>'emp',
5 policy_name =>'audit_emp');
6 end;
7 /
PL/SQL 过程已成功完成。
SQL> select object_schema,object_name,policy_name,policy_owner,enabled from dba_audit_policies;
OBJECT_SCHEMA OBJECT_NAME
------------------------------ ------------------------------
POLICY_NAME POLICY_OWNER ENA
------------------------------ ------------------------------ ---
SCOTT EMP
AUDIT_EMP SYS YES
SQL> begin
2 dbms_fga.drop_policy(
3 object_schema =>'scott',
4 object_name =>'emp',
5 policy_name =>'audit_emp');
6 end;
7 /
PL/SQL 过程已成功完成。
SQL> select object_schema,object_name,policy_name,policy_owner,enabled from dba_audit_policies;
未选定行
可以使用shell脚本
[oracle@oracle ~]$ cat dbms_fga.add_policy.sql
#!/bin/bash
sqlplus / as sysdba <<EOF
begin
dbms_fga.add_policy (
object_schema =>'scott',
object_name =>'emp',
policy_name =>'audit_emp',
audit_condition=>'empno=7788',
audit_column =>'sal,comm',
enable =>true,
statement_types =>'select,update');
end;
/
EOF
[oracle@oracle ~]$ vim dbms_fga.add_policy.sql
[oracle@oracle ~]$ ./dbms_fga.add_policy.sql
SQL*Plus: Release 11.2.0.1.0 Production on 星期六 5月 14 17:19:32 2016
Copyright (c) 1982, 2009, Oracle. All rights reserved.
连接到:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> 2 3 4 5 6 7 8 9 10 11
PL/SQL 过程已成功完成。
SQL> select object_schema,object_name,policy_name,policy_owner,enabled from dba_audit_policies;
OBJECT_SCHEMA OBJECT_NAM POLICY_NAME POLICY_OWN ENABLED
------------------------------ ---------- ------------------------------ ---------- ----------
SCOTT EMP AUDIT_EMP SYS NO
编写 sql语句
vim geshi.sql
内容
col timestamp for a20
col db_user for a10
col os_user for a10
col object_schema for a10
col object_namefor a10
col sql_text for a60
select to_char(timestamp,'yyyy-mm-dd hh24:mi:ss'),db_user,os_user,
object_schema,object_name,sql_text from dba_fga_audit_trail;
SQL> @geshi.sql
TO_CHAR(TIMESTAMP,' DB_USER OS_USER OBJECT_SCH OBJECT_NAM
------------------- ---------- ---------- ---------- ----------
SQL_TEXT
--------------------------------------------------
2016-05-14 17:39:54 SCOTT oracle SCOTT EMP
select * from emp where deptno=20
使用exec
[oracle@oracle ~]$ ./dbms_fga.add_policy.sql
SQL*Plus: Release 11.2.0.1.0 Production on 星期六 5月 14 17:56:32 2016
Copyright (c) 1982, 2009, Oracle. All rights reserved.
连接到:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> 2 3 4 5 6 7 8 9 10 11
PL/SQL 过程已成功完成。
SQL> select object_schema,object_name,policy_owner,policy_name,enabled from dba_audit_policies;
OBJECT_SCHEMA OBJECT_NAME
------------------------------ ------------------------------
POLICY_OWNER POLICY_NAME ENA
------------------------------ ------------------------------ ---
SCOTT EMP
SYS AUDIT_EMP YES
SQL> exec dbms_fga.disable_policy('SCOTT','EMP','AUDIT_EMP');
PL/SQL 过程已成功完成。
SQL> select object_schema,object_name,policy_owner,policy_name,enabled from dba_audit_policies;
OBJECT_SCHEMA OBJECT_NAME
------------------------------ ------------------------------
POLICY_OWNER POLICY_NAME ENA
------------------------------ ------------------------------ ---
SCOTT EMP
SYS AUDIT_EMP NO
SQL> exec dbms_fga.enable_policy('SCOTT','EMP','AUDIT_EMP');
PL/SQL 过程已成功完成。
SQL> select object_schema,object_name,policy_owner,policy_name,enabled from dba_audit_policies;
OBJECT_SCHEMA OBJECT_NAME
------------------------------ ------------------------------
POLICY_OWNER POLICY_NAME ENA
------------------------------ ------------------------------ ---
SCOTT EMP
SYS AUDIT_EMP YES
SQL> exec dbms_fga.drop_policy('SCOTT','EMP','AUDIT_EMP');
PL/SQL 过程已成功完成。
SQL> select object_schema,object_name,policy_owner,policy_name,enabled from dba_audit_policies;
数据库审计
最新推荐文章于 2024-07-30 23:29:06 发布
1569
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
