Proxy Users

Here’s a little experiment that may surprise you – to be run only on 10gR2, using an account with DBA privileges.

A couple of warnings before you start. The script assumes you don’t already have a couple of users called end_user and application_user; the script assumes you have the (commonly installed) tablespace called users. If these assumptions cause a problem, change the code before you run it.

The question is – what do you expect to see as the final two sets of output:

create user application_user identified by application_user;
grant create session to application_user;              

create user end_user identified by end_user;
grant create session, create table to end_user;              

alter user end_user grant connect through application_user;
alter user end_user quota unlimited on users;              

create table end_user.tableX(col1 number);              

connect application_user[end_user]/application_user              

select user from dual;
desc tableX             

As you can see, the last steps of the program show you who are, and try to describe a table that belongs to user end_user.

And when you run the code you find that you are end_user and can describe that table, despite the fact that you have apparently connected to the application_user account, using only the application_user password.

It’s a final enhancement to the proxy user concept which was introduced, but poorly documented and limited to OCI programs, in Oracle 8i. In 9i the feature was extended to Java with a set of Java Classes, and now in 10gR2 you can even take advantage of it in SQL*Plus.

This is rather useful, of course, if you have to deal with a system that does complicated things with logon triggers, privileges, and fine grain access control (FGAC) – also known as virtual private database (VPD) or row level security (RLS).

With the appropriate privilege, you can connect to the database as another user without knowing their password – and everything you do will be audited, triggered, and secured as if you were that user. So any performance tests you need to do on the database can be done in someone else’s working environment rather than your own.