Database monitoring is part of database security and through the monitoring process we can find many answers to some matters that would remain ambiguous without the monitoring process.

The monitoring process can be divided into three types:-

1- Standard Database Auditing:

It is to follow up and save information about the connection and disconnection processes to the database, and also to follow up on the use of (Object Privileges and System Privileges)

Within the database, such as creating, deleting, querying, modifying, and other operations.

2 - Value-based auditing: In the first type, we follow the connection operations as well as follow the operations inside the database, but we may need to preserve the values ​​that are changed in the database.

3 - Fine-grained auditing: This type is to save the SQL statements that were executed in the database.

But before going into detail about the types of monitoring, it must be noted that the Oracle database must be initialized before starting the monitoring operations using the AUDIT_TRAIL variable, which takes one of the following values:

1 - NONE: This means that the database is not ready for the monitoring process.

2- DB: This means that the monitoring information is saved in the database in tables belonging to the user SYS.

3- OS: This means that the monitoring information is saved at the operating system level. If we are working on a Windows environment, the data is saved in the Event Log. If we are working on UNIX or LINUX, the information is stored as files in the path specified in the AUDIT_FILE_DEST variable.

Oracle Database supports different types of monitoring, so that the database administrator can monitor everything that happens in the database. Imagine with me the size of the data that is stored if we monitor everything that happens in the database. There is no doubt that this will negatively affect the performance of the database, so it is necessary for the database administrator Focus on monitoring so that we monitor what we need without over-monitoring. For example, if we want to monitor some tables, it is better to focus on the type of operations that must be monitored, such as deletion only. However, if you monitor everything that happens to the tables, this will affect the performance of the database.

Queries, modifications, deletions, etc. will be monitored. Therefore, focusing is important in the monitoring process.

The first thing we need to do is to initialize the database by changing the value of the variable AUDIT_TRAIL from the value NONE to one of the following values ​​(OS or DB)

Let's assume here that we want to store the monitoring information inside the database, so we choose the value DB.

But before that, we make sure of the variable AUDIT_TRAIL

SHOW PARAMETER AUDIT_TRAIL; 

We convert the value of the variable AUDIT_TRAIL to the value DB, taking into account that this variable is not automatic, i.e. it requires closing and opening the database in order for the value of the variable to be affected.

ALTER SYSTEM SET AUDIT_TRAIL=DB SCOPE=SPFILE; 

Then we close and open the database and check the value of the variable.

1-Standard Database Auditing :

As we mentioned earlier, this type of monitoring focuses on monitoring the database connection process as well as (System Privileges and Object Privileges).

It should also be noted that there are two options during the monitoring process (BY ACCESS & BY SESSION).

BY SESSION: It means storing one field of information during monitoring operations for a specific type of operations for each SESSION. To make the vision clearer, let's assume that we monitored the modification process

on the tables of a specific user, let's assume that he is X. One of the users opened a SESSION and used this SESSION to modify 5 tables belonging to user X.

The monitoring process will store only one field for the modification process as long as he made the modification with the same SESSION, in other words it is equivalent to the GROUP BY SESSIION statement.

BY ACCESS: The same previous example can be taken, but here 5 fields will be stored, each field is the modification process that occurred for each table.

There is no doubt that the BY SESSION option may not be sufficient sometimes to know the details of the monitoring information, so we may resort to the BY ACCESS option, which provides more details, but of course increases the information storage operations. In this type of monitoring, as we mentioned, the best thing is the focusing process, and the focus is by specifying the user as well as by specifying the success or failure of the process (SUCCESSFUL OR NOT SUCCESSFUL). This type of Standard Database Auditing contains subsections:-

SESSION AUDITING:

It is part of the first type of Standard Database Auditing, where the connection and disconnection processes to the database are monitored, and it is possible to focus on specifying the user whose connection processes we want to monitor, and it is also possible to follow up on all users using the AUDIT SESSION command, and it is also possible to focus on specifying a process Success or failure of the connection process.

Assuming that we want to monitor all successful connection and disconnection processes for the user TEST and because we want to know the connection and disconnection time, it is better to use the ACCESS option.

