Feed aggregator

Find the enabled events using ORADEBUG EVENTDUMP

Mihajlo Tekic - Sat, 2014-03-15 09:46

Just a quick note on how to use ORADEBUG in order to find events that are enabled on system or session level.

Starting from Oracle 10.2 you could use ORADEBUG EVENTDUMP in order to get all events enabled either on session or system level. (not sure if this is available in 10.1)


The synopsis is:


oradebug eventdump


Where level is either system, process or session:
 


SQL> oradebug doc event action eventdump
eventdump
- list events that are set in the group
Usage
-------
eventdump( group < system | process | session >)


For demonstration purposes I will set three events, two on session and one on system level.
 


SQL> alter session set events '10046 trace name context forever, level 8';

Session altered.

SQL> alter session set events '942 trace name errorstack';

Session altered.

SQL> alter system set events 'trace[px_control][sql:f54y11t3njdpj]';

System altered.


Now, let's check what events are enabled on SESSION or SYSTEM level using ORADEBUG EVENTDUMP

SQL> oradebug setmypid

SQL> oradebug eventdump session;
trace [RDBMS.PX_CONTROL] [sql:f54y11t3njdpj]
sql_trace level=8
942 trace name errorstack

SQL> oradebug eventdump system
trace [RDBMS.PX_CONTROL] [sql:f54y11t3njdpj]


As you may already know, ORADEBUG requires SYSDBA privilege. In order to check events set for other session, one could do so by attaching to the other session process using oradebug  setospid or oradebug setorapid.

This note was more for my own reference. I hope someone else finds it useful too.

What to Expect When You're Changing the DB_UNIQUE_NAME

Don Seiler - Wed, 2014-03-12 18:31
I recently had to change the db_unique_name of a database to make it jive with our typical database/DataGuard naming policy of appending the datacenter location. For the sake of this post let's say it was changed from ORCL to ORCL_NYC, since this database is in our fictional New York City datacenter.

I did a quick set of tests and thought I'd share the findings to save anyone any unpleasant surprises. Here are the things to expect when changing DB_UNIQUE_NAME.

Change the Parameter Value
First we obviously have to change the value. How we do so is important.
The command:
alter system set db_unique_name=orcl_nyc scope=spfile;

will result in a db_unique_name of ORCL_NYC, in all uppercase, which is used for the path changes we'll discuss later. However using quotes instead:
alter system set db_unique_name='orcl_nyc' scope=spfile;

will result in a lowercase orcl_nyc value used in the parameter and some paths. In either case, the fun begins when you next restart the instance!



ADR Location (i.e. alert log)
The ADR location appends the DB_UNIQUE_NAME to the location specified by the DIAGNOSTIC_DEST initialization parameter (defaulting to the $ORACLE_BASE environment variable value). When you restart after setting the DB_UNIQUE_NAME, your ADR location will be in a new location using the new DB_UNIQUE_NAME. Probably of most interest to you is that this means your alert log location will move, so any tools or scripts that referenced that file directly (e.g. error-scraping scripts or log-rotation jobs) will need to be updated. 

Regardless of quotes or not, the ADR path always used a lowercase string in the path.

Before:

SQL> show parameter db_unique_name

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_unique_name                       string      ORCL
SQL> show parameter background_dump_dest

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
background_dump_dest                 string      /oracle/app/diag/rdbms/orcl/or
                                                 cl/trace

After:

SQL> show parameter db_unique_name

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_unique_name                       string      ORCL_NYC
SQL> show parameter background_dump_dest

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
background_dump_dest                 string      /oracle/app/diag/rdbms/orcl_ny
                                                 c/orcl/trace


Datafile and Online Redo Log OMF Location
If you have the db_create_file_dest (and optionally db_create_online_log_dest_N) parameter set, then Oracle will use the DB_UNIQUE_NAME value in the OMF location for any new datafiles and redo logs created after the change, assuming a full path isn't specified.

SQL> alter tablespace users add datafile size 10m;

Tablespace altered.

SQL> select file_name from dba_data_files
  2  where tablespace_name='USERS';

FILE_NAME
--------------------------------------------------------------------------------
/oracle/app/oradata/ORCL/datafile/data_D-ORCL_I-3995253326_TS-USERS_FNO-4_1r
p30nup
/oracle/app/oradata/ORCL_NYC/datafile/o1_mf_users_9l1r8dx9_.dbf

In this case, Oracle will use an uppercase string regardless of whether or not the DB_UNIQUE_NAME is in upper or lower case. Note that existing files won't be affected, this will only apply to new files.

If this database is part of a DataGuard configuration, you'll want to be sure to update your db_file_name_convert and log_file_name_convert parameters to point to the new location.

FRA (Backups, Archivelogs, Flashback Logs)
In the same spirit of OMF, the FRA will also change locations. Similar to the previous case, the uppercase value of the DB_UNIQUE_NAME is used in the path, regardless of the original case. So, after a change and a couple of log switches, you would see something like this:

SQL> select name from v$archived_log;

NAME
--------------------------------------------------------------------------------

/oracle/app/fast_recovery_area/ORCL/archivelog/2014_03_12/o1_mf_1_13_9l1sgt20_.a
rc

/oracle/app/fast_recovery_area/ORCL/archivelog/2014_03_12/o1_mf_1_14_9l1shfjo_.a
rc

/oracle/app/fast_recovery_area/ORCL_NYC/archivelog/2014_03_12/o1_mf_1_15_9l1sj59
6_.arc

/oracle/app/fast_recovery_area/ORCL_NYC/archivelog/2014_03_12/o1_mf_1_16_9l1sjw4
k_.arc

Again, this will only affect newly created files. Existing backups, archivelogs and flashback logs will not be affected, they remain cataloged in their present locations and will be accessed just fine. RMAN will delete them when needed (or commanded) and then you could choose to delete the empty directories.

Oracle Wallet
I admit this one I didn't think of. If you use an Oracle Wallet, and do not specify a location in the sqlnet.ora file, Oracle looks in the default location of $ORACLE_BASE/admin/<DB_UNIQUE_NAME>/wallet/. Even more interesting, the DB_UNIQUE_NAME value is case sensitive. So you'll need to be aware of this when moving your wallet files to the new location. Here is a quick look at my findings on this matter:

-- db_unique_name set with no quotes
SQL> select wrl_parameter from v$encryption_wallet;

WRL_PARAMETER
--------------------------------------------------------------------------------
/oracle/app/admin/ORCL_NYC/wallet

-- db_unique_name set with quotes, lowercase
SQL> select wrl_parameter from v$encryption_wallet;

WRL_PARAMETER
--------------------------------------------------------------------------------
/oracle/app/admin/orcl_nyc/wallet

I can't say if this makes a difference when running on Windows, which I believe is case-insensitive. But on Linux it makes a difference.

Conclusion
That wraps this one up. I hope it helps a few people out and saves them an hour or two of head-scratching or worse. If there's anything I forgot, please let me know and I'll update this post.
Categories: DBA Blogs

What is real-time? Depends on who you ask

Steve Jones - Wed, 2014-03-12 13:29
"Real-time" its a word that gets thrown about a lot in IT and its worth documenting a few of the different ways it gets used Hard Real-time This is what Real-time Java was created to address (along with Soft Real-time) what is this?  Easiest way to say it is that often in Hard Real-time environments the following statement is true If it doesn't finish in X milliseconds then people might die So
Categories: Fusion Middleware

Mandated Third Party Static Analysis: Bad Public Policy, Bad Security

Mary Ann Davidson - Tue, 2014-03-11 16:08
Many commercial off-the-shelf (COTS) vendors have recently seen an uptick of interest by their customers in third party static analysis or static analysis of binaries (compiled code). Customers who are insisting upon this in some cases have referenced the (then-)SANS Top Twenty Critical Controls (http://www.sans.org/critical-security-controls/) to support their position, specifically, Critical Control 6, Application Software Security:

"Configuration/Hygiene: Test in-house developed and third-party-procured web and other application software for coding errors and malware insertion, including backdoors, prior to deployment using automated static code analysis software. If source code is not available, these organizations should test compiled code using static binary analysis tools (emphases added). In particular, input validation and output encoding routines of application software should be carefully reviewed and tested."

Recently, the "ownership" of the 20 Critical Controls has passed to the Council on CyberSecurity and the particular provision on third party static analysis has been excised. Oracle provided feedback on the provision (more on which below) and appreciates the responsiveness of the editors of the 20 Critical Controls to our comments.

The argument for third party code analysis is that customers would like to know that they are getting “reasonably defect-free” code in a product. All things being equal, knowing you are getting better quality code than not is a good thing, while noting that there is no defect-free or even security defect-free software – no tool finds all problems and they generally don’t find design defects. Also, a product that is “testably free” of obvious defects may still have significant security flaws – like not having any authentication, access control, or auditing. Nobody is arguing that static analysis isn’t a good thing – that’s why a lot of vendors already do it and don’t need a third party to “attest” to their code (assuming there is a basis for trusting the third party other than their saying “trust us”).

Oracle believes third party static analysis is at best infeasible for organizations with mature security assurance practices and – well, a bad idea, not to put too fine a point on it. The reasons why it is a bad idea are expanded upon in detail below, and include: 1) worse, not better security 2) increased security risk to customers, 3) an increased risk of intellectual property theft and 4) increased costs for commercial software providers without a commensurate increase in security. Note that this discussion does not address the use of other tools - such as so-called web vulnerability analysis tools – that operate against “as installed” object code. These tools also have challenges (i.e., a high rate of false positives) but do not in general pose the same security threats, risks and high costs that static analysis as conducted by third parties does.

Discussion: Static analysis tools are one of many means by which vendors of software, including commercial off-the-shelf (COTS) software, can find coding defects that may lead to exploitable security vulnerabilities. Many vendors – especially large COTS providers - do static analysis of their own code as part of a robust, secure software development process. In fact, there are many different types of testing that can be done to improve security and reliability of code, to include regression testing (ensuring that changes to code do not break something else, and that code operates correctly after it has been modified), “fuzzing” tools, web application vulnerability tools and more. No one tool finds all issues or is necessarily even suitable for all technologies or all programming languages. Most companies use a multiplicity of tools that they select based on factors such as cost, ease-of-use, what the tools find, how well and how accurately, programming languages the tool understands, and other factors. Note of course that these tools must be used in a greater security assurance context (security training, ethical hacking, threat modeling, etc.), echoing the popular nostrum that security has to be “baked in, not bolted on.” Static analysis and other tools can’t “bake in” security – just find coding errors that may lead to security weaknesses. More to the point, static analysis tools should correctly be categorized as “code analysis tools” rather than “code testing tools,” because they do not automatically produce accurate or actionable results when run and cannot be used, typically, by a junior developer or quality assurance (QA) person.

These tools must in general be “tuned” or “trained” or in some cases “programmed” to work against a particular code base, and thus the people using them need to be skilled developers, security analysts or QA experts. Oracle has spent many person years evaluating the tools we use, and have made a significant commitment to a particular static analysis tool which works the best against much – but not all – of our code base. We have found that results are not typically repeatable from code base to code base even within a company. That is, just because the tool works well on one code base does not mean it will work equally well on another product -- another reason to work with a strong vendor who will consider improving the tool to address weaknesses. In short, static analysis tools are not a magic bullet for all security ills, and the odds of a third party being able to do meaningful, accurate and cost-effective static code analysis are slim to none.

1. Third party static analysis is not industry-standard practice.
Despite the marketing claims of the third parties that do this, “third party code review” is not “industry best practice.” As it happens, it is certainly not industry-standard practice for multiple reasons, not the least of which is the lack of validation of the entities and tools used to do such “validation” and the lack of standards to measure efficacy, such as what does the tool find, how well, and how cost effectively? As Juvenal so famously remarked, “Quis custodiet ipsos custodes?” (Who watches the watchmen?) Any third party can claim, and many do, that “we have zero false positives” but there is no way to validate such puffery – and it is puffery. (Sarcasm on: I think any company that does static analysis as a service should agree to have their code analyzed by a competitor. After all, we only have Company X’s say-so that they can find all defects known to mankind with zero false positives, whiten your teeth and get rid of ring-around-the-collar, all with a morning-fresh scent!)

