K2BAudit Oracle Configuration

Introduction

This page contains configuration instructions to use K2BAudit with Oracle. These instructions steps must be followed whenever a new environment using this DBMS is created. Some of them also apply when deploying an application with K2BAudit that uses Oracle.

Step 1 - Configure the K2BAudit datastore

In your KB, a new DataStore called “K2BAudit” will appear after executing the “Initialize Audit” action. This DataStore is the location where the audit logs are stored. This DataStore must be configured to connect to the K2BAuditAnalyzer database.

If you are configuring the DataStore in the GeneXus IDE, you can find it in the DataStores section of your environment’s configuration.

K2BAuditDataStoreOracle
K2BAudit DataStore

If you are configuring the DataStore in the generated application, you must use the configuration file.

Step 2 - Application user retrieval configuration

When an operation is audited, the user that is currently using the application is saved together with the operation values.

In order to get the application user, K2BAudit uses a procedure that is configured in the “After connect” property in the generator. The procedure that should be used in Oracle environments is the “K2BSaveAuditUserOracle”.

K2BAuditAfterConnectProcedures
K2BAudit After connect procedures

In this procedure there is a section delimited by comments with the text “Set User Code”. The developer must update this section’s contents to load the correct User Code in the &User variable. The default implementation loads the user code from the application context.

K2BAuditAfterConnectSetUserCodeSection
Set User Code section

After updating the procedure’s code, the “After connect” property in the model must be updated to reference this procedure.

K2BAuditAfterConnectPropertyOracle
After connect property

Step 3 - LUW information configuration

When using K2BAudit with an Oracle database, it is possible to store information about which LUW an operation was executed. For each LUW, K2BAudit stores all the operations performed in audited tables, the date and time when the commit command was executed, and the GeneXus object that executed the commit command.

In order to enable this feature, set the "Before commit" property in the generator to "K2BBeforeCommitOracle".

K2BAuditBeforeCommitPropertyOracle
Before connect property

Step 4 - Oracle configuration

4.1 - User permissions

You must grant SELECT permissions to the database user used by the application for the tables V_$SESSION and V_$MYSTAT, following these steps:

  1. Open Oracle's SQLPlus

  2. conn /as sysdba

  3. GRANT SELECT ON V_$SESSION TO <dbmsUser> (Where <dbmsUser> should be replaced by the user's name)

  4. GRANT SELECT ON V_$MYSTAT TO <dbmsUser> (Where <dbmsUser> should be replaced by the user's name)

4.2 - Load the K2BAudit package

The "K2BSaveAuditUserOracle" procedure uses an Oracle package to load the application user's data. This package must be loaded in the database, using the script located in <GX_INSTALL_DIR>\Packages\K2BTools\Audit\Oracle\Audk2b.sql. To do this follow these steps:

  1. Open Oracle's SQLPlus
  2. Connect to the database
  3. Use the username and password for the DBMS user used by the application
  4. Execute the command @"<GX_INSTALL_DIR>\Packages\K2BTools\Audit\Oracle\Audk2b.sql".

If the command fails, the errors can be viewed using the command "Show Errors"

Troubleshooting

When creating the package you get the following error:

0/0      PL/SQL: Compilation unit analysis terminated
1/14     PLS-00304: cannot compile body of 'AUDK2B' without its specification
1/14     PLS-00905: object dbname.AUDK2B is invalid

In order to solve the issue check that step 4.1 was property done.