AUDIT SESSION BY TEST BY ACCESS WHENEVER SUCCESSFUL; 

What if user TEST connects to the database?

Now the database administrator can see information about the above connection

SELECT SERNAME,TERMINAL,ACTION_NAME,EXTENDED_TIMESTAMP FROM DBA_AUDIT_SESSION; 

You will find that it appears to the database administrator that the user TEST has connected to the database on the date and time specified by the NBS device. You can also view other information about the device user name and other information.

What if the user TEST disconnects? Of course, additional information will appear to the database administrator.

SELECT USERNAME,TERMINAL,ACTION_NAME,TO_CHAR(EXTENDED_TIM ESTAMP,'DD-MM-YYYY:HH-MI- SS'),TO_CHAR(LOGOFF_TIME,'DD-MM-YYYY:HH-MI-SS') FROM DBA_AUDIT_SESSION; 

Now it appears to the database administrator that user TEST has disconnected from the database at the specified time.

You can also query the connection and disconnection information to the database via (DBA_AUDIT_TRAIL).

If the connection and disconnection information to the database is available in each

Replace the text with codes

From (DBA_AUDIT_TRAIL & DBA_AUDIT_SESSION).

The database administrator can query the monitoring options that he has configured in the database by:

DBA_OBJ_AUDIT_OPTS

DBA_STMT_AUDIT_OPTS

DBA_PRIV_AUDIT_OPTS

SELECT USER_NAME,AUDIT_OPTION,SUCCESS,FAILURE FROM DBA_STMT_AUDIT_OPTS; 

Of course, the monitoring process can be cancelled. If we wanted to cancel the user connection monitoring process TEST that we monitored previously.

NOAUDIT SESSION BY TEST;

Now if we perform a query to display the monitoring options that we have configured, we will not find the option to monitor the user connection TEST.

SQL STATEMENT AUDITING:

Imagine with me that the database administrator wants to monitor DDL operations such as creating or deleting a table. In this type of monitoring, the focus can be on identifying the user and also on the success or failure of the operation.

But this is within the framework of what the user owns. For example, if a user creates a table in SCHEMA for another user, then This type of monitoring falls under what is called AUDITING PRIVILEGE SYSTEM, which we will discuss later, but here only within the framework of what the user owns. Let us assume that we want to monitor the process of creating tables by the user TEST within his space, i.e. in the same SCHEMA.

AUDIT CREATE TABLE BY TEST BY ACCESS; 

What if user TEST now creates a table?

CREATE TABLE USER_MASTER (USER_NO NUMBER(٥),USER_NAME VARCHAR٢(٠٤),CONSTRAINT PK_USER_NO PRIMARY KEY(USER_NO)); 

The information will be displayed to the database administrator.

SELECT USERNAME,OWNER,TO_CHAR(TIMESTAMP,'DD-MM- YYYY:HH-MI-SS'),OBJ_NAME,ACTION_NAME FROM DBA_AUDIT_TRAIL WHERE OWNER='TEST'; 

If the user TEST, for example, creates a table in another SCHEMA, the above monitoring options will not provide any information unless we monitor the TABLE ANY CREATE permission, which we will discuss in the next step.

The options that we have configured to monitor SQL STATEMENT can be inquired about by:

DBA_STMT_AUDIT_OPTS

DBA_PRIV_AUDIT_OPTS

Monitoring can be canceled by:

NOAUDIT CREATE TABLE BY TEST; 

• SYSTEM PRIVILEGE AUDITING:

This is to monitor the operations of using SYSTEM PRIVILEGES privileges within the database, for example the CREATE ANY TABLE privilege. Of course, the process of monitoring the privileges can be focused by a specific user, as well as by the success or failure of the process. Originally, the SYSTEM PRIVILEGES privilege monitoring operations are stored as a field for each process, in order to use the BY ACCESS option as a mode. By default, but of course the SESSION BY option can be used to reduce the amount of data stored, so that one field is stored for each SESSION that used the controlled permission. Let's assume that we want to monitor the TABLE ANY CREATE permission for the user TEST, in other words, we want to monitor all the tables that the user TEST creates in another SCHEMA using the CREATE ANY TABLE permission, which means creating a table in another SCHEMA.