The current International Standards Organization (ISO) standard for assurance (which encompasses the validation of secure code development), the international Common Criteria (ISO-15408), is, in fact, retreating from the need for source code access currently required at higher assurance levels (e.g., Evaluation Assurance Level (EAL) 4). While limited vulnerability analysis has been part of higher assurance evaluations currently being deprecated by the U.S. National Information Assurance Partnership (NIAP), static analysis has not been a requirement at commercial assurance levels. Hence, “the current ISO assurance standard” does not include third party static code analysis and thus, “third party static analysis” is not standard industry practice. Lastly, “third party code analysis” is clearly not “industry best practice” if for no other reason than all the major COTS vendors are opposed to it and will not agree to it. We are already analyzing our own code, thanks very much.

(It should be noted that third party systematic manual code review is equally impractical for the code bases of most commercial software. The Oracle database, for example, has multiple millions of lines of code. Manual code review for the scale of code most COTS vendors produce would accomplish little except pad the bank accounts of the consultants doing it without commensurate value provided (or risk reduction) for either the vendor or the customers of the product. Lastly, the nature of commercial development is that the code is continuously in development: the code base literally changes daily. Third party manual code review in these circumstances would accomplish absolutely nothing. It would be like painting a house while it is under construction.)

2. Many vendors already use third party tools to find coding errors that may lead to exploitable security vulnerabilities.
As noted, many large COTS vendors have well-established assurance programs that include the use of a multiplicity of tools to attempt to find not merely defects in their code, but defects that lead to exploitable security vulnerabilities. Since only a vendor can actually fix a product defect in their proprietary code, and generally most vulnerabilities need a “code fix” to eliminate the vulnerability, it makes sense for vendors to run these tools themselves. Many do.

Oracle, for example, has a site license for a COTS static analysis tool and Oracle also produces a static analysis tool in-house (Parfait, which was originally developed by Sun Labs). With Parfait, Oracle has the luxury of enhancing the tool quickly to meet Oracle-specific needs. Oracle has also licensed a web application vulnerability testing tool, and has produced a number of in-house tools that focus on Oracle’s own (proprietary) technologies. It is unlikely that any third party tool can fuzz Oracle PL/SQL as well as Oracle’s own tools, or analyze Oracle’s proprietary SQL networking protocol as well as Oracle’s in-house tools do. The Oracle Ethical Hacking Team (EHT) also develops tools that they use to “hack” Oracle products, some of which are “productized” for use by other development and QA teams. As Oracle runs Oracle Corporation on Oracle products, Oracle has a built-in incentive to write and deliver secure code. (In fact, this is not unusual: many COTS vendors run their own businesses on their own products and are thus highly motivated to build secure products. Third party code testers typically do not build anything that they run their own enterprises on.)

The above tool usage within Oracle is in addition to extensive regression testing of functionality to high levels of code coverage, including regression testing of security functionality. Oracle also uses other third party security tools (many of which are open source) that are vetted and recommended by the Oracle Software Security Assurance (OSSA) team. Additionally, Oracle measures compliance with “use of automated tools” as part of the OSSA program. Compliance against OSSA is reported quarterly to development line-of-business owners as well as executive management (the company president and the CEO). Many vendors have similarly robust assurance programs that include static analysis as one of many means to improve product security.

Several large software vendors have acquired static analysis (or other) code analysis tools. HP, for example, acquired both Fortify and WebInspect and IBM acquired Coverity. This is indicative both of these vendors’ commitment to “the secure code marketplace” but also, one assumes, to secure development within their own organizations. Note that while both vendors have service offerings for the tools, neither is pushing “third party code testing,” which says a lot. Everything, actually.

Note that most vendors will not provide static analysis results to customers for valid business reasons, including ensuring the security of all customers. For example, a vendor who finds a vulnerability may often fix the issue in the version of product that is under development (i.e., the “next product train leaving the station”). Newer versions are more secure (and less costly to maintain since the issue is already fixed and no patch is required). However, most vendors do not - or cannot - fix an issue in all shipping versions of product and certainly not in versions that have been deprecated. Telling customers the specifics of a vulnerability (i.e., by showing them scan results) would put all customers on older, unfixed or deprecated versions at risk.

3. Testing COTS for coding errors and malware insertion, including backdoors, prior to deployment using automated static code analysis software increases costs without a commensurate return on investment (ROI).
The use of static code analysis software is a highly technical endeavor requiring skilled development personnel. There are skill requirements and a necessity for detailed operational knowledge of how the software is built to help eliminate false positives, factors that raise the cost of this form of “testing.” Additionally, static code analysis tools are not the tool of choice for detecting malware or backdoors. (It is in fact, trivial, to come up with a “backdoor” that, if inserted into code, would not be detected by even the best static analysis tools. There was an experiment at Sandia Labs in which a backdoor was inserted into code and code reviewers told where in code to look for it. They could not find it – even knowing where to look.)

If the real concern of a customer insisting on a third party code scan is malware and backdoor detection: it won’t work and thus represents an extremely expensive – and useless – distraction.

4. Third party code analysis will diminish overall product security.
It is precisely leading vendors’ experience with static analysis tools that contributes to their unwillingness to have third parties attempt to analyze code – emphasis on “attempt.” None of these tools are “plug and play": in some cases, it has taken years, not months, to be able to achieve actionable results, even from the best available static analysis tools. These are in fact code analysis tools and must be “tuned” – and in some cases actually “programmed”- to understand code, and must typically be run by an experienced developer (that is, a developer who understand the particular code base being analyzed) for results to be useful and actionable. There are many reasons why static analysis tools either raise many false positives, or skip entire bodies of code. For example, because of the way Oracle implements particular functionality (memory management) in the database, static analysis tools that look for buffer overflows either do not work, or raise false positives (Oracle writes its own checks to look for those issues).

The rate of false positives from use of a “random” tool run by inexperienced operators – especially on a code base as large as that of most commercial products – would put a vendor in the position of responding to unsubstantiated fear, uncertainty, and doubt (FUD). In a code base of 10,000,000 lines of code, even a false positive rate of one per 1000 lines of code would yield 10,000 “false positives” to chase down. The cost of doing this is prohibitive. (One such tool run against a large Oracle code base generated a false positive for every 3.4 lines of code, or about 160,000 false positives in toto due to the size of the code base.)

This is why most people using these tools must “tune” them to drown out “noise.” Many vendors have already had this false positive issue with customers running web application vulnerability tools and delivering in some cases hundreds of pages of “alarms” in which there were, perhaps, a half page of actionable issues. The rate of false positives is the single biggest determinant whether these tools are worth using or an expensive distraction (aka “rathole”).

No third party firm has to prove that their tool is accurate – especially not if the vendor is forced to use a third party to validate their code – and thus there is little to no incentive to improve their tool. Consultants get paid more the longer they are on site and working. A legislative or “standards” requirement for “third party code analysis” is therefore a license for the third party doing it to print money. Putting it differently, if the use of third party static analysis was accurate and cost effective, why wouldn’t vendors already be doing it? Instead, many vendors use static analysis tools in-house, because they own the code, and are willing to assume the cost of going up the learning curve for a long term benefit to them of reduced defects (and reduced cost of fixing these defects as more vulnerabilities are found earlier in the development cycle).

In short, the use of a third party is the most expensive, non-useful, high-cost attempt at “better code” most vendors could possibly use, and would result in worse security, not better security as in-house “security boots on the ground” are diverted to working with the third party. It is unreasonable to expect any vendor to in effect tune a third party tool and train the third party on their code – and then have to pay the third party for the privilege of doing it. Third party static analysis represents an unacceptably high opportunity cost caused by the “crowding out effect” of taking scarce security resources and using them on activity of low value to the vendor and to their customers. The only “winner” here is the third party. Ka-chink. Ka-chink.

5. Third party code analysis puts customers at increased risk.
As noted, there is no standard for what third party static analysis tools find, let alone how well and how economically they find it. More problematically, there are no standards for protection of any actual vulnerabilities these tools find. In effect, third party code analysis allows the third party to amass a database of unfixed vulnerabilities in products without any requirements for data protection or any recourse should that information be sold, incorporated into a hacking tool or breached. The mere fact of a third party amassing such sensitive information makes the third party a hacker target. Why attempt to hack products one by one if you can break into a third party’s network and get a listing of defects across multiple products – in the handy “economy size?” The problem is magnified if the “decompiled” source code is stored at the third party: such source code would be an even larger hacker target than the list of vulnerabilities the third party found.

Most vendors have very strict controls not merely on their source code, but on the information about product vulnerabilities that they know about and are triaging and fixing. Oracle Corporation, for example, has stringent security vulnerability handling policies that are promulgated and “scored” as part of Oracle’s software and hardware assurance program. Oracle uses its own secure database technology (row level access control) to enforce “need to know” on security vulnerabilities, information that is considered among the most sensitive information the company has. Security bugs are not published (meaning, they are not generally searchable and readable across the company or accessible by customers). Also, security bug access is stringently limited to those working on a bug fix (and selected others, such as security analysts and the security point of contact (SPOC) for the development area).

One of the reasons Oracle is stringent about limiting access to security vulnerability information is that this information often does leak when “managed” by third parties, even third parties with presumed expertise in secret-keeping. In the past, MI5 circulated information about a non-public Oracle database vulnerability among UK defense and intelligence entities (it should be noted that nobody reported this to Oracle, despite the fact that only Oracle could issue a patch for the issue). Oracle was only notified about the bug by a US commercial company to whom the information had leaked. As the saying goes, two people can’t keep a secret.

There is another risk that has not generally been considered in third party static analysis, and that is the increased interest in cyber-offense. There is evidence that the market for so-called zero-day vulnerabilities is being fueled in part by governments seeking to develop cyber-offense tools. (StuxNet, for example, allegedly made use of at least four “zero-day” vulnerabilities: that is, vulnerabilities not previously reported to a vendor.) Coupled with the increased interest in military suppliers/system integrators in getting into the “cyber security business,” it is not a stretch to think that at last some third parties getting into the “code analysis” business can and would use that as an opportunity to “sell to both sides” – use legislative fiat or customer pressure to force vendors to consent to static analysis, and then surreptitiously sell the vulnerabilities they found to the highest bidder as zero-days. Who would know?

Governments in particular cannot reasonably simultaneously fuel the market in zero days, complain at how irresponsible their COTS vendors are for not building better code and/or insist on third party static analysis. This is like stoking the fire and then complaining that the room is too hot.

6. Equality of access to vulnerability information protects all customers.
Most vendors do not provide advance information on security vulnerabilities to some customers but not others, or more information about security vulnerabilities to some customers but not others. As noted above, one reason for this is the heightened risk that such information will leak, and put the customers “not in the know” at increased risk. Not to mention, all customers believe their secrets are as worthy of protection as any other customer: nobody wants to be on the “Last Notified” list.

Thus, third party static analysis is problematic because it may result in violating basic fairness and equality in terms of vulnerability disclosure in the rare instances where these scans actually find exploitable vulnerabilities. The business model for some vendors offering static analysis as a service is to convince the customers of the vendor that the vendor is an evil slug and cannot be trusted, and thus the customer should insist on the third party analyzing the vendors’ code base.

There is an implicit assumption that the vendor will fix vulnerabilities that the third party static analysis finds immediately, or at least, before the customer buys/installs the product. However, the reality is more subtle than that. For one thing, it is almost never the case that a vulnerability exists in one and only one version of product: it may also exist on older versions. Complicating the matter: some issues cannot be “fixed” via a patch to the software but require the vendor to rearchitect functionality. This typically can only be done in so-called major product releases, which may only occur every two to three years. Furthermore, such issues often cannot be fixed on older versions because the scope of change is so drastic it will break dependent applications. Thus, a customer (as well as the third party) has information about a “not-easily-fixed” vulnerability which puts other customers at a disadvantage and at risk to the extent that information may leak.

Therefore, allowing some customers access to the results of a third party code scan in advance of a product release would violate most vendors’ disclosure policies as well as actually increasing risk to many, many customers, and potentially that increased risk could exist for a long period of time.

7. Third party code analysis sets an unacceptable precedent that risks vendors’ core intellectual property (IP).
COTS vendors maintain very tight control over their proprietary source code because it is core, high-value IP. As such, most COTS vendors will not allow third parties to conduct static analysis against source code (and for purposes of this discussion, this includes static analysis against binaries, which typically violates standard license agreements).

