Uniplus Consultants

Partitioning in Oracle 11i: A brief case study of partitioning performance

Oracle provides arguably the most advanced and scalable Relational Database Management System (RDBMS) and is the industry leader in the RDBMS space. One of the most powerful features of Oracle is that it offers advanced partitioning features that are leading edge in the enterprise database market. Generally many RDBMS products offer a range of partitioning options including range, list, hash, and composite portioning techniques. Oracle offers all the standard partitioning techniques mentioned as well as newer advanced techniques available in Oracle versions 10.2 and 11.1.6. In more recent versions Oracle is offering extensive partitioning options with feature that project beyond other RDBMS offerings. Recently here at UNIPLUS, a group of developers were engaged in testing some of the latest capabilities of Oracles new partitioning features. They set out to test some of the advanced partitioning features including multi-column range (10.2), and composite range-range partitioning available in 11.1.6.

Test System

The Developers used an enterprise installation of Oracle on a windows Vista 64x Server. The test system was a small mid-range quad-core server with 6GB ram. The hard disk was standard SATA (2.0) configuration with a transfer speed of 3.0MB/s.

Test Configuration

A small Oracle database of 40GB size was configured with dozens of medium-large size tables with more than 20 million records each. Additionally, several DB schemas were also configured with the same number of tables and column structure across all schemas. The same volume of data was loaded into each schema. Each schema would be used as the sole test-bed for a series of test that would measure the performance of the DB tables based on the assigned test criteria. To setup and measure the performance, the developers decided to configure a standard baseline test schema with un-partitioned tables. Another schema tested standard range partitioning. The next two schemas tested the performance for multi-column range partitioning and range-range composite partitioning configurations. The last two schemas tested the performance of multi-column range and range-range composite partitioning configurations with the use of additional global partitioned indexes on the tables.

Test Method

The tests involved measuring the performance of access and retrieve times from the database. The tests were conducted for both single record parse as well as multi-record parse. Additional test were conducted for retrieve and update time to the database.

Results

The test results from the case study were rather interesting. As expected testing in the non-partitioned schemas provided the longest access time, and thus proved to be the worst performers. The performance measurements improved dramatically in benchmark testing of the access times in the standard range partitioned schema. Next, performance measurements in the multi-column range schemas provided some of the best results in all of the study. A single test result of multi-column range partitioning provided the most outstanding results of the study. However the team was unable to replicate the top performance in the study, in any subsequent test of the multi-column range configuration. As such the team discarded the top result and attributed the said result to an anomaly.

Performance benchmarks conducted in the range-range composite partitioning schema provided nearly identical results to the multi-column range partitioning. However the superior structure and ease of configuration in the range-range composite partitioning was much desired over the cumbersome effort of configuring multi-column range partitions. Addition of global partitioned indexes to the advanced multi-column range and range-range partitioning provided nearly no performance gains. However, this may have to do with the small size of the database. Global partitioned indexes should provide more complex and/or larger database size with desirable performance gains.

Conclusion

Overall the test results were somewhat expected. Partitioning in Oracle provided significant improvement in access time over non-partitioned tables. More significantly, both multi-column range partition and range-range partition configured schemas, dramatically improved the access time over standard range partition schemas. This was rather significant and illustrates the performance gains and usefulness of new partitioning features available in Oracle versions 10.2 and 11.1.6. In conclusion the study would recommend configuring database tables to use advanced partitioning features in Oracle. These features provide significant performance gains over standard Oracle partitioning techniques.

Limitations

The research is based on a case study performance of several Oracle database schemas on Windows platform. Although the study was rather useful, additional research in other platforms would be of significance. A compare-contrast study of the performance of partitioning of different DB platforms (i.e. IBM, Sybase) would be highly desirable. Although the study observed significant performance gains, it is thought that performance gains in testing of larger databases could prove to be even more significant. Finally, performance testing in a number of environments, with different configurations, and different database sizes, could strengthen the results obtained from this study.

Web Application Security Testing: IBM AppScan to the Rescue!

In my previous blog, I wrote about the challenges executives and developers are encountering while trying to integrate security tools and processes into the software development life cycle. Since then, I have done a bit research to identify and evaluate the market-leading product in the field of web application security scanning and believe I have a result worth sharing.

Overview

With over a third of global market share, Watchfire AppScan from IBM’s Rational family of products has to be considered the leading web application security scanner worldwide. AppsScan offers solutions for all types of security testing – outsourced, desktop-user and enterprise-wide analysis. Users of all kinds benefit from this application, including application developers, quality assurance teams, penetration testers, security auditors and senior management. AppScan’s engine seems to be quite powerful and capable of providing continuous monitoring and audit of web applications, testing for security and compliance issues, and delivery of reports with specific fix recommendations.

Vulnerability Detection

AppScan also seems to credibly provide security vulnerability detection through simulation of hacker attacks as Cross-Site Scripting; HTTP Response Splitting, Parameter Tampering, Hidden Field Manipulation. Backdoors/Debug Options, Stealth Commanding, Forceful Browsing, Application Buffer Overflow, Cookie Poisoning, SQL Injections, Content Spoofing, LDAP Injection, Xpath Injection and Session Fixation. In addition, AppScan maps to the Open Web Application Security Project (OWASP) Top 10 and to the SANS Top 20 vulnerabilities.

Reporting and Remediation

During a test, AppScan highlights the code that has been determined to be the cause of a specific vulnerability. Reasoning testing is provided in natural language to explain the logic of the test and why an issue was identified. Appscan delta analysis report provides you with the changes that have occurred from one scan to the next. The reported information includes what has been fixed and new security issues that have been introduced since the initial scan.

