演示简单审计
准备工作
|
SQL> conn / as sysdba Connected. SQL> show parameter audit_tr
NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ audit_trail string NONE SQL> alter system set audit_trail =a scope=spfile; alter system set audit_trail =a scope=spfile * ERROR at line 1: ORA-00096: invalid value A for parameter audit_trail, must be from among extended, xml, db_extended, false, true, none, os, db 会提示可以选择的值有哪些。 http://docs.oracle.com/cd/B19306_01/network.102/b14266/cfgaudit.htm#i1014788 在官方文档中直接搜索AUDIT_TRAIL=
SQL> 修改参数重启使参数生效 SQL> alter system set audit_trail =db,extended scope=spfile;
System altered.
SQL> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. SQL> startup
查看是否有dba_audit_trail视图 SQL> desc dba_audit_trail Name Null? Type ----------------------------------------- -------- ---------------------------- OS_USERNAME VARCHAR2(255) USERNAME VARCHAR2(30) USERHOST VARCHAR2(128) TERMINAL VARCHAR2(255) TIMESTAMP DATE OWNER VARCHAR2(30) OBJ_NAME VARCHAR2(128) ACTION NOT NULL NUMBER ACTION_NAME VARCHAR2(28) NEW_OWNER VARCHAR2(30) NEW_NAME VARCHAR2(128) OBJ_PRIVILEGE VARCHAR2(16) SYS_PRIVILEGE VARCHAR2(40) ADMIN_OPTION VARCHAR2(1) GRANTEE VARCHAR2(30) AUDIT_OPTION VARCHAR2(40) SES_ACTIONS VARCHAR2(19) LOGOFF_TIME DATE LOGOFF_LREAD NUMBER LOGOFF_PREAD NUMBER LOGOFF_LWRITE NUMBER LOGOFF_DLOCK VARCHAR2(40) COMMENT_TEXT VARCHAR2(4000) SESSIONID NOT NULL NUMBER ENTRYID NOT NULL NUMBER STATEMENTID NOT NULL NUMBER RETURNCODE NOT NULL NUMBER PRIV_USED VARCHAR2(40) CLIENT_ID VARCHAR2(64) ECONTEXT_ID VARCHAR2(64) SESSION_CPU NUMBER EXTENDED_TIMESTAMP TIMESTAMP(6) WITH TIME ZONE PROXY_SESSIONID NUMBER GLOBAL_UID VARCHAR2(32) INSTANCE_NUMBER NUMBER OS_PROCESS VARCHAR2(16) TRANSACTIONID RAW(8) SCN NUMBER SQL_BIND NVARCHAR2(2000) SQL_TEXT NVARCHAR2(2000)
SQL> 如果不存在执行cataaudit脚本 SQL> @?/rdbms/admin/cataudit
|
实现系统权限审计scott用户 create table
|
SQL> audit create table by scott;
Audit succeeded.
SQL> 可以查看对什么权限或者语句进行审计 SQL> select * from dba_priv_audit_opts;
USER_NAME PROXY_NAME ------------------------------ ------------------------------ PRIVILEGE SUCCESS FAILURE ---------------------------------------- ---------- ---------- SCOTT CREATE TABLE BY ACCESS BY ACCESS
SQL> 必须重新创建一个会话 [oracle@yang admin]$ sqlplus scott/tiger
SQL*Plus: Release 10.2.0.1.0 - Production on Tue May 6 03:35:39 2014
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production With the Partitioning, OLAP and Data Mining options 创建表2次 SQL> create table a1(id number);
Table created.
SQL> SQL> create table a1(id number); create table a1(id number) * ERROR at line 1: ORA-00955: name is already used by an existing object
SQL> 查看审计结果 SQL> col username for a10 SQL> col action_name for a10 SQL> col sql_text for a20 SQL> select username,returncode,action_name,sql_text from dba_audit_trail;
USERNAME RETURNCODE ACTION_NAM SQL_TEXT ---------- ---------- ---------- -------------------- SCOTT 0 CREATE TAB create table a1(id n LE umber)
SCOTT 955 CREATE TAB create table a1(id n LE umber)
SQL> 取消审计 SQL> noaudit create table by scott;
Noaudit succeeded.
SQL> select * from dba_priv_audit_opts;
no rows selected
SQL>
|
实现只审计scott用户创建失败的表
|
SQL> audit create table by scott whenever not successful;
Audit succeeded.
SQL> 重连会话 SQL> exit Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production With the Partitioning, OLAP and Data Mining options [oracle@yang admin]$ sqlplus scott/tiger
SQL*Plus: Release 10.2.0.1.0 - Production on Tue May 6 03:49:44 2014
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production With the Partitioning, OLAP and Data Mining options
SQL> 创建成功表 SQL> create table a2(id number);
Table created.
SQL> 查看审计信息发现不存在 SQL> select username,returncode,action_name,sql_text from dba_audit_trail;
USERNAME RETURNCODE ACTION_NAM SQL_TEXT ---------- ---------- ---------- -------------------- SCOTT 0 CREATE TAB create table a1(id n LE umber)
SCOTT 955 CREATE TAB create table a1(id n LE umber)
SQL> 建立失败的表
SQL> create table a2(id number); create table a2(id number) * ERROR at line 1: ORA-00955: name is already used by an existing object
SQL> 查看审计信息 SQL> select username,returncode,action_name,sql_text from dba_audit_trail;
USERNAME RETURNCODE ACTION_NAM SQL_TEXT ---------- ---------- ---------- -------------------- SCOTT 0 CREATE TAB create table a1(id n LE umber)
SCOTT 955 CREATE TAB create table a1(id n LE umber)
SCOTT 955 CREATE TAB create table a2(id n LE umber)
发现记录在审计信息中。 其中 955 代表失败的。 SQL> 取消审计
SQL> noaudit create table by scott whenever not successful;
Noaudit succeeded.
SQL> |
审计权限 select any table 与select any table by session
|
开启审计 SQL> audit select any table;
Audit succeeded.
SQL> 授予hr用户权限 SQL> grant select any table to hr;
Grant succeeded.
SQL>
新建会话登录到hr用户查看scott用户dept表 [oracle@yang admin]$ sqlplus hr/hr
SQL*Plus: Release 10.2.0.1.0 - Production on Tue May 6 03:56:25 2014
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production With the Partitioning, OLAP and Data Mining options
SQL> select * from scott.dept;
DEPTNO DNAME LOC ---------- -------------- ------------- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON
SQL> 查看审计信息 SQL> select username,returncode,action_name,sql_text from dba_audit_trail;
USERNAME RETURNCODE ACTION_NAM SQL_TEXT ---------- ---------- ---------- -------------------- SCOTT 0 CREATE TAB create table a1(id n LE umber)
SCOTT 955 CREATE TAB create table a1(id n LE umber)
SCOTT 955 CREATE TAB create table a2(id n LE umber)
HR 0 SESSION RE select * from scott. C dept
USERNAME RETURNCODE ACTION_NAM SQL_TEXT ---------- ---------- ---------- --------------------
SQL> 取消审计 SQL> noaudit select any table;
Noaudit succeeded.
SQL> 审计权限 select any table by session
SQL> audit select any table by session; Audit succeeded.
SQL> 登录会话查询表 [oracle@yang admin]$ sqlplus hr/hr
SQL*Plus: Release 10.2.0.1.0 - Production on Tue May 6 04:03:58 2014
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production With the Partitioning, OLAP and Data Mining options
SQL> select * from scott.emp; 查看审计信息 SQL> select username,returncode,action_name,sql_text from dba_audit_trail;
USERNAME RETURNCODE ACTION_NAM SQL_TEXT ---------- ---------- ---------- -------------------- SCOTT 0 CREATE TAB create table a1(id n LE umber)
SCOTT 955 CREATE TAB create table a1(id n LE umber)
SCOTT 955 CREATE TAB create table a2(id n LE umber)
HR 0 SESSION RE select * from scott. C dept
USERNAME RETURNCODE ACTION_NAM SQL_TEXT ---------- ---------- ---------- --------------------
HR 0 SESSION RE select * from scott. C emp
5 rows selected.
SQL> 在查询dept表与emp表查看审计信息 SQL> select * from scott.dept;
DEPTNO DNAME LOC ---------- -------------- ------------- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON
SQL> select * from scott.emp;
SQL>
SQL> select username,returncode,action_name,sql_text from dba_audit_trail;
USERNAME RETURNCODE ACTION_NAM SQL_TEXT ---------- ---------- ---------- -------------------- SCOTT 0 CREATE TAB create table a1(id n LE umber)
SCOTT 955 CREATE TAB create table a1(id n LE umber)
SCOTT 955 CREATE TAB create table a2(id n LE umber)
HR 0 SESSION RE select * from scott. C dept
USERNAME RETURNCODE ACTION_NAM SQL_TEXT ---------- ---------- ---------- --------------------
HR 0 SESSION RE select * from scott. C emp
HR 0 SESSION RE select * from scott. C dept 一个会话查询相同的表只记录一次
6rows selected.
SQL> 取消审计 SQL> noaudit select any table by session; noaudit select any table by session * ERROR at line 1: ORA-01718: BY ACCESS | SESSION clause not allowed for NOAUDIT
SQL> noaudit select any table;
Noaudit succeeded.
SQL> |
对象权限的审计 ----当访问scott用户下的dept表时 进行审计
|
SQL> audit select on scott.dept;
Audit succeeded.
SQL>
[oracle@yang admin]$ sqlplus hr/hr
SQL*Plus: Release 10.2.0.1.0 - Production on Tue May 6 05:31:56 2014
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production With the Partitioning, OLAP and Data Mining options
SQL> select * from scott.dept where deptno=10;
DEPTNO DNAME LOC ---------- -------------- ------------- 10 ACCOUNTING NEW YORK
SQL> select * from scott.dept where deptno=20;
DEPTNO DNAME LOC ---------- -------------- ------------- 20 RESEARCH DALLAS SQL>
查看审计信息只有一条
SQL> select username,returncode,action_name,sql_text from dba_audit_trail;
USERNAME RETURNCODE ACTION_NAM SQL_TEXT ---------- ---------- ---------- -------------------- SCOTT 0 CREATE TAB create table a1(id n LE umber)
SCOTT 955 CREATE TAB create table a1(id n LE umber)
SCOTT 955 CREATE TAB create table a2(id n LE umber)
HR 0 SESSION RE select * from scott. C dept
USERNAME RETURNCODE ACTION_NAM SQL_TEXT ---------- ---------- ---------- --------------------
HR 0 SESSION RE select * from scott. C emp
HR 0 SESSION RE select * from scott. C dept
HR 0 SESSION RE select * from scott.
USERNAME RETURNCODE ACTION_NAM SQL_TEXT ---------- ---------- ---------- -------------------- C dept
7 rows selected.
SQL> 如果想记录多条 SQL> noaudit select on scott.dept;
Noaudit succeeded.
SQL> 加参数 by access 按照访问来审计 SQL> audit select on scott.dept by access;
Audit succeeded.
SQL> 再次查询表sdet [oracle@yang admin]$ sqlplus hr/hr
SQL*Plus: Release 10.2.0.1.0 - Production on Tue May 6 05:41:13 2014
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production With the Partitioning, OLAP and Data Mining options
SQL> select * from scott.dept where deptno=10;
DEPTNO DNAME LOC ---------- -------------- ------------- 10 ACCOUNTING NEW YORK
SQL> select * from scott.dept where deptno=20;
DEPTNO DNAME LOC ---------- -------------- ------------- 20 RESEARCH DALLAS
SQL> 查看审计信息 SQL> select username,returncode,action_name,sql_text from dba_audit_trail;
USERNAME RETURNCODE ACTION_NAM SQL_TEXT ---------- ---------- ---------- -------------------- SCOTT 0 CREATE TAB create table a1(id n LE umber)
SCOTT 955 CREATE TAB create table a1(id n LE umber)
SCOTT 955 CREATE TAB create table a2(id n LE umber)
HR 0 SESSION RE select * from scott. C dept
USERNAME RETURNCODE ACTION_NAM SQL_TEXT ---------- ---------- ---------- --------------------
HR 0 SESSION RE select * from scott. C emp
HR 0 SESSION RE select * from scott. C dept
HR 0 SESSION RE select * from scott.
USERNAME RETURNCODE ACTION_NAM SQL_TEXT ---------- ---------- ---------- -------------------- C dept
HR 0 SELECT select * from scott. dept where deptno=10
HR 0 SELECT select * from scott. dept where deptno=20
9 rows selected.
SQL> |
对session 审计 只要登陆到会话 做的任何操作都会记录下来
|
SQL> audit session;
Audit succeeded.
SQL>
[oracle@yang admin]$ sqlplus scott/tiger
SQL*Plus: Release 10.2.0.1.0 - Production on Tue May 6 05:53:52 2014
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production With the Partitioning, OLAP and Data Mining options
SQL> update emp set sal=sal+1 where empno=7844;
1 row updated.
SQL> commit;
Commit complete.
SQL> 查看审计信息 SQL> select username,returncode,action_name,sql_text from dba_audit_trail;
USERNAME RETURNCODE ACTION_NAM SQL_TEXT ---------- ---------- ---------- -------------------- SCOTT 0 CREATE TAB create table a1(id n LE umber)
SCOTT 955 CREATE TAB create table a1(id n LE umber)
SCOTT 955 CREATE TAB create table a2(id n LE umber)
HR 0 SESSION RE select * from scott. C dept
USERNAME RETURNCODE ACTION_NAM SQL_TEXT ---------- ---------- ---------- --------------------
HR 0 SESSION RE select * from scott. C emp
HR 0 SESSION RE select * from scott. C dept
HR 0 SESSION RE select * from scott.
USERNAME RETURNCODE ACTION_NAM SQL_TEXT ---------- ---------- ---------- -------------------- C dept
HR 0 SELECT select * from scott. dept where deptno=10
HR 0 SELECT select * from scott. dept where deptno=20
HR 0 LOGOFF HR 0 SELECT select * from scott. dept
USERNAME RETURNCODE ACTION_NAM SQL_TEXT ---------- ---------- ---------- --------------------
SCOTT 0 LOGON
12 rows selected.
SQL> |
删除审计信息
|
SQL> select * from aud$ where obj$name='EMP'; SQL> select * from aud$ where obj$name='DEPT'; 可以将不用的审计信息删除 SQL> delete aud$ where obj$name='DEPT';
6 rows deleted.
SQL> SQL> delete aud$ where obj$name='EMP';
2 rows deleted.
SQL> 再次查询还是有很多信息 SQL> select * from aud$ ; 如果想要删除所有的信息可以 SQL> truncate table aud$;
Table truncated. 在查看审计相关记录 SQL> select * from aud$ ;
no rows selected
SQL> select username,returncode,action_name,sql_text from dba_audit_trail;
no rows selected
SQL> SQL> select audit_type,db_user,object_name,statement_type,sql_text,os_privilege from dba_common_audit_trail;
no rows selected
SQL> |
正确的关闭审计 修改参数
|
SQL> show parameter audit_trail
NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ audit_trail string DB, EXTENDED SQL> SQL> alter system set audit_trail=none scope=spfile;
System altered.
SQL>
|
演示 FGA审计
如果对scott用户下的emp表中的ename字段进行insert 的长度大于5字节时 做审计
|
授予权限给scott用户 SQL> grant execute on dbms_fga to scott;
Grant succeeded.
SQL> begin dbms_fga.add_policy (object_schema => 'scott', object_name => 'emp', policy_name => 'emp_ename_length', audit_condition => 'length(ename)>5', enable => True, audit_trail => dbms_fga.DB_EXTENDED, statement_types => 'insert'); end; / PL/SQL procedure successfully completed.
SQL> 向表中插入数据 [oracle@yang tmp]$ sqlplus scott/tiger
SQL*Plus: Release 10.2.0.1.0 - Production on Tue May 6 07:45:05 2014
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production With the Partitioning, OLAP and Data Mining options
SQL> insert into emp(empno,ename,sal) values(200,'A123456',4000);
1 row created.
SQL> commit;
Commit complete.
SQL> insert into emp(empno,ename,sal) values(201,'A123',3000);
1 row created.
SQL> commit;
Commit complete.
SQL>
查询审计信息 SQL> select db_user,object_schema,object_name,sql_text from dba_fga_audit_trail;
DB_USER OBJECT_SCHEMA OBJECT_NAME SQL_TEXT ---------- ------------------------------ -------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------- SCOTT SCOTT EMP insert into emp(empno,ename,sal) values( 200,'A123456',4000)
Ename列超过5个字节 的记录被审计 SQL> |
FGA审计的启用、禁用与删除
授予scott用户SELECT_CATALOG_ROLE角色权限
|
查询user_audit_policies视图 SQL> show user USER is "SCOTT" SQL> SQL> desc user_audit_policies ERROR: ORA-04043: object "SYS"."USER_AUDIT_POLICIES" does not exist
SQL>
SQL> GRANT SELECT_CATALOG_ROLE TO SCOTT;
Grant succeeded.
SQL> SQL> set role SELECT_CATALOG_ROLE;
Role set. 重新建立会话查询视图 SQL> [oracle@yang tmp]$ sqlplus scott/tiger
SQL*Plus: Release 10.2.0.1.0 - Production on Tue May 6 08:10:39 2014
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production With the Partitioning, OLAP and Data Mining options
SQL> desc user_audit_policies Name Null? Type ----------------------------------------- -------- ---------------------------- OBJECT_NAME NOT NULL VARCHAR2(30) POLICY_NAME NOT NULL VARCHAR2(30) POLICY_TEXT VARCHAR2(4000) POLICY_COLUMN VARCHAR2(30) PF_SCHEMA VARCHAR2(30) PF_PACKAGE VARCHAR2(30) PF_FUNCTION VARCHAR2(30) ENABLED VARCHAR2(3) SEL VARCHAR2(3) INS VARCHAR2(3) UPD VARCHAR2(3) DEL VARCHAR2(3) AUDIT_TRAIL VARCHAR2(12) POLICY_COLUMN_OPTIONS VARCHAR2(11)
SQL> 查询状态 （在sysdba用户下可以用dba_audit_policies视图查看） SQL> select policy_name,enabled,policy_text from user_audit_policies;
POLICY_NAME ENA ------------------------------ --- POLICY_TEXT -------------------------------------------------------------------------------- EMP_ENAME_LENGTH YES length(ename)>5
SQL> 禁用策略 SQL> exec dbms_fga.disable_policy(object_schema => 'scott',object_name=>'emp',policy_name=>'emp_ename_length');
PL/SQL procedure successfully completed.
SQL> select policy_name,enabled,policy_text from user_audit_policies;
POLICY_NAME ENA ------------------------------ --- POLICY_TEXT -------------------------------------------------------------------------------- EMP_ENAME_LENGTH NO length(ename)>5
SQL> 启动策略 SQL> exec dbms_fga.enable_policy(object_schema => 'scott',object_name=>'emp',policy_name=>'emp_ename_length');
PL/SQL procedure successfully completed.
SQL> SQL> select policy_name,enabled,policy_text from user_audit_policies;
POLICY_NAME ENA ------------------------------ --- POLICY_TEXT -------------------------------------------------------------------------------- EMP_ENAME_LENGTH YES length(ename)>5
SQL> 删除策略 SQL> exec dbms_fga.drop_policy(object_schema => 'scott',object_name=>'emp',policy_name=>'emp_ename_length');
PL/SQL procedure successfully completed.
SQL> |
FGA 审计星期六与星期日 访问emp表的例子
|
首先要找到星期六与星期日是什么字符表示的
SQL> select to_char(sysdate+4,'dy') from dual;
TO_CHA ------ sat
SQL> select to_char(sysdate+5,'dy') from dual;
TO_CHA ------ sun
SQL> 创建策略 [oracle@yang tmp]$ vi w1.sql
begin dbms_fga.add_policy (object_schema => 'scott', object_name => 'emp', policy_name => 'emp_select', audit_condition => 'to_char(sysdate,''dy'') in (''sat'',''sun'')', enable => True, audit_trail => dbms_fga.DB_EXTENDED, statement_types => 'select'); end; / SQL> @/tmp/w1.sql
PL/SQL procedure successfully completed.
SQL> SQL> select policy_name,enabled,policy_text from user_audit_policies;
POLICY_NAME ENA ------------------------------ --- POLICY_TEXT -------------------------------------------------------------------------------- EMP_SELECT YES to_char(sysdate,'dy') in ('sat,'sun')
SQL> 修改系统时间到星期六 [root@yang ~]# date -s 05/10/2014 Sat May 10 00:00:00 CST 2014 [root@yang ~]# date -s 16:46 Sat May 10 16:46:00 CST 2014 [root@yang ~]# clock -w [root@yang ~]# date Sat May 10 16:46:11 CST 2014 [root@yang ~]# 重建连接到scott用户查看emp表 [oracle@yang tmp]$ sqlplus scott/tiger
SQL*Plus: Release 10.2.0.1.0 - Production on Sat May 10 17:46:19 2014
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production With the Partitioning, OLAP and Data Mining options
SQL> select * from emp; 查看审计信息 SQL> select db_user,object_schema,object_name,sql_text from dba_fga_audit_trail;
DB_USER OBJECT_SCHEMA ------------------------------ ------------------------------ OBJECT_NAME -------------------------------------------------------------------------------- SQL_TEXT -------------------------------------------------------------------------------- SCOTT SCOTT EMP insert into emp(empno,ename,sal) values(200,'A123456',4000)
SCOTT SCOTT EMP select * from emp
DB_USER OBJECT_SCHEMA ------------------------------ ------------------------------ OBJECT_NAME -------------------------------------------------------------------------------- SQL_TEXT --------------------------------------------------------------------------------
SCOTT SCOTT EMP select * from emp
SCOTT SCOTT EMP
DB_USER OBJECT_SCHEMA ------------------------------ ------------------------------ OBJECT_NAME -------------------------------------------------------------------------------- SQL_TEXT -------------------------------------------------------------------------------- select * from emp
SQL> |
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29532781/viewspace-1174614/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/29532781/viewspace-1174614/
3825
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