Virtually all companies are aware of the tremendous cost of intellectual property theft: billions of dollars per year, according to published reports. Many nation states, including those that condone if not encourage wholesale intellectual property theft, are now asking for source code access as a condition of selling COTS products into their markets. Most COTS vendors have refused these requests. One can easily imagine that for some nation states, the primary reason to request source code access (or, alternatively, “third party analysis of code”) is for intellectual property theft or economic espionage. Once a government-sanctioned third party has access to source code, so may the government. (Why steal source code if you can get a vendor to gift wrap it and hand it to you under the rubric of “third party code analysis?”)

Another likely reason some governments may insist on source code access (or third party code analysis) is to analyze the code for weaknesses they then exploit for their own national security purposes (e.g., more intellectual property theft). All things being equal, it is easier to find defects in source code than in object code. Refusing to accede to these requests – in addition to, of course, a vendor doing its own code analysis and defect remediation – thus protects all customers. In short, agreeing to any third party code analysis involving source code – either static analysis or static analysis of binaries - would make it very difficult if not impossible for a vendor to refuse any other similar requests for source code access, which would put their core intellectual property at risk. Third party code analysis is a very bad idea because there is no way to “undo” a precedent once it is set.

Summary
Software should have a wide variety of tests performed before it is shipped and additional security tests (such as penetration tests) should be used against “as-deployed” software. However, the level of testing should be commensurate with the risk, which is both basic risk management and appropriate (scarce) resource management. A typical firm has many software elements, most probably COTS, and to suggest that they all be tested with static analysis tools begs a sanity check. The scope of COTS alone argues against this requirement: COTS products run the gamut from operating systems to databases to middleware, business intelligence and other analytic tools, business applications (accounting, supply chain management, manufacturing) as well as specialized vertical market applications (e.g., clinical trial software),representing a number of programming languages and billions – no, hundreds of billions - of lines of code.

The use of static analysis tools in development to help find and remediate security vulnerabilities is a good assurance practice, albeit a difficult one because of the complexity of software and the difficulty of using these tools. The only utility of these tools is that they be used by the producer of software in a cost- effective way geared towards sustained vulnerability reduction over time. The mandated use of third party static analysis to “validate” or “test” code is unsupportable, for reasons of cost (especially opportunity cost), precedence, increased risk to vendors’ IP and increased security risk to customers. The third party static code analysis market is little more than a subterfuge for enabling the zero-day vulnerability market: bad security, at a high cost, and very bad public policy.

Book of the Month
It’s been so long since I blogged, it’s hard to pick out just a few books to recommend. Here are three, and a "freebie":

Hawaiki Rising: Hōkūle’a, Nainoa Thompson and the Hawaiian Renaissance by Sam Low
Among the most amazing tidbits of history are the vast voyages that the Polynesians made to settle (and travel among) Tahiti, Hawai’i and Aotearoa (New Zealand) using navigational methods largely lost to history. (Magellan – meh – he had a compass and sextant.) This book describes the re-creation of Polynesian wayfinding in Hawai’i in the 1970s via the building of a double-hulled Polynesian voyaging canoe, the Hōkūle’a, and how one amazing Hawaiian (Nainoa Thompson) – under the tutelage of one of the last practitioners of wayfinding (Mau Piailug) – made an amazing voyage from Hawai’i to Tahiti using only his knowledge of the stars, the winds, and the currents. (Aside: one of my favorite songs is “H
ōkūle’a Hula,” which describes this voyage, and is so nicely performed by Erik Lee.) Note: the Hōkūle’a is currently on a voyage around the world.

The Korean War by Max Hastings
Max Hastings is one of the few historians whom I think is truly balanced: he looks at the moral issues of history, weighs them, and presents a fair analysis – not “shove-it-down-your-throat revisionism.” He also makes use of a lot of first-person accounts, which makes history come alive. The Korean War is in so many cases a forgotten war, especially the fact that it literally is a war that never ended. It’s a good lesson of history, as it is made clear that the US drew down their military so rapidly and drastically after the World War II that we were largely (I am trying not to say “completely”) unprepared for Korea. (Moral: there is always another war.)

Code Talker by Chester Nez
Many people now know of the crucial role that members of the Navajo Nation played in the Pacific War: the code they created that provided a crucial advantage (and was never broken). This book is a first-person account of the experiences of one Navajo code talker, from his experiences growing up on the reservation to his training as a Marine, and his experiences in the Pacific Theater. Fascinating.

Securing Oracle Database 12c: A Technical Primer
If you are a DBA or security professional looking for more information on Oracle database security, then you will be interested in this book. Written by members of Oracle's engineering team and the President of the International Oracle User Group (IOUG), Michelle Malcher, the book provides a primer on capabilities such as data redaction, privilege analysis and conditional auditing. If you have Oracle databases in your environment, you will want to add this book to your collection of professional information. Register now for the complimentary eBook and learn from the experts.


Mandated Third Party Static Analysis: Bad Public Policy, Bad Security

Mary Ann Davidson - Tue, 2014-03-11 16:08

Many commercial off-the-shelf (COTS) vendors have recently seen an uptick of interest by their customers in third party static analysis or static analysis of binaries (compiled code). Customers who are insisting upon this in some cases have referenced the (then-)SANS Top Twenty Critical Controls (http://www.sans.org/critical-security-controls/) to support their position, specifically, Critical Control 6, Application Software Security:

"Configuration/Hygiene: Test in-house developed and third-party-procured web and other application software for coding errors and malware insertion, including backdoors, prior to deployment using automated static code analysis software. If source code is not available, these organizations should test compiled code using static binary analysis tools (emphases added). In particular, input validation and output encoding routines of application software should be carefully reviewed and tested."


Recently, the "ownership" of the 20 Critical Controls has passed to the Council on CyberSecurity and the particular provision on third party static analysis has been excised. Oracle provided feedback on the provision (more on which below) and appreciates the responsiveness of the editors of the 20 Critical Controls to our comments.


The argument for third party code analysis is that customers would like to know that they are getting “reasonably defect-free” code in a product. All things being equal, knowing you are getting better quality code than not is a good thing, while noting that there is no defect-free or even security defect-free software – no tool finds all problems and they generally don’t find design defects. Also, a product that is “testably free” of obvious defects may still have significant security flaws – like not having any authentication, access control, or auditing. Nobody is arguing that static analysis isn’t a good thing – that’s why a lot of vendors already do it and don’t need a third party to “attest” to their code (assuming there is a basis for trusting the third party other than their saying “trust us”).


Oracle believes third party static analysis is at best infeasible for organizations with mature security assurance practices and – well, a bad idea, not to put too fine a point on it. The reasons why it is a bad idea are expanded upon in detail below, and include: 1) worse, not better security 2) increased security risk to customers, 3) an increased risk of intellectual property theft and 4) increased costs for commercial software providers without a commensurate increase in security. Note that this discussion does not address the use of other tools - such as so-called web vulnerability analysis tools – that operate against “as installed” object code. These tools also have challenges (i.e., a high rate of false positives) but do not in general pose the same security threats, risks and high costs that static analysis as conducted by third parties does.


Discussion: Static analysis tools are one of many means by which vendors of software, including commercial off-the-shelf (COTS) software, can find coding defects that may lead to exploitable security vulnerabilities. Many vendors – especially large COTS providers - do static analysis of their own code as part of a robust, secure software development process. In fact, there are many different types of testing that can be done to improve security and reliability of code, to include regression testing (ensuring that changes to code do not break something else, and that code operates correctly after it has been modified), “fuzzing” tools, web application vulnerability tools and more. No one tool finds all issues or is necessarily even suitable for all technologies or all programming languages. Most companies use a multiplicity of tools that they select based on factors such as cost, ease-of-use, what the tools find, how well and how accurately, programming languages the tool understands, and other factors. Note of course that these tools must be used in a greater security assurance context (security training, ethical hacking, threat modeling, etc.), echoing the popular nostrum that security has to be “baked in, not bolted on.” Static analysis and other tools can’t “bake in” security – just find coding errors that may lead to security weaknesses. More to the point, static analysis tools should correctly be categorized as “code analysis tools” rather than “code testing tools,” because they do not automatically produce accurate or actionable results when run and cannot be used, typically, by a junior developer or quality assurance (QA) person.


These tools must in general be “tuned” or “trained” or in some cases “programmed” to work against a particular code base, and thus the people using them need to be skilled developers, security analysts or QA experts. Oracle has spent many person years evaluating the tools we use, and have made a significant commitment to a particular static analysis tool which works the best against much – but not all – of our code base. We have found that results are not typically repeatable from code base to code base even within a company. That is, just because the tool works well on one code base does not mean it will work equally well on another product -- another reason to work with a strong vendor who will consider improving the tool to address weaknesses. In short, static analysis tools are not a magic bullet for all security ills, and the odds of a third party being able to do meaningful, accurate and cost-effective static code analysis are slim to none.


1. Third party static analysis is not industry-standard practice.
Despite the marketing claims of the third parties that do this, “third party code review” is not “industry best practice.” As it happens, it is certainly not industry-standard practice for multiple reasons, not the least of which is the lack of validation of the entities and tools used to do such “validation” and the lack of standards to measure efficacy, such as what does the tool find, how well, and how cost effectively? As Juvenal so famously remarked, “Quis custodiet ipsos custodes?” (Who watches the watchmen?) Any third party can claim, and many do, that “we have zero false positives” but there is no way to validate such puffery – and it is puffery. (Sarcasm on: I think any company that does static analysis as a service should agree to have their code analyzed by a competitor. After all, we only have Company X’s say-so that they can find all defects known to mankind with zero false positives, whiten your teeth and get rid of ring-around-the-collar, all with a morning-fresh scent!)


The current International Standards Organization (ISO) standard for assurance (which encompasses the validation of secure code development), the international Common Criteria (ISO-15408), is, in fact, retreating from the need for source code access currently required at higher assurance levels (e.g., Evaluation Assurance Level (EAL) 4). While limited vulnerability analysis has been part of higher assurance evaluations currently being deprecated by the U.S. National Information Assurance Partnership (NIAP), static analysis has not been a requirement at commercial assurance levels. Hence, “the current ISO assurance standard” does not include third party static code analysis and thus, “third party static analysis” is not standard industry practice. Lastly, “third party code analysis” is clearly not “industry best practice” if for no other reason than all the major COTS vendors are opposed to it and will not agree to it. We are already analyzing our own code, thanks very much.


(It should be noted that third party systematic manual code review is equally impractical for the code bases of most commercial software. The Oracle database, for example, has multiple millions of lines of code. Manual code review for the scale of code most COTS vendors produce would accomplish little except pad the bank accounts of the consultants doing it without commensurate value provided (or risk reduction) for either the vendor or the customers of the product. Lastly, the nature of commercial development is that the code is continuously in development: the code base literally changes daily. Third party manual code review in these circumstances would accomplish absolutely nothing. It would be like painting a house while it is under construction.)


2. Many vendors already use third party tools to find coding errors that may lead to exploitable security vulnerabilities.
As noted, many large COTS vendors have well-established assurance programs that include the use of a multiplicity of tools to attempt to find not merely defects in their code, but defects that lead to exploitable security vulnerabilities. Since only a vendor can actually fix a product defect in their proprietary code, and generally most vulnerabilities need a “code fix” to eliminate the vulnerability, it makes sense for vendors to run these tools themselves. Many do.


Oracle, for example, has a site license for a COTS static analysis tool and Oracle also produces a static analysis tool in-house (Parfait, which was originally developed by Sun Labs). With Parfait, Oracle has the luxury of enhancing the tool quickly to meet Oracle-specific needs. Oracle has also licensed a web application vulnerability testing tool, and has produced a number of in-house tools that focus on Oracle’s own (proprietary) technologies. It is unlikely that any third party tool can fuzz Oracle PL/SQL as well as Oracle’s own tools, or analyze Oracle’s proprietary SQL networking protocol as well as Oracle’s in-house tools do. The Oracle Ethical Hacking Team (EHT) also develops tools that they use to “hack” Oracle products, some of which are “productized” for use by other development and QA teams. As Oracle runs Oracle Corporation on Oracle products, Oracle has a built-in incentive to write and deliver secure code. (In fact, this is not unusual: many COTS vendors run their own businesses on their own products and are thus highly motivated to build secure products. Third party code testers typically do not build anything that they run their own enterprises on.)


