ITPub博客

首页 > Linux操作系统 > Linux操作系统 > [实验]DB,DB_EXTENDED类型的数据库审核

[实验]DB,DB_EXTENDED类型的数据库审核

原创 Linux操作系统 作者:highclous 时间:2011-07-30 10:32:21 0 删除 编辑
1 准备
以system身份连接
create user auditor identified by oracle;
create table system.audi as select * from all_users;
grant create session,select any table to auditor;
grant select on audi to auditor;
创建一些审核权限
audit select any table by access;
audit all on system.audi by access;

2 审核有关的参数
对audit_trail和audit_sys_operations进行设定。这两个参数都是静态参数,故需要重新启动实例。
audit_trail 审核跟踪目标
audit_sys_operations 是否启用权限用户审核

3 小插曲==》有关spfile

-----------
SCOPE参数有三个可选值:MEMORY:只改变当前实 例运行;SPFILE:只改变SPFILE的设置(如果修改的是静态参数,则必须指定SCOPE=SPFILE,否则将会报ORA-02095 错。);BOTH:改变实例及SPFILE(使用BOTH选项实际上等同于不带参数的ALTER SYSTEM语句)。
by
http://blog.csdn.net/aten_xie/article/details/6174097
-----------

另外,spfile文件的默认位置
SQL> show parameter spfile;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      /u01/app/oracle/product/11.2.0
                                                 /db_1/dbs/spfileocp11g.ora
如果是集群系统,需要更改每一台节点的参数
更好的办法应该是,在pfile文件中指定spfile的位置,而这个位置在共享的存储空间内。

-----------

Oracle启动调用pfilespfile顺序:

Oracle启动的时候可以指定pfile进行启动(startup pfile=’’;),如果不指定pfile,则oracle自动会找寻spfile进行启动。所以完整的顺序就是:


1)指定的pfile->2)若指定pfile里有指定的spfileSPFILE'XXXXXXX/rspfile'-〉(3)再去找该spfile-〉没有就结束-〉(4)若没有指定pfile就去默认路径找spfile.ora-〉(5 没有就去找 spfile.ora-〉(6initSID.ora->如果都没有就报错。

by
http://blog.csdn.net/aten_xie/article/details/6174097
-----------

4 DB审核
(1)修改参数
alter system set audit_trail='DB' scope=spfile;
alter system set audit_sys_operations=true scope=spfile;
(starup force)

(2)重启后,以sys连接
SQL> select  count(*) from system.audi;
  COUNT(*)
----------
        37
SQL> show parameter audit_file_dest
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest                      string      /u01/app/oracle/admin/ocp11g/adump
SQL> host
[oracle@*** ~]$ cd /u01/app/oracle/admin/ocp11g/adump/
[oracle@*** adump]$ ls -lt -u
总计 972
-rw-r----- 1 oracle oinstall 2377 07-30 09:35 ocp11g_ora_2392_1.aud
-rw-r----- 1 oracle oinstall  959 07-30 09:35 ocp11g_ora_2203_1.aud
......

查看ocp11g_ora_2392_1.aud文件,其中有一条:
Sat Jul 30 09:36:09 2011 +08:00
LENGTH : '187'
ACTION :[33] 'select  count(*) from system.audi'
DATABASE USER:[1] '/'
PRIVILEGE :[6] 'SYSDBA'
CLIENT USER:[6] 'oracle'
CLIENT TERMINAL:[5] 'pts/3'
STATUS:[1] '0'
DBID:[10] '3527866229'

(3)以auditor身份连接
SQL> conn auditor/oracle
Connected.
SQL> select count(*) from system.product_user_profile;
  COUNT(*)
----------
         0
SQL> select count(*) from system.audi;
  COUNT(*)
----------
        37
SQL> select sql_text,priv_used,action_name from dba_audit_trail where username='AUDITOR';
select sql_text,priv_used,action_name from dba_audit_trail where username='AUDITOR'
                                           *
ERROR at line 1:
ORA-00942: table or view does not exist
哈哈,自己没有权限查看。
待我以system进入
SQL_TEXT
--------------------------------------------------------------------------------
PRIV_USED                                ACTION_NAME
---------------------------------------- ----------------------------

                                         SELECT


                                         SELECT


CREATE SESSION                           LOGON


SQL_TEXT
--------------------------------------------------------------------------------
PRIV_USED                                ACTION_NAME
---------------------------------------- ----------------------------

SELECT ANY TABLE                         SELECT


                                         LOGOFF

5 DB_EXTENDED审核
(1) alter system set audit_trail='DB_EXTENDED' scope=spfile;
(2) sys用户
SQL> conn / as sysdba
Connected.
SQL> select count(*) from system.audi;

  COUNT(*)
----------
        37

日志:
Sat Jul 30 10:17:31 2011 +08:00
LENGTH : '186'
ACTION :[32] 'select count(*) from system.audi'
DATABASE USER:[1] '/'
PRIVILEGE :[6] 'SYSDBA'
CLIENT USER:[6] 'oracle'
CLIENT TERMINAL:[5] 'pts/3'
STATUS:[1] '0'
DBID:[10] '3527866229'

貌似对sys的审核木有什么变化哈
(3) auditor用户
select count(*) from system.product_user_profile;
select count(*) from system.product_user_profile;
SQL> select sql_text,priv_used,action_name from dba_audit_trail where username='AUDITOR';

SQL_TEXT
--------------------------------------------------------------------------------
PRIV_USED                                ACTION_NAME
---------------------------------------- ----------------------------
select count(*) from system.product_user_profile
SELECT ANY TABLE                         SELECT


SELECT ANY TABLE                         SELECT


CREATE SESSION                           LOGON


SQL_TEXT
--------------------------------------------------------------------------------
PRIV_USED                                ACTION_NAME
---------------------------------------- ----------------------------

CREATE SESSION                           LOGON


                                         LOGOFF


                                         LOGOFF


SQL_TEXT
--------------------------------------------------------------------------------
PRIV_USED                                ACTION_NAME
---------------------------------------- ----------------------------
select count(*) from system.audi
                                         SELECT


                                         SELECT


                                         SELECT
对用户的审核是有了sql语句了

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/20399666/viewspace-703591/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2011-07-27

  • 博文量
    11
  • 访问量
    14424