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

Audit mechanism is one of the important parts of security management of DM database management system. DM database not only provides data security protection measures, but also provides post-audit supervision of daily events. DM has a flexible audit subsystem that allows you to record system-level events, individual user behavior, and access to database objects. By investigating and tracking audit information, database auditors can view the form of user access and the operations they have tried to do to the system, so as to take positive and effective measures.

Audit switch

In the DM system, the switch is specially set for the audit. In order to use the audit function, the audit switch must be turned on first. The audit switch is controlled by the INI parameter ENABLE_AUDIT of DM and has three values:

0: turn off audit

1: open general audit

2: open general audit and real-time audit

In the normal version, the default value of ENABLE_AUDIT is 0; in the secure version, the default value of ENABLE_AUDIT is 2.

The audit switch must be set by an administrator with DBA privileges.

The system administrator can query the current value of V$PARAMETER by querying the ENABLE_AUDIT dynamic view.

SQL > select * from v$parameter where name='ENABLE_AUDIT' LINEID ID NAME TYPE VALUE SYS_VALUE FILE_VALUE DESCRIPTION- -- 1 385 ENABLE_AUDIT SYS 000 Flag For Allowing Audit 0: no audit 1: normal audit 2:normal audit and realtime auditused time: 8.170 (ms). Execute id is 24948.SQL > sp_set_para_value, DMSQL executed successfullyused time: 80.901 (ms). Execute id is 24968.SQL > select * from v$parameter where name='ENABLE_AUDIT' LINEID ID NAME TYPE VALUE SYS_VALUE FILE_VALUE DESCRIPTION- -- 1 385 ENABLE_AUDIT SYS 2 22 Flag For Allowing Audit 0: no audit 1: normal audit 2:normal audit and realtime auditused time: 6.829 (ms). Execute id is 24969.SQL > select * from v$dm_ini where para_name='ENABLE_AUDIT' LINEID PARA_NAME PARA_VALUE MIN_VALUE MAX_VALUE MPP_CHK SESS_VALUE FILE_VALUE DESCRIPTION PARA_TYPE-- - -1 ENABLE_AUDIT 2 02 N 2 2 Flag For Allowing Audit 0: no audit 1: normal audit 2:normal audit and realtime audit SYSused time: 7.090 (ms). Execute id is 24970.SQL >

You can also reset the value of ENABLE_AUDIT by using the client tool Console or by calling the system procedure SP_SET_PARA_VALUE, where ENABLE_AUDIT is a dynamic INI parameter.

Setting and cancellation of audit

Database auditors specify that the activities of the audited objects are called audit settings, and only auditors with AUDIT DATABASE privileges can make audit settings. DM provides an audit setup system process to achieve this setting, and the audited object can be a certain type of operation or the entire whereabouts of some users in the database. Only preset operations and users can be automatically audited by the DM system.

DM allows audit settings at three levels

System level: the startup and shutdown of the system. The audit at this level cannot and does not need to be set by the user. As long as the audit switch is turned on, the corresponding audit record will be automatically generated.

Statement level: causes an audit of a particular SQL or statement group that affects a particular type of database object. For example, AUDIT TABLE will audit statements such as CREATE TABLE, ALTER TABLE and DROP TABLE.

Object level: statements that audit acts on special objects. Such as the INSERT statement on the test table

The audit settings are stored in the DM dictionary table SYSAUDIT. One audit setting adds a corresponding record in SYSAUDIT, and if the audit is cancelled, the corresponding record in SYSAUDIT is deleted.

Object-level audit

Object-level audit occurs on a specific object, and the schema name and object name need to be specified.

The system process for setting up an object-level audit is as follows:

VOIDSP_AUDIT_OBJECT (TYPE VARCHAR, USERNAME VARCHAR, SCHNAME VARCHAR, TVNAME VARCHAR, WHENEVER VARCHAR) VOIDSP_AUDIT_OBJECT (TYPE VARCHAR, USERNAME VARCHAR, SCHNAME VARCHAR, TVNAME VARCHAR, COLNAME VARCHAR, WHENEVER VARCHAR)

Parameter description:

TYPE object-level audit option, the first column in the table above

USERNAME user name

SCHNAME schema name, empty time setting 'null'

TVNAME table, view, stored procedure name cannot be empty

COLNAME column name

Timing of WHENEVER audit. Available values are:

ALL: all of them

SUCCESSFUL: when the operation is successful

FAIL: when an operation fails

Example 1, audit the successful addition and modification of table PERSON.ADDRESS by SYSDBA.