The above tool usage within Oracle is in addition to extensive regression testing of functionality to high levels of code coverage, including regression testing of security functionality. Oracle also uses other third party security tools (many of which are open source) that are vetted and recommended by the Oracle Software Security Assurance (OSSA) team. Additionally, Oracle measures compliance with “use of automated tools” as part of the OSSA program. Compliance against OSSA is reported quarterly to development line-of-business owners as well as executive management (the company president and the CEO). Many vendors have similarly robust assurance programs that include static analysis as one of many means to improve product security.


Several large software vendors have acquired static analysis (or other) code analysis tools. HP, for example, acquired both Fortify and WebInspect and IBM acquired Coverity. This is indicative both of these vendors’ commitment to “the secure code marketplace” but also, one assumes, to secure development within their own organizations. Note that while both vendors have service offerings for the tools, neither is pushing “third party code testing,” which says a lot. Everything, actually.


Note that most vendors will not provide static analysis results to customers for valid business reasons, including ensuring the security of all customers. For example, a vendor who finds a vulnerability may often fix the issue in the version of product that is under development (i.e., the “next product train leaving the station”). Newer versions are more secure (and less costly to maintain since the issue is already fixed and no patch is required). However, most vendors do not - or cannot - fix an issue in all shipping versions of product and certainly not in versions that have been deprecated. Telling customers the specifics of a vulnerability (i.e., by showing them scan results) would put all customers on older, unfixed or deprecated versions at risk.


3. Testing COTS for coding errors and malware insertion, including backdoors, prior to deployment using automated static code analysis software increases costs without a commensurate return on investment (ROI).
The use of static code analysis software is a highly technical endeavor requiring skilled development personnel. There are skill requirements and a necessity for detailed operational knowledge of how the software is built to help eliminate false positives, factors that raise the cost of this form of “testing.” Additionally, static code analysis tools are not the tool of choice for detecting malware or backdoors. (It is in fact, trivial, to come up with a “backdoor” that, if inserted into code, would not be detected by even the best static analysis tools. There was an experiment at Sandia Labs in which a backdoor was inserted into code and code reviewers told where in code to look for it. They could not find it – even knowing where to look.)


If the real concern of a customer insisting on a third party code scan is malware and backdoor detection: it won’t work and thus represents an extremely expensive – and useless – distraction.


4. Third party code analysis will diminish overall product security.
It is precisely leading vendors’ experience with static analysis tools that contributes to their unwillingness to have third parties attempt to analyze code – emphasis on “attempt.” None of these tools are “plug and play": in some cases, it has taken years, not months, to be able to achieve actionable results, even from the best available static analysis tools. These are in fact code analysis tools and must be “tuned” – and in some cases actually “programmed”- to understand code, and must typically be run by an experienced developer (that is, a developer who understand the particular code base being analyzed) for results to be useful and actionable. There are many reasons why static analysis tools either raise many false positives, or skip entire bodies of code. For example, because of the way Oracle implements particular functionality (memory management) in the database, static analysis tools that look for buffer overflows either do not work, or raise false positives (Oracle writes its own checks to look for those issues).


The rate of false positives from use of a “random” tool run by inexperienced operators – especially on a code base as large as that of most commercial products – would put a vendor in the position of responding to unsubstantiated fear, uncertainty, and doubt (FUD). In a code base of 10,000,000 lines of code, even a false positive rate of one per 1000 lines of code would yield 10,000 “false positives” to chase down. The cost of doing this is prohibitive. (One such tool run against a large Oracle code base generated a false positive for every 3.4 lines of code, or about 160,000 false positives in toto due to the size of the code base.)


This is why most people using these tools must “tune” them to drown out “noise.” Many vendors have already had this false positive issue with customers running web application vulnerability tools and delivering in some cases hundreds of pages of “alarms” in which there were, perhaps, a half page of actionable issues. The rate of false positives is the single biggest determinant whether these tools are worth using or an expensive distraction (aka “rathole”).


No third party firm has to prove that their tool is accurate – especially not if the vendor is forced to use a third party to validate their code – and thus there is little to no incentive to improve their tool. Consultants get paid more the longer they are on site and working. A legislative or “standards” requirement for “third party code analysis” is therefore a license for the third party doing it to print money. Putting it differently, if the use of third party static analysis was accurate and cost effective, why wouldn’t vendors already be doing it? Instead, many vendors use static analysis tools in-house, because they own the code, and are willing to assume the cost of going up the learning curve for a long term benefit to them of reduced defects (and reduced cost of fixing these defects as more vulnerabilities are found earlier in the development cycle).


In short, the use of a third party is the most expensive, non-useful, high-cost attempt at “better code” most vendors could possibly use, and would result in worse security, not better security as in-house “security boots on the ground” are diverted to working with the third party. It is unreasonable to expect any vendor to in effect tune a third party tool and train the third party on their code – and then have to pay the third party for the privilege of doing it. Third party static analysis represents an unacceptably high opportunity cost caused by the “crowding out effect” of taking scarce security resources and using them on activity of low value to the vendor and to their customers. The only “winner” here is the third party. Ka-chink. Ka-chink.


5. Third party code analysis puts customers at increased risk.
As noted, there is no standard for what third party static analysis tools find, let alone how well and how economically they find it. More problematically, there are no standards for protection of any actual vulnerabilities these tools find. In effect, third party code analysis allows the third party to amass a database of unfixed vulnerabilities in products without any requirements for data protection or any recourse should that information be sold, incorporated into a hacking tool or breached. The mere fact of a third party amassing such sensitive information makes the third party a hacker target. Why attempt to hack products one by one if you can break into a third party’s network and get a listing of defects across multiple products – in the handy “economy size?” The problem is magnified if the “decompiled” source code is stored at the third party: such source code would be an even larger hacker target than the list of vulnerabilities the third party found.


Most vendors have very strict controls not merely on their source code, but on the information about product vulnerabilities that they know about and are triaging and fixing. Oracle Corporation, for example, has stringent security vulnerability handling policies that are promulgated and “scored” as part of Oracle’s software and hardware assurance program. Oracle uses its own secure database technology (row level access control) to enforce “need to know” on security vulnerabilities, information that is considered among the most sensitive information the company has. Security bugs are not published (meaning, they are not generally searchable and readable across the company or accessible by customers). Also, security bug access is stringently limited to those working on a bug fix (and selected others, such as security analysts and the security point of contact (SPOC) for the development area).


One of the reasons Oracle is stringent about limiting access to security vulnerability information is that this information often does leak when “managed” by third parties, even third parties with presumed expertise in secret-keeping. In the past, MI5 circulated information about a non-public Oracle database vulnerability among UK defense and intelligence entities (it should be noted that nobody reported this to Oracle, despite the fact that only Oracle could issue a patch for the issue). Oracle was only notified about the bug by a US commercial company to whom the information had leaked. As the saying goes, two people can’t keep a secret.


There is another risk that has not generally been considered in third party static analysis, and that is the increased interest in cyber-offense. There is evidence that the market for so-called zero-day vulnerabilities is being fueled in part by governments seeking to develop cyber-offense tools. (StuxNet, for example, allegedly made use of at least four “zero-day” vulnerabilities: that is, vulnerabilities not previously reported to a vendor.) Coupled with the increased interest in military suppliers/system integrators in getting into the “cyber security business,” it is not a stretch to think that at last some third parties getting into the “code analysis” business can and would use that as an opportunity to “sell to both sides” – use legislative fiat or customer pressure to force vendors to consent to static analysis, and then surreptitiously sell the vulnerabilities they found to the highest bidder as zero-days. Who would know?


Governments in particular cannot reasonably simultaneously fuel the market in zero days, complain at how irresponsible their COTS vendors are for not building better code and/or insist on third party static analysis. This is like stoking the fire and then complaining that the room is too hot.


6. Equality of access to vulnerability information protects all customers.
Most vendors do not provide advance information on security vulnerabilities to some customers but not others, or more information about security vulnerabilities to some customers but not others. As noted above, one reason for this is the heightened risk that such information will leak, and put the customers “not in the know” at increased risk. Not to mention, all customers believe their secrets are as worthy of protection as any other customer: nobody wants to be on the “Last Notified” list.


Thus, third party static analysis is problematic because it may result in violating basic fairness and equality in terms of vulnerability disclosure in the rare instances where these scans actually find exploitable vulnerabilities. The business model for some vendors offering static analysis as a service is to convince the customers of the vendor that the vendor is an evil slug and cannot be trusted, and thus the customer should insist on the third party analyzing the vendors’ code base.


There is an implicit assumption that the vendor will fix vulnerabilities that the third party static analysis finds immediately, or at least, before the customer buys/installs the product. However, the reality is more subtle than that. For one thing, it is almost never the case that a vulnerability exists in one and only one version of product: it may also exist on older versions. Complicating the matter: some issues cannot be “fixed” via a patch to the software but require the vendor to rearchitect functionality. This typically can only be done in so-called major product releases, which may only occur every two to three years. Furthermore, such issues often cannot be fixed on older versions because the scope of change is so drastic it will break dependent applications. Thus, a customer (as well as the third party) has information about a “not-easily-fixed” vulnerability which puts other customers at a disadvantage and at risk to the extent that information may leak.


Therefore, allowing some customers access to the results of a third party code scan in advance of a product release would violate most vendors’ disclosure policies as well as actually increasing risk to many, many customers, and potentially that increased risk could exist for a long period of time.


7. Third party code analysis sets an unacceptable precedent that risks vendors’ core intellectual property (IP).
COTS vendors maintain very tight control over their proprietary source code because it is core, high-value IP. As such, most COTS vendors will not allow third parties to conduct static analysis against source code (and for purposes of this discussion, this includes static analysis against binaries, which typically violates standard license agreements).


Virtually all companies are aware of the tremendous cost of intellectual property theft: billions of dollars per year, according to published reports. Many nation states, including those that condone if not encourage wholesale intellectual property theft, are now asking for source code access as a condition of selling COTS products into their markets. Most COTS vendors have refused these requests. One can easily imagine that for some nation states, the primary reason to request source code access (or, alternatively, “third party analysis of code”) is for intellectual property theft or economic espionage. Once a government-sanctioned third party has access to source code, so may the government. (Why steal source code if you can get a vendor to gift wrap it and hand it to you under the rubric of “third party code analysis?”)


Another likely reason some governments may insist on source code access (or third party code analysis) is to analyze the code for weaknesses they then exploit for their own national security purposes (e.g., more intellectual property theft). All things being equal, it is easier to find defects in source code than in object code. Refusing to accede to these requests – in addition to, of course, a vendor doing its own code analysis and defect remediation – thus protects all customers. In short, agreeing to any third party code analysis involving source code – either static analysis or static analysis of binaries - would make it very difficult if not impossible for a vendor to refuse any other similar requests for source code access, which would put their core intellectual property at risk. Third party code analysis is a very bad idea because there is no way to “undo” a precedent once it is set.


Summary
Software should have a wide variety of tests performed before it is shipped and additional security tests (such as penetration tests) should be used against “as-deployed” software. However, the level of testing should be commensurate with the risk, which is both basic risk management and appropriate (scarce) resource management. A typical firm has many software elements, most probably COTS, and to suggest that they all be tested with static analysis tools begs a sanity check. The scope of COTS alone argues against this requirement: COTS products run the gamut from operating systems to databases to middleware, business intelligence and other analytic tools, business applications (accounting, supply chain management, manufacturing) as well as specialized vertical market applications (e.g., clinical trial software),
representing a number of programming languages and billions – no, hundreds of billions - of lines of code.


The use of static analysis tools in development to help find and remediate security vulnerabilities is a good assurance practice, albeit a difficult one because of the complexity of software and the difficulty of using these tools. The only utility of these tools is that they be used by the producer of software in a cost- effective way geared towards sustained vulnerability reduction over time. The mandated use of third party static analysis to “validate” or “test” code is unsupportable, for reasons of cost (especially opportunity cost), precedence, increased risk to vendors’ IP and increased security risk to customers. The third party static code analysis market is little more than a subterfuge for enabling the zero-day vulnerability market: bad security, at a high cost, and very bad public policy.