AUDIT CREATE ANY TABLE BY TEST WHENEVER SUCCESSFUL;

Now what if the user TEST created a table in the EMP SCHEMA, i.e. in the EMP user space?

CREATE TABLE EMP.EMPLOYEE (EMP_NO NUMBER(٤),EMP NAME VARCHAR(40)); 

Of course, the database administrator can follow this step.

SELECT USERNAME,OWNER,OBJ_NAME,ACTION_NAME,TO_CHAR(TIMEST AMP,'DD-MM-YYYY:HH-MI-SS') FROM DBA_AUDIT_OBJECT WHERE USERNAME='TEST'; 

You will notice that the user TEST has already created the EMPLOYEE table in the user EMP at the specified time.

You can also follow up on the rest of the permissions, for example (DROP ANY TABLE) and other permissions SYSTEM PRIVILEGES.

To inquire about the configuration of SYSTEM monitoring options.

SELECT * FROM DBA_PRIV_AUDIT_OPTS WHERE PRIVILEGE='CREATE ANY TABLE'; 

Monitoring can also be cancelled.

NOAUDIT CRATE ANY TABLE BY TEST WHENEVER SECCESSFUL;

• OBJECT PRIVILEGE AUDITING:

This type is for monitoring operations that occur on objects such as tables, views, and other objects.

It is possible to focus by specifying the user and also by the success or failure of the operation. The origin of this type of monitoring is to be BY SESSION, but the option BY ACCESS can be specified.

Let's assume that we want to monitor the additions in the USER_MASTER table in the SCHEMA called TEST.

AUDIT INSERT ON TEST.USER_MASTER BY ACCESS; 

Now if the user TEST adds data to the USER_MASTER table the database administrator can follow up on that.

SELECT USERNAME,TO_CHAR(TIMESTAMP,'DD-MM-YY:HH- MI-SS'),OBJ_NAME,ACTION_NAME FROM DBA_AUDIT_TRAIL WHERE USERNAME='TEST' AND OBJ_NAME='USER_MASTER' AND ACTION_NAME='INSERT'; 

To inquire about OBJECT PRIVILEGES monitoring configuration options.

select * from DBA_OBJ_AUDIT_OPTS WHERE OWNER='TEST' AND OBJECT_NAME='USER_MASTER'; 

There are three values:

 means no monitoring:

A: means that monitoring information is stored ACCESS BY.

S: It means that the monitoring information is stored SESSION .BY

Note that opposite the INS field, the value A/A is found, meaning that the INSERT operation is monitored using the BY ACCESS option, whether the operation succeeds or fails.

However, if the option is WHENEVER SUCCESSFUL, the value will be A/-

The monitoring operation can be canceled using. 

NOAUDIT INSERT ON TEST.USER_MASTER; 

Thus, we have discussed the available options for monitoring that belong to the first part, Standard Database Auditing, which are:

SESSION AUDITING

SQL STATEMENT AUDITING

SYSTEM PRIVILEGE AUDITING

OBJECT PRIVILEGE AUDITING

2- Value-Based Auditing:

In the first type of monitoring, Standard Database Auditing, we can monitor and save information about the operations that occur in the database, but sometimes we may need to save the values ​​that are modified or deleted from the database. These values ​​are not saved in Standard Database Auditing. This type of monitoring, Value-Based Auditing, works based on creating a Trigger so that it is executed after the operation. Modify or delete to save the information and values ​​we want and save them in a table that we create specifically to store this information.

Of course, this type of Value-Based Auditing reduces the performance of the database more than the Standard Database Auditing type because the trigger is executed after each modification or deletion operation.

Now let's assume that we want to monitor and save the values ​​that were modified in the BOOK_NAME field in the BOOK table owned by the user TEST.

The database administrator must first determine the information that he wants to save, and let's assume that it is:-

1- IP Address of the device that was used to connect to the database.

2- The name of the operating system user who connected to the database.

3- Date and time.

4- Field value before and after modification.

Second: The database administrator must create the table to store information about modification operations.

