Oracle Scratchpad

December 18, 2006

Proxy Users

Filed under: Infrastructure — Jonathan Lewis @ 10:47 pm BST Dec 18,2006

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.

10 Comments »

  1. Great to know that we can now do it in SQL*Plus.
    Just two remarks: application_user does not need to have been granted “create session”, just end_user needs to; end_user does not need to have a valid password.
    I think this is one of the purposes of proxy users: end_user can connect only through application which is the only one to know the password of application_user.

    Comment by Michel Cadot — December 19, 2006 @ 8:13 am BST Dec 19,2006 | Reply

  2. I’ve typically seen this feature talked about in an n-tier environment when the application connects to the database via an application server and the application server connects to the database as the same user for all connections. Using this feature means that proper auditing of the real connecting user may take place as you suggest. It also makes for more efficient re-use of JDBC pooled connections and may simplify the pooled connection setup.

    As Michel says; I believe that your demo may also work with less privileges for the reasons stated.

    You allude to another possible use; testing performance for a specific user without requiring their password. Is this just a convenience or is there more to this? Would you care to say a few more words?

    Regards.

    Comment by Mathew Butler — December 19, 2006 @ 8:50 am BST Dec 19,2006 | Reply

  3. I forgot to thank you for sharing this one! Its good to see we can now do this directly from sql.

    Ive just started working with 10.2 and still have the new features guide on my to do list. Notes like this enthuse me to read the docs again.

    mat.

    Comment by Mathew Butler — December 19, 2006 @ 8:57 am BST Dec 19,2006 | Reply

  4. [...] Thanks to Jonathan Lewis post today Proxy Users, I could imagine using the proxy functionality in sqlplus to do a su [...]

    Pingback by Laurent Schneider » Blog Archive » su in sqlplus — December 19, 2006 @ 9:25 am BST Dec 19,2006 | Reply

  5. Michel, Good point about the end_user account not needing a valid password. My strategy for blocking the end-user from any access to the data is to have an application role protected by a call to a packaged procedure that checks the sys_context(‘userenv’,’proxy_user’) to check that a user had connected legally through the application.
    I’ve left the application_user with the ability to create a session so that it could prepare (global) contexts for the end_user before creating a session for them.

    Comment by Jonathan Lewis — December 19, 2006 @ 9:03 pm BST Dec 19,2006 | Reply

  6. Is it possible to use SQL*Plus to connect to an “authentication required” proxy user? The form ‘sqlplus appuser[enduser]/apppassword’ works for normal proxy users, but if proxy authentication is required, the form ‘sqlplus appuser[enduser/endpassword]/apppassword’ is a syntax error.

    Comment by Philip Douglass — July 13, 2007 @ 5:39 pm BST Jul 13,2007 | Reply

  7. [...] on July 23rd, 2007. I was going through an interesting post by Jonathan Lewis on Proxy Authentication that is available for end users via Sql Plus in 10g R2. This post made me think about the [...]

    Pingback by BI Publisher 10.1.3.2 and VPD « Business Intelligence - Oracle — July 23, 2007 @ 2:27 pm BST Jul 23,2007 | Reply

  8. Can you have a proxy user that owns objects? If so, will this proxy user allow users to query their own data in addition to the proxied id?

    Comment by tas_gibbs — June 30, 2011 @ 4:04 pm BST Jun 30,2011 | Reply

  9. I did as below but I am getting while connecting thrugh proxy, Anyone can help?

    SQL> create user          proxy_user
      2  identified by        pw_proxy
      3  default tablespace   users
      4  temporary tablespace temp;
    
    User created.
    
    SQL> create user          target_user
      2  identified by        pw_target
      3  default tablespace   users
      4  temporary tablespace temp
      5  quota unlimited on   users;
    
    User created.
    
    SQL> alter user target_user grant connect through proxy_user;
    
    User altered.
    
    SQL> grant create session,
      2        create table
      3  to    target_user;
    
    Grant succeeded.
    
    SQL> connect target_user/pw_target@dev
    Connected.
    SQL> create table targets_table (
      2    col  varchar2(10)
      3  );
    
    Table created.
    
    SQL> insert into targets_table values ('foo');
    
    1 row created.
    
    SQL> connect proxy_user[target_user]/pw_proxy@dev
    ERROR:
    ORA-01017: invalid username/password; logon denied 
    
    
    Warning: You are no longer connected to ORACLE.
    

    ==========================

    Thanks

    Comment by Dennis — February 20, 2012 @ 1:38 pm BST Feb 20,2012 | Reply

  10. Dennis, what is your Oracle version ?

    Comment by ram — February 23, 2012 @ 10:17 pm BST Feb 23,2012 | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

The Rubric Theme. Blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 3,905 other followers