SQL > sp_audit_object ('INSERT','SYSDBA','PERSON','ADDRESS','SUCCESSFUL'); DMSQL executed successfullyused time: 18.455 (ms). Execute id is 25077.SQL > sp_audit_object ('UPDATE','SYSDBA','PERSON','ADDRESS','SUCCESSFUL'); DMSQL executed successfullyused time: 16.263 (ms). Execute id is 25080.

Example 2, audit the successful modification made by SYSDBA to the ADDRESS1 column of table PERSON.ADDRESS.

SQL > sp_audit_object ('UPDATE','SYSDBA','PERSON','ADDRESS','ADDRESS1','SUCCESSFUL'); DMSQL executed successfullyused time: 15.465 (ms). Execute id is 25096.SQL > select * from v$auditrecords LINEID USERID USERNAME ROLEID ROLENAME IP SCHID SCHNAME OBJID OBJNAME OPERATION SUCC_FLAG SQL_TEXT- -- -DESCRIBTION OPTIME MAC-- 1 50331649 SYSDBA 67108864 DBA:: ffff:127.0.0.1-1-1 DROP TABLE Y drop table cs purge 2020-05-27 19 ffff:127.0.0.1 28 CREATE TABLE Y create table cs 06.000000 000000 DBA 000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000, 06.000000000000, DBA:: ffff:127.0.0.1-1-1 CREATE TABLE Y create table cs (id int,name varchar (20)) 2020-05-27 19 ffff:127.0.0.1 2819.000000 0000 CREATE USER Y create user cs2 identified by 0000 50331649 SYSDBA 67108864 DBA:: ffff:127.0.0.1-1-1 CREATE USER Y create user cs2 identified by 2020-05-27 19 32-18 million 00:00:00:00:00:00LINEID USERID USERNAME ROLEID ROLENAME IP SCHID SCHNAME OBJID OBJNAME OPERATION SUCC_FLAG SQL_TEXT- - -DESCRIBTION OPTIME MAC -4 50331750 JY 67108864 DBA:: ffff:127.0.0.1 150995951 JY 1454 T1 UPDATE Y update T1 set c2 where c1 2020-05-27 19 JY 36 DELETE Y delete from 39.000000 00000000000000 JY 67108864 DBA:: ffff:127.0.0.1 150995951 JY 1454 T1 DELETE Y delete from T1 2020-05-27 19 ffff:127.0.0.1 37 SYSDBA 08.000000 0000 DBA:: ffff:127.0.0.1 150995945 PERSON 67108864 ADDRESS INSERT Y insert into person.address values ('938 Wuling Avenue, Wuling District, Changde', null,' de Wuling District', '415700 Jingjing10) 2020-05-27 19 Suzhou 5814 29.0000000000000000000000000000000000

The system process for canceling an object-level audit is as follows:

VOIDSP_NOAUDIT_OBJECT (TYPE VARCHAR, USERNAME VARCHAR, SCHNAME VARCHAR, TVNAME VARCHAR, WHENEVER VARCHAR) VOIDSP_NOAUDIT_OBJECT (TYPE VARCHAR, USERNAME VARCHAR, SCHNAME VARCHAR, TVNAME VARCHAR, COLNAME VARCHAR, WHENEVER VARCHAR)

Parameter description:

TYPE object-level audit option, the first column in the table above

USERNAME user name

SCHNAME schema name, empty time setting 'null'

TVNAME table, view, stored procedure name cannot be empty

COLNAME column name

Timing of WHENEVER audit. Available values are:

ALL: all of them

SUCCESSFUL: when the operation is successful

FAIL: when an operation fails

Instructions for use:

Cancel the audit statement and set the audit statement to match, only the exact match can cancel the audit, otherwise the audit can not be cancelled.

Example 1, cancel the audit of the successful addition and modification of table PERSON.ADDRESS by SYSDBA.

SQL > sp_noaudit_object ('INSERT','SYSDBA','PERSON','ADDRESS','SUCCESSFUL'); DMSQL executed successfullyused time: 14.435 (ms). Execute id is 25099.SQL > sp_noaudit_object ('UPDATE','SYSDBA','PERSON','ADDRESS','SUCCESSFUL'); DMSQL executed successfullyused time: 15.512 (ms). Execute id is 25100.

Example 2, cancel the audit of the successful modification made by SYSDBA to the ADDRESS1 column of table PERSON.ADDRESS.

SQL > sp_noaudit_object ('UPDATE','SYSDBA','PERSON','ADDRESS','ADDRESS1','SUCCESSFUL'); DMSQL executed successfullyused time: 25.550 (ms). Execute id is 25102.

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.