Shulou(Shulou.com)05/31 Report--

How to analyze the statement-level audit in DM7 audit? I believe that many inexperienced people are at a loss about it. Therefore, this paper summarizes the causes and solutions of the problem. Through this article, I hope you can solve this problem.

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.

Statement-level audit

The action of statement-level audit is global and does not correspond to specific database objects.

ALL: all statement-level audit options, all auditable actions

USER:CREATE USER,ALTER USER,DROP USER create / modify / delete user actions

ROLE: CREATE ROLE,DROP ROLE create / delete role operation

TABLESPACE:CREATE TABLESPACE,ALTER TABLESPACE,DROP TABLESPACE, create / modify / delete tablespace operation

SCHEMA:CREATE SCHEMA,DROP SCHEMA,SET SCHEMA create / delete / set current mode operation

TABLE:CREATE TABLE,ALTER TABLE,DROP TABLE,TRUNCATE TABLE create / modify / delete / empty base table operation

VIEW:CREATE VIEW, ALTER VIEW,DROP VIEW create / modify / delete view operation

INDEX:CREATE INDEX,DROP INDEX create / delete index operation

PROCEDURE:CREATE PROCEDURE,ALTER PROCEDURE,DROP PROCEDURE create / modify / delete enclosure operation

TRIGGER:CREATE TRIGGER,ALTER TRIGGER,DROP TRIGGER create / modify / delete trigger operation

SEQUENCE:CREATE SEQUENCE,ALTER SEQUENCE,DROP SEQUENCE create / modify / delete sequence operation

CONTEXT:CREATE CONTEXT INDEX,ALTER CONTEXT INDEX,DROP CONTEXT INDEX create / modify / delete full-text index operation

SYNONYM:CREATE SYNONYM,DROP SYNONYM create / delete synonyms

GRANT:GRANT grant permission operation

The system process for setting up a statement-level audit is as follows:

VOIDSP_AUDIT_STMT (TYPE VARCHAR (30), USERNAME VARCHAR (128), WHENEVER VARCHAR (20))

Parameter description:

The TYPE statement-level audit option, which is the first column in the table above

USERNAME user name. NULL means no limit.

Timing of WHENEVER audit. Available values are:

ALL: all of them

SUCCESSFUL: when the operation is successful

FAIL: when an operation fails

Example 1, the creation, modification and deletion of the audit table.

SQL > sp_audit_stmt ('table','null','all'); DMSQL executed successfullyused time: 10.709 (ms). Execute id is 24979.SQL > select * from sysaudit LINEID LEVEL UID TVPID COLID TYPE WHENEVER-1 1- 1-1-1 15 3used time: 0.955 (ms). Execute id is 24980.SQL > drop table cs purge;executed successfullyused time: 138.141 (ms). Execute id is 24994.SQL > create table cs (id int,name varchar (20)); executed successfullyused time: 31.431 (ms). Execute id is 24996.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 2819.000000 00:00:00:00:00:00used time: 0.891 (ms) Execute id is 24997.

In example 2, the SYSDBA created user was successfully audited.

SQL > sp_audit_stmt ('user','sysdba','successful'); DMSQL executed successfullyused time: 22.858 (ms). Execute id is 25029.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 00:00:00:00:00:00used time 18.000000 (ms). Execute id is 25032.

Example 3, audit table modifications and deletions made by user jy, regardless of failure or success.

SQL > sp_audit_stmt ('update table','jy','all'); DMSQL executed successfullyused time: 15.729 (ms). Execute id is 25037.SQL > sp_audit_stmt ('delete table','jy','all'); DMSQL executed successfullyused time: 13.254 (ms). Execute id is 25038.SQL > update T1, set c2, influence rows 1used time: 17.490 (ms). Execute id is 25043.SQL > rollback;executed successfullyused time: 1.001 (ms). Execute id is 25045.SQL > delete from T1 influence rows 2used time: 27.257 (ms). Execute id is 25046.SQL > rollback;executed successfullyused time: 0.831 (ms). Execute id is 25049.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 JY 32 UPDATE Y update 18.000000 000000 JY 00000000Get0040000000040040000000000400000000000000000000000000000000000000000GOUD 004 50331750 JY 67108864 DBA:: ffff:127.0.0.1 150995951 JY 1454 T1 UPDATE Y update T1 set c2 people WY '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 37 00:00:00:00:00:00used time: 1.242 (ms). Execute id is 25047.

The systematic process of canceling a statement-level audit is as follows:

VOIDSP_NOAUDIT_STMT (TYPE VARCHAR (30), USERNAME VARCHAR (128), WHENEVER VARCHAR (20))

Parameter description:

The TYPE statement-level audit option, which is the first column in the table above

USERNAME user name. NULL means no limit.

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 creation, modification, and deletion of the table.

SQL > sp_noaudit_stmt ('table','null','all'); DMSQL executed successfullyused time: 64.146 (ms). Execute id is 25059.

Example 2, cancel the successful audit of the SYSDBA creation user.

SQL > sp_noaudit_stmt ('user','sysdba','successful'); DMSQL executed successfullyused time: 11.380 (ms). Execute id is 25060.

Example 3, cancel the audit of table modifications and deletions made by user jy.

SQL > sp_noaudit_stmt ('update table','jy','all'); DMSQL executed successfullyused time: 18.614 (ms). Execute id is 25062.SQL > sp_noaudit_stmt ('delete table','jy','all'); DMSQL executed successfullyused time: 15.548 (ms). Execute id is 25064. After reading the above, have you mastered how to analyze the statement-level audit in DM7 audit? If you want to learn more skills or want to know more about it, you are welcome to follow the industry information channel, thank you for reading!

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.