Shulou(Shulou.com)06/01 Report--

Oracle uses the audit_trail parameter to control whether auditing is enabled

The parameters of audit_trail are as follows:

NONE: do not enable audit

OS: indicates that the audit information is put in the system summary. If it is Linux, then it is decided by audit_file_dest. If it is Windows, then it is decided by the event viewer.

DB or TRUE: indicates that the audit information is stored in the database, that is, the aud$ table of the sys user.

The meaning of the audit_sys_operations parameter:

False: do not audit sys users. Default is not audit.

True: audit sys users

The audit scope is divided into session and access.

Session: indicates that the same SQL executed by the user after login is recorded only once, and the other same SQL is no longer recorded

Access: indicates that each executed SQL has an audit record.

Refer to the official documents in detail

Http://docs.oracle.com/cd/B19306_01/network.102/b14266/cfgaudit.htm#CIHDICID

1. Enable audit parameters

SQL > show parameter audit NAME TYPE VALUE--- audit_file_dest String / u01/app/oracle/admin/mydb/adumpaudit_sys_operations boolean FALSEaudit_syslog_level stringaudit_trail string NONE SQL > alter system setaudit_trail=db Extended scope=spfile

2. Restart the database

Static parameters, in order for the parameters to take effect, you need to restart the database

SQL > shutdown immediate;Database closed.Database dismounted.ORACLE instance shut down.SQL > startupORACLE instance started. Total System Global Area 599785472 bytesFixed Size 2085776 bytesVariable Size 192941168 bytesDatabase Buffers 398458880 bytesRedo Buffers 6299648 bytesDatabase mounted.Database opened.SQL > show parameter audit NAME TYPE VALUE- -audit_file_dest string / u01/app/oracle/admin/mydb/adumpaudit_sys_operations boolean FALSEaudit_syslog_level stringaudit_trail string DB EXTENDED

3. Set up to audit the table

In this way, every time a user operates on the table, a corresponding record is added to the aud$, and Oracle creates a view to make it easier to read the data.

Although each user's actions on the table are recorded, the actions of the sys user are not recorded, and all other users do.

SQL > conn / as sysdbaConnected.SQL > audit all on zx.num_t by accesswhenever successful; Audit succeeded. SQL > set linesize 200SQL > select * from dba_obj_audit_opts OWNER OBJECT_NAME OBJECT_TYPE ALT AUD COM DEL GRA IND INS LOC REN SEL UPD REF EXE CRE REA WRI FBK -ZX NUM_T TABLE A-A

In the previous column

A stands for access, and each audited operation will be recorded. For example, if the select audit of scott.emp is enabled, then anyone select this table will trigger an audit and record it in the aud$.

S stands for session, and the audited operation of each session is recorded once.

Use different users to access the zx.num_t table differently:

SQL > conn zx/zxConnected.SQL > select count (*) from zx.num_t; COUNT (*)-0SQL > insert into zx.num_t (id1) values (1); 1 row created.SQL > commit;Commit complete.SQL > conn scott/tigerConnected.SQL > select count (*) from zx.num_t; COUNT (*)-1SQL > delete from zx.num_t;1 row deleted.SQL > commit Commit complete.SQL > insert into zx.num_t (id2) values (2), 1 row created.SQL > rollback;Rollback complete.

4. Inquire about audit records

SQL > alter session setnls_date_format='yyyymmdd hh34:mi:ss'; Session altered. SQL > set lines 200col OS_USERNAME for a10col USERNAME for a11col USERHOST for a10col TERMINAL for a10col TIMESTAMP for a20col obj_name for a10col OWNER for a10col ACTION_NAME for a11col TRANSACTIONID for a16col sql_text for a50SELECT USERNAME, USERHOST, TIMESTAMP, OWNER, OBJ_NAME, ACTION_NAME, SQL_TEXT FROM DBA_AUDIT_TRAIL WHERE OBJ_NAME='NUM_T' ORDER BY TIMESTAMP USERNAME USERHOST TIMESTAMP OWNER OBJ_NAME ACTION_NAME SQL_TEXT-- -- ZX rhel5 20161107 11:57:55 ZX NUM_T NOAUDIT OBJ noaudit all on num_t ECT ZX rhel5 20161107 12:00:07 ZX NUM_T SELECT select count (*) from zx.num_tZX rhel5 20161107 12:00:21 ZX NUM_T INSERT insert into zx.num_t (id1) values (1) SCOTT rhel5 20161107 12:00:37 ZX NUM_T SELECT select count (*) from zx.num_tSCOTT rhel5 20161107 12:00:45 ZX NUM_T DELETE delete from zx.num_tSCOTT rhel5 20161107 12:01:27 ZX NUM_T INSERT insert into zx.num_t (id2) values (2) 6 rows selected.

5. Cancel the audit

SQL > noaudit all on num_t;Noaudit succeeded.

6. Clear aud$

This system table can be truncated using the TRUNCATE command. After you delete it, the record in the view disappears accordingly.

SQL > truncate table aud$;SQL > SELECT * FROM DBA_FGA_AUDIT_TRAIL; no rows selected

Refer to the official documentation for more details.

Http://docs.oracle.com/cd/B19306_01/network.102/b14266/cfgaudit.htm#BABCFIHB

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.