CREATE TABLE BOOK_AUDIT(

OS_USER VARCHAR2(70),

UPDATE_DATE DATE,

IP_ADDRESS VARCHAR2(60),

OLD_NEW_NAME VARCHAR2(100)); 

Third, the database administrator creates the trigger.

CREATE OR REPLACE TRIGGER BOOK_NAME_AUDIT AFTER UPDATE OF BOOK_NAME

ON TEST.BOOK

REFERENCING NEW AS NEW OLD AS OLD FOR EACH ROW

BEGIN

IF :OLD.BOOK_NAME != :NEW.BOOK_NAME THEN INSERT INTO BOOK_AUDIT

VALUES (SYS_CONTEXT('USERENV','OS_USER'), SYSDATE, SYS_CONTEXT('USERENV','IP_ADDRESS'),

:NEW.BOOK_NO ||' BOOK_NO) CHANGED FROM '||:OLD.BOOK_NAME||

' TO '||:NEW.BOOK_NAME); END IF;

END; 

This is how the Trigger is created and let's assume that the user TEST has made a modification in the BOOK table.

The database administrator can track this modification via the BOOK_AUDIT table.

SELECT OS_USER,TO_CHAR(UPDATE_DATE,'DD-MM-YY:HH- MI-SS'),IP ADDRESS,OLD NEW NAME FROM BOOK_AUDIT; 

To stop this type of monitoring, you can disable or delete this trigger.

3- Fine-Grained Auditing (FGA):

The previous types of monitoring can save information about the operations that occur in the database, and it is also possible to save the values ​​that change. As for this type of monitoring, it is for saving the SQL statements that are executed in the database and includes all of the following statements (DELETE & UPDATE & INSERT & SELECT). This is done using DBMS_FGA PACKAGE, this package contains four Procedures:-

1 - ADD_POLICY: To add a new POLICY to monitor SQL statements in the database according to the values ​​that we will specify in the procedure.

2- DROP_POLICY: To delete an existing AUDIT POLICY.

3- ENABLE_POLICY : To run AUDIT POLICY

4- DISABLE POLICY : To disable AUDIT POLICY.

Now let's assume that we want to save the SELECT statements that occur for the BOOK table owned by the user TEST, and in order not to save all the SELECT statements that occur for the BOOK table, it is possible to focus

on some fields by using some conditions.

WHERE BOOK_NO=1 for example

This is to focus the monitoring process.

The database administrator should always take care not to overdo the monitoring process, but always focus on what is required. Now we execute the ADD_POLICY procedure to add AUDIT POLICY to save the SELECT statements that are executed on the BOOK table according to the conditions that we specify. Of course, this procedure contains a set of variables for which we must specify values. We will talk about these variables in some detail later. These procedures are present in the database within the DBMG_FGA package, meaning that the database administrator does not need to create them. He only needs to execute them after specifying the values ​​of the variables.

DECLARE

OBJECT_SCHEMA VARCHAR2(200);

OBJECT_NAME VARCHAR2(200);

POLICY_NAME VARCHAR2(200);

AUDIT_CONDITION VARCHAR2(200);

AUDIT_COLUMN VARCHAR2(200);

HANDLER_SCHEMA VARCHAR2(200);

HANDLER_MODULE VARCHAR2(200);

ENABLE BOOLEAN;

STATEMENT_TYPES VARCHAR2(200);

AUDIT_TRAIL BINARY_INTEGER;

AUDIT_COLUMN_OPTS BINARY_INTEGER;

BEGIN

OBJECT_SCHEMA := 'TEST';

OBJECT_NAME := 'BOOK';

POLICY_NAME := 'BOOK_SELECT';

AUDIT_CONDITION := 'BOO_NO=1';

AUDIT_COLUMN := 'BOOK_NAME';

HANDLER_SCHEMA := NULL;

HANDLER_MODULE := NULL;

ENABLE := TRUE;

STATEMENT_TYPES := 'SELECT';

AUDIT_TRAIL := 1;

AUDIT_COLUMN_OPTS := 0;

