The proxy user only has "create session" privileges but can't do much else.

The real user does not have "create session" privileges and cannot log on to the database without knowing the proxy user name and password (and that is only set on the web/application server).

The USER function returns the real user name, and all standard database auditing, roles, etc. work as usual.

As of version 1.3 of the Thoth Gateway, proxy authentication is now supported. Here is how it works:

IIS Setup

Set up the application (virtual directory) in IIS where the gateway runs with Integrated Windows Authentication, so that the CGI environment variable LOGON_USER will be populated with the client's Windows username. (If the user is using Internet Explorer to browse the site, his identity will be passed on to the web server/gateway automatically; if using another browser, then an explicit logon is required.)

Oracle Setup

Define an "application server user", ie the common user that connections will be established through:

Notice the value "LOGON_USER" specified for the DatabaseUserName parameter. This is a reserved string that will be replaced with the actual value of the LOGON_USER value from the web request (ie. the user's Windows username, typically "domain\username"). You can also specify "LOGON_USER_NO_DOMAIN" to strip away the domain part of the user name -- what you use will depend on how you have set up your user accounts in Oracle.

Testing It

To test that everything works at this point, create a procedure similar to the following, and execute it via the gateway (don't forget to grant execute privileges on it to the end-user's account, and create a public synonym for it unless you prefix with the procedure owner's name in the URL).

If successful, the USER function should return the end-user's Windows username, and the Proxy User should display as "appserver".

Postscript: A little enigma

Actually, if you do as described above, you could possibly get this error when you try to run the procedure via the gateway:

ORA-1045: user %s lacks CREATE SESSION privilege; logon denied

At least, that's what I got . To get around it, I had to explicitly grant this to the "appserver" user:

grant create session to appserver;

The funny thing is that my example above, tested via SQL*Plus, shows that this works without the grant! But when attempting the same connection via ODP.NET, it gives the above error unless the grant is made.

And if I revoke the "create session" from the end_user, the above example doesn't work in SQL*Plus, because of the missing privilege. Which seems to contradict the purpose of proxying, as defined at the top of this blog post.

About Me

I have been working as a consultant and software developer for 18 years (since 1997), with a special passion for relational databases. I have done a fair share of work using Microsoft tools (including SQL Server, VB, ASP, .NET and C#) and other tools (most notably Delphi), but my favorite tool is the Oracle database with PL/SQL and Apex.