oracle审计功能复习进程.doc

Good is good, but better carries it.精益求精，善益求善。oracle审计功能-ORACLE审计功能测试功能介绍使用10g的审计功能，可以指定审计某一用户的操作，对某对象的指定操作审计等操作测试步骤1）审计的细节设定参数audit_trail配置选项none|os|db|db,extended|xml|xml,extendednone不打开审计db_extended打开并记录sql_text和sql_bandxml记录到xml文件xml记录到xml并记录sql_text和sql_band-打开审计并记录sqltextsysORCL>altersystemsetaudit_trail=db_extendedscope=spfile;-重启数据库sysORCL>shutdownimmediate;sysORCL(128.192.128.1)>startupORACLEinstancestarted.TotalSystemGlobalArea1895825408bytesFixedSize2046296bytesVariableSize1157629608bytesDatabaseBuffers721420288bytesRedoBuffers14729216bytes设置对用户的审计sysORCL>auditallbyzbtestbyaccess;ZBTEST用户操作测试sysORCL>connzbtest/zbtestConnected.sysORCL)>insertintozbtestt1values(1);1rowcreated.ORCL>createtablezbtestt2(c1number);Tablecreated.sysORCL(128.192.128.1)>deletefromzbtestt2;0rowsdeleted.查看是否成功抓取SQLselectusername,sql_textfromdba_audit_trail;USERNAMESQL_TEXT-ZBTESTinsertintozbtestt1values(1)ZBTESTcreatetablezbtestt2(c1number)ZBTESTdeletefromzbtestt2取消对用户的统计noauditallbyzbtest其他功能分析也可以用dbms_fga.add_policy进行更加灵活的审计ORCL>conn/assysdbaConnected.execdbms_fga.add_policy(object_schema=>'ZBTEST',object_name=>'zbtestt1',policy_name=>'check_t_audit',statement_types=>'INSERT,UPDATE,DELETE,SELECT');PL/SQLproceduresuccessfullycompleted.解释：Object_schema:用户名Object_name:对象名Policy_name:策略名Statemenet_types:对哪种操作进行审计2）对t_audit表增删改查操作一番ORCL>connzbtest/zbtestConnected.secora10g>select*fromzbtestt1;X-2345676rowsselected.ORCL>deletefromzbtestt1wherex=5;1rowdeleted.ORCL>updatet_zbtestt1setx=8wherex=7;1rowupdated.ORCL>insertintot_zbtestt1values(1);1rowcreated.ORCL>commit;Commitcomplete.4）详细查看一下对应的SQL操作，FGA还是很强悍的ORCL>colDB_USERfora10ORCL>colSQL_TEXTfora50ORCL>selectdb_user,sql_textfromdba_fga_audit_trail;DB_USERSQL_TEXT-SECselect*fromzbtestt1SECdeletefromzbtestt1wherex=5SECupdatezbtestt1setx=8wherex=7SECinsertintozbtestt1values(1)针对上面添加的审计策略进行调整：disable_policy、enable_policy和drop_policy的方法使策略失效的方法：ORCL>execdbms_fga.disable_policy(object_schema=>'ZBTEST',object_name=>'zbtestt1',policy_name=>'check_t_audit');使策略生效的方法：ORCL>execdbms_fga.enable_policy(object_schema=>'ZBTEST',object_name=>'zbtestt1',policy_name=>'check_t_audit');彻底删除策略的方法：ORCL>execdbms_fga.drop_policy(object_schema=>'ZBTEST',object_name=>'zbtestt1',policy_name=>'check_t_audit');PL/SQLproceduresuccessfullycompleted.-