SYS.DBMS_FGA.ADD_POLICY ( OBJECT_SCHEMA, OBJECT_NAME, POLICY_NAME, AUDIT_CONDITION, AUDIT_COLUMN, HANDLER_SCHEMA, HANDLER_MODULE, ENABLE, STATEMENT_TYPES, AUDIT_TRAIL, AUDIT_COLUMN_OPTS );

COMMIT;

END;

/ 

OBJECT_SCHEMA: It means the user that contains the table or the object that we want to monitor the SELECT statements on.

OBJECT_NAME: It is the object that we want to monitor the SELECT statements on.

POLICY_NAME: It is the name of the POLICY AUDIT that we want to create.

AUDIT_CONDITION: It is to specify the conditions to focus the monitoring process.

AUDIT_COLUMN: This is the column on which we want to focus the monitoring process.

HANDLER_SCHEMA: Sometimes we may need to specify a Procedure to perform some additional events during the monitoring process, so here we specify the user that contains this procedure.

ENABLE: To activate the POLICY AUDIT operation, and this variable takes the value TRUE in the default mode.

STATEMENT_TYPES: To specify the type of SQL statements we want to monitor, default is SELECT.

AUDIT_TRAIL: To store the monitored SQL statements in the AUDIT_TRAIL.

AUDIT_COLUMN_OPTS: What if you specify a number of columns in the AUDIT_COLUMN variable, here to specify whether (ALL OR ANY).

Now what if the EMP user makes a query on the BOOK table belonging to the TEST user with the same conditions mentioned above?

SELECT * FROM TEST.BOOK WHERE BOOK_NO=1; 

The database administrator can now follow this query.

SELECT USERHOST,OS_USER,DB_USER,TO_CHAR(TIMESTAMP,'DD-MM- YY:HH-MI-SS'),SQL_TEXT FROM DBA_FGA_AUDIT_TRAIL WHERE DB_USER='EMP;

If the EMP user makes another query that does not meet the conditions specified in the ADD_POLICY procedure, the information from that query will not be displayed to the database administrator.

Of course, the database administrator can query AUDIT_POLICIES that operates in the DBA_AUDIT_POLICIES database.

SELECT POLICY_NAME,OBJECT_SCHEMA,OBJECT_NAME,POLICY_COLUM N,SEL FROM DBA_AUDIT_POLICIES; 

This POLICY AUDIT can be disabled by the DISABLE_POLICY action.

DECLARE

OBJECT_SCHEMA VARCHAR2(200);

OBJECT_NAME VARCHAR2(200);

POLICY_NAME VARCHAR2(200);

BEGIN

OBJECT_SCHEMA := 'TEST'; OBJECT_NAME := 'BOOK'; POLICY_NAME := 'BOOK_SELECT';

SYS.DBMS_FGA.DISABLE_POLICY ( OBJECT_SCHEMA, OBJECT_NAME, POLICY_NAME );

COMMIT;

END;

/

It can also be enabled again using the ENABLE_POLICY action.

DECLARE

OBJECT_SCHEMA VARCHAR2(200);

OBJECT_NAME VARCHAR2(200);

POLICY_NAME VARCHAR2(200);

 

BEGIN

OBJECT_SCHEMA := 'TEST';

OBJECT_NAME := 'BOOK';

POLICY_NAME := 'BOOK_SELECT';

 

SYS.DBMS_FGA.ENABLE_POLICY ( OBJECT_SCHEMA, OBJECT_NAME, POLICY_NAME );

COMMIT;

END;

/ 

Finally, POLICY AUDIT can be deleted using the DROP_POLICY action.

DECLARE

OBJECT_SCHEMA VARCHAR2(200);

OBJECT_NAME VARCHAR2(200);

POLICY_NAME VARCHAR2(200);

BEGIN

OBJECT_SCHEMA := 'TEST';

OBJECT_NAME := 'BOOK';

POLICY_NAME := 'BOOK_SELECT';

SYS.DBMS_FGA.DROP_POLICY ( OBJECT_SCHEMA, OBJECT_NAME, POLICY_NAME );

COMMIT;

END;

/ 

Now if you do a query for AUDIT_POLICIES you will not find this policy in the database.