Auditing View Calls and Stored Procedures Calls

Views

Views is a powerful tool for working with data. View is a virtual table, contents of which resembles contents of database tables included into view. View’s content is changed dynamically together with the contents of database tables included into view. Views can be based on database tables or on another views.

Stored Procedures

Often, data managing requires using of a set of instructions (queries), executed in a certain order. For example, if you want to buy an item from an online store, you should add some data into your table which contains customers’ orders. But you should check some conditions before that – for example if the item is in stock and so on. Thus, the shopping process includes a number of actions you should perform in a certain sequence. In this case it would be wise to enclose all these actions into a single object – a stored procedure. Stored procedures enable you to work with a set of actions as with a single object, and stored procedures are executed faster than regular SQL queries because procedures are compiled only one time and then they are stored in compiled state in your database. Besides that, stored procedures enable you to run complex scenarios included into procedures with only one statement. This enables avoiding of sending a lot of data from client to server which increases performance.

What DataSunrise Offers?

DataSunrise’s Data Audit feature can audit calls (SELECTs) of views and stored procedures if they are associated with a table included into a DataSunrise’s Audit Rule. In other words, if you create a Rule and select a table to audit access to, DataSunrise will log not only direct SELECTs but also SELECTs of views if the table of interest is included into the view and stored procedure calls if the procedure accesses the table of interest. This enables you to fully cover your database with a single audit functionality.

Activating the Feature

• First, you should navigate to the System Settings -> Additional parameters and enable the required parameters:
  1. UseMetadataFunctionDDL – for Stored procedures audit
  2. UseMetadataViewDDL – for audit of view calls
• The next step is to create an Audit Rule in the Audit subsection to audit access to the table of interest. Once you enable the required additional parameters, you will see that the following check boxes become active:
  1. Process Queries to Tables and Functions through Function Call
  2. Process Queries to Tables through VIEW

Check the required check box/es.

And that’s it. Now you’re ready to audit view SELECTs and stored procedure calls to your table of interest.