Script to Show Audit Options/Audit Trail (Doc ID 1019552.6)
Script to Show Audit Options/Audit Trail (Doc ID 1019552.6)
Abstract | |
---|---|
Show Audit Options/Audit Trail and audited system privileges | |
Product Name, Product Version |
Oracle Server Enterprise Edition
|
Platform | Generic |
Creation Date | 29-OCT-2002 |
Check Date | ***Checked for relevance on 21-Feb-2013*** |
Instructions | |
You can run TFSAUDIT.SQL when you have the following privileges: SELECT on DBA_OBJ_AUDIT_OPTS, DBA_STMT_AUDIT_OPTS, DBA_AUDIT_TRAIL and DBA_PRIV_AUDIT_OPTS. PROOFREAD THIS SCRIPT BEFORE USING IT! Due to differences in the way text editors, e-mail packages, and operating systems handle text formatting (spaces, tabs, and carriage returns), this script may not be in an executable state when you first receive it. Check over the script to ensure that errors of this type are corrected. |
|
Description | |
This script will show you what is being audited in the database and will provide the audit trail. | |
References | |
Note.1020945.6 How to Setup Auditing Note.167293.1 Some examples about auditing and output of auditing Note.41800.1 Quick Reference to Auditing Information Note.222807.1 How To Audit GRANT ANY PRIVILEGE Or GRANT ANY ROLE |
|
Script | |
SET ECHO off REM NAME: TFSAUDIT.SQL REM USAGE:"@path/tfsaudit" REM -------------------------------------------------------------------------- REM REQUIREMENTS: REM SELECT ON DBA_OBJ_AUDIT_OPTS, DBA_STMT_AUDIT_OPTS, DBA_AUDIT_TRAIL REM and DBA_PRIV_AUDIT_OPTS REM -------------------------------------------------------------------------- REM AUTHOR: REM Geert De Paep - Oracle Belgium REM -------------------------------------------------------------------------- REM PURPOSE: REM see what is being audited in the database, and to see the audit_trail REM ------------------------------------------------------------------------- REM DISCLAIMER: REM This script is provided for educational purposes only. It is NOT REM supported by Oracle World Wide Technical Support. REM The script has been tested and appears to work as intended. REM You should always run new scripts on a test instance initially. REM -------------------------------------------------------------------------- REM Main text of script follows: col user_name for a12 heading "User name" col proxy_name for a12 heading "Proxy name" col privilege for a30 heading "Privilege" col user_name for a12 heading "User name" col audit_option format a30 heading "Audit Option" col timest format a13 col userid format a8 trunc col obn format a10 trunc col name format a13 trunc col sessionid format 99999 col entryid format 999 col owner format a10 col object_name format a10 col object_type format a6 col priv_used format a15 trunc break on user_name set pages 1000 set pause 'Return...' pause Press return to see the audit related parameters... col name for a20 col display_value for a20 SELECT NAME ,DISPLAY_VALUE FROM V$PARAMETER WHERE UPPER(NAME) LIKE UPPER('%audit%') ORDER BY NAME,ROWNUM / prompt prompt System auditing options across the system and by user select * from sys.dba_stmt_audit_opts order by user_name, proxy_name, audit_option / pause Press return to see auditing options on all objects... select owner, object_name, object_type, alt,aud,com,del,gra,ind,ins,loc,ren,sel,upd,ref,exe from sys.dba_obj_audit_opts where alt !='-/-' or aud !='-/-' or com !='-/-' or del !='-/-' or gra !='-/-' or ind !='-/-' or ins !='-/-' or loc !='-/-' or ren !='-/-' or sel !='-/-' or upd !='-/-' or ref !='-/-' or exe !='-/-' / pause Press return to see audit trail... Note that the query returns the audit data for the last day only col acname format a12 heading "Action name" select username userid, to_char(timestamp,'dd-mon hh24:mi') timest , action_name acname, priv_used, obj_name obn, ses_actions from sys.dba_audit_trail where timestamp>sysdate-1 order by timestamp / pause Press return to see system privileges audited across the system and by user... select * from dba_priv_audit_opts order by user_name, proxy_name, privilege / |
|
Sample Output | |
SQL> @tfsaudit System auditing options across the system and by user User name Proxy name Audit Option SUCCESS FAILURE ------------ ------------ ------------------------------ ---------- ---------- SCOTT ALTER ANY CLUSTER BY ACCESS BY ACCESS ALTER ANY DIMENSION BY ACCESS BY ACCESS ALTER ANY INDEX BY ACCESS BY ACCESS ... 129 rows selected. Press return to see auditing options on all objects... OWNER OBJECT_NAM OBJECT ALT AUD COM DEL GRA IND INS LOC REN SEL UPD REF EXE ---------- ---------- ------ --- --- --- --- --- --- --- --- --- --- --- --- --- CTXSYS SYS_IOT_OV TABLE / / / / / / / / / / / / / ER_24695 CTXSYS SYS_IOT_OV TABLE / / / / / / / / / / / / / ER_24786 Press return to see audit trail... USERID TIMEST Action name PRIV_USED OBN SES_ACTIONS -------- ------------- ------------ --------------- ---------- ------------------- SYSTEM 20-jun 11:07 SYSTEM GRANT GRANT ANY ROLE SYSTEM 20-jun 11:07 SYSTEM GRANT SYSTEM 20-jun 11:07 SYSTEM GRANT ... 19 rows selected. Press return to see system privileges audited across the system and by user... User name Proxy name Privilege SUCCESS FAILURE ------------ ------------ ------------------------------ ---------- ---------- SCOTT ADMINISTER DATABASE TRIGGER BY ACCESS BY ACCESS ADMINISTER RESOURCE MANAGER BY ACCESS BY ACCESS ALTER ANY CLUSTER BY ACCESS BY ACCESS ... 118 rows selected. |
|
Disclaimer | |
EXCEPT WHERE EXPRESSLY PROVIDED OTHERWISE, THE INFORMATION, SOFTWARE, PROVIDED ON AN "AS IS" AND "AS AVAILABLE" BASIS. ORACLE EXPRESSLY DISCLAIMS ALL WARRANTIES OF ANY KIND, WHETHER EXPRESS OR IMPLIED, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND NON-INFRINGEMENT. ORACLE MAKES NO WARRANTY THAT: (A) THE RESULTS THAT MAY BE OBTAINED FROM THE USE OF THE SOFTWARE WILL BE ACCURATE OR RELIABLE; OR (B) THE INFORMATION, OR OTHER MATERIAL OBTAINED WILL MEET YOUR EXPECTATIONS. ANY CONTENT, MATERIALS, INFORMATION OR SOFTWARE DOWNLOADED OR OTHERWISE OBTAINED IS DONE AT YOUR OWN DISCRETION AND RISK. ORACLE SHALL HAVE NO RESPONSIBILITY FOR ANY DAMAGE TO YOUR COMPUTER SYSTEM OR LOSS OF DATA THAT RESULTS FROM THE DOWNLOAD OF ANY CONTENT, MATERIALS, INFORMATION OR SOFTWARE. ORACLE RESERVES THE RIGHT TO MAKE CHANGES OR UPDATES TO THE SOFTWARE AT ANY TIME WITHOUT NOTICE. |
|
Limitation of Liability | |
IN NO EVENT SHALL ORACLE BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL OR CONSEQUENTIAL DAMAGES, OR DAMAGES FOR LOSS OF PROFITS, REVENUE, DATA OR USE, INCURRED BY YOU OR ANY THIRD PARTY, WHETHER IN AN ACTION IN CONTRACT OR TORT, ARISING FROM YOUR ACCESS TO, OR USE OF, THE SOFTWARE. SOME JURISDICTIONS DO NOT ALLOW THE LIMITATION OR EXCLUSION OF LIABILITY. ACCORDINGLY, SOME OF THE ABOVE LIMITATIONS MAY NOT APPLY TO YOU. |
|
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/17252115/viewspace-1136254/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- FGA審計及audit_trail引數AI
- Camstar MDB setfieldex 修改建模欄位不記錄Audit TrailAI
- ORACLE AUDITOracle
- mysqlalchemy audit extensionMySql
- audit by user by table
- Oracle Audit setupOracle
- AUDIT審計(2)
- MySQL審計auditMySql
- fluentd 推送 mariadb audit log
- ORACLE AUDIT審計(1)Oracle
- 2.2.6 Overview of Common Audit ConfigurationsView
- linux監控工具auditLinux
- SQL Server 審計(Audit)SQLServer
- [20190530]oracle Audit檔案管理.txtOracle
- 【AUDIT]Oracle審計配置及常用sqlOracleSQL
- MySQL審計外掛-MariaDB Audit PluginMySqlPlugin
- [20191129]oracle Audit檔案管理3.txtOracle
- [20191128]oracle Audit檔案管理2.txtOracle
- [20191203]enq: ZA - add std audit table partition.txtENQ
- mysql 5.7新增server_audit 安全審計功能MySqlServer
- Oracle Audit 審計功能的認識與使用Oracle
- Disable Database Audit In Oracle 19c RAC-20220111DatabaseOracle
- Error processing condition on org.springframework.boot.actuate.autoconfigure.audit.AuditEventsEndpoiErrorSpringFrameworkboot
- PostgreSQL DBA(148) - pgAdmin(Show script for psql command)SQL
- [20191128]11GR2 asm例項audit檔案.txtASM
- [20230306]學習UNIFIED audit--dg相關問題.txtNifi
- Oracle 最新PSU (Doc ID 2118136.2)Oracle
- Oracle 標準審計,設定AUDIT_SYSLOG _LEVEL引數Oracle
- [20230301]學習UNIFIED audit-整理AUDSYS.AUD$UNIFIED.txtNifi
- expdp/impdp變慢 (Doc ID 2469587.1)
- Reading and Understanding Systemstate Dumps (Doc ID 423153.1)
- [20230303]學習UNIFIED audit--定期清理AUDSYS.AUD$UNIFIED.txtNifi
- RMAN -- Frequently Asked Question (FAQ) (Doc ID 469777.1)
- Oracle 下載最新psu (Doc ID 2118136.2)Oracle
- [20230403]學習UNIFIED audit--驗證清理AUDSYS.AUD$UNIFIED.txtNifi
- [20210420]ORA-00904 REF invalid identifier 19c dba_obj_audit_opts.txtIDEOBJ
- PRCT-1011 : Failed to run "oifcfg" (Doc ID 1380183.1)AI
- How to Collect Diagnostics for Database Hanging Issues (Doc ID 452358.1)Database
- [轉帖]Release Schedule of Current Database Releases (Doc ID 742060.1)Database