It's been a while since I've had time to lurk out here...
Is anyone out there using Oracle's (10g and above) Proxy authentication?
Our team is using it, also with Enterprise Users (LDAP/OID based user
list) with some success in Java/web applications.
I spent a little time and got DBD::Oracle to hack to use proxy users ...
however, some things are not as they seem, if you understand proxying.
For example, given the following configuration:
create user app_pool identified by app_pool;
grant connect to app_pool;
create user test_user identified by test_user;
grant connect, resource to test_user;
alter user test_user grant connect through to app_pool;
sqlplus app_pool/app_pool; -- should work
sqlplus test_user/test_user; -- should work again
sqlplus app_pool[test_user]/app_pool; -- should still work -- sqlplus
automatically handles the [ ] in the connect string:)
You connect as app_pool, but switch over to login as like test_user. Think
of sudo su - test_user...
This is a very simple case of it's larger purpose.
Currently, we use the proxy technique in our Java based web applications,
so that the app server can pool connections and then "switch over" to the
actual DB user (or enterprise user) during that user's transaction. It
makes connections stay fast and helps ensure the database understands who
is really doing the work. It makes VPD policies more straight forward,
too, at least for us. It doesn't matter how "test_user" gets to the DB,
sqlplus, report tools, etc, they get the same rights/privs/view of the db.
I could see this used in Apache::DBI like situations, too.
Now, of course, we have some night time processing we need to do on behalf
of cleint users. The VPD will fail, unless we truly act as that user.
We'd like to use Perl, of course, so I'm trying to hack something in.
I've added a few lines to DBD::Oracle's code to handle simple proxying,
but of course, nothing this deep is very simple...for example tests which
assume they know who you are (the primary key tests in t/20select.t, for
example, uses primary_key to get the user's primary_keys for the test
table, but that fails on the surface. The test in t/20select.t assumes
$dbh->{USER} or $dbh->{Username}...so that obviously will fail. It's
easily fixed by using the value of
select sys_context('USERENV', 'SESSION_USER') from dual;
But that may not be the correct point...
My first inclination is that things need to be different with proxy
authentication, such as:
$dbh->{USER} or $dbh->{Username} should work, but work for the
proxied ("destination"?) user, not the connect through account -- i.e.
test_user
We may want a ProxyUser attribute to understand/track who we
really used for the connection, but I can't really see how that's useful,
to be honest.
Other affects?
Thoughts/questions/comments?
Thanks,
Jeff