Book of the Month
It’s been so long since I blogged, it’s hard to pick out just a few books to recommend. Here are three, and a "freebie":


Hawaiki Rising: Hōkūle’a, Nainoa Thompson and the Hawaiian Renaissance by Sam Low
Among the most amazing tidbits of history are the vast voyages that the Polynesians made to settle (and travel among) Tahiti, Hawai’i and Aotearoa (New Zealand) using navigational methods largely lost to history. (Magellan – meh – he had a compass and sextant.) This book describes the re-creation of Polynesian wayfinding in Hawai’i in the 1970s via the building of a double-hulled Polynesian voyaging canoe, the Hōkūle’a, and how one amazing Hawaiian (Nainoa Thompson) – under the tutelage of one of the last practitioners of wayfinding (Mau Piailug) – made an amazing voyage from Hawai’i to Tahiti using only his knowledge of the stars, the winds, and the currents. (Aside: one of my favorite songs is “H
ōkūle’a Hula,” which describes this voyage, and is so nicely performed by Erik Lee.) Note: the Hōkūle’a is currently on a voyage around the world.


The Korean War by Max Hastings
Max Hastings is one of the few historians whom I think is truly balanced: he looks at the moral issues of history, weighs them, and presents a fair analysis – not “shove-it-down-your-throat revisionism.” He also makes use of a lot of first-person accounts, which makes history come alive. The Korean War is in so many cases a forgotten war, especially the fact that it literally is a war that never ended. It’s a good lesson of history, as it is made clear that the US drew down their military so rapidly and drastically after the World War II that we were largely (I am trying not to say “completely”) unprepared for Korea. (Moral: there is always another war.)


Code Talker by Chester Nez
Many people now know of the crucial role that members of the Navajo Nation played in the Pacific War: the code they created that provided a crucial advantage (and was never broken). This book is a first-person account of the experiences of one Navajo code talker, from his experiences growing up on the reservation to his training as a Marine, and his experiences in the Pacific Theater. Fascinating.


Securing Oracle Database 12c: A Technical Primer
If you are a DBA or security professional looking for more information on Oracle database security, then you will be interested in this book. Written by members of Oracle's engineering team and the President of the International Oracle User Group (IOUG), Michelle Malcher, the book provides a primer on capabilities such as data redaction, privilege analysis and conditional auditing. If you have Oracle databases in your environment, you will want to add this book to your collection of professional information. Register now for the complimentary eBook and learn from the experts.




Microservices - Money for old rope or re-badging SOA for the cool kids

Steve Jones - Tue, 2014-03-11 13:00
Hat tip to John Evedemon for the heads up on this one.  Martin Fowler is peddling a new approach, 'Microservices' which... wait for it is a way of developing applications as a suite of services.  Each one of which has its own process thread and 'communicates via lightweight mechanisms' such as.... over HTTP. But wait there is more, you'll be stunned to know that these services can be built
Categories: Fusion Middleware

What are the types of Data Scientist?

Steve Jones - Tue, 2014-03-11 11:00
There are various views going around on what a Data Scientist is and what their value is to an organisation and the salaries they command.  To me however asking 'what is a Data Scientist?' is like asking 'What is a Physicist?' sure 'someone who studies Physics' might be a factually accurate but pointless definition.  How does that separate someone who did Physics in High School from Albert
Categories: Fusion Middleware

The Impact of Change

Antony Reynolds - Sun, 2014-03-09 16:01
Measuring Impact of Change in SOA Suite
Mormon prophet Thomas S. Monson once said:

When performance is measured, performance improves. When performance is measured and reported, the rate of performance accelerates.

(LDS Conference Report, October 1970, p107)

Like everything in life, a SOA Suite installation that is monitored and tracked has a much better chance of performing well than one that is not measured.  With that in mind I came up with tool to allow the measurement of the impact of configuration changes on database usage in SOA Suite.  This tool can be used to assess the impact of different configurations on both database growth and database performance, helping to decide which optimizations offer real benefit to the composite under test.

Basic Approach

The basic approach of the tool is to take a snapshot of the number of rows in the SOA tables before executing a composite.  The composite is then executed.  After the composite has completed another snapshot is taken of the SOA tables.  This is illustrated in the diagram below:

An example of the data collected by the tool is shown below:

Test NameTotal Tables ChangedTotal Rows AddedNotesAsyncTest11315Async Interaction with simple SOA composite, one retry to send response.AsyncTest21213Async interaction with simple SOA composite, no retries on sending response.AsyncTest31213Async interaction with simple SOA composite, no callback address provided.OneWayTest11213One-Way interaction with simple SOA composite.SyncTest177Sync interaction with simple SOA composite.

Note that the first three columns are provided by the tool, the fourth column is just an aide-memoir to identify what the test name actually did. The tool also allows us to drill into the data to get a better look at what is actually changing as shown in the table below:

Test NameTable NameRows AddedAsyncTest1AUDIT_COUNTER1AsyncTest1AUDIT_DETAILS1AsyncTest1AUDIT_TRAIL2AsyncTest1COMPOSITE_INSTANCE1AsyncTest1CUBE_INSTANCE1AsyncTest1CUBE_SCOPE1AsyncTest1DLV_MESSAGE1AsyncTest1DOCUMENT_CI_REF1AsyncTest1DOCUMENT_DLV_MSG_REF1AsyncTest1HEADERS_PROPERTIES1AsyncTest1INSTANCE_PAYLOAD1AsyncTest1WORK_ITEM1AsyncTest1XML_DOCUMENT2

Here we have drilled into the test case with the retry of the callback to see what tables are actually being written to.

Finally we can compare two tests to see difference in the number of rows written and the tables updated as shown below:

Test NameBase Test NameTable NameRow DifferenceAsyncTest1AsyncTest2AUDIT_TRAIL1

Here are the additional tables referenced by this test

Test NameBase Test NameAdditional Table NameRows AddedAsyncTest1AsyncTest2WORK_ROWS1How it Works

I created a database stored procedure, soa_snapshot.take_soa_snaphot(test_name, phase). that queries all the SOA tables and records the number of rows in each table.  By running the stored procedure before and after the execution of a composite we can capture the number of rows in the SOA database before and after a composite executes.  I then created a view that shows the difference in the number of rows before and after composite execution.  This view has a number of sub-views that allow us to query specific items.  The schema is shown below:

The different tables and views are:

  • CHANGE_TABLE
    • Used to track number of rows in SOA schema, each test case has two or more phases.  Usually phase 1 is before execution and phase 2 is after execution.
    • This only used by the stored procedure and the views.
  • DELTA_VIEW
    • Used to track changes in number of rows in SOA database between phases of a test case.  This is a view on CHANGE_TABLE.  All other views are based off this view.
  • SIMPLE_DELTA_VIEW
    • Provides number of rows changed in each table.
  • SUMMARY_DELTA_VIEW
    • Provides a summary of total rows and tables changed.
  • DIFFERENT_ROWS_VIEW
    • Provides a summary of differences in rows updated between test cases
  • EXTRA_TABLES_VIEW
    • Provides a summary of the extra tables and rows used by a test case.
    • This view makes use of a session context, soa_ctx, which holds the test case name and the baseline test case name.  This context is initialized by calling the stored procedure soa_ctx_pkg.set(testCase, baseTestCase).

I created a web service wrapper to the take_soa_snapshot procedure so that I could use SoapUI to perform the tests.

Sample OutputHow many rows and tables did a particular test use?

Here we can see how many rows in how many tables changed as a result of running a test:

-- Display the total number of rows and tables changed for each test
select * from summary_delta_view
order by test_name;

TEST_NAME            TOTALDELTAROWS TOTALDELTASIZE TOTALTABLES
-------------------- -------------- -------------- -----------
AsyncTest1                   15              0          13
AsyncTest1noCCIS             15              0          13
AsyncTest1off                 8              0           8
AsyncTest1prod               13              0          12
AsyncTest2                   13              0          12
AsyncTest2noCCIS             13              0          12
AsyncTest2off                 7              0           7
AsyncTest2prod               11              0          11
AsyncTest3                   13              0          12
AsyncTest3noCCIS             13          65536          12
AsyncTest3off                 7              0           7
AsyncTest3prod               11              0          11
OneWayTest1                  13              0          12
OneWayTest1noCCI             13          65536          12
OneWayTest1off                7              0           7
OneWayTest1prod              11              0          11
SyncTest1                     7              0           7
SyncTest1noCCIS               7              0           7
SyncTest1off                  2              0           2
SyncTest1prod                 5              0           5

20 rows selected

Which tables grew during a test?

Here for a given test we can see which tables had rows inserted.

-- Display the tables which grew and show the number of rows they grew by
select * from simple_delta_view
where test_name='AsyncTest1'
order by table_name;
TEST_NAME            TABLE_NAME                      DELTAROWS  DELTASIZE
-------------------- ------------------------------ ---------- ----------
AsyncTest1       AUDIT_COUNTER                           1          0
AsyncTest1       AUDIT_DETAILS                           1          0
AsyncTest1       AUDIT_TRAIL                             2          0
AsyncTest1       COMPOSITE_INSTANCE                      1          0
AsyncTest1       CUBE_INSTANCE                           1          0
AsyncTest1       CUBE_SCOPE                              1          0
AsyncTest1       DLV_MESSAGE                             1          0
AsyncTest1       DOCUMENT_CI_REF                         1          0
AsyncTest1       DOCUMENT_DLV_MSG_REF                    1          0
AsyncTest1       HEADERS_PROPERTIES                      1          0
AsyncTest1       INSTANCE_PAYLOAD                        1          0
AsyncTest1       WORK_ITEM                               1          0
AsyncTest1       XML_DOCUMENT                            2          0
13 rows selected

Which tables grew more in test1 than in test2?

Here we can see the differences in rows for two tests.

-- Return difference in rows updated (test1)
select * from different_rows_view
where test1='AsyncTest1' and test2='AsyncTest2';

TEST1                TEST2                TABLE_NAME                          DELTA
-------------------- -------------------- ------------------------------ ----------
AsyncTest1       AsyncTest2       AUDIT_TRAIL                             1

Which tables were used by test1 but not by test2?

Here we can see tables that were used by one test but not by the other test.

-- Register base test case for use in extra_tables_view
-- First parameter (test1) is test we expect to have extra rows/tables
begin soa_ctx_pkg.set('AsyncTest1', 'AsyncTest2'); end;
/
anonymous block completed
-- Return additional tables used by test1
column TEST2 FORMAT A20
select * from extra_tables_view;
TEST1                TEST2                TABLE_NAME                      DELTAROWS
-------------------- -------------------- ------------------------------ ----------
AsyncTest1       AsyncTest2       WORK_ITEM                               1

Results

I used the tool to find out the following.  All tests were run using SOA Suite 11.1.1.7.

The following is based on a very simple composite as shown below:

Each BPEL process is basically the same as the one shown below:

Impact of Fault Policy Retry Being Executed OnceSettingTotal Rows Written Total Tables UpdatedNo Retry1312One Retry1513

When a fault policy causes a retry then the following additional database rows are written:

Table NameNumber of RowsAUDIT_TRAIL1WORK_ITEM1Impact of Setting Audit Level = Development Instead of ProductionSettingTotal Rows Written Total Tables UpdatedDevelopment1312Production1111

When the audit level is set at development instead of production then the following additional database rows are written:

Table NameNumber of RowsAUDIT_TRAIL1WORK_ITEM1Impact of Setting Audit Level = Production Instead of OffSettingTotal Rows Written Total Tables UpdatedProduction1111Off77

When the audit level is set at production rather than off then the following additional database rows are written:

Table NameNumber of RowsAUDIT_COUNTER1AUDIT_DETAILS1AUDIT_TRAIL1COMPOSITE_INSTANCE1Impact of Setting Capture Composite Instance StateSettingTotal Rows Written Total Tables UpdatedOn1312Off1312

When capture composite instance state is on rather than off then no additional database rows are written, note that there are other activities that occur when composite instance state is captured:

Impact of Setting oneWayDeliveryPolicy = async.cache or syncSettingTotal Rows Written Total Tables Updatedasync.persist1312async.cache77sync77

When choosing async.persist (the default) instead of sync or async.cache then the following additional database rows are written:

Table NameNumber of RowsAUDIT_DETAILS1DLV_MESSAGE1DOCUMENT_CI_REF1DOCUMENT_DLV_MSG_REF1HEADERS_PROPERTIES1XML_DOCUMENT1

As you would expect the sync mode behaves just as a regular synchronous (request/reply) interaction and creates the same number of rows in the database.  The async.cache also creates the same number of rows as a sync interaction because it stores state in memory and provides no restart guarantee.

Caveats & Warnings

The results above are based on a trivial test case.  The numbers will be different for bigger and more complex composites.  However by taking snapshots of different configurations you can produce the numbers that apply to your composites.

The capture procedure supports multiple steps in a test case, but the views only support two snapshots per test case.

Code Download

The sample project I used us available here.

The scripts used to create the user (createUser.sql), create the schema (createSchema.sql) and sample queries (TableCardinality.sql) are available here.

The Web Service wrapper to the capture state stored procedure is available here.

The sample SoapUI project that I used to take a snapshot, perform the test and take a second snapshot is available here.

The Impact of Change

Antony Reynolds - Sun, 2014-03-09 16:01
Measuring Impact of Change in SOA Suite
Mormon prophet Thomas S. Monson once said:

When performance is measured, performance improves. When performance is measured and reported, the rate of performance accelerates.

(LDS Conference Report, October 1970, p107)

Like everything in life, a SOA Suite installation that is monitored and tracked has a much better chance of performing well than one that is not measured.  With that in mind I came up with tool to allow the measurement of the impact of configuration changes on database usage in SOA Suite.  This tool can be used to assess the impact of different configurations on both database growth and database performance, helping to decide which optimizations offer real benefit to the composite under test.

Basic Approach

The basic approach of the tool is to take a snapshot of the number of rows in the SOA tables before executing a composite.  The composite is then executed.  After the composite has completed another snapshot is taken of the SOA tables.  This is illustrated in the diagram below:

An example of the data collected by the tool is shown below:

Test Name Total Tables Changed Total Rows Added Notes AsyncTest1 13 15 Async Interaction with simple SOA composite, one retry to send response. AsyncTest2 12 13 Async interaction with simple SOA composite, no retries on sending response. AsyncTest3 12 13 Async interaction with simple SOA composite, no callback address provided. OneWayTest1 12 13 One-Way interaction with simple SOA composite. SyncTest1 7 7 Sync interaction with simple SOA composite.

Note that the first three columns are provided by the tool, the fourth column is just an aide-memoir to identify what the test name actually did. The tool also allows us to drill into the data to get a better look at what is actually changing as shown in the table below:

Test Name Table Name Rows Added AsyncTest1 AUDIT_COUNTER 1 AsyncTest1 AUDIT_DETAILS 1 AsyncTest1 AUDIT_TRAIL 2 AsyncTest1 COMPOSITE_INSTANCE 1 AsyncTest1 CUBE_INSTANCE 1 AsyncTest1 CUBE_SCOPE 1 AsyncTest1 DLV_MESSAGE 1 AsyncTest1 DOCUMENT_CI_REF 1 AsyncTest1 DOCUMENT_DLV_MSG_REF 1 AsyncTest1 HEADERS_PROPERTIES 1 AsyncTest1 INSTANCE_PAYLOAD 1 AsyncTest1 WORK_ITEM 1 AsyncTest1 XML_DOCUMENT 2

Here we have drilled into the test case with the retry of the callback to see what tables are actually being written to.

Finally we can compare two tests to see difference in the number of rows written and the tables updated as shown below:

Test Name Base Test Name Table Name Row Difference AsyncTest1 AsyncTest2 AUDIT_TRAIL 1

Here are the additional tables referenced by this test

Test Name Base Test Name Additional Table Name Rows Added AsyncTest1 AsyncTest2 WORK_ROWS 1 How it Works

I created a database stored procedure, soa_snapshot.take_soa_snaphot(test_name, phase). that queries all the SOA tables and records the number of rows in each table.  By running the stored procedure before and after the execution of a composite we can capture the number of rows in the SOA database before and after a composite executes.  I then created a view that shows the difference in the number of rows before and after composite execution.  This view has a number of sub-views that allow us to query specific items.  The schema is shown below:

The different tables and views are:

  • CHANGE_TABLE
    • Used to track number of rows in SOA schema, each test case has two or more phases.  Usually phase 1 is before execution and phase 2 is after execution.
    • This only used by the stored procedure and the views.
  • DELTA_VIEW
    • Used to track changes in number of rows in SOA database between phases of a test case.  This is a view on CHANGE_TABLE.  All other views are based off this view.
  • SIMPLE_DELTA_VIEW
    • Provides number of rows changed in each table.
  • SUMMARY_DELTA_VIEW
    • Provides a summary of total rows and tables changed.
  • DIFFERENT_ROWS_VIEW
    • Provides a summary of differences in rows updated between test cases
  • EXTRA_TABLES_VIEW
    • Provides a summary of the extra tables and rows used by a test case.
    • This view makes use of a session context, soa_ctx, which holds the test case name and the baseline test case name.  This context is initialized by calling the stored procedure soa_ctx_pkg.set(testCase, baseTestCase).

I created a web service wrapper to the take_soa_snapshot procedure so that I could use SoapUI to perform the tests.

Sample Output How many rows and tables did a particular test use?

Here we can see how many rows in how many tables changed as a result of running a test:

-- Display the total number of rows and tables changed for each test
select * from summary_delta_view
order by test_name;

TEST_NAME            TOTALDELTAROWS TOTALDELTASIZE TOTALTABLES
-------------------- -------------- -------------- -----------
AsyncTest1                   15              0          13
AsyncTest1noCCIS             15              0          13
AsyncTest1off                 8              0           8
AsyncTest1prod               13              0          12
AsyncTest2                   13              0          12
AsyncTest2noCCIS             13              0          12
AsyncTest2off                 7              0           7
AsyncTest2prod               11              0          11
AsyncTest3                   13              0          12
AsyncTest3noCCIS             13          65536          12
AsyncTest3off                 7              0           7
AsyncTest3prod               11              0          11
OneWayTest1                  13              0          12
OneWayTest1noCCI             13          65536          12
OneWayTest1off                7              0           7
OneWayTest1prod              11              0          11
SyncTest1                     7              0           7
SyncTest1noCCIS               7              0           7
SyncTest1off                  2              0           2
SyncTest1prod                 5              0           5

20 rows selected

Which tables grew during a test?

Here for a given test we can see which tables had rows inserted.

-- Display the tables which grew and show the number of rows they grew by
select * from simple_delta_view
where test_name='AsyncTest1'
order by table_name;
TEST_NAME            TABLE_NAME                      DELTAROWS  DELTASIZE
-------------------- ------------------------------ ---------- ----------
AsyncTest1       AUDIT_COUNTER                           1          0
AsyncTest1       AUDIT_DETAILS                           1          0
AsyncTest1       AUDIT_TRAIL                             2          0
AsyncTest1       COMPOSITE_INSTANCE                      1          0
AsyncTest1       CUBE_INSTANCE                           1          0
AsyncTest1       CUBE_SCOPE                              1          0
AsyncTest1       DLV_MESSAGE                             1          0
AsyncTest1       DOCUMENT_CI_REF                         1          0
AsyncTest1       DOCUMENT_DLV_MSG_REF                    1          0
AsyncTest1       HEADERS_PROPERTIES                      1          0
AsyncTest1       INSTANCE_PAYLOAD                        1          0
AsyncTest1       WORK_ITEM                               1          0
AsyncTest1       XML_DOCUMENT                            2          0
13 rows selected

Which tables grew more in test1 than in test2?

Here we can see the differences in rows for two tests.

-- Return difference in rows updated (test1)
select * from different_rows_view
where test1='AsyncTest1' and test2='AsyncTest2';

TEST1                TEST2                TABLE_NAME                          DELTA
-------------------- -------------------- ------------------------------ ----------
AsyncTest1       AsyncTest2       AUDIT_TRAIL                             1

Which tables were used by test1 but not by test2?

Here we can see tables that were used by one test but not by the other test.

-- Register base test case for use in extra_tables_view
-- First parameter (test1) is test we expect to have extra rows/tables
begin soa_ctx_pkg.set('AsyncTest1', 'AsyncTest2'); end;
/
anonymous block completed
-- Return additional tables used by test1
column TEST2 FORMAT A20
select * from extra_tables_view;
TEST1                TEST2                TABLE_NAME                      DELTAROWS
-------------------- -------------------- ------------------------------ ----------
AsyncTest1       AsyncTest2       WORK_ITEM                               1

 

Results

I used the tool to find out the following.  All tests were run using SOA Suite 11.1.1.7.

The following is based on a very simple composite as shown below:

Each BPEL process is basically the same as the one shown below:

Impact of Fault Policy Retry Being Executed Once Setting Total Rows Written Total Tables Updated No Retry 13 12 One Retry 15 13

When a fault policy causes a retry then the following additional database rows are written:

Table Name Number of Rows AUDIT_TRAIL 1 WORK_ITEM 1 Impact of Setting Audit Level = Development Instead of Production Setting Total Rows Written Total Tables Updated Development 13 12 Production 11 11

When the audit level is set at development instead of production then the following additional database rows are written:

Table Name Number of Rows AUDIT_TRAIL 1 WORK_ITEM 1 Impact of Setting Audit Level = Production Instead of Off Setting Total Rows Written Total Tables Updated Production 11 11 Off 7 7

When the audit level is set at production rather than off then the following additional database rows are written:

Table Name Number of Rows AUDIT_COUNTER 1 AUDIT_DETAILS 1 AUDIT_TRAIL 1 COMPOSITE_INSTANCE 1 Impact of Setting Capture Composite Instance State Setting Total Rows Written Total Tables Updated On 13 12 Off 13 12

When capture composite instance state is on rather than off then no additional database rows are written, note that there are other activities that occur when composite instance state is captured:

Impact of Setting oneWayDeliveryPolicy = async.cache or sync Setting Total Rows Written Total Tables Updated async.persist 13 12 async.cache 7 7 sync 7 7

When choosing async.persist (the default) instead of sync or async.cache then the following additional database rows are written:

Table Name Number of Rows AUDIT_DETAILS 1 DLV_MESSAGE 1 DOCUMENT_CI_REF 1 DOCUMENT_DLV_MSG_REF 1 HEADERS_PROPERTIES 1 XML_DOCUMENT 1

As you would expect the sync mode behaves just as a regular synchronous (request/reply) interaction and creates the same number of rows in the database.  The async.cache also creates the same number of rows as a sync interaction because it stores state in memory and provides no restart guarantee.

Caveats & Warnings

The results above are based on a trivial test case.  The numbers will be different for bigger and more complex composites.  However by taking snapshots of different configurations you can produce the numbers that apply to your composites.

The capture procedure supports multiple steps in a test case, but the views only support two snapshots per test case.

Code Download

The sample project I used us available here.

The scripts used to create the user (createUser.sql), create the schema (createSchema.sql) and sample queries (TableCardinality.sql) are available here.

The Web Service wrapper to the capture state stored procedure is available here.

The sample SoapUI project that I used to take a snapshot, perform the test and take a second snapshot is available here.

Pre-digested authentication

Gary Myers - Sun, 2014-03-09 04:03
A bit of a follow-up to my previous post on Digest authentication.

The fun thing about doing the hard yards to code up the algorithm is that you get a deeper level of understanding about what's going on. Take these lines:

    v_in_str := utl_raw.cast_to_raw(i_username||':'||i_realm||':'||i_password);
    v_ha1 := lower(DBMS_OBFUSCATION_TOOLKIT.md5(input => v_in_raw));

Every time we build the "who we are" component for this site, we start with exactly the same hash made up of the username, realm (site) and password. This is a batch routine, which means somewhere we would store the username and password for the site - whether that is a parameter in a scheduling tool, coded into a shell script or OS file, or somewhere in the database. If you've got the security option for Oracle, you can use the Wallet, with its own security layers.

But digest authentication gives us another option. Since we actually use the hashed value of the user/site/password, we can store that instead. The receiving site has no idea the code doesn't actually know the REAL password.

Now turn that over in your head. We can call the web service as this user WITHOUT knowing the password, just by knowing the hash. I don't know about you, but it makes me a little bit more worried when I hear of user details being leaked or hacked from sites. It's all very well reassuring us the passwords are hashed and can't be reverse engineered (assuming your own password can't be brute-forced). But depending on the security mechanism, a leak of those hashes can be dangerous. If a hacked provider advises people to change their passwords, take their advice. 

'Basic' authentication doesn't have the same weakness. In that environment the provider can store the password hash after applying their own 'secret sauce' mechanism (mostly a salt). When you authenticate, you send the password, they apply the secret sauce and compare the result. You can't get away without knowing the password, because all the work is done at their end.

There's no secret sauce for digest authentication, and there can't be. Even if the provider had the password in the clear, there's no way they can be sure the client has the password since all the client needs is the result of the hash. The provider must store, or be able to work out, the result of that hash because they need to replicate the final hash result using both the client and server nonces. They can store either that same user/realm/password hash as is, or they can encrypt it in a reversible manner, but a one-way hash wouldn't be usable.

In short, digest authentication means that our batch routine doesn't need to 'know' the actual password, just a hash. But it also makes those hashes a lot more dangerous.

I'm an amateur in this field. I checked around and it does seem this is a recognized limitation of digest authentication. EG: This Q&A and this comparison of Digest and Basic.

PL/SQL, UTL_HTTP and Digest Authentication

Gary Myers - Fri, 2014-03-07 17:28
For the first time in what seems like ages, I've actually put together a piece of code worth sharing. It's not that I haven't been working, but just that it has all been very 'in-house' specific.

However I had a recent requirement to use a web service that makes use of Digest Authentication. If you have look at the UTL_HTTP SET_AUTHENTICATION subprogram, it only addresses Basic authentication (and, apparently, Amazon S3 which looks intriguing).

In Basic authentication, the username and password get sent across as part of the request. Going through SSL, that doesn't seem too bad, as it is encrypted over the transfer and the certificates should ensure you are talking to the legitimate destination. However if that destination has been compromised, you've handed over your username and password. In an ideal world, the server shouldn't need to know your password, which is why database should only have hashed versions of passwords. 

Outside of SSL, you might as well just print the username and password on the back of a postcard.


In Digest authentication, you get a more complex interaction that keeps the password secret. You ask for a page, the server responds with an "Authentication Required" plus some bits of information including a nonce. You come up with a hashed value based on the server nonce, your own nonce and a hash of your username and password and send it back with the next request. The server has its own record of your username/password hash and can duplicate the calculations. If everyone is happy, the server can fulfill your request and nobody ever actually needs to know the password.

Our server used SSL, and thanks to Tim's article on SSL and UTL_HTTP, it was a simple set up. I've done it before, but that was in the days when it seemed a lot hard to get certificates OUT of a browser to put them in your Oracle Wallet.

The Interwebs were a lot less forthcoming on a PL/SQL implementation of Digest authentication though. The closest I got was this discussion, which can be summed up as "This may be complex, but I do not see these offhand as being impossible to do in PL/SQL....No Digest configured web server nearby or I would definitely have had a bash at this"

A read through the Wikipedia article, and I came up with the code below:

Firstly, after the initial request, go through the header to get the 'WWW-Authenticate' item. Take the value associated with that header, and pass it to the "auth_digest" procedure. 


    l_max := UTL_HTTP.GET_HEADER_COUNT(l_http_response);
    l_ind := 1;
    l_name := '-';
    while l_ind <= l_max AND l_name != 'WWW-Authenticate' LOOP
      UTL_HTTP.GET_HEADER(l_http_response, l_ind, l_name, l_value);
      IF  l_name = 'WWW-Authenticate'
      AND l_http_response.status_code = UTL_HTTP.HTTP_UNAUTHORIZED THEN
        --
        -- Unauthorized. Using the Authorization response header, we can come up with the
        -- required values to allow a re-request with the authentication/authorisation details
        --
        dbms_application_info.set_action('auth:'||$$PLSQL_LINE);
        UTL_HTTP.END_RESPONSE(l_http_response);
        --
        dbms_application_info.set_action('auth_req:'||$$PLSQL_LINE);
        l_http_request := UTL_HTTP.BEGIN_REQUEST(l_server||l_method);
        auth_digest (io_http_request => l_http_request, i_auth_value => l_value,
          i_username => nvl(i_username,'xxxx'), i_password => nvl(i_password,'xxxx'), 
          i_req_path => l_method, i_client_nonce => null);
        dbms_output.put_line($$PLSQL_LINE||':Get Response from authenticated request');
        dbms_application_info.set_action('auth_resp:'||$$PLSQL_LINE);
        l_http_response := UTL_HTTP.GET_RESPONSE(l_http_request);
        dump_resp (l_http_response);
        dump_hdr (l_http_response);
      END IF;
      l_ind := l_ind + 1;

    END LOOP;

The auth_digest starts with an extraction of the 'valuables' from that value string. I've used regular expressions here. I spent time working with grep, awk and perl, and regexes are habit forming.

  procedure extract_auth_items
    (i_text in varchar2,
    o_realm out varchar2, o_qop out varchar2, o_nonce out varchar2, o_opaque out varchar2) is
  begin
    o_realm   := substr(regexp_substr(i_text, 'realm="[^"]+' ),8);
    o_qop     := substr(regexp_substr(i_text, 'qop="[^"]+'   ),6);
    o_nonce   := substr(regexp_substr(i_text, 'nonce="[^"]+' ),8);
    o_opaque  := substr(regexp_substr(i_text, 'opaque="[^"]+'),9);

  end extract_auth_items;

Next is the 'meat' where the values are combined in the various hashes. Yes, there's a hard-coded default client nonce in there that, by a strange coincidence, matches on in the wikipedia article. That's how this stuff gets developed, by following through a worked example. Just like school.

  function digest_auth_md5_calcs
      (i_username     in varchar2, i_password     in varchar2, i_req_path      in varchar2,
      i_realm         in varchar2, i_server_nonce in varchar2,
      i_qop           in varchar2 default 'auth',
      i_client_nonce  in varchar2 default '0a4f113b',
      i_req_type      in varchar2 default 'GET',  i_req_cnt IN NUMBER default 1)
  return varchar2 is
    --
    v_in_str    varchar2(2000);
    v_in_raw    raw(2000);
    v_out       varchar2(60);
    --
    v_ha1       varchar2(40);
    v_ha2       varchar2(40);
    v_response  varchar2(40);
    --
  begin
    --
    v_in_str := i_username||':'||i_realm||':'||i_password;
    v_in_raw := utl_raw.cast_to_raw(v_in_str);
    v_out := DBMS_OBFUSCATION_TOOLKIT.md5(input => v_in_raw);
    v_ha1 := lower(v_out);
    --
    v_in_str := i_req_type||':'||i_req_path;
    v_in_raw := utl_raw.cast_to_raw(v_in_str);
    v_out := DBMS_OBFUSCATION_TOOLKIT.md5(input => v_in_raw);
    v_ha2 := lower(v_out);
    --
    v_in_str := v_ha1||':'||i_server_nonce||':'||lpad(i_req_cnt,8,0)||':'||
                   i_client_nonce||':'||i_qop||':'||v_ha2;
    v_in_raw := utl_raw.cast_to_raw(v_in_str);
    v_out := DBMS_OBFUSCATION_TOOLKIT.md5(input => v_in_raw);
    v_response := lower(v_out);
    --
    return v_response;
  end digest_auth_md5_calcs;

And this is the full auth_digest bit

  procedure auth_digest
    (io_http_request  in out UTL_HTTP.REQ,  i_auth_value    in varchar2,
    i_username        in varchar2,          i_password      in varchar2,
    i_req_path        in varchar2,          i_qop           in varchar2 default 'auth',
    i_req_cnt         in number default 1,  i_client_nonce  in varchar2 default null)
  is
    l_realm         varchar2(400);
    l_qop           varchar2(30);
    l_server_nonce  VARCHAR2(400);
    l_opaque        varchar2(100);
    --
    l_response      varchar2(40);
    l_value         VARCHAR2(1024);
    --
    l_client_nonce  varchar2(30);
    --
  begin
    --
    -- Apply the username / password for Digest authentication
    --
    extract_auth_items (i_auth_value,
                    l_realm, l_qop, l_server_nonce, l_opaque);
    --
    IF i_client_nonce is not null then
      l_client_nonce := i_client_nonce;
    ELSE
      l_client_nonce := lower(utl_raw.cast_to_raw(DBMS_OBFUSCATION_TOOLKIT.md5(
                            input_string=>dbms_random.value)));
    END IF;
    --
    l_response := digest_auth_md5_calcs
      (i_username => i_username, i_password    => i_password,     i_req_path => i_req_path,
      i_realm     => l_realm,    i_server_nonce => l_server_nonce,
      i_client_nonce => l_client_nonce);
    --i_qop default to auth, i_req_type default to GET and i_req_cnt default to 1
    --
    l_value := 'Digest username="' ||i_username          ||'",'||
               ' realm="'          ||l_realm             ||'",'||
               ' nonce="'          ||l_server_nonce      ||'",'||
               ' uri="'            ||i_req_path          ||'",'||
               ' response="'       ||l_response          ||'",'||
               ' qop='             ||i_qop               ||',' ||
               ' nc='              ||lpad(i_req_cnt,8,0) ||',' ||
               ' cnonce="'         ||i_client_nonce      ||'"'
               ;
    --
    IF l_opaque is not null then
      l_value := l_value||',opaque="'||l_opaque||'"';
    END IF;
    dbms_output.put_line(l_value);
    UTL_HTTP.SET_HEADER(io_http_request, 'Authorization', l_value);
    --

  end auth_digest;

A package with the code is available from my CodeSpace page, or directly here. There's a lot of debug 'stuff' in there. The code I'm using is still tailored to my single specific need, and I've stripped specific values from this published variant. You'll need to hard-code or parameterize it for any real use. I may be able to do a 'cleaned-up' version in the future, but don't hold your breath.

BI change is coming, time to get over it and get on with the job

Steve Jones - Fri, 2014-03-07 11:15
One of the things that always stuns me in IT is how people don't appear to like change.  Whether it was the EAI folks pushing back on Web Services in 2000 in favour of their old-school approaches.  The package guys pushing back against SaaS or now the BI guys pushing back against the new wave of BI technologies and approaches the message is always the same: We are happy doing what we are doing,
Categories: Fusion Middleware

Finally...the official sizing guide for Oracle Application Express

Joel Kallman - Thu, 2014-03-06 06:29
The following question was recently posted on an internal mailing list:
"Is there a sizing/capacity/scalability guide available for APEX?"
I'm always fascinated by this question.  I appreciate the fact that this is a standard, acceptable practice in the industry, and people come to expect it.  How else could architects and planners appropriately allocate resources without some form of estimate?  This impacts capital expenditures and budgets and rack space and energy costs and support costs and human capital.  People seem to be looking for some simple formula like:
(X number of pages in an APEX application) * (Y number of concurrent users) = (W number of processors) + (Z number of GB of RAM)
Voila!  Plug that formula into your favorite spreadsheet and away you go.  Well....if I lured you in with the title of this blog post, I have to be honest - it's all fiction.  There is no such thing.  But why not?  There are a number of reasons.

  1. There is no such thing as a representative, typical application.  As I've often bloviated in the past, Oracle Application Express is as fast or as slow as you, the developer, make it.  The overhead associated with the APEX engine itself is fairly static (measured in hundredths of a second). If you have a query that takes 30 seconds to execute and you put this query in a report in an APEX application, you can expect the execution of that page to take just over 30 seconds per page view.

  2. What does "concurrent" mean?  Is that the total number of users in an hour?  Total number of users in a 5-minute interval?  Or is that the high-water mark of number of users all clicking the mouse or hitting the Enter key, all at the same time?

  3. What is the typical "think time" of an end user?  Effectively, resources are only being consumed when there is a request actively being processed by the APEX engine.  So while the end user is interpreting the results of a report or keying in data in a form, they aren't (typically) making any requests to the APEX engine.

  4. How much memory will be consumed by the typical page view?  Does your application allocate GB's of in-memory LOBs, per user per page view?  This would have a definite impact on scalability.
The total number of pages in an application has close to zero correlation to scalability and throughput.  You can have a 1,000-page application, each page with sub-second performance, which will be far more scalable than a 1-page application that consumes 15 seconds per page view.

