topic

Audit in a XML-format

Creation of magazine of audit in a XML-format

One of corner stones of an infrastructure of system of safety in Oracle Database server - kontroliruemost` (accountability): an opportunity of registration of actions of users in system of a database. When actions occur (for example, the user updates the certain table), the server of a database registers these events in magazine of audit (audit trails) which can be or in a database in special table AUD $ circuits SYS, or in special files operational systems (OS). When these data are stored{kept} in a database, they are protected by reserve copying of this base, and it is easy for manager of a database to request them, using usual operators of language SQL. However in this case after fulfilment of ill-intentioned updating any who has access to circuit SYS, can potentially erase the corresponding data from magazine of audit.

The magazine of audit in the environment of OS belongs to the owner of the software of Oracle server, therefore his{its} storage in special files of OS - one of ways of protection against access of users with privileges SYS. You can have separate accounts of OS for managers of a database who allow them to administer a database and even they can have privileges SYSDBA, but these privileges do not allow to delete or change to managers files of magazine of audit. Use of files of magazine of audit in file system (with accurate differentiation of access at a level of OS and a database) can satisfy requirements on safety of many organizations.

Other advantage of magazine of audit in the environment of OS will be concluded in his{its} availability even if the copy of the server of a database does not work. Inconvenience of such magazine will be, that for his{its} interpretation it is necessary to analyze files of magazine of audit, using the tools suitable to this OS and a format of files.

In Oracle Database 10g Release 2 server functionalities of audit at a level of OS have been expanded - now in the environment of OS it is possible to create files of magazine of audit in a standard XML-format. XML-documents easily raspoznavaemy, there are many tools (working in many OS) for reading and formatting of these documents, therefore such magazines of audit are easy for analyzing. For simplification of searches to contents of these magazines of audit there is also a corresponding SQL-interface. In this clause{article}, I shall show how to adjust magazine of audit in a XML-format and effectively to use it{him}.

Initial adjustment

By default audit in Oracle Database 10g Release 2 server is not switched on. For his{its} inclusion and data recording of audit in a XML-format it is necessary to insert into a file of parameters of initialization the next line only:

AUDIT_TRAIL = XML

It - static parameter, therefore, that he started to operate, it is necessary to restart a copy of the server of a database.

Let's prepare for this clause{article} the demonstration data, having executed with privileges SYSDBA the following operators:

SQL> CREATE USER bank IDENTIFIED BY bank;

SQL> GRANT CONNECT, RESOURCE TO bank;

SQL> CONNECT bank/bank

SQL> CREATE TABLE accounts (accno NUMBER);

SQL> GRANT SELECT ON accounts TO SCOTT;

SQL> INSERT INTO accounts VALUES (104);

Then we shall switch on audit of the created table. Here we want to audit everyone who chooses the data from table ACCOUNTS (bank bills), taking place in circuit BANK (bank). For this purpose we execute:

AUDIT SELECT ON bank.accounts;

This operator can execute (and to switch on audit of this table) user BANK (the owner of the table) or any other user with system privilege AUDIT ANY. After that step when any user having objective privilege SELECT on this table, chooses from it{her} something, this fact is registered in magazine of audit. For example, if user SCOTT is connected to system of a database and chooses something from this table, carrying out:

CONNECT scott/tiger

...

SELECT * FROM bank.accounts WHERE accno = 104;

This operator SELECT generates recording audit. Parameter AUDIT_TRAIL matters "XML", therefore recording is generated in a XML-format.

Files of magazine of audit enter the name in the catalogue specified in parameter of initialization AUDIT_FILE_DEST, which default value - $ORACLE_BASE/admin / $ ORACLE_SID/adump. You can dynamically change this site, not restarting a copy of the server of a database. If you want to create these files in the other catalogue, such, for example, as/audit_trail, execute the following operator (as SYSDBA):

ALTER SYSTEM SET AUDIT_FILE_DEST = '/audit_trail ' DEFERRED;

After performance of this operator of recording of audit for again created sessions will act in the specified catalogue.

Check of magazine of audit

Now, when you know, where recordings audit are generated, you can check up magazine of audit. It will be a XML-file in the catalogue which is specified in parameter of initialization AUDIT_FILE_DEST. The file generated by executed action (operator SELECT), is shown on listing 1. Let's consider how to interpret it{him}.

<? xml version = " 1.0" encoding = "UTF-8"?>

<Audit xmlns = " http: // xmlns.oracle.com/oracleas/schema/dbserver_audittrail-10_2.xsd " xmlns:

xsi = " http: // www.w3.org/2001/XMLSchema-instance " xsi:schemaLocation = " http: // xmlns.oracle.com/

oracleas/schema/dbserver_audittrail-10_2.xsd ">

<Audit_Type> 1 </Audit_Type>

<Session_Id> 108802 </Session_Id>

<Extended_Timestamp> 2005-10-09T00:20:02.284327 </Extended_Timestamp>

<DB_User> SCOTT </DB_User>

<OS_User> oracle </OS_User>

<Userhost> prolin1 </Userhost>

<OS_Process> 22158 </OS_Process>

<Instance_Number> 0 </Instance_Number>

<Object_Schema> BANK </Object_Schema>

<Object_Name> ACCOUNTS </Object_Name>

</AuditRecord>

</Audit>

Listing 1. Magazine of audit in a XML-format.

Logging of audit is carried out in usual XML-style:

<Audit>

<Audit_Record>

<Audit_Type>...

<Session_Id>...

<StatementId>...

<EntryId>...

<Extended_Timestamp>...

<DB_User>...

<OS_User>...

<Userhost>...

<OS_Process>...

<Terminal>...

<Instance_Number>...

<Object_Schema>...

<Object_Name>...

<Action>...

<Returncode>...

<Scn>...

<SesActions>...

</Audit_Record>

</Audit>

Recording of audit contains inside tegov <Audit_Record> and </Audit_Record> together with special tegami, showing the detailed information which is in recording. For example, teg <DB_User> shows the user of a database who initiated action, sgenerirovavshee this recording. If the session initiated more than one action in a file of audit sets of details of each action will be shown, made in tegi <Audit_Record>.

The first teg each set - <Audit_Type> which specifies type of recording of audit. On listing 1 value <Audit_Type> - 1 which specifies usual audit in a XML-format. You can use also a XML-format for detailed audit, in this case teg will show value 2. If you switch on audit of SYS-operations (by default audit of these operations is not carried out), having established in parameter of initialization AUDIT_SYS_OPERATIONS value TRUE this teg will show value 4. At last, obligatory (mandatory) recordings of audit in a XML-format are shown by value 8. Examples of obligatory recordings of audit: recordings about start and a stop of a copy of the server the databases generated irrespective of an option value of initialization AUDIT_TRAIL. All recordings of magazine of audit contain this in a XML-format teg; he helps to differentiate types of these recordings.

The following teg, <Session_Id>, shows the identifier of a session (not the system identifier of a copy of the server of database SID) which has generated this recording audit. Pay attention, you can see this identifier in stolbce AUDSID performances V$SESSION:

SELECT AUDSID

FROM V$SESSION

WHERE SID = <SID>;

In the same session the user could carry out plural operators, the identifier of each of which is shown separate tegom <StatementId>. Time of registration of recording of audit is shown in tege <Extended_Timestamp>. Pay attention, on listing 1 time of registration: 2005-10-09T00:20:02.284327. Time is shown not in time zone of local time, and in belt{zone} UTC (Universal Time Coordinated, the universal synchronized time, also named average by time across Greenwich (GMT)); therefore the format of a mark of time seems strange.

Other part tegov shows the user who carried out actions, and other essential details of these actions. Tegi <DB_User>, <OS_User>, <Userhost>, <OS_Process>, <Terminal>, <Instance_Number>, <Object_Schema>, <Object_Name> and <Action> show accordingly a login name of a database, a login name of OS, a name of the host - machine, to which the user, the identifier of process of OS, the identifier of the terminal of the user, number{room} of a copy is connected, to which the user (in Oracle Real Application Clusters environment), the owner of the table from which the user works, a name of this table and a numerical code such as action is connected.

Action, which audit it is shown on listing 1, it was executed successfully, therefore teg a return code <Returncode> shows value 0. Pay attention, if this action was executed unsuccessfully, number{room} of a mistake of Oracle server would be shown. For example, if you have tried to remove the nonexistent table, you would receive mistake ORA-00955 and teg <Returncode> would show number 955.

This successful action has been executed, when system number{room} of change SCN (system change number) was equal 6447392335 as shows teg <Scn>. It is very useful in retrospective searches for finding-out of values stolbcov during the certain moment of time. For example, suppose, value of column BALANCE (the rest on the bill) for past period of time has considerably changed. How you can learn{find out} exact value which was seen by the user? You could use retrospective search and see value of column BALANCE what it was during it SCN:

SELECT balance

FROM accounts

AS OF SCN 6447392335

WHERE accno = 104;

Teg <SesActions> shows the actions executed in a session. In tege the line in length of 16 symbols from which the first are important 12 contains. He shows result of the actions executed by the user; in each position the result of performance of the certain action is shown: Alter (change), Audit (audit), Comment (note), Delete (removal{distance}), Grant (granting), Index (indexing), Insert (insert), Lock (blocking), Rename (renaming), Select (sample), Update (updating) and Flashback (retrospective operation).

For example, on listing 1 value tega <SesActions> - "----------S------" where S (Success) in 10-th position specifies successful performance of action SELECT. It means, that user SCOTT has executed in a session of one or more successful actions SELECT and more than any other actions subject to audit.

If user SCOTT also would execute successful action ALTER in this session, in the first position instead of "-" letter S would appear. If at performance the operator SELECT shown above there was a failure in 10-th position instead of letter S letter F (Failure) would appear. If user SCOTT has executed more than one operator SELECT, and at performance of some from them there were failures whereas others have been executed successfully 10-th positions letter B (Both) would appear, specifying both on successful, and on unsuccessful performance of actions.

The expanded audit

Recording of audit on listing 1 shows action (operator SELECT) and object above which this action (table ACCOUNTS) has been executed. However she does not show the SQL-operator who was executed by user SCOTT. In the mechanism of audit there are also means of expansion of his{its} functionalities, allowing to write down the text of these SQL-operators. For their inclusion establish in a file of parameters of initialization the following parameter and restart a copy of the server of a database.

AUDIT_TRAIL = XML, EXTENDED

After inclusion of the expanded audit user SCOTT has executed other operator SELECT:

SELECT * FROM accounts WHERE accno =:i;

The generated XML-file is shown on listing 2. He contains two additional elements which did not enter into the file of usual audit shown on listing 1:

* <Sql_Bind> *1:107 </Sql_Bind>

And

* <Sql_Text> select * from bank.accounts where accno =:i </Sql_Text>.

<? xml version = " 1.0" encoding = "UTF-8"?>

<Audit xmlns = " http: // xmlns.oracle.com/oracleas/schema/dbserver_audittrail-10_2.xsd " xmlns:

xsi = " http: // www.w3.org/2001/XMLSchema-instance " xsi:schemaLocation = " http: // xmlns.oracle.com/

oracleas/schema/dbserver_audittrail-10_2.xsd ">

<Audit_Type> 1 </Audit_Type>

<Session_Id> 108844 </Session_Id>

<Extended_Timestamp> 2005-10-10T18:26:18.720548 </Extended_Timestamp>

<DB_User> SCOTT </DB_User>

<OS_User> oracle </OS_User>

<Userhost> prolin1 </Userhost>

<OS_Process> 22584 </OS_Process>

<Instance_Number> 0 </Instance_Number>

<Object_Schema> BANK </Object_Schema>

<Object_Name> ACCOUNTS </Object_Name>

<Sql_Bind> *1:107 </Sql_Bind>

<Sql_Text> select * from bank.accounts where accno =:i </Sql_Text>

</AuditRecord>

</Audit>

<Sql_Bind> *1:107 </Sql_Bind>

<Sql_Text> select * from bank.accounts where accno =:i </Sql_Text>

Listing 2. The expanded XML-format.

Teg <Sql_Text> shows the text of the actual SQL-operator executed by user SCOTT. In this concrete SQL-operator there is a variable of linkage (:i). Value of this variable is shown in tege <Sql_Bind> in a format *?«??µ?n????????«® (DlinaZnachenijaPeremennoj): ZnacheniePeremennojSvjazyvanija. Listing 2 shows, that there is only one variable of linkage (*1), the length of its{her} value is equal to three symbols (3), and value - 107. Use of the expanded XML-audit allows to register SQL-operators and the used values of variables of linkage.

Pay attention, the expanded XML-audit was fixed only by that SQL-operator, which user SCOTT has executed after installation of parameter AUDIT_TRAIL=XML, EXTENDED and restart of a copy of the server of a database. The results of XML-audit shown on listings 1 and 2, are received in different sessions.

Viewing of files of audit in relational performance

The XML-files which have been written down by means of audit, - usual files of OS which can be considered{examined} any browser of XML-documents, but you can regret about old familiar magazine of audit in the table of a database with which it was possible to work, using SQL-operator.s do not worry, you still can use usual SQL for performance of searches to the data which have been written down in XML-files of magazine of audit. Contents of these files show new performance of the dictionary of data V$XML_AUDIT_TRAIL. To see the information of audit, you can choose all stolbcy this performance:

SELECT * FROM

V$XML_AUDIT_TRAIL;

The result of this search for simplification of viewing is shown on listing 3 in a vertical format. Pay attention, if there were plural recordings XML-audit, you would see in this performance one recording for each recording XML-audit. Names of all stolbcov performances coincide with names tegov in a XML-file of magazine of audit; for example, teg <DB_User> in a file it is shown in performance as column DB_USER. The mark of time is shown in stolbce EXTENDED_TIMESTAMP, but time is shown in time zone of local time, instead of in belt{zone} UTC, as in a file of XML-audit. Stolbcy which are not filled in a XML-file, have in performance value NULL.

SELECT * FROM

V$XML_AUDIT_TRAIL;

AUDIT_TYPE: 1

SESSION_ID: 108844

PROXY_SESSIONID: 0

STATEMENTID: 10

ENTRYID: 1

EXTENDED_TIMESTAMP: 10-OCT-05 06.26.18.720548 PM-04:00

GLOBAL_UID:

DB_USER: SCOTT

CLIENTIDENTIFIER:

EXT_NAME:

OS_USER: oracle

OS_HOST: prolin1

OS_PROCESS: 22584

TERMINAL: pts/3

INSTANCE_NUMBER: 0

OBJECT_SCHEMA: BANK

OBJECT_NAME: ACCOUNTS

POLICY_NAME:

NEW_OWNER:

NEW_NAME:

ACTION: 103

STATEMENT_TYPE: 0

TRANSACTIONID:

RETURNCODE: 0

SCN: 6447496045

COMMENT_TEXT:

AUTH_PRIVILEGES:

GRANTEE:

PRIV_USED: 0

SES_ACTIONS:---------S------

OS_PRIVILEGE:

ECONTEXT_ID:

SQL_BIND: *1:107

SQL_TEXT: select * from bank.accounts where accno =:i

Listing 3. Contents V$XML_AUDIT_TRAIL

Additional protection

Naturally, for increase kontroliruemosti, you want "to condense" protection of an infrastructure of audit. The above-stated procedure of initial installation has one potential problem - any, having the system privilege of performance of delivered package UTL_FILE, can remove a file of magazine of audit from file system of OS, using procedure FREMOVE. To lower this risk, you can limit these opportunities:

* Having cancelled this privilege at group of users PUBLIC;

* Having cancelled system privilege CREATE DIRECTORY at group of users PUBLIC.

The first variant - a little bit radical, but he represents a reliable way of decrease{reduction} in risk. The second variant is, probably, more practical. For removal{distance} of this file users should have access to the given catalogue of OS or an opportunity of creation of objects of a database such as DIRECTORY (catalogue). If you at group of users PUBLIC will withdraw system privilege CREATE DIRECTORY only users with role DBA can create catalogues in that catalogue of OS in which there is a magazine of audit, but not usual users. If they cannot create the catalogue, they cannot and remove a file, using package UTL_FILE. In any case, in methods of the advanced practice it is considered, that system privilege CREATE DIRECTORY should be withdrawn at group of users PUBLIC.

The user of OS who owns software Oracle, owns also files of XML-audit, therefore any who has access rights to the server and to this account of the user of OS, can remove these files. Nevertheless, limiting privileges, you can achieve a comprehensible level of safety.

In Oracle server other type of audit is offered also. At this audit the magazine of audit is written to system magazines of OS (system logs, syslogs) which belong to the exclusive user (such, as "root" in OS UNIX) and cannot leave other users, including the owner of software Oracle.

System magazines

The above-stated variants will not work, if the owner of the software of the server of a database, usually the user "oracle", will decide to remove all recordings in these files of XML-audit. In addition to protect them, you can use the utility (system) syslog. This system writes down messages in the special file belonging to the exclusive user of OS (the user "root"), therefore any other user cannot remove it{him}. You can establish parameters of initialization so that the magazine of audit entered the name system syslog:

AUDIT_TRAIL=OS

AUDIT_SYSLOG_LEVEL=USER.ALERT

Now after restart of a copy of the server of a database all recordings of audit will enter the name system syslog according to the established means (a source of messages) - facility - (kernel (a nucleus of OS), user (the user processes) and so on) and the certain level of gravity of messages - level - (such, as notice (unusual statuses), warning (preventions{warnings}), err (statuses of mistakes) and so on). The second line of the code shown above specifies, that recordings of audit enter the name with established by means user and a level alert (urgent situations). If you will not specify anything more these recordings of audit will act in a file of messages of the server by default - usually in Linux server it is a file/var/log/messages. However in this file all messages act, switching and messages of OS, therefore you can create other file only for the purposes of audit, say, audit.log. Specify a site of this file for the given means in a configuration file of system syslog, usually taking place v/etc/syslog.conf:

user.alert/var/log/audit.log

This line specifies, that messages of means user at a level alert should act in a file/var/log/audit.log. Now restart process of system syslog. The actual command depends on system, your system administrator should apply the correct command. In OS UNIX as the user "root" execute the command:

/etc/init.d/syslog restart

She will restart process of system syslog which will write messages of means user at a level alert to a file/var/log/audit.log file. After that, when the user will execute search to table ACCOUNTS, in this file the next line will appear:

Oct 13 01:26:55 oradba Oracle Audit [28955]: SESSIONID: "25386"

ENTRYID: "1" STATEMENT: "8" USERID: "SCOTT" USERHOST: "prolin1"

TERMINAL: "pts/2" ACTION: "103" RETURNCODE: "0" OBJ$CREATOR: "ARUP" OBJ$NAME:

"ACCOUNTS" SES$ACTIONS: "---------S------"

SES$TID: "76564" OS$USERID: "oracle"

Unfortunately, she not in a XML-format, but actions are quite clear. This file belongs to the user root, therefore the user oracle cannot remove or change it{him}, that provides very good protection.

The conclusion

The magazine of audit in a XML-format in Oracle Database 10g Release 2 server allows you to have without necessity of a choice both that and another - the magazine of the audit separated from a database for amplification{strengthening} of protection against non-authorized access, and the same familiar SQL-interface for performance of searches to the data which raises efficiency of job. It is very useful for maintenance of observance of many laws and requirements on safety. You can use the shared browsers of XML-documents developed by foreign manufacturers, and you (or, probably, experts of a department which is obliged to supervise your command, but not having to use SQL) can use opportunities XML-analyzers with tables of the styles, allowing to create custom-made reports on files of magazine of audit.