Support for a total of 40 global regulatory compliance and standards reporting is another interesting feature of this product. BASEL II, Electronic Fund and Transfer Act (EFTA), Payment Card Industry (PCI) Data Security Standards are among supported compliance and standards reporting.

Summing Up

AppScan (v7.5) looks like a valid option for helping to ensure the security and compliance of web applications throughout the software development life cycle. It's been designed for the broadest range of users, from business users to advanced power users who can utilize the added tools and API to create a customized scanning environment. In an upcoming blog I will provide more detailed information on the scanning abilities of AppScan as it pertains to building secure web applications – much more to come!

What to look for in a Web Application Security Testing Solution

In the previous blog, I described the new security tools and methodologies that IBM envisions – since then, I’ve been taking a closer look at the challenges executives and developers are encountering while trying to integrate security tools and processes into the software development lifecycle and have some interesting results to share!

Traditionally, organizations depend on perimeter controls to keep them secure. Unfortunately, network firewalls and network vulnerability scanners can not stop application-level attacks. By design, web applications allow outsiders to interact with an organization’s systems and data. This interaction passes through network defense mechanisms such as firewalls and network-based intrusion detection systems.

Studies indicate that 75 percent of attacks are now targeting web applications, and that these tend to be vulnerable to these types of attacks. Clearly, web application security can no longer be ignored.

From a technology perspective, the use of automated web application security tools is beneficial in scale and cost. An automated tool allows consistent, reliable and scalable examination of web application security vulnerabilities across large diverse environments. In addition, an automated tool can be used to provide consistent and relevant recommendations that are consistent with corporate policy and requirements.

From a process perspective, integrating web application security testing into the software development lifecycle is a key initiative for establishing good risk management. While this can and should be performed by a dedicated and knowledgeable security assessment team as part of the final review, it should also be integrated into the early stages of application development to focus on security issues as they appear and to save on time and cost.

Threats to web applications include but are not limited to:

• Cross-site scripting: 80% of web applications are vulnerable to this threat that would permit Impersonating another user
• SQL injection: 60% of web applications are vulnerable to this threat that would permit total data compromise
• Parameter tampering: 60% of web applications are vulnerable to this threat that would permit fraud and altering distributions
• Cookie poisoning: 40% of web applications are vulnerable to this threat that would open the door to stealing someone’s identity

The optimal web application testing tool would allow dedicated security auditors to provide detailed information around web application vulnerabilities as well as executive, management and developer reporting that is specifically designed for the role of the unique individual.

In the next blog, we will look at IBM Watchfire which is known as the best web application vulnerability assessment software.

In conclusion, Web Application Security Testing tools are becoming more integrated into the software development lifecycle. In my future blog, I would like to evaluate the best product in this category. IBM WatchFire AppScan happens the market leader in this space – much more to come!

First Insights into IBM's $1.5 Billion Security Initiative

In the previous blog, I described IBM’s enormous new security initiative – since then, I’ve been trying to dig deeper into the new tools and methodologies that IBM envisions, and have some results to share!

One of our contacts within IBM was willing to give a fairly detailed under-the-hood look into their thinking:

First, IBM is making sure that their new security framework addresses the full web of compliance requirements, with the following viewed as core:

·         Sarbanes-Oxley Act (SOX), Section 404

·         Health Insurance Portability and Accountability Act (HIPAA)

·         Gramm-Leach-Bliley Act (GLBA) Section 501

·         European Union Data Protection Act

·         Basel II

·         PATRIOT Act

Second, a bit of insight into the methodology - IBM’s approach to security consists of multi-faceted protection from the core to the perimeter and by helping clients put their security policy into practice through applying a unified process for assessing and addressing security and compliance concerns.

The steps involved in this unified process consist of Assessment, Planning, Implement ation and Monitoring.

ASSESSMENT: IBM consultants will inventory enterprise assets, apply security policies and identify and prioritize vulnerabilities – elements will include:

·         Gaining a clear understanding of client’s security and compliance posture

·         Independently identify and prioritize vulnerabilities

·         Inventory enterprise assets

·         Determine adequacy of security systems, processes and policies

·         Continually assess threat profile

IBM intends to identify gaps in client’s security posture by conducting Information Security Assessment or Payment Card Industry Assessment or empower clients to do it themselves using:

·         IBM Proventia® Enterprise Vulnerability Scanner software

·         IBM Tivoli® Security Compliance Manager software

PLANNING: In this step, IBM intends to help organizations to define an enterprise security roadmap that will close any gaps. Enterprise security policies, processes and procedures and enterprise security architecture are also developed and ongoing risk management and compliance programs are put in place. IBM also plans to help clients align security and business priorities or empower them to do it themselves by providing an information security framework

IMPLEMENTATION: Leading-edge intrusion defense, data security, application security and network security to keep clients ahead of the threat. Components:

·         Execute plans to preemptively help protect against internal and external threats

·         Implement security architecture and encryption to help protect critical data

·         Implement identity and access management

·         Centralize policy enforcement for business data and unstructured information

·         Design security incident response management plan

IBM will offer help in implementing plans through Encryption architecture, design and implementation services or empower clients to do it themselves using:

·         IBM FileNet® P8 4.0 software for enterprise content management

·         IBM Tivoli identity and access management technologies

·         IBM Emergency Response Services

MONITORING: This last step consists of advanced monitoring and reporting capabilities designed to help organizations proactively detect, analyze and react to threats through the following services:

·         Monitor and manage security infrastructure 24x7

·         Maintain audit-ready posture

·         Proactively detect, analyze and react to threats

·         Continually monitor trends for emerging threats

In conclusion, this is obviously an ambitious program, but has caught my interest b/c I agree with IBM that the industry’s current approach is neither effective nor sustainable. I’m in favor of any attempt to improve the state of the art, and will continue to dig for details – much more to come!