OraTransplant

Using Proxy Authentication with SQLPlus

For our web application we’re using a nice Oracle database feature: Proxy Authentication. I only managed to use it in a J2EE application, not in sqlplus or other SQL tools (like TOAD). Today I discovered how to use Proxy Authentication from any tool you like.

First let me explain a little bit about Proxy Authentication. Our application server maintains a pool of database sessions, as most application servers do. This means that each session from this pool uses the same (fixed) database user to connect to the database. Looking in V$SESSION you see tons of sessions from the same user.

This makes it difficult to distinguish the sessions for a DBA. But more importantly you loose a lot of security functionality in the database:

• SELECT USER FROM DUAL now always returns the same username. This means that views restricting the returned rows based on the currently logged on user no longer work as expected
• Journaling (writing changes to journal tables from database triggers) loses its value since all changes appear to have been made by the same user
• Auditing (insert_user/update_user columns in a table) loses its value since all changes appear to have been made by the same user
• You can no longer easily use database roles to limit access. The user being used for the database connection needs all the roles of all the application users combined. And by default all these roles are enabled when creating a new database connection, unless you add specific code to enable/disable specific roles

The answer to all of this is Proxy Authentication. This process is quite simple. The application server first establishes a connection to the database with the same fixed account. This account is configured to only allow database connections (CREATE SESSION privilege) and nothing else. Once this session is established you can open a proxy connection to another user. This other user is the actual user logged on to the web application (e.g. JSMITH). This immediately gives you all the built-in security features of the database (grants, journaling, auditing, etc).

This is all great, but to open a proxy connection you have to issue an OCI or JDBC call. You can do this from programs you code yourself, like or ADF Faces applications. But for testing and debugging, you also want access to this feature from sqlplus or other SQL tools (like TOAD).

Fortunately this feature was introduced in version 10.2. When connecting, specify “user[proxyuser]” as username. This will try to log you in as “user” and then immediately open a proxy connection to “proxyuser”:

C:\>sqlplus bigappuser[enduser]/bigappuser@dev01

The same trick also works when logging in with other tools like TOAD. Be sure to use an Oracle Net client version 10.2. If you use 10.1 or any previous version this won’t work.

Update 19 dec 2006: Jonathan Lewis blogged about this very same feature of using Proxy Authentication with SQLPlus

OpenWorld: Project Raptor – watch out TOAD

Oracle released an Early Access version of project Raptor. This made much of the following article obsolete as you can now see for yourself.

I just attended a session about new developments by the Server Technologies’ Database Division. One of the most interesting things (for me) was the short demo of project Raptor. This is the long awaited visual tool for database development. It could be a real competitor for the well known TOAD product from Quest. They did a short demo in which you could view and alter all the known database objects. The also showed some PL/SQL development features including code formatting and a powerful debugger. The session was also about other new developments in database 10gR2 and coming releases. This mean there was only a couple of minutes to demo project Raptor but it was really interesting. A lot of people approached the speaker afterwards to ask about Raptor.

It looked very familiar to me (knowing JDeveloper). It doesn’t require an Oracle client, just as JDeveloper. It’s just a matter of unzipping and start running. Looking at the screen it looks like it just makes a JDBC connection. It’s based on the JDeveloper framework but it is much more powerful then the existing database and PL/SQL editing features in JDeveloper. As I understood it will eventually replace the current database and PL/SQL editing in JDeveloper in some feature release. But before that, project Raptor will be offered as a standalone product on OTN. Due to all the new regulations Oracle people cannot give estimated delivery dates other then “this fiscal year”. For Oracle that means before May 31st 2006, but the speaker told me he expects it on OTN very soon.

I used my digital camera to make some photos of the demoed screens. The unfortunate thing is that I forgot to bring my USB cable to the Moscone center, so I can’t upload the pictures before this evening. Check back again to see the screenshots.

Some of the other things discussed in the session were Oracle Secure Backup, Patch distribution by Enterprise Manager Grid control 10gR2, TimesTen in-memory database, XQuery, Transparent data encryption, HTML-DB and two new security features: AuditVault and DataVault.

To me, TimesTen and AuditVault were the most interesting as they seemed to target actual business problems we are having right now. I’ll have to look into both technologies when I’m back at the office. I guess attending OpenWorld will take more then just the one week I’m here. I get the feeling I have a couple of weeks work when I get back in checking out all the new stuff I discover here.

