Feed aggregator

We are holding a public training event in my home city of York on July 13th and July 14th 2009. The two day seminar is aimed at getting anyone who needs to perform a security audit of an Oracle database....[Read More]

Posted by Pete On 01/06/09 At 12:52 PM

I have just uploaded the slides from two recent talks. I gave my Oracle security masterclass in Helsinki for the OUGF last week which went really well and also yesterday I gave my talk "the right way to secure a....[Read More]

Posted by Pete On 21/05/09 At 05:10 PM

A question was posted on the Oak table mailing list some time back asking if its possible to validate a users password from within the database without creating a session. One of the replies suggested looking at my PL/SQL based....[Read More]

Posted by Pete On 11/05/09 At 02:28 PM

I have just posted the slides to my recent talk ( The Right Method To Secure An Oracle Database ) at the UKOUG Northern Server technology day held in my home city of York to my Oracle security white papers....[Read More]

Posted by Pete On 01/05/09 At 11:08 AM

Oracle Corp. issued 43 fixes Tuesday as part of its quarterly Critical Patch Update, repairing flaws in its database management system, application server and application product lines. " Oracle issues 43 updates, fixes serious database flaws " Oracle's advisory is....[Read More]

Posted by Pete On 15/04/09 At 05:57 PM

I was asked by a colleague a couple of weeks ago if it was possible to create ENUM's in PL/SQL like its possible to create in languages such as C. The actual example the person emailed me is too business/market....[Read More]

Posted by Pete On 14/04/09 At 03:40 PM

I received a copy of Ron Ben Natan's new book " How To Secure and audit Oracle 10g and 11g " (The link is Amazon.co.uk because I am in the UK, you can find the book on Amazon.com from the....[Read More]

Posted by Pete On 14/04/09 At 11:12 AM

I am going to be in Edinburgh on April 21st and April 22nd to teach my popular two day class " How to perform a security audit of an Oracle database " with PiSec Limited. This class is great for....[Read More]

Posted by Pete On 02/04/09 At 02:13 PM

I have released a number of scripts to my site for creating heirarchical reports of privileges. These include find_all_privs.sql, who_has_priv.sql, who_can_access.sql, who_has_role.sql and check_parameter.sql. These are standalone sqlplus scripts. I was asked a week or so ago about whether these....[Read More]

Posted by Pete On 01/04/09 At 12:25 PM

One of the ways to migrate a database to ASM is to use the Rman “Backup as Copy” command to create a database copy into ASM storage and then switch the database to the copy. This technique can be used,... alejandro.vargas

Go to Oracle Fusion Middleware 11g and click on Downloads


In the list of available tools, search for "Portal, Forms, Reports and Discoverer"



then relax, while downloading 2.1 GB and enjoy the new Oracle Forms 11g R1.

Available on Windows and Linux. Solaris, HP and IBM is "Coming soon"
Have fun
Gerd

It’s been a while since I’ve updated my blog. I feel guilty Lately, I’ve been using a lot of Python to do my Oracle research and I needed a way to do simple selects across multiple versions and platforms from the same IDLE shell. On top of that, I need to connect as SYSDBA. [...]

A reader recently asked where she could find a summary of the E-Business Suite Release 12 technology stack components for different R12 releases.  As it turns out, there's a long answer to this deceptively-simple question.  This level of information is spread in a variety of release-specific Notes, making it tricky to compare which components were delivered as part of each Apps 12 Rapid Install.  Here's a high-level architectural diagram showing an overview of the major techstack components in R12: It's possible to add on additional database options not shown above, including 11g Advanced Compression, 11g Advanced Security, and others.  Here's a summary of the versions for the important major techstack components that were included in the Rapid Install footprints for Oracle E-Business Suite Release 12:   EBS Release 12 Rapid Install Version   12.0.0 12.0.4 12.1.1 Database 10.2.0.2 10.2.0.4 11.1.0.7 OracleAS 10.1.2 Forms & Reports 10.1.2.0.2 10.1.2.2 10.1.2.3 OracleAS 10.1.3 OC4J 10.1.3.0.0 10.1.3.0.0 10.1.3.4 App Tier Java (JDK) 1.5.0_10 1.5.0_13 1.6.0_10 Desktop Client Java (JRE) 1.5.0_10-erdist 1.5.0_13 1.6.0_u10 Steven Chan http://blogs.oracle.com/stevenChan/about.html

This weekend marks the anniversary of the birth of a nation whose motto is about unity of purpose while acknowledging the differences in those who contribute to that purpose. That’s a very interesting duality that effective networks share; each person in the network has unique capabilities and individual goals, and different ways in which they contribute, but the network is unified in purpose.

As social networking tools gain acceptance in the enterprise, folks are recognizing that first, networks have always existed inside companies and second, that these network tools are more about making the networks more visible and more easily acted upon and utilized. As a result, when properly used, these tools accelerate productivity, innovation and engagement. However, like any tools, used improperly, these tools can damage those objectives as well.

Previously, we discussed how networks are inherently “opt-in” since they are usually not formal organizational structures. This means social factors such as trust play a large role in whether people will participate and make the network effective. But even after you achieve participation, the potential to wreck the network still exists in subtle and insidious ways.

One way is in the very structure of networks themselves. We know that the more connections that exist in a network, the higher the likelihood of information finding its way to the right person. Of course, everybody knows you can’t have ridiculously high levels of connections (although whether the number in 150+ or 1,500+ is debated) but is it right to assume that people in the network who have a significantly lower number of connections than others are somehow not as effective (or vice-versa?) As Steve Boese commented on a previous post, simply measuring the number (or extent) of connections doesn’t really tell you whether or not it’s working; it depends on the role or the person’s objectives. It can go beyond that as well; in some roles, some individuals might simply be more effective having only a tight set of connections with just a few of which reach outside their close circle of colleagues. Now, imagine a manager who simply measures effective use of the network by number and extent of connections dinging that individual for not having a high enough “social score.” Why risk their departure or reduced participation if the individual was an expert in a particularly strategic area and had contributed perfectly well through a “bridge” connection into the network?

What’s a better way? Managers need to look at the big picture and understand first what the purpose of the various networks are that their employees are members of. In that context, the manager can than understand what role an employee has in that network and then coach the employee if it seems that either the network is not getting what it needs, or even more effectively, if the employee is feeling they aren’t getting the most use from the network.

The most energetic proponents of network tools in the enterprise are not surprisingly heavy users of these tools. As such, they can easily fall into the perfectly natural human behavior of thinking other participants should be just as active, or more subtlety, not pay effective attention to those who aren’t as active. They can unwittingly alienate the very kind of employee that otherwise might not have had as much voice or impact on the success of the company from using the very tool that would have overcome the obstacle of organization structure, etc. Especially during the delicate initial phase of encouraging the use of network tools, it’s a good idea to look out for the non-productive effects of social pressure. As the culture becomes more familiar with their use, social norms will start to take effect and help people understand the different ways everyone contributes. Gradually, job and role requirements can then be added where appropriate in order to more clearly communicate expectations and guide career development.

Photo by: pursuethepassion

Oracle Input Output Numbers, ORION, provides a simple way to check storage performance before the Oracle Database is created, that gives us a clean storage performance report that can be compared with the awr IO statistics once the database is... alejandro.vargas

On this Lab we create the ASM instance on a single instance environment, and create ASM diskgroups in several different ways using different kind of devices. Note that these exercises provide some degree of confidence on managing devices but are... alejandro.vargas

Using OLE DB to get SQL Server to connect to Oracle servers can be done quite easily, but there are a few little tricks you should know to make it go smoothly. Once it’s working it seems to work quite well. I hope this blog post will save you a few headaches.

Recently a client asked me to create a simple SSIS package that would connect to Oracle, pick up some data with queries they provided, import it to SQL Server, and eventually export the data as flat, delimited text files.

With SSIS you can use the OLE DB provider that Oracle provides. If your SQL Server is 32-bit, you can install the 32-bit Oracle client and stop there.

If it’s 64-bit, there are a couple different ways to get the Oracle providers working. The method I’ve had the most success with, is to install both the 32- and 64-bit Oracle clients (in separate directories). I’m not sure why you have to have both clients. The only explanation I’ve seen is that part of SQL Server 64 is still 32-bit. I’m not sure if this is true, but if you look at the shortcuts, SSMS and Visual Studio’s EXEs reside in the 32-bit Program Files folder:

SQL Server Management Studio and DTEXECUI.exe can be found in: C:\Program Files (x86)\Microsoft SQL Server\90\Tools\binn\VSShell\Common7\IDE\

Visual Studio: C:\Program Files (x86)\Microsoft Visual Studio 8\Common7\IDE\Devenv.exe

If you look at SQL Server and SQL Agent, they both reside in the 64-bit folder: C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn\

Once both clients were installed, I was able to access the provider in SSIS, and also successfully schedule and run package. The trouble started when the requirements for the project changed, and we decided to use SQL in a stored procedure with a linked server instead of SSIS. I thought this would be very simple to get working, since I already had the providers installed and working in SSIS. I was wrong.

We created the linked server and used OPENROWSET to pass in the queries that the client had written and tested in SQLPlus running against their test system. Now it was time to try out our linked server. I passed it a simple query and instead of seeing some records, got this error:

Msg 7399, Level 16, State 1, Line 1
The OLE DB provider “OraOLEDB.Oracle” for linked server [Oracle Server Name Withheld] reported an error. Access denied.

Msg 7301, Level 16, State 2, Line 1
Cannot obtain the required interface (”IID_IDBCreateCommand”) from OLE DB provider “OraOLEDB.Oracle” for linked server [Oracle Server Name Witheld]

Naturally, I thought something had changed with our credentials used to access Oracle, so we tested the query and credentials in SQLPlus, which worked. Thinking something had gotten messed up on the SQL Server, I decided to check the providers, and ran the query from SSIS against the provider/Oracle data source. This worked.

We tried creating the linked servers with different settings, and even tried creating them on different servers. Nothing worked.

At this point, I was very frustrated, and thinking about how best to do what we wanted in SSIS. I did some research on the web and eventually stumbled on OPENQUERY - Access denied which suggested the “Allow InProcess” option be enabled on the provider. I enabled this option (instructions below), closed all open query windows, opened a new one and then re-tested my simple/test query using

OPENROWSET

and everything worked like a charm.

Happy cross-platform querying.
Chris.

Here are the step by step instructions to enable this:

1. Expand Linked Servers | Providers.
2. Right click on the Ora provider and go to Properties.
3. Find the “Allow InProcess” option and tick it.
4. Then make a new connection window and run your query (I had to close my open connection windows).

  Today I came across a brief post on the OTN Forum for JDeveloper and ADF (http://forums.oracle.com/forums/thread.jspa?threadID=923587&tstart=0). The latest demo application (11.1.1.1.0) for ADF 11g Faces RC can be downloaded from http://download.oracle.com/otn/java/jdeveloper/111/extensions/adf-richclient-demo.war. It comes as a WAR file that can be deployed on WebLogic 11g. Alternatively, we can turn the WAR file into a JDeveloper project, to inspect [...]

This post from Patrick is very useful when trying to identify the column ID of the tabular form. But when column order has been moved up/down or some are marked not shown, the column ID might be difficult to determine.

This simple technique of using Javascript event on the Element Attributes of the needed columns can be used to quickly identify the column ID.

onmouseover="alert(this.id);"

You can definitely choose a different event that fits your situation.

See sample here.

Welcome to the 152nd edition of Log Buffer, the weekly review of database blogs.

PostgreSQL

Courtesy the United States PostgreSQL Association, the big news: PostgreSQL 8.4 Released!.

Josh Berkus writes, “Now that PostgreSQL 8.4 is out, I thought I’d write a little about my favorite 8.4 feature. As Mr. Performance Whack-a-Mole, what makes me happy about 8.4 is the ability to whack moles faster … which is why I’m very fond of pg_stat_statements.”

On ad’s corner, Andreas Scherbaum says, “Up to PostgreSQL 8.3 it was only possible to grant (and revoke) permissions on the entire table. If column level permissions were needed, a workaround like a view solved (more or less) the problem . . .  This . . . is uneloquent, error prone and does not scale well.  . . .  PostgreSQL 8.4 solves the problem with a shiny new feature: column level permissions.”

David Fetter looks into WITH (so much drama in the CTE): ” By now, you’ve probably seen that PostgreSQL 8.4 can produce Mandelbrot sets . . . but what are Common Table Expressions really about? [They] are just views you create at run time. Oh, and they can refer to themselves. That latter capability is powerful and flexible (read confusing), so I’ll go over that one later. Run-time views are pretty handy, though.”

Andrew’s PostgreSQL blog introduces parallel pg_restore for PostgreSQL 8.4: “I try to complete at least one significant feature item per PostgreSQL release. This time the feature is making pg_restore run in parallel. This is quite important for many users, particularly some large enterprise users.”

SQL Server

Eric Johnson introduces SSIS 2008 and the new lookup: “SSIS is a pretty useful tool for designing ETL processes. One of the transformations I was disappointed with in 2005 was the lookup. I found it a little better than the lookup functionality in DTS, but not too much better. Well, Microsoft must have read my mind because SQL Server 2008 has a new shiny Lookup transformation that is, in my opinion, much improved.”

Kimberly L. Tripp was thinking about looking for security vulnerabilities in database code. “I’ve always been concerned with security and I’ve always stressed the importance of auditing the REAL user context not just the current user . . .  So, I generally try to avoid using dynamic string execution and if necessary create well tested/protected parameters . . .  Having said that, what if I’m looking at a database for the first time . . .  I’ve come up with a quick query… And, while it’s not going to “solve” your problem . . . or even truly verify if you’re vulnerable, it gives you a ‘quick list’ of where you should look first! ”

Adam Machanic exposed the hidden costs of INSERT EXEC, beginning, “INSERT and EXEC: relational brothers in arms, helping you get your data and then put it somewhere. But like all brothers, their relationship has its ups and downs and sometimes you need to look just below the surface to see the real issues. In this post I will cover hidden penalties that a decision–seemingly purely aesthetic–can bring to the fore.”

Martin Bell offered the reminder, after disabling TDE you still requires the certificate to restore the database.

Data Management has a first-rate HOWTO on dynamic column names and fields in SSRS. “I had no choice but to do work with creating dynamic column headings and dynamically determine what field in my dataset should go where in a report today. Sense [sic] this is the second time I’ve gone through this exercise and knowing the lack of information out there on really how to do it, I thought it deserves a blog entry.  . . .  So here is the basis of the requirements. You have a query that uses PIVOT but thrown into the mix is the need for dynamic columns in the PIVOT.”

MySQL

Here on the Pythian Blog, Sheeri Cabral expressed some concerns and what does not work in XtraDB backup.

On xaprb, Baron Schwartz has a review of MySQL Administrator’s Bible. “I think that MySQL Administrator’s Bible is a very good complement to High Performance MySQL, 2nd Edition. The former is useful to people who have varying levels of knowledge, while the latter assumes a lot of experience and doesn’t cover introductory material much.  . . .  So if you’re new to databases, or if you’re new to MySQL, you might do well to start with Sheeri’s book . . . ”

Over on the MySQL Performance Blog, Baron looked into gathering queries from a server with Maatkit and tcpdump: “For the last couple of months, we’ve been quietly developing a MySQL protocol parser for Maatkit. It isn’t an implementation of the protocol: it’s an observer of the protocol. This lets us gather queries from servers that don’t have a slow query log enabled, at very high time resolution.”

Ronald Bradford looked at verifying MySQL replication in action, with “ . . . a very simple test to show MySQL replication in action and to also better understand the basics of MySQL Replication. With a configured MySQL environment we can run the following test on your MySQL master, and monitor the MySQL slave.”

The MySQL Workbench Team Blog says, Time To Upgrade - MySQL Workbench 5.1 Is Here on Win, Mac & Linux. Perty pictures follow.

Oracle

H. Tonguç Yilmaz asserted, Instrumented Code is Better: An Example on How to Generate Session Level ASH Reports. ” After 10g if you have its license ASH based performance monitoring is an alternative to the traditional SQL Tracing in my opinion. You may either use EM to generate ASH reports or directly query ASH dictionary. The handy additional hint here is that you can set filters to ASH data in order to focus on a specific session, the filter options at ASH report screen at EM can be used for this purpose or below is an example to monitor the waits from v$active_session_history view.”

Doug Burns responded with his item on session-level ASH reports: “I think [Tonguç's] post is really showing two different things, one more successfully than the other.”

Randolf Geist reports a Dynamic sampling and set current_schema anomaly: “If I’m now supposed to analyse a rather complex SQL with dozens of table and/or view references I like to use the ALTER SESSION SET CURRENT_SCHEMA command . . .  This basically works just fine, but I recently have come across an interesting anomaly in 10.2.0.4: If the optimizer uses dynamic sampling as part of the parsing then it doesn’t do so anymore if the CURRENT_SCHEMA has been altered to a non-default schema.”

On the Oramoss Blog, Jeff Moss looks at the case of no pruning for MIN/MAX of partition key column: “Recently, I wanted to work out the maximum value of a column on a partitioned table. The column I wanted the maximum value for, happened to be the (single and only) partition key column. The table in question was range partitioned on this single key column, into monthly partitions for 2009, with data in all the partitions behind the current date, i.e. January through mid June were populated. There were no indexes on the table.” Jeff and his readers examine the evidence and discuss some workarounds to the problem.

Oracle, MySQL, PostgreSQL, SQL Server. How’bout . . . 

NoSQL

(No SQL?!? Edwards, you’re mad!) Well, it’s not me. Here’s Curt Monash on NoSQL: “Eric Lai emailed today to ask what I thought about the NoSQL folks, and especially whether I thought their ideas were useful for enterprises in general, as opposed to just Web 2.0 companies. That was the first I heard of NoSQL, which seems to be a community discussing SQL alternatives popular among the cloud/big-web-company set, such as BigTable, Hadoop, Cassandra and so on.”

PostgreSQL stuff also had some thoughts on those who say No to SQL. “Every time I see something or hear something like this I sigh a little bit. Not only when it’s related to SQL but in the world of computer professionals in general. ‘The right tool for the job’ seems to be a hard concept to understand sometimes. I wonder why?”

Back to Oracle for a moment. Tanel Poder has a secret preview of Oracle 12g CBO leaked from Oracle labs. Would you like fries with your cost-based optimizer?

That’s all for now. See you in a week’s time!