Enable AUDIT

This type of monitoring is disabled by default in Oracle. So we need to set it up and then restart the database. We log in as SYS, and then issue the command:

ALTER SYSTEM SET AUDIT_TRAIL = TRUE SCOPE = SPFILE;

* To see exactly what SQL user entered (also failed attempts ie no permissions to the object), we need to modify the above commands so that instead of TRUE it was DB, EXTENDED:

ALTER SYSTEM SET AUDIT_TRAIL = DB, EXTENDED SCOPE = SPFILE;

Then, as SYS, you need to do SHUTDOWN IMMEDIATE (note that this option will break all sessions and roll back the transaction). We log in as SYS to the disabled (!) Base. To do so, we use SQL * Plus and log in without the “@adres_ip”. As a result, you need to do this from the computer on which the Oracle database is installed. If you have disabled the database with SQL * Plus, you do not need to re-login to the database. We enable it with the STARTUP command.

Now the audit option is already enabled. We can make sure by calling:

SELECT * FROM V $ PARAMETER WHERE NAME LIKE ‘% audit_trail’;

User Tracking

When you enable the audit option, you can enable the specific user tracking option. The AUDIT ALL option for the user observes his actions such as creating database links, synonyms, tables – by the word DDL.

AUDIT ALL BY USER;

 

We can only select some user actions that we want to monitor. To do this we use the command:

AUDIT SELECT table, insert table, delete table, update table BY user BY ACCESS;

 

The BY ACCESS option keeps the user’s information even if the same type of statement was executed within the session (eg, more than one SELECT was performed on a table). The opposite of this option is BY SESSION, but then the action is only recorded once.

To start tracking a user is required to log out (we can also force it to be disconnected) and re-login to the database.

To view the trace results, log on to SYS. Then select the table DBA_AUDIT_TRAIL. By asking the right question, we are able to trace the activity of a particular user.

SELECT username,

extended_timestamp,

owner,

obj_name,

action_name

FROM dba_audit_trail

WHERE USERNAME LIKE ‘EXAMPLES’

ORDER BY timestamp;

 

AND OWNER NOT LIKE ‘SYS’ is used to not display changes to system tables that are performed in connection with SYS user activity. The ORDER BY timestamp condition displays events by the time they occur.

We may also display information about activities related to objects owned by the creator:

SELECT username,

extended_timestamp,

owner,

obj_name,

action_name

FROM dba_audit_trail

WHERE owner LIKE ‘EXAMPLES’

ORDER BY timestamp;

 

Only a few columns have been displayed, because only those of us are interested at this time. The column USERNAME is the information of who performed the action, in the OWNER column on the object (here our user changed data in his own object). OBJ_NAME contains information about the name of the object on which the action was taken, and ACTION_NAME holds information about what type of activity was performed by the user.

Tracking activities on the object

At this point, we will track who changes the contents of a particular object, regardless of which user.

From SYS level:

 

AUDIT DELETE, INSERT, UPDATE ON username. Table_name BY ACCESS;

 

This command will enable you to observe the table of products in the user schema. Only deletion, addition, and change of data in this table will be observed. Of course, you can list activities that interest us (eg select).

Please modify your previous request. In this case, change the WHERE condition. We only display information about actions that have a ‘PRODUCT’ object in the name of the object, and therefore the name of the table that interests us. For the sake of certainty, we can add a condition to the OWNER column to make sure it is a specific user table. A table with the same name may exist in several schemes.

SELECT username,

extended_timestamp,

owner,

obj_name,

action_name

FROM dba_audit_trail

WHERE obj_name LIKE ‘PRODUCTS’

ORDER BY timestamp;

 

Disable AUDIT

The AUDIT monitoring option adds rows to the table that record information about the activities of the observed users or actions on the observed tables. When a large number of operations are observed on the tables or observers, the table begins to occupy more and more space. As a result, you can quickly fill up the disk space for the database. After performing the audit tasks we are interested in, this option should be disabled. To do this, we call a command similar to the one that turns on the AUDIT option except that we replace the word TRUE with the word FALSE:

ALTER SYSTEM SET AUDIT_TRAIL = FALSE SCOPE = SPFILE;

 

exercises

1. Enable audit to observe all operations on HR data and objects.

Create a new user, give it permission to execute INSERT, DELETE, UPDATE, SELECT on the employees table in the HR user schema.

Ask the next person to log in to the newly created user and perform several operations on the employees table. Specify what SQL commands to use.

4. Write a SQL command that will display all ip addresses / hostnames from which the HR user has ever logged.