Detective Controls — Database Activity Monitoring (DAM)
This is the second article in my series on Database Security that talks about “Detective Controls”. The introductory first article can be checked from https://meenalkin.medium.com/database-security-series-part-i-abf481a020ce
The detective controls for database are also called Database Activity Monitoring (DAM) controls by many regulations/ compliances.
Database Activity Monitoring Compliance Requirements:
The compliance requirements for database monitoring applicable to Indian Financial Organizations can be summarized as follows:
· Digital evidence (IT Act 2008 Rules 2011, Aadhar Act, RBI, ISO27001, SEBI, SWIFT, PCIDSS — -Mandatory to provide digital evidence for sensitive financial transactions
· Traceability
· Accountability
· Integrity of digital evidence (Tamperproof audit maintenance)
These requirements pertain to several practical challenges for the database administrators. Monitoring of all sensitive and critical database operations is required by many regulators or compliances e.g. PCIDSS requires all card operations to be monitored and logged which technical translates to all data manipulation operations (DMLs — INSERT, UPDATE, DELETE) to be logged or data privacy requirements demand that all access to privacy data is to be monitored leading to monitoring all SELECTs on the privacy data. One can enable the logging of such activities by using database native auditing features. However, auditing is an additional process activated on the database so will contribute to the load on the database. Hence, it is highly impractical to enable native auditing for all such DML or data access operation monitoring requirements, as data access and DML operations are very frequent operations and hence auditing the same will put considerable additional load on the database, thereby reducing the efficiency of the database operations for business. Apart from the effect on database performance efficiency, manual audit maintenance also poses the following challenges.
- Near real time analysis of audit records to detect issues not feasible due to large no. of records
- Audit record maintenance and analysis for multiple databases is tedious
- Auditors demand proof that the audit records are maintained tamperproof
- Multiple reports are required for audits
Solution for the DAM Requirements:
Solution to the challenges is using an appropriate Database Activity Monitoring (DAM) tool. A standard DAM tool should have ability to:
· Collect audit records from target databases
· Monitor SQL transactions fired on the target databases independent of the database native auditing activity
· Maintain the collected audit and SQL transaction details in tamperproof manner
· Provide a centralized control for managing and analyzing the collected audit and transaction logs and report and alert generation
· Provide secure access means with facility for segregation of duty for accessing the audit data, reports and alerts
· Efficiently analyze the collected audit and SQL transaction data
· Facilitate configuration of alerts for specific SQL events that will be fired near real time to the SQL event
· Facilitate reliable and efficient report generation for specific SQL events for specific target database
There are several DAM tools available in the market. Most of these tools support heterogeneous databases. Note that, with the exception of Oracle AVDF, most of the widely used tools are part of suits which is amalgamation of various detective, assessment and preventive features. You should not confuse the features presented by these suits with the DAM requirements of regulations. As specified above, the regulatory requirement focus is on accountability and non-repudiation. In addition, you may want to identify irregular / undesired operations quickly so as to prevent / control the damage. So, some preventive abilities added to DAM tool, which is essentially a detective control, may be desirable. However, as specified in my previous article, relying solely on a single tool as both preventive as well as detective control is not a recommended strategy due to mainly the following reasons:
· DAM tools are mostly reactive in nature i.e. they act after the specified event takes place. A comprehensive database security implementation would require some proactive measures as well.
· If the DAM tool fails or is compromised, then there is no line of defense.
· In any kind of pattern detection there are bound to be some false positives ( the SQL pattern is not actually problematic but is declared as problematic by the tool) and false negatives (the SQL pattern is actually problematic but is not identified as problematic by the tool). False positives may lead to denial of service for legitimate user operations, thereby leading to dissatisfied customers and penalties if your service level agreements are violated due to the denial. So, you need to be careful about how and which operational patterns you are blocking. Hence, additional preventive controls are needed to proactively protect against any illegitimate or erroneous operations.
Thus, your Detective / Database Activity Monitoring tool selection should be fitting in with your security plan for your critical databases with all three, preventive, detective and administrative controls combined together. While selecting your DAM tool, you need to consider the following factors:
· Are the basic regulatory requirements of Detective / Activity Monitoring satisfied by the tool.
· Types and number of your critical databases vs the types of databases supported by the tool and capacity of the tool.
· Hardware and network requirements of the tool and flexibility of deployment architectures.
· Impact of deploying the tool on the monitored databases.
· The ease with which the tool can be installed and configured.
· Granularity and ease of configuring the monitoring and blocking rules.
· Features available for scalability, high availability and failover.
· Security of the collected audit and monitoring data.
· Facilities for generation of various reports required by your information security auditors.
· Features available in the tool for securing the tool itself and also facilitating best practices like segregation of duties and need based access for the collected audit data.
As before, since my experience is mostly on the Oracle tool, I am going to elaborate in detail on the Oracle DAM tool, Audit Vault Database Firewall (AVDF).
Oracle DAM Tool, Audit Vault Database Firewall (AVDF)
As specified in my previous article, Oracle has number of preventive controls that are specifically for Oracle database. These preventive controls are all paid extensions of Oracle database — so follow defense from inside. While an AVDF is Oracle’s detective control which is an independent external product that supports activity monitoring of not only Oracle but other databases as well. Now let us see how Oracle Audit Vault Database Firewall (AVDF) provides for the above-mentioned requirements:
· Provides a centralized Detective control for collecting and monitoring audit data from multiple databases, Operating system (OS) directories and other log formats.
· SQL operations on the databases can be monitored and logged independent of database native audit, using Database Firewall (DBFW).
· Secure access management of audit data, reports and alerts.
· Various deployment architectures and collection methods available so as to suit your requirements and network architecture.
· Segregation of duty enabled by separating the administrative and auditing tasks between separate users with facility to restrict the administrative or audit access to specific target databases for specific named users.
· Ensures tamper proof audit data maintenance as the audit data in AVDF repository is protected by database vault and transparent data encryption.
· Easy and efficient report generation facility to satisfy various compliance audit requirements with its out of box reports for majority compliance requirements like PCIDSS, SOX … as well as facility to create custom reports.
· Near real time fine grained corelated alert generation facility for critical database events based on analysis and policy enforcement of consolidated logs.
· Custom reports and powerful BI tools that allow organizations to go as deep as necessary for forensic analysis or e-discovery purposes.
· Easy-to-deploy software appliance based on hardened operating system and database that does not require database administrator (DBA) skills, allowing the solution to be owned and managed by IT security staff.
· Scalability and high availability feature available.
· Easy graphical configuration facilities for monitoring policies and alerts. So not much coding required for the same. Can be handled by information security team not having much database skills.
· With DBFW in “in-line” mode, certain SQL patterns can be blocked or replaced. So, reactive preventive features available.
Introduction to Oracle Audit Vault Database Firewall (AVDF):
Oracle AVDF is a very effective “Detective Control” for heterogeneous databases. AVDF comes as a software appliance (OS + application+ database) and has two component appliances, that can be accessed via browser and SSH connection.
1. Audit Vault Server (AVServer) — This is mandatory component. AVServer is a complete application with Oracle data warehouse at its core, called Audit Vault Repository (AVRepository), and an Oracle application server running the Audit Vault applications and console. AVRepository is used for managing database and OS audit data that is collected by means of Audit Vault Agent (AVAgent) and monitored SQL data collected by means of Database Firewall (DBFW). AVServer also facilitates user management, AVServer and DBFW administration tasks, configurations for monitoring, formulating database audit policies and analysis of the logged data, reporting and alerting. It uses Oracle Database Vault for protecting the audit data in the AVRepository, the Oracle wallet for securing certificates and ASO to encrypt network traffic for databases connections.
2. Database Firewall (DBFW) — The audit data collection can be done by using two different means, Audit Vault Agent (AVAgent) and Database Firewall (DBFW). AVAgent is a lightweight JAVA process that need to be installed on the monitored target host. It collects the audit data generated by the database and /or OS target. DBFW is an optional component. It is a separate appliance that sits at network level and collects SQL traffic for the monitored target. Both AVAgent and DBFW pass on the collected data to AVServer where it is analyzed for report and alert generation.
Different architectures are available for AVDF deployment, depending on your need. To understand the two component appliances specified above, with the two means of audit data collection let us consider the simplest architecture. AVDF installation in purely monitoring mode (out of band mode with SQL traffic port mirroring) is shown in the following architecture diagram:
As explained earlier, there are two component appliances AVServer and DBFW. To optimize the benefit of AVDF, it is recommended to use both these components, with the database and OS audit records collected by AVAgent and SQL network traffic collected by DBFW. As per AVDF terminology, a secured target is a database or database host server that is to be monitored by AVDF.
Since the AVAgent can only collect the audit generated by the database or host OS, it is recommended to activate the database native auditing for only very critical events that take place less frequently and are likely to be carried out locally on the database by database administrator (DBA) users, like the DDL (Data Definition Language — CREATE, ALTER, DROP) commands that will be collected by AVAgent. e.g. user creation or dropping (CREATE / DROP USER) or user profile modification (ALTER PROFILE) operations in a database are not likely to be frequent operations in a production database, if database operation good work practices are followed and these operations are more likely to be carried out by out of box or custom DBA user locally on the database and may have huge impact on the database from security point of view. Most compliances require native database auditing to be enabled for such operations. So, such operations should be audited by native database auditing facilities and the audit details should be collected using the AVAgent.
While DBFW should be used to collect the required SQL traffic for more frequent DML (Data Manipulation Language — INSERT, UPDATE, DELETE) and data access (SELECT) commands. E.g. PCIDSS (Payment Card Industry Data Security Standard) requires that ALL card operations are to be monitored. Now these operations on card data are going to be SELECT or DML operations and likely quite frequent operations that are considerable in number. It is advised that DBFW should be used to monitor and log these operations so that database performance efficiency is not hampered by excessing additional load of audit generation.
A comparison of the two collection mechanisms used by AVDF is represented in the following table:
Considerations for AVDF Deployment:
The following aspects need to be considered for effective AVDF deployment to satisfy your database activity monitoring needs:
· Target Identification- Identify the databases / host servers (secured targets) that need to be monitored and the security compliance norms and data retention norms applicable to these secured targets.
· AVDF Architecture Planning — For AVAgent based monitoring / Host monitoring, network ports need to be opened between AVServer and secured target. For DBFW there are two possible architectures proxy mode and out-of-band mode. In proxy mode, DBFW is introduced as an additional network hop between the network switch managing target database SQL traffic and the target database. In out-of-band mode the SQL traffic for the target database is to be port mirrored to the DBFW. The summary of different DBFW deployment architectures is given below:
You can have multiple DBFW monitoring different target groups, associated with the same AVServer. Depending on your monitoring /monitoring and blocking requirement, network architecture, switch capacity, network port availability and network port opening rules of your organization, decide on the number of AVServers and DBFWs you want to deploy with the corresponding DBFW deployment mode.
· Evaluate High Availability Requirements — You can configure Database Firewalls pairs or Audit Vault Server pairs, or both, to provide a high-availability system architecture. These are known as resilient pairs. It is active-passive configuration. Plan on your high availability configurations as per your business continuity planning (BCP) requirements and organization policies.
· Perform AVServer and DBFW Capacity Planning — AVServer and DBFW comes as a software appliance (OS + database + application bundle of binaries as iso files). You need to install the AVServer and DBFW in appropriate mode on physical or virtual machines. For effective and durable deployment, it is essential to do appropriate sizing for AVServer and DBFW machines. You can download the AVDF sizing calculator excel from Oracle support note “Audit Vault and Database Firewall Best Practices and Sizing Calculator for AVDF 12.2 and AVDF 20 (Doc ID 2092683.1)”. The support note can be accessed by connecting to https://support.oracle.com/portal/ with your Oracle support credentials and searching in “Knowledge Base” for 2092683.1. You need to provide details about the target database audit record generation and number of transactions per second handled by the target database. Giving below the tips for using this sizing calculator.
Tips for using the AVDF Sizing Calculator
The following note explains how to get the sizing details for custom Oracle database as per required by the sizing calculator “AVDF SIZING EXCEL” that can be downloaded using Oracle support note Audit Vault and Database Firewall Best Practices and Sizing Calculator for AVDF 12.2 and AVDF 20 (Doc ID 2092683.1).
The support note can be accessed by connecting to https://support.oracle.com/portal/ with your Oracle support credentials and searching in “Knowledge Base” for 2092683.1.
Please note that the excel cell references given below are as per the sizing guide version 2.4 that can be downloaded from the above-mentioned support note, as updated on 27 Nov. 2020. In case you have a different version of sizing guide excel, then make the entries in the appropriate cells for providing custom information of the target databases.
— — — — — — — — — — — — — — — — — — — — — — — — — — — — — — —
/* Input 1: To find average number of audit records per day per target — cell D12, D13, D14 */
— — — — — — — — — — — — — — — — — — — — — — — — — — — — — — —
— For each of Oracle target databases follow the steps given below:
— Find the database version
select * from V$VERSION;
— If the database is 12C or higher version find whether the database is using unified auditing
select VALUE from V$OPTION where PARAMETER = ‘Unified Auditing’;
— if value is TRUE then the database is using PURE Unified Auditing otherwise it is using standard auditing
— Case1: For 11g database or database not using Unified Auditing
select ceil(max(NO_OF_AUDIT_RECORDS_IN_DAY)) “AVG_AUDIT_REC_PER_DAY” from (select to_char(NTIMESTAMP#,’DAY’),count(*) “NO_OF_AUDIT_RECORDS_IN_DAY”, count(*)*(select AVG_ROW_LEN from dba_tables where table_name=’AUD$’ and owner = ‘SYS’) “AUDIT_SIZE_IN_BYTES_PER_DAY” from SYS.AUD$ where NTIMESTAMP# between (SYSTIMESTAMP-7) and (SYSTIMESTAMP-1) group by to_char(NTIMESTAMP#,’DAY’));
— enter the output of above query in D12/D13/D14 for the target database
— Case 2: Database is using Pure Unified Auditing
select ceil(max(NO_OF_AUDIT_RECORDS_IN_DAY)) “AVG_AUDIT_REC_PER_DAY” from (select to_char(EVENT_TIMESTAMP,’DAY’), count(*) “NO_OF_AUDIT_RECORDS_IN_DAY” ,count(*)*(select AVG_ROW_LEN from dba_tables where table_name=’AUD$UNIFIED’ and owner = ‘AUDSYS’) “AUDIT_SIZE_IN_BYTES_PER_DAY” from AUDSYS.AUD$UNIFIED where EVENT_TIMESTAMP between (SYSTIMESTAMP-7) and (SYSTIMESTAMP-1) group by to_char(EVENT_TIMESTAMP,’DAY’));
— enter the output of above query in D12/D13/D14 for the target database
/* — The Excel cells F12, F13, F14 give the average on-line retention period of 90 days. You need to change this period as per your storage availability and on-line data retention requirement.
Please note that this is the online retention period. After the period is over, the audit data collected in the Audit Vault Server repository database will be available for archival. So, if you keep it 90 days then you will not be able to archive the collected audit data unless it is 90 days old. Hence, should have sufficient storage available to Audit Vault Server to maintain the data online. You cannot change data retention policy in retroactive manner. i.e. any data retention policy changes are applicable to the data collected after the policy change. So, the more is your online retention period the more is Audit Vault storage requirement.
In case you need to run a live report for any of your archived data, you can always retrieve the required data from archival locations back to Audit Vault Server repository temporarily and generate the report. Since you can have multiple archival locations or use NFS archival location, there is more flexibility for arranging archival storage. The minimum online audit retention period for AVServer repository is 30 days.
— For sizing the archival location — check the AVServer storage requirement and on-line retention period. Accordingly calculate the storage required for the archival period needed as per your data retention policy. As per the category of data handled by the database, the retention period can be 1 financial/calendar year to 10 years. */
— — — — — — — — — — — — — — — — — — — — — — — — — — — — — — —
— Input 2: To find the average number of statements logged per day per target D20, D21, D22
— — — — — — — — — — — — — — — — — — — — — — — — — — — — — — —
select ceil(max(TXNCOUNT)*24*60/10) “AVG_NO_STATE_LOGGED_PER_DAY” from V$UNDOSTAT;
— enter the output of above query in cell D20/D21/D22
— — — — — — — — — — — — — — — — — — — — — — — — — — — — — — —
— Agent sizing
— — — — — — — — — — — — — — — — — — — — — — — — — — — — — — —
— Input: Number of audit trails C35 to C39
— Case 1: Oracle database non-unified auditing
— First find whether any fine grain auditing policies are used by using following query
SELECT * FROM DBA_AUDIT_POLICIES ORDER BY POLICY_NAME;
/* If no result is returned then 3 trails per Oracle target database 1- AUD$ 2- adump (OS mandatory auditing and SYS auditing) 3- DB Host OS audit directory. If there are fine grain auditing policies, then 4 trails per Oracle target database 1- AUD$ 2- adump (OS mandatory auditing and SYS auditing) 3- DB Host OS audit directory 4- FGA_LOG$ (fine grain audit table). In case you also want to monitor before / after values then add one more audit trail to above number */
— Case 2: Oracle database using pure unified auditing
/* In case of unified auditing 2 trails per Oracle target database 1- UNIFIED_AUDIT_TRAIL (view with DB and adump audit data) 2- DB Host OS audit directory. In case you also want to monitor before / after values then add one more audit trail to above number. */
— Case 3: Non-Oracle database
/* two audit trails per target database 1- DB audit directory 2- DB Host audit directory */
— — — — — — — — — — — — — — — — — — — — — — — — — — — — — — —
There may be a change needed in sizing calculator 2.4 formula in case you want to go for in-memory option. This option is used to keep data of certain days in memory so as to speed up report generation.
Field G25 which is titled as “1. How many months data will you keep in memory? (optional, needed only if using the in-memory feature. Default=0)” is for specifying the data to be kept in-memory in number of months which leads to very high (2000+ GB) in-memory and even higher total memory requirement for AVServer for majority of practical setups. Such huge data retention in memory is very unlikely to be needed. Generally interactive report generation is for only couple of days, so this field should be changed to “1. How many days data will you keep in memory?” like in the sizing calculator version 2.3.
Also, the corresponding formula for calculation of In-memory option memory size in Field D29 should also be modified. In case you want to use the in-memory feature, then make the following change to formula and then enter the number of “Days” retention in Field G25.
Current Field D29 — In-memory size formula — =ROUNDUP(SUM(H8:H22)*30*ROUNDUP(G25,0),0)
The modified formula — =ROUNDUP(SUM(H8:H22)*ROUNDUP(G25,0),0)
— — — — — — — — — — — — — — — — — — — — — — — — — — — — — — —
In the next article we will see more details on AVDF installation and monitoring configurations.