As the Oracle Database Performance and Tuning Guide states, there are many variables involved in workload estimation, and it's typically done via either benchmarking or extrapolation from a similar system.  But what is "a similar system" for an APEX application?  Does a call-center application at one enterprise approximate the back-office order processing system at another company?

I can understand how a formula can be prepared for a COTS application.  If you're deploying Fusion Applications or the eBusiness Suite or JD Edwards or SAP, those applications are created, the business logic is written, the queries and transactions are crafted, and concurrency has been measured on representative systems for a given workload.  But I don't understand how someone can produce a sizing guide for any application development framework - Application Express, ADF, .NET, Java.  It's like asking "how scalable is C?"

An application that our team wrote and runs for Oracle is quite scalable (the oft-mentioned Aria People employee directory).  Yesterday (05-MAR), there were 2.1M page views on this system with a median page rendering time of 0.03 seconds from 45,314 distinct users.  The busiest hour saw 129,284 page views through the APEX engine (35.9 page views/second).  If another team within Oracle wrote this same system but didn't tune the SQL like we did, is that a reflection on the scalability of APEX?  And if the answer to that question is "no", then is the hardware configuration all that relevant?

Back in 2007, my manager Mike Hichwa took a draft note that I wrote and published an article for  Oracle Magazine entitled "Sizing up Performance".  There is a very simple formula which can be used to estimate the throughput of an APEX application.  This isn't going to help you determine how much hardware to buy or how to estimate the size of your VM, but it will help estimate (in back-of-the-napkin form) how scalable an existing APEX application will be on an existing system.

With all this said, we, on the Oracle Application Express team, have been deficient.  At a minimum, we should have a list of systems developed by our customers, with specific information about the hardware configuration, purpose of the system, and number of end-users served.  Maybe we should also obtain the level of expertise of the developers.  We will gather this information and publish it online (without specific customer names).  If nothing else, this can serve as the foundation for extrapolation by architects and designers.


Unintended, but interesting consequences

Nuno Souto - Thu, 2014-03-06 02:56
It's interesting how from time to time something happens that makes sense and seems logical afterwards, but at the time it causes a bit of a surprise.  Part of the fun of working with this type of software! A few days ago we had an incident in an Oracle DW database when a developer tried to load an infinitely big file from a very large source.  Yeah, you got it: big-data-ish!  Suffice to say: Noonshttp://www.blogger.com/profile/04285930853937157148noreply@blogger.com6

Internal Links

Tim Dexter - Wed, 2014-03-05 20:06

Another great question today, this time, from friend and colleague, Jerry the master house re-fitter. I think we are competing on who can completely rip and replace their entire house in the shortest time on their own. Every conversation we have starts with 'so what are you working on?' He's in the midst of a kitchen re-fit, Im finishing off odds and ends before I re-build our stair well and start work on my hidden man cave under said stairs. Anyhoo, his question!

Can you create a PDF document that shows a summary on the first page and provides links to more detailed sections further down in the document?

Why yes you can Jerry. Something like this? Click on the department names in the first table and the return to top links in the detail sections. Pretty neat huh? Dynamic internal links based on the data, in this case the department names.

Its not that hard to do either. Here's the template, RTF only right now.


The important fields in this case are the ones in red, heres their contents.

TopLink

<fo:block id="doctop" />

Just think of it as an anchor to the top of the page called doctop

Back to Top

<fo:basic-link internal-destination="doctop" text-decoration="underline">Back to Top</fo:basic-link>

Just a live link 'Back to Top' if you will, that takes the user to the doc top location i.e. to the top of the page.

DeptLink

<fo:block id="{DEPARTMENT_NAME}"/>

Just like the TopLink above, this just creates an anchor in the document. The neat thing here is that we dynamically name it the actual value of the DEPARTMENT_NAME. Note that this link is inside the for-each:G_DEPT loop so the {DEPARTMENT_NAME} is evaluated each time the loop iterates. The curly braces force the engine to fetch the DEPARTMENT_NAME value before creating the anchor.

DEPARTMENT_NAME

<fo:basic-link  internal-destination="{DEPARTMENT_NAME}" ><?DEPARTMENT_NAME?></fo:basic-link>

This is the link for the user to be able to navigate to the detail for that department. It does not use a regular MSWord URL, we have to create a field in the template to hold the department name value and apply the link. Note, no text decoration this time i.e. no underline.

You can add a dynamic link on to anything in the summary section. You just need to remember to keep link 'names' as unique as needed for source and destination. You can combine multiple data values into the link name using the concat function.

Template and data available here. Tested with 10 and 11g, will work with all BIP flavors.

Categories: BI & Warehousing

Oracle Direct NFS and Infiniband: A Less-Than-Perfect Match

Don Seiler - Wed, 2014-03-05 19:54
Readers of an earlier post on this blog will know about my latest forays into the world of Direct NFS. Part of that means stumbling over configuration hiccups or slamming into brick walls when you find new bugs.

To quickly re-set the table, my organization purchased the Oracle ZFS Storage Appliance (ZFSSA) 7420. Oracle sold us on the Infiniband connectivity as a way to make a possible future transition to Exadata easier. However the pre-sales POC testing was done over 10gb Ethernet (10gigE). So it was that everything (including their Infiniband switches and cables) arrived at the datacenter and was installed and connected by the Oracle technicians. There were a few initial hiccups and frustrating inconsistencies with their installation and configuration, but those are outside the scope of this post.

We decided to put a copy of our standby database on the ZFSSA and have it run as a second standby. The performance problems were quick to appear, and they weren't pretty.



Configuring SharesWe configured the ZFS project shares by the common Oracle best practices in terms ZFS recordsize and write bias. For example, datafile shares were set to an 8k recordsize (to match the db_block_size) and throughput write bias, where as redo log shares were set to 128k recordsize and latency bias. Note that with Oracle Database 12c, Direct NFS over NFSv4, and the more recent ZFSSA firmware, you gain the benefit of Oracle Intelligent Storage Protocol (OISP), which will determine the recordsize and write bias automatically based on the type of file it recognizes.

Copying the DatabaseTo start out we needed to get a copy of the database onto the ZFSSA shares. This was easily done with RMAN's backup as copy database command, specifying the ZFSSA mount as the format destination. We were fairly impressed with the Direct NFS transfer speed during the copy and so we were optimistic about how it would stand up with our production load.

Starting Recovery!
Once everything was set, we started managed recovery on the standby. Our earlier excitement gave way to a sort of soul-crushing disappointment as the recovery performance basically ground to a standstill and traffic to the ZFSSA went from hundreds of Mbps to barely a trickle. We could stop recovery and copy a big file with great speed, but something in managed recovery was not playing nicely.

We found that we could disable Direct NFS (requires a database restart and software relinking), and managed recovery would actually perform better over the kernel NFS, although still not nearly as well as we would need.

This started a blizzard of SR creations, including SRs being spawned from other SRs. We had SRs open for the ZFSSA team, the Direct NFS team, the Data Guard team, and even the Oracle Linux and Solaris teams, even though we were not on Oracle Linux or Solaris (we use RHEL). It came to a point where I had to tell our account manager to have support stop creating new SRs, since every new SR meant I had to explain the situation to a new technician all over again.

At this point we were having twice-daily conference calls with our account manager and technical leads from the various departments. Their minions were working hard on their end to replicate the problem and find a solution, but we were running into a 4th week of this craziness.

The Infiniband BanditAfter many frustrating weeks of changing configurations, cables, cards, and just generally grasping at straws, it was finally narrowed down to the Infiniband. Or rather, a bug in the open fabric (OFA) linux kernel module that dealt with Infiniband that was triggered when Direct NFS would fire off a whole lot of connections, like when DataGuard managed recover would fire up 80 parallel slaves. We tested out the 10gigE channel we had for the management UI and performance was like night and day with just the one channel.

Oracle Support suggested it might be related to bug 15824316, which also deals with dramatic performance loss with Direct NFS over Infiniband. The bug in the OFA kernel module was fixed in recent versions of Oracle Enterprise Linux (OEL) (specifically the UEK kernel), but Oracle is not sharing this fix with Red Hat (or anyone else, presumably). Since we're on RHEL, we had little choice but to send all the Infiniband networking hardware back and order up some 10gigE replacements.

We're still in the process of getting the 10gigE switches and cables all in place for the final production setup. If you're curious, it's 4 10gigE cards per server, bonded to a single IP to a 10gigE switch into the ZFSSA heads. This 10gigE network is dedicated exclusively to ZFSSA traffic.

So, in the end, if you're on (a recent version of) of OEL/UEK, you should have nothing to worry about. But if you're on RHEL and planning to use Direct NFS, you're going to want to use 10gigE and NOT Infiniband.

Update - 6 Mar 2014Some of have asked, and I want to re-iterate: Oracle have claimed that the OFA module was entirely re-written, and their fix is specific to OEL and is not covered by GPL or any similar license. We were told that they have no plans to share their code with RHEL. Also there is no MOS bug number for the OFA issue, it was apparently re-written from scratch with no bug to track the issue. If this all sounds rather dubious to you, join the club. But it's what our account manager told us at the end of last year.

Another Update - 6 Mar 2014Bjoern Rost and I discussed this privately and after quite a bit of research and analysis he shared this conclusion:

Oracle Support suggested that this issue would not occur with the OFA module used in Oracle Linux with the UEK kernel. RedHat changed their support in RHEL6 from shipping the whole openfabrics stack to just including the drivers that were also present in the upstream mainline kernel. This is RedHat’s policy to ensure stability in the version of the kernel they ship. Oracle offers an OFA package with some additional patches (all GPL/BSD license) for the UEKr1 and UEKr2 kernels. Unfortunately, these two different approaches make it very hard to pinpoint specific patches or create backports for the RedHat kernel version.
Categories: DBA Blogs

The OLAP Extension is now available in SQL Developer 4.0

Keith Laker - Tue, 2014-03-04 14:57


The OLAP Extension is now in SQL Developer 4.0.

See
http://www.oracle.com/technetwork/developer-tools/sql-developer/downloads/sqldev-releasenotes-v4-1925251.html for the details.

The OLAP functionality is mentioned toward the bottom of the web page.
You will still need AWM 12.1.0.1.0 to
  • Manage and enable cube and dimension MV's.
  • Manage data security.
  • Create and edit nested measure folders (i.e. measure folders that are children of other measure folders)
  • Create and edit Maintenance Scripts
  • Manage multilingual support for OLAP Metadata objects
  • Use the OBIEE plugin or the Data Validation plugin
What is new or improved:
  • New Calculation Expression editor for calculated measures.  This allows the user to nest different types to calculated measures easily.  For instance a user can now create a Moving Total of a Prior Period as one calculated measure.  In AWM, it would have required a user to create a Prior Period first and then create a Moving Total calculated measure which referred to the Prior Period measure.  Also the new Calculation Expression editor displays hypertext helper templates when the user selects the OLAP API syntax in the editor.
  • Support for OLAP DML command execution in the SQL Worksheet.  Simply prefix OLAP DML commands by a '~' and then select the execute button to execute them on the SQL Worksheet.  The output of the command will appear in the DBMS Output Window if it is opened, or the Script Output Window if the user has executed 'set serveroutput on' before executing the DML command.
  • Improved OLAP DML Program Editor integrated within the SQL Developer framework.
  • New diagnostic reports in the SQL Developer Report navigator.
  • Ability to create a fact view with a measure dimension (i.e. "pivot cube").  This functionality is accessible from the SQL Developer Tools-OLAP menu option.
  • Cube scripts have been renamed to Build Specifications and are now accessible within the Create/Edit Cube dialog.  The Build Specifications editor there, is similar to the calculation expression editor as far as functionality.
Categories: BI & Warehousing

Latest for the folks who have to deal with Peoplesoft

Nuno Souto - Tue, 2014-03-04 05:51
Dang, been a while since the last posts!  A lot of water under the bridge since then. We've ditched a few people that were not really helping anything, and are now actively looking at cloud solutions, "big data" use, etcetc. Meanwhile, there is the small detail that business as usual has to continue: it's very easy to parrot about the latest gimmick/feature/funtastic technology that will Noonshttp://www.blogger.com/profile/04285930853937157148noreply@blogger.com2

Pages

Subscribe to Oracle FAQ aggregator