PS. On a more personal note: congratulations to my wife for our second wedding anniversary. Couldn’t think of a better/more romantic way to celebrate it than to be on my own in San Francisco visiting a tech conference

Update 19-sep-2005 18:30: I’ve just returned to my hotel room to connect my digital camera to my laptop and upload the pictures. I didn’t want to use my flash when making the pictures during the session so I needed quite a long shutter time. Unfortunately that made the first picture kind of blurry. Nonetheless I will post all three pictures below. You can click on any of the pictures to get a bit larger view.

If you want to get the full size images, you can find them here, here and here.

The first screenshot is the blurry one but you can (hardly) see the tree control showing all database objects and the code editor for stored PL/SQL. The second shot shows the context menu including the Format SQL option for code formatting. The third and final shot shows the debugger in action. You can see all variables in the lower right corner. By the way: hovering over a variable in the code editor will show its value as a tooltip. You can also see the debug console at the lower end of the screen and the call stack in the lower left. I must say Raptor looked very promising for the five minutes we got to look at it. Let’s hope Oracle will show it at the demogrounds later this week and for the screenshots Brian has promised.

I’m off to the OTN underground event. Check back tomorrow for more updates on OpenWorld.

Update 21-sep-2005: Raptor was just briefly demoed during the keynote of Chuck Rozwat. Unfortunately I was watching the keynote in an overflow room where the screen is too small to make any good pics. If anyone attended the keynote in the big room and made some usable pics, you can contact me to put them up here.
I’ll have a look at Raptor at the HTML DB demoground myself, but I doubt they will let me near it with a camera

Update 22-sep-2005: Brian Duff has just published some real screenshots for project Raptor. These actually have some detail compares to mu blurry pictures.

Update 31-dec-2005: Added a comment at the top of the article about the release of an Early Access version of project Raptor.

Generating N rows from dual

Sometimes in a query you just need a list of numbers. With a neat trick you can select these from DUAL:

select * from (
    select level lvl
    from dual
    connect by level <= N
)

I found this useful tip on Eddie Awad’s blog. He left a comment on mine and that’s how I found him. He’s an Oracle application developer and runs quite an interesting blog. Shouldn’t he be on OraBlogs?

PS. Orablogs still seems to be having DNS problems and can be reached by http://83.170.75.145/orablogs/

Why you need indexes on FK columns

I’ve been developing Oracle systems for about 10 years. Over the years all environments I worked had the rule to create indexes for each foreign key. I knew it had something to do with locking, but never knew the exact details. Until today….

I just started working at Eurotransplant last November, so I wasn’t involved in building the system. As it turns out quite a large number of foreign keys do not have indexes. Together with a quirk in Oracle Forms this led to a locking nightmare.

A note on metalink quite clearly describes the locking related to referential integrity. It was originally written for Oracle v7.0, but it includes notes where newer versions (v7.1.6, v9.0.1 and v9.2.0) changed their behavior. We’re still running v8.1.7.4 so I guess we’re using the v7.1.6 specs of this document.

When you Update or Delete a record in the parent table, a share lock is required on the child table. This lock is held until the end of the transaction (commit or rollback). Other users can also Update or Delete records from the same parent table and they also will acquire a shared lock on the detail table. The problems start when someone wants to make changes to the detail table. They cannot acquire the lock and will hang until the lock is freed.

Most of our transactions come from Oracle Forms. These applications tend to issue DML and follow it with a commit immediately. So, normally a user would not hold this locks for a considerable amount of time. However, we also have an ON-MESSAGE trigger that prevents a message from showing in the status bar but shows it in an actual alert box. So, when a user makes changes and presses Save, he/she gets an alert box stating “FRM-40400 Transaction complete: # records applied and saved”. As it turns out the changes haven’t been applied and saved yet. The DML statement have been sent by Oracle Forms, but the COMMIT is only issued after the user dismissed the alert.

Due to trigger logic changing some records can take a considerable amount of time. Users tend to press Save and then continue work in another application or will do something else. After 10-20 seconds the DML is completed and Forms presents the alert box to the user. By then, the user is doing something and who know how long it will take before he/she dismisses the alert box. All that time, he/she still holds the locks, including the shared table lock on the child table

Lesson learned: Create indexes on all foreign keys or at least for the ones where the parent table can be changed. For this locking problem, foreign keys to static tables (e.g. domain values) do not have to be indexed.