Skip to Main Content

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Atin.

Asked: July 29, 2004 - 5:13 pm UTC

Last updated: April 05, 2022 - 8:21 am UTC

Version: 9.2.0.1.0

Viewed 100K+ times! This question is

You Asked

Hi Tom,
Just wanted to know what is or are the differences between a Normal User and a Proxy User.
Does the Proxy User have access to all the objects of the underlying User?
Can the Proxy User be restricted using FGA?
Thanks for your answers and valuable time.
Atin.

and Tom said...

a proxy user is a user that is allowed to "connect on behalf of another user"

say you have a middle tier application. You want to use a connection pool. You need to use a single user for that. Say that user is "midtier"

Scott can grant connect through to this midtier user.

So now the midtier user (which has just "create session" and "connect through to scott") authenticates to the database and sets up the connection pool. This midtier user is just a regular user -- anything you can do to scott, you can do to midtier, but it generally isn't relevant. For the only thing midtier will do in the database is connect really!

So, scott comes along and convinces the midtier "i am really scott". The midtier then says to the database "you know me, I'm midtier and I'd like to pretend to be scott for a while". the database looks and says "yes midtier, you are allowed to be scott for a while -- go ahead". At this point -- that midtier connection will have a session where by "select user from dual" will return SCOTT -- not midtier.


Scott never gave the midtier his password to the database, in fact, scott might not even KNOW what his password to the database it!

Now, this SCOTT session that was created on behalf of the midtier connection is subject to all of the rules and privs around the user SCOTT -- it can only do what scott is allowed to do.


The nice thing about this is:

o you have auditing back, the database knows who is using it. no more of this "single username" junk.

o you have grants back, you don't have to reinvent security over and over and over.

o you have identity preserved all of the way from the browser through the middle tier and into the database.

Rating

  (76 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Great Answer Tom!!!!!!!!

A reader, July 31, 2004 - 2:01 pm UTC

Thanks for the answer Tom. I understood the use for having a Proxy User.
The reason I asked this question was that I want to utilize this feature in our database. At present the salesman or sellers are authenticated through a table in a user's schema. So I was thinking why not change them to Proxy Users and let them access the database and the underlying tables.
But was scared as to what can they do if they get hold of the password of the real user.
As I was reading your answer I noticed that the proxy user does not have a password and that it uses the password of the Real User internally. So if we are careful, then no security threats, right?
Also is auditing done on the Proxy user or the Real User.

Sorry for bothering you again with this question,
Thanks and regards
Atin

Tom Kyte
July 31, 2004 - 2:31 pm UTC

the proxy user DOES have a password, the proxy user authenticates to the database "normally".

user at browser authenticates to middle tier

middle tier is already authenticated on the database

middle tier "becomes the other user"


It does NOT use the password of the "real user", in fact it was designed to avoid having the real user tell it what its database password in fact is!


You protect the account in the middle tier (you secure that account, that accounts password) as you would SYS, SYSTEM, your DBA's, whatever.

Is Proxy a OID user/ Enterprise user

Suhail Ahmad, September 23, 2004 - 5:00 pm UTC

Tom,

We have setup Oracle Directory and are in the process of implementing Enterprise User Security. As for as I know that a proxy user is being created into the database ie using CREATE USER command. So if I take your example where midtier is a proxy user. I will do following to setup my connection.

SQL> create user midtier identified by test;
SQL> grant create session to midtier;
Now I will alllow midtier to proxy to scott/tiger ie.

SQL> alter user scott grant connect through midtier;

Is there a way to make OID user looks like a proxy user? Can I achive Proxy authentication mode by DN?

Thanks for your answer.

Suhail Ahmad 

Tom Kyte
September 24, 2004 - 9:55 am UTC

If you are interested in a pretty good book that covers all of this stuff -- David Knox just put out a book with Oracle Press -- 

http://www.amazon.com/exec/obidos/tg/detail/-/0072231300/

I'll ask him to take a peek at this and see what he has to say...

(David Knox provided the following response)
Yes, there is a way to make the Enterprise User Security (EUS) users work with proxy authentication. Let's set this up with comments for explanation. 

Just as review, you want to create the midtier account, i.e., the account to which your connection pool is established, with the least amount of privileges. For Oracle, this means the CREATE SESSION privilege. (Note, don't use the CONNECT role as it contains other privileges.) This ensures that if the connection pool account is compromised, the hacker cannot do anything other than connect to the database.

For EUS, the LDAP users are maintained in the directory. The important part is that there is usually a schema identified in the database to which the EUS will connect. That is, there is no SCOTT schema in the DB. There is a SCOTT user, and when he connects to the DB, the DB authenticates him against the directory and uses a mapping to determine where the user should operate. For this example, we can create such a schema:
SQL> create user EUS_MAPPED_SCHEMA identified globally as '';
SQL> grant create session to EUS_MAPPED_SCHEMA;

At the directory level, we use Oracle's Enterprise Security Manager to create the SCOTT user and map it to the EUS_MAPPED_SCHEMA created above (the name EUS_MAPPED_SCHEMA is arbitrary, but the syntax to create that user has to be done as identified globally as ''
At this point you have two database schemas: MIDTIER to which your connection pool attaches (only create session) and EUS_MAPPED_SCHEMA to which your directory based user is mapped.

For the actual proxy authentication, you can simply supply the user's name, SCOTT in this example, or the DN for the user. The DN is easy to obtain when using Oracle's Application Server as it is provided to you from the Single Sign-On server using the following code:
String userDN = request.getHeader("Osso-User-Dn"); 

Then, your connection to the SCOTT schema from MIDTIER will occur, but only after you grant the DB privileges to allow this. 
SQL> alter user EUS_MAPPED_SCHEMA grant connect through MIDTIER;

The proxy code to do this (in Java) will look like this:
    String tnsAlias = "(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = " +
                      "  (PROTOCOL = TCP)(HOST = dknox)(PORT = 1521)) )" +
                      "  (CONNECT_DATA = (SERVICE_NAME = knox10g) ) )";
      OracleOCIConnectionPool ods = new OracleOCIConnectionPool();
      ods.setURL("jdbc:oracle:oci:@" + tnsAlias);
      ods.setUser("midtier");
      ods.setPassword("strongPasswordforMidtier");

      java.util.Properties userNameProp = new java.util.Properties();
      userNameProp.setProperty(OracleOCIConnectionPool.PROXY_USER_NAME, 
                               "scott");
      Connection conn = ods.getProxyConnection(
                          OracleOCIConnectionPool.PROXYTYPE_USER_NAME, 
                          userNameProp);

The Effective Security by Design book gives much more details and many more examples on Proxy Authentication and connection pools including architecture trade offs, performance implications and security pluses and minuses.

 

I bought this book long ago

Suhail, September 24, 2004 - 10:41 am UTC

Tom,

I bought this good book long ago, you only recommended it to me. Somehow not be able to achive what I want. It seems to me that Proxy user should be mapped to an exclusive shared schema. Here, I got confused, first we are creating a database user using THROUGH option and then we are mapping this to a shared schema. Proxy user is a database user. But where is Enterprise user then. Enterprise users are created in OID only and not in DB. My OID users are also mapped to an OID group so that I can use VPD for group based authentication.

Do I have to create Proxy user in OID too with the same name/password? So I got confused with all this indirect authentication process and really need some help.



Tom Kyte
September 24, 2004 - 11:54 am UTC

"long ago"? it was just published? I'll ask David if he wouldn't mind commenting (sort of out of my personal area of expertise)

long ago

Suhail, September 24, 2004 - 12:13 pm UTC

means two months ago, I bought it.:)

see

Suhail, September 24, 2004 - 12:17 pm UTC

this thread </code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:9312070542748, <code>
You recommended it to me on July 9the and I ordered the next day. Its long ago ie almost 75 days:)

Proxy Authentication

Suhail, October 11, 2004 - 8:15 am UTC

Tom,

Please convey my sincere Thanks to Knox for his reply to my question about Proxy Authentication by DN. I was wandering is there a way to implement Proxy Authentication (or any other alternative) using PL/SQL. We have applications in ASP, Cold Fusion etc and it seems to me that the code which Knox's has provided can be used in Java ( or in C OCI). I would like to implement Proxy Authentication by DN using PL/SQL. Is there a way to use DN to authenticate users from applications and I can still use my other database security such as enterprise roles, VPD. Auditing etc.

thanks.

Tom Kyte
October 11, 2004 - 8:31 am UTC

in order to be in plsql, one is already "in" the database -- you already *have* a session.

therefore, you have already authenticated -- proxy or otherwise.

your ASP/Cold Fusion environment is what creates the session that would run PLSQL. by the time you are in plsql, it is too late to "proxy authenticate".

Is there any alternative

Suhail, October 11, 2004 - 9:08 am UTC

Yes, you are right. But is there any alternative to proxy authentication, I mean can I still connect using DN from middle tier and have all the database security functions be referenced.



Tom Kyte
October 11, 2004 - 9:44 am UTC

sure, once you are connected using proxy authentication, everything "works" -- plsql, C, java, whatever -- they are all "proxy authenticated to the database"



Is there any alternative to Proxy Authentication

Suhail, October 11, 2004 - 10:49 am UTC

I think I did not explain to you my problem. I can use Proxy Authentication ( DN Mode) if I am building Java application ( Connection Pooling). As Knox pointed out in his code, he is first conencting to database using Middle Tier user ( which is a proxy user) ie
OracleOCIConnectionPool ods = new OracleOCIConnectionPool();
ods.setURL("jdbc:oracle:oci:@" + tnsAlias);
ods.setUser("midtier");
ods.setPassword("strongPasswordforMidtier");

Now next he is using DN to connect ie

java.util.Properties userNameProp = new java.util.Properties();
userNameProp.setProperty(OracleOCIConnectionPool.PROXY_DISTINGUISHED_NAME,
"scott");
Connection conn = ods.getProxyConnection(
OracleOCIConnectionPool.PROXYTYPE_DISTINGUISHED_NAME,
userNameProp);

So, if I write a pl/sql to be called from ASP/Cold Fusion, I will first create an ODBC connection using "midtier" user( which is a proxy user account) and after got connected, will use DN to connect. How would I do the second connection in PL/SQL after I am already connected using a Proxy User?





Tom Kyte
October 11, 2004 - 10:57 am UTC

I didn't misunderstand you - the problem is IN ORDER TO BE IN PLSQL YOU ARE BY DEFINITION ALREADY CONNECTED.

It does not make any sense whatsoever to talk about connecting in plsql stored procedures, you are *already there*.

Your MIDDLE TIER must establish the connection -- this is a question for the authors of "ASP" and "Cold Fusion" -- you need to ask them how to use proxy authentication with their tools. You need to ask them if they support this.

PLSQL is out of the equation there -- you do not connect in plsql, you are *already connected* in plsql.

Proxy User for application user

Vidya, January 17, 2005 - 9:11 pm UTC

My question is related with the main question. If I am correct in understanding Proxy User, Proxy users after loggon to database or Oracle directory switches identity to the end users ie real user. Is this true for application users too, I mean if I am storing my users in a database tables ie neither my user is db user nor LDAP{ users, then how does Proxy Users switches its identity to end user? I read that use of CLIENT_IDENTIFIER attribute could be usefull in preserving the identity but donot know how to do it?.

Thanks

Vidya

Tom Kyte
January 18, 2005 - 9:29 am UTC

you need to be a database "user" at some level in order to be proxied to. Your question "how does proxy users switch ...." is unanswerable -- since we cannot "switch" to some user that doesn't really exist.


If you click on the links I like above -- there is a book on security you may well be interested in. it covers proxy auth, client_identifier and many many other things in some detail

then how does Proxy Authentication work for apps user

Vidya, January 18, 2005 - 12:59 pm UTC

So, does this means that we can create a user with just create session privilege ( no CONNECT THROUGH option) and then use something similar to following JDBC string to populate CLIENT_IDENTIFIER for end user:

PreparedStatement ps = conn.prepareCall(
"begin dbms_session.set_identifier(?);
end;");
ps.setString(1, userName);
ps.execute();

and then we can control the auditing , VPD etc for the real users ( ie apps user not known to db)?

Thanks



Tom Kyte
January 18, 2005 - 1:07 pm UTC

well, except for the teeny tiny fact that dbms_session is executable by public....


you can use set_identifier to let yourself sometimes, probably know that the user is "id=abc".


I prefer application contexts myself.

an example

Vidya, January 19, 2005 - 11:02 am UTC

Could you give me an example how to use application context to solve my situation instead of using "begin dbms_session.set_identifier(?) end" .

Thank you.

Tom Kyte
January 19, 2005 - 9:03 pm UTC

meaning, use application contexts to set values.

you cannot set a context value without calling a special piece of code that you write and you control access to.

anyone can call dbms_sesesion, anyone.

non db and non ldap user

Suhail, March 01, 2005 - 3:14 pm UTC

My question is related to your answer to the original question.

<quote>
o you have auditing back, the database knows who is using it. no more of this "single username" junk.

o you have grants back, you don't have to reinvent security over and over and over.

o you have identity preserved all of the way from the browser through the middle tier and into the database.
</quote>

My apps user's login info (logon id, password, name etc) are stored in a SQL table. I am using db trigger to capture created_by and modified_by columns for every transaction, so identity is preserved withought using Proxy User, I know who, when has created or modified a record . So how come this approach is different then that of using proxy user?. How would you use proxy user when you have millions of user for a dot com apps?.

You need grant when you have enterprise user or db user ,in my case where users are stored in a table , I donot need any grant. I have to use a big user any how with all privilege.

How does Oracle maintain security for its own application such as Oracle 11i -Financial or HR?

Suahil

Tom Kyte
March 01, 2005 - 3:33 pm UTC

because you are writing all of the code yourself, you do authorization (no grants), you do auditing (no builtin auditing) you do not have identity preserved (ask the DBA to tell you "who is running stuff right now")

If the millions of users have accounts, where are these accounts? why not in a directory? why create your own directory? You need a directory of millions of users with their passwords and their contact information and so on.

ldap is used in oracle apps.

millions of account

A reader, March 01, 2005 - 3:45 pm UTC

Is it a good idea to create millions of users account in ldap/OID ? I heared ldap should be used for smaller user entries , specially where you want to manage your own employee and not external/internet users. Do you think amazon.com or ebay.com use ldap based architecture for maintianing users account?

Tom Kyte
March 01, 2005 - 4:02 pm UTC

our largest customers are telco companies using ldap to manage their many user accounts.

LDAP was designed to store large respositories of directory information in a scalable fashion, with redundancy and so on.

it is a database application.

A reader, March 01, 2005 - 4:10 pm UTC

thanks, good to know it.

Proxy Auth

Rich, April 08, 2005 - 4:17 pm UTC

I would appreciate some clarification on how to setup
proxy auth. I got a copy of David Knox's book which
provides very good examples.

My question is: will it be possible to use proxy auth
if the connection pool is not maintained by Oracle (i.e.
BEA Weblogic in our case)? I guess the difference is that
we use BEA's DataSource object to get a connection from
the pool. The examples of proxy auth I have seen so far
use OracleOCIConnectionPool.

I am not a webserver nor a java expert so forgive me if
this is a basic question.

Tom Kyte
April 08, 2005 - 4:26 pm UTC

I'll ask Mr Knox to comment.

and he says:

....
To answer the question, the JDBC drivers can be added to any J2EE application server. The specific ones used rely on the OCI layer so the Oracle Network libraries will also need to be accessible to the application server. For this to work, they will have to abandon the BEA drivers for ours. Otherwise, they can look to use Application Context/Client Identifiers in conjunction with their current drivers. This then requires that they will have to modify the code to set the Ctx. While not as good as proxy authentication, it will be able to provide the identity to the DB.
.........

Maintain proxy users in two places?

Mark Freeman, May 11, 2005 - 1:54 pm UTC

Tom, if my ASP application needs to authenticate the user and then pass the user name to the database as a proxy, and I have to have that username created in Oracle, it sounds like I need to maintain the user list in two places. (Oracle has users MidTier and Scott, ASP has user Scott in its own user table in the database which also includes his encrypted web site password and a list of his access rights for use with VPD.)

This sounds like an administration nightmare in the making, so I must be misunderstanding this. Can you elaborate on this point? Does anyone ever need to know Oracle's password for Scott?


Tom Kyte
May 11, 2005 - 7:22 pm UTC

you have global users, enterprise users - they can all be maintained in a single ldap repository.

no one needs to know scotts password ever, even scott

Re: Maintain proxy users in two places?

Mark Freeman, May 11, 2005 - 10:35 pm UTC

We won't have LDAP or SSO. Just an ASP.NET app working with an Oracle 9i database. Will I need to have Scott setup as an Oracle user as well as having a separate application login?

Is there any way to get the application to somehow pass the ID and password entered on the web form to Oracle for authentication instead of the web app having its own password for Scott?

Conversely, is there any way to enable the web app to create users in Oracle so that the user IDs and passwords are managed solely through the web app?

The idea is that I want to have the users either maintained by a DBA or by an application admin, but not both. I'd prefer to do it through the app, so I can get help desk people to do it and not bother a DBA with it.


Tom Kyte
May 12, 2005 - 7:41 am UTC

if you don't have a centralized repository of users, then you would have to register them everywhere -- yes (but that is not a fault of the implementation of proxy users but rather a decision on your part).

You can pass whatever you want from your application to oracle to authenticate with, but if Oracle does not have that user known to it -- it would not "do aanything". Yes, you can authenticate from any middle tier application to the database.

You can also create an application to create users in the database -- but at that point why? If you don't control this, I'll just create accounts left and right -- what have you achieved?

You can do either, the application can use the database to authenticate (just write the code to connect with their user/pass). The application can have their own fake users and implement security, access control, auditing, roles, grants, everything.

You might be interested in the effective oracle security by design book by David Knox referenced in the links I like tab above.

Application/Oracle authentication

Mark Freeman, May 12, 2005 - 10:11 pm UTC

Last pass at this, I promise.

Perhaps I'm making things too complex in trying to simplify them. I want to be able to have one person add the user and assign them appropriate rights all in one place. Since the security model will involve a matrix of role, location, and client for each user, it would be easier for whoever gets stuck administering the accounts if that could be done in the application with a nice user interface including multi-select dropdown lists for those items. They would be the only one who would be able to get to that screen.

The code for adding users could check if the username already exists in ALL_USERS. If it does, it would reject the username as a duplicate. If it doesn't, it could send Oracle a command to create the user with a generated password, and then add the appropriate records to the application's USERS_ROLES, USERS_LOCATIONS, and USERS_CLIENTS tables (which in turn have FKs to the ROLES, LOCATIONS, and CLIENTS tables to make up the many-to-many relationships). The administrator would then provide the user with their username and generated password.

When the user logs in to the app, is there a way for the app to accept the username and password and pass it through to Oracle for validation? Could it do so without actually logging in with that user account? (I assume that if the app logs in as the user instead of with its own MiddleTier account, I would lose connection pooling and I want to keep that.)

That would avoid the application having to store its own password for the user. All authentication would be done by Oracle. Once validated, the application could do the usual proxy authentication to identify the user to the database but still use its MiddleTier logon and shared connection pool for all further communication between the user and the database.

If all that would work, the only remaining issue is how to let the user change their Oracle password through the application interface.

This may seem to be a somewhat perverse approach, but would it work?

I just don't want to have to have a two step process where a DBA has to add the user to Oracle (with a random password) and then the application administrator (different department) has to then add the user and a real password to an application-owned USERS table so they can assign the roles, locations, and clients to the user. That would be inelegant and create workflow issues.


Tom Kyte
May 13, 2005 - 9:10 am UTC

<quote>I want to be
able to have one person add the user and assign them appropriate rights all in
one place. </quote>

You just said "i want a directory, like ldap, to centralize user management". You said it really loud and clear!


<quote>
When the user logs in to the app, is there a way for the app to accept the
username and password and pass it through to Oracle for validation?
</quote>

Yes, we call that "connect", but you would have to create a session for that user (you don't have to connect again, just create a new session -- but not sure if your api lets you do that or not)

If you want to use Oracle to authenticate, you have to create a session for them using that username and password.

Confused about OID Proxy User

Yuan, June 01, 2005 - 12:45 pm UTC

In David Knox's first response in this thread, wouldn't SELECT User FROM Dual give you EUS_MAPPED_SCHEMA? If so, don't you lose:

>o you have auditing back, the database knows who is using >it. no more of this
>"single username" junk.

Tom Kyte
June 07, 2005 - 1:12 pm UTC

Hey, it's me David Knox - yes, SELECT user FROM dual returns the name of the schema "EUS_MAPPED_SCHEMA". But, SELECT sys_context('userenv','external_name') FROM dual returns the distinguished name of the user. For auditing purposes, both the schema and distinguished name are captured. Therefore, you see not only who did it, but how they connected.

Yuan, June 07, 2005 - 3:12 pm UTC

Awesome. Thanks David!

OID

Yuan, June 08, 2005 - 9:12 am UTC

David,

>But, SELECT sys_context('userenv','external_name')
FROM dual returns the distinguished name of the user.

Is this independent of using proxy users? If an OID user logged in as the schema owner directly, would sys_context('userenv', 'external_name') still return the distinguished name of the user?

Tom Kyte
June 08, 2005 - 10:28 am UTC

Yes, Enterprise Users are independent of proxy authentication. If you think about it, it would have to be that way as you're likely setting up security and auditing on the Enterprise User. Furthermore, for proxy authentication, you can determine the original connection i.e., the connection path, by querying: SELECT sys_context('userenv','proxy_user') FROM dual

At the end of the day, you can then determine the proxy account from which the original connection was made - sys_context('userenv','proxy_user'); the schema to which the user is now operating within - USER or sys_context('userenv','session_user'); and the actual identity of the LDAP-managed user - sys_context('userenv','external_name'). The last two are automatically audited and you can always manipulate the Client Identifier to seed it with the proxy information if you like thereby capturing all three pieces of information in the audit trail.

OID

Yuan, June 09, 2005 - 9:44 am UTC

Thanks again! Great info. I'm gonna have to get your book.

A couple of Oracle reps came over to do a demo on OID, and when we asked about auditing, they said many clients accomplish that using logon triggers. Any idea what this is about?

Tom Kyte
June 09, 2005 - 11:20 am UTC

not really sure how you would "audit" beyond "connect" via logon triggers.


But the right answer should have been "DBMS_FGA" and "AUDIT" command.

Non-Database Authentication

PM, June 21, 2005 - 6:28 pm UTC

Hi Tom,
I wanted to know if there is a way to authenticate the users using sqlplus or ODBC without database authentication? I have a small data w/h for Peoplesoft data and want the users to connect using their Peoplesoft userid and password. I can have the userid's created in the database but have no clue what the users password is (which is encrypted in one of the PS table). I can use Peoplesoft API to authenticate the user. Other option is, I can use LDAP authentication instead of Peoplesoft userid and password. But, the question is, how? I can use any of these authentication method for the web application but, unfortunately, Most of the user are using MS Access, Discoverer, Sql*Plus and other ODBC products to connect to the database.

Thanks

Tom Kyte
June 21, 2005 - 9:10 pm UTC

no, the database has no "concept" or "clue" what a peoplesoft (or any application that uses application users) user is, what privs they have, nothing.

Helena Marková, June 22, 2005 - 2:06 am UTC


Need a DML login ?

Nags, July 25, 2005 - 5:57 pm UTC

We have a very simple web application which connects to an oracle database and to one schema. The application uses one username/password which is stored in a properties file on the application server (no developer is allowed access on this server, and we periodically keep changing the password).

We need another login which will be distributed to all developers who will connect to the database to develop applications or just check the data. Now the developers should be able to do only DML.

One way is to create a second user/role and GRANT SELECT, INSERT, UPDATE, DELETE, EXECUTE on each individual object (on development databases). Sometime they need only SELECT permissions (on test/production databases). But, the problem is everytime an object is created or recreated, we have to give permission again. And if a new schema is created, we have to grant permissions to the users.

Is there a better way ? Or is this the best ? Will proxy user help here ?

Tom Kyte
July 25, 2005 - 7:38 pm UTC

Use a role, maintain the role with the LEAST privileges (just exactly what it needs) and grant that role as needed.

Need a DML login ?

Nags, July 26, 2005 - 10:17 am UTC

Yup. This is exactly what I am going to do. Thank you.

I think Oracle should have an additional level of privileges, like object type privileges.

Like

GRANT SELECT ON TABLES TO <User>
GRANT EXECUTE ON PROCEDURE TO <User>

and then probably DENY privileges.

Most of the companies I worked have similar requirements wherein, one users owns the schema and the second user needs SELECT or DML privileges. In addition, the second user should not be able to access one or two special tables.

This is just a thought.

recreate 'OE' Schema

Dawar, July 26, 2005 - 6:03 pm UTC

OS: Linux

Database version is 10.1.0.3.0

How to recreate 'OE' Schema?

I want to remove default 'OE' Schema and create again.

Regards,
Dawar

Tom Kyte
July 26, 2005 - 6:23 pm UTC

there are oe_drop and oe_cre scripts in the demo/schema/order_entry directory

Dawar, July 26, 2005 - 6:38 pm UTC

Tom,

I do not see OE_DROP but I have oc_cre.sql

Here is my directory info.

oracle@abc:~/product/10.1.0/db_1/demo/schema/order_entry
> ls
coe_xml.sql oc_comnt.sql oc_cre.sql oc_main.sql oc_popul.sql oe_views.sql

Regards,
Dawar

Tom Kyte
July 26, 2005 - 6:55 pm UTC

You don't have the demo schema's installed from the Companion CD, they are on there (and not installed by default)

You'll need to fire up that disk or "steal" them from a 9i install

recreate

Dawar, July 26, 2005 - 7:29 pm UTC

Tom,

what about below?


SQL> select count (*) from dba_objects
  2  where owner = 'OE' and status = 'INVALID';

  COUNT(*)
----------
         9

Its mean is installed. Does it?

Regards,
Dawar 

Tom Kyte
July 26, 2005 - 7:45 pm UTC

could be from the "seed" database, but the files to create the schema are not there.

recreate

Dawar, July 26, 2005 - 8:17 pm UTC

Tom,

Is it possible I can remove 'OE' from scratch and install 'OE' from companion CD?

This is my production DB server, so I am extra caution.

Regards,
Dawar

Tom Kyte
July 27, 2005 - 8:38 am UTC

why would a production server have sample schemas? test this and any change in test first please

and you should probably just have the demos in a play/test database.

There's a real need here that Oracle does not meet.

Michael Friedman, July 26, 2005 - 10:34 pm UTC

Tom,

There is a real need here that Oracle does not meet.

People who are not developing a proxy server (and may not even be using a proxy server) need the ability to connect to an Oracle database using a non-privileged user account with a public password and then call a procedure that authenticates them, sets up appropriate session settings, and then connects them to a privileged database account.

This is usually a shared account and the user should never know the actual password.

C/S systems can't handle this today with Oracle. Most implementations (including EBS) connect to a non-privileged account and then use an encryption function in either the client or the Oracle server to obtain another database password. They then reconnect to the DB using a privileged account with the new DB password. Anyone who knows the internals of these apps can use the same technique to get the DB password and connect with SQL Plus or another tool.

The frustrating thing is that Oracle apparently already has the tools to solve this problem. There is an undocumented and unsupported system privilege called "become user". I have read that it can be used from OCI calls but not from PL/SQL. Supposedly Oracle uses it to implement IMP and EXP.

Tom Kyte
July 27, 2005 - 8:46 am UTC


but, in fact, you have that, it is called a secure application role. It exists.


become user is not what you are looking for, but a secure application role sure sounds like it is. (enable a role to give you privs, set the client identifier for auditing)



Aren't roles turned off in packages?

Michael Friedman, July 28, 2005 - 8:19 pm UTC

We considered this... but the fact that roles are turned off in packages seems to make it not very useful.

Tom Kyte
July 29, 2005 - 8:20 am UTC

umm, the only way to ENABLE a secure application role is to call a procedure or package (and no, they are not always disabled, there is authid current_user)

This is pretty much the functionality you just asked for:

Q (paraphrased) I would like to call a piece of code that would validate someones credentials and then enable privileges for them.

A: secure application roles do that.


Putting it all together

rakesh, December 31, 2005 - 3:41 am UTC

Hi Tom,

Seasons greetings to you and readers of this excellent site.

I have read countless threads on this topic of security. I understand you Tom are not a great fan of the 'one user connection pool' implementation common with Java (and other languages) applications and the constant reinventing of security functionality when Oracle already has alot of this built in.

These threads promise the solution of moving away from this practise and I would like to run through typical scenarios I have encountered and how they would be addressed by Oracle if i was to go to with it....

Firstly, here's what I currently do (and i think many other people have done) time and time again:

1. Create an account for the middle tier (muser). muser owns ntothing and has execute privileges on business package functionality. This accout is used to create the Java side connection pool.
2. Create an account app_owner which owns the db objects. This account is kept secure and is used for development purposes.
3. Create a table to hold users of the application and their (encrypted) passwords. In addition another table can hold relevent data for this account (eg email address, application defined roles, etc). There is also a package which contains procs to handle security such as login, change password, auditing, etc.
4. When the user submits their credentials, the username and password is passed to Oracle for checking. Assuming the lookup in the table(s) succeeds a special object is returned to Java which holds the users details (like those mentioned above).
5. Now Java can inspect this user object to make decisions about what this user can do. So for example, if i have a app user called rpatel who wants to run BUSINESS_PKG.CREATE_ORDER then i check the user object for that privilege (perhaps they have the app role CREATE_ORDER) and then call the stored proc. Of course its really muser calling this proc so if i want to record that it is rpatel I would have to pass this through to the stored proc as a parameter.

Problems with the above approach have been outlined by you already. I do agree. So to move to a proxy authentication system like mentioned above and in other threads would intially seem to be a good idea but it leaves me with the following questions:

1. If app users are now real db accounts, and i set that passwords must be changed every 30 thirty days, how would that work?
2. Java needs to know about the user and its roles because their may well be security controlled functionality NOT held in the database. I think this is a major issue overlooked by db folks. Say I have a business function that puts a message in a (IBM MQ Series) queue. I want to check the user has that privilige and then call a Java proc to do the work. Using my earlier system, I can check the user object for the right privilige and then execute the java proc.
3. How would you store extra information about db accounts? Eg email addresses, office location, full name, dob, etc.

Any comments?

Thanks

Rakesh

Tom Kyte
December 31, 2005 - 11:03 am UTC

1) I disagree with your thought that many other people have done this. In my experience people create the "super user account" and all of the SQL is in the middle tier and stored procedures are avoided (database independence after all)

2) I wish pepole would consider doing that, but most don't. In fact, far too many say "create me a DBA account and let me use that"

3) this is a bad idea, given that hundreds of industry standard identity management packages exist.

4) here is where sql injection starts to come into play...

5) and here is the big big problem. You've reinvented the proverbial wheel AND have made it so that the only way to use this data is via this java layer. And when something new needs to be done, this layer has to be reinvented typically all over again and again and agin....


1) Identity management - LDAP directories, single sign on, centralized management.

2) Nope, see #1 right above, store whatever you WANT in the LDAP repository, put it there so that anything that wants it can get it easily using standard protocols.

And why isn't MQ smart enough to understand who is allow to do what?

3) see #1 right above (the second #1)

Putting it all together 2

rakesh, January 01, 2006 - 10:58 am UTC

“…and here is the big big problem. You've reinvented the proverbial wheel AND
have made it so that the only way to use this data is via this java layer. And
when something new needs to be done, this layer has to be reinvented typically
all over again and again and agin....”

Yes Tom, I know what you are saying. But think about it. Its what the IT industry is all about. Rewriting apps using the latest new language and tools is the bread and butter of many, many companies and their staff. Trying to prevent this is not an issue for me personally right now. My job is to build a system using Java TODAY. We are not all VPs with alot of clout within our organizations. Most of us are front line grunts trying to get apps built with some really stupid constraints and people who we wished were top of their game but unfortunately won’t even use packages because they’ve never used them before.

What I do want to do is to build an application using Java and Oracle within time and budget. And this means I do not have the time or money to invest in third party identity management solutions. That may be the PERFECT solution but it may not be a practical one. My company is so bureaucratic its amazing they get anything done!!! Please assume that’s not an option.

Does that now mean I cannot use Oracle security features? I may as well just re-use my own home-built system.

RP


Tom Kyte
January 01, 2006 - 11:19 am UTC

So, because people do things "not the best way", we shoula all just give up and say "ok, ok, you win, do it not the good way - we'll stop pointing out the issues with your approach and just sit over here and watch you go to it"

It has nothing to do with being a "insert job title here". You asked me a question, I stated the obvious.

But now I don't know what you want me to say? I cannot see the forest(question) for the trees here.

Of couse you can use Oracle security features. Unless you tell me you cannot use Oracle security features. If you want to re-invent your own identity management (over and over and over again - how many systems do you log into with different credentials)?

If you do it yourself, then yes, you won't be using commericial off the shelf (COTS) software features - you'll have paid for these features of course, and you'll have paid and will keep paying to build your own on top of that, but you won't be able to use them.


Midtier authentication

mobra, January 02, 2006 - 8:42 am UTC

<quote>So, scott comes along and convinces the midtier "i am really scott". The midtier then says to the database "you know me, I'm midtier and I'd like to pretend to be scott for a while". the database looks and says "yes midtier, you
are allowed to be scott for a while -- go ahead". At this point -- that midtier connection will have a session where by "select user from dual" will return SCOTT -- not midtier.

Scott never gave the midtier his password to the database, in fact, scott might not even KNOW what his password to the database it!

</quote>

In this kind of setup, how would the midtier typically go about checking the credentials of the end-user? Would you have a database table called USERS (or similar) that contained usernames and (possibly encrypted) passwords for the end-users? (If so, the midtier would obviously need the capability to log into the database on its own and select from this table.)

Or are there other ways for the midtier to "know" who the end-user really is? Can LDAP or similar be used?



Tom Kyte
January 02, 2006 - 10:30 am UTC

The middle tier can do whatever it likes.

For example:

Middle tier could demand a PKI (x.509) certificate from client.
Middle tier could have its own table of user/passwords and do it itself.
Middle tier could take credentials provided by client and send them off to an LDAP repository for authentication.
Middle tier could be using a single sign on solution from any number of vendors with its associated API (we have such offerings, so do others)





Using Oracle accounts

R, January 05, 2006 - 1:19 pm UTC

Hi Tom,

I am building an application using Java/Web front-end/Oracle back end mix.

I am not using a connection pool as the time to connect is not an issue and we have only about a dozen users. Also the security controlled functionality is all in the db so I want to use roles for access.

When the user logs on with incorrect credentials, I can catch that exception and redisplay the form. The problem is more functionality is need and I wanted to know if there was some way for me to interact with the oracle security model:

1. The users must change their password every 30 days.
2. The passwords must be of a certain min length.
3. The passwords must contain a min number of uppers and numerics.
4. 3 invalid logon attempts within 15 minutes should disable the account and a DBA should then be involved to unlock it.

Is it possible to do this?

Thanks

R

Tom Kyte
January 05, 2006 - 1:28 pm UTC

yes, and the cool thing is - you don't need to touch your application, you do all of this in the database.

</code> http://docs.oracle.com/docs/cd/B19306_01/network.102/b14266/policies.htm#sthref863 <code>



Looks good but...

R, January 06, 2006 - 6:31 am UTC

Hi Tom,

I looked through the docs you linked to. It seems the Oracle account features are exactly what i need but i do not know how to interact with them.

For example, change password:

1. The user enters their credentials on the login page.
2. The app tries to use these credentials but recevies an error (i think?).
3. Redirect to the change password page and gather new password.
4. ????? Not sure what happens next. I need to have a session to the db in order to issue the SQL but I don't have one.

Am i meant to keep a seperate DBA-level connection available for these things?

Cheers

R

Tom Kyte
January 06, 2006 - 1:55 pm UTC

why would user receive an error on #2 - they are changing their password so they give you

a) their current one
b) their new one

you

1) log in using a) - if that fails, yell at end user, ask them again for their *real* password. if that succeeds goto 2)

2) issue alter user <theirname> identified by <their new password>;



A reader, January 09, 2006 - 7:52 am UTC

that assumes the password change is initiated by the user. What if the user must change the password every 30 days and on the 31 day they try to log on?

R

Tom Kyte
January 09, 2006 - 8:24 am UTC

you tell me - what would you like to have happen?

do they have a grace period?
did you specify the account to be locked?

what would you like to have happen?

A reader, January 09, 2006 - 9:55 am UTC

the java app is somehow notified by Oracle that the accont needs to have the password changed (a specific error is thrown?).

The application redirects to the change password page, gets the old and new password and then supplies this to Oracle. Assuming the new password is ok, processing continues. If not, a specific error is returned to the user to try again(eg password not long enough, not enough upper case chars, etc).

I'm hoping there is some 'API' I can work with to do these things. Is there?

R

Tom Kyte
January 09, 2006 - 10:23 am UTC

see support Note 124970.1

A reader, January 09, 2006 - 10:46 am UTC

that is EXACTLY the sort of information I need!!!

BUT:

1. Its hidden away in metalink and i have to go through the dbas for access.
2. Its an old document (8i) and only works for the OCI driver.

For all i know it may work for the newest thin driver. Where is the official docs on this? This sort of info is what the Java ppl need!!!

R

Tom Kyte
January 09, 2006 - 11:00 am UTC

It is OCI only. The note is still current.

A reader, January 09, 2006 - 11:16 am UTC

I did a bit of googling and found this:

</code> http://www.idevelopment.info/data/Programming/java/jdbc/PasswordManagement.java <code>

one of his comments seem to say that it is supported in 9i thin driver.

Still, the question remains, where is this functionality documented for all to see?

Tom Kyte
January 09, 2006 - 11:28 am UTC

That note is the sum of the documentation that I am aware of.

The note said 8i-9i and from what I read elsewhere, it was still thick in 10g - but I did not try it myself.

One DB User - Multiple end users

Raman Bansal, February 21, 2006 - 8:43 pm UTC

Hi Tom

I am the DBA for a small ASP company. We have one database user QXPL which owns all the application objects (tables, procedures etc). This includes a table USERS which has all the end user info including the username and passwords. We have Weblogic and use a connection pool with this DB user QXPL. My problem is that when I see in V$SESSION, I only see the QXPL as the USERNAME for all the sessions. I need a way to map each DB session with an end user, but don't have the luxury of using an LDAP solution for now. Do we have to create a DB user for each end-user, or is there a workaround for this? I will applreciate your input on how to achieve this without LDAP.

Thanks,
Raman.

Tom Kyte
February 22, 2006 - 8:30 am UTC

ldap would not anything automagical for you here either.

The application sort of has to do this for you - they could in many ways - the easiest just being to use dbms_application_info.set_client_info, or dbms_session.set_identifier

Or by using ntier proxy authentication

Or - whaterver.

but you see, if all the application does is log into the database using some user/pass - well, that is just about the sum of the knownledge the database has about the real user.

Proxy Authentication with Active Directory based LDAP user

Sami, March 28, 2006 - 11:50 am UTC

Could I use Proxy Authentication for users which are stored in Active Directory or any other Directory (not stored in Oracle Internet Directory)?

Thanks



Tom Kyte
March 28, 2006 - 8:03 pm UTC

right now, you'd have to replicate them into an ldap repository for Oracle to "see" them.

HTMLDB and proxy users

A reader, April 06, 2006 - 3:00 pm UTC

Tom, after some research on HTMLDB forum I found that proxy users are not supported by HTMLDB (at least - this is my understanding, quite possible I'm wrong). Is it true? If so - why? It would be so nice to have. Or may be there is some way to mimic this behaviour? Please advice...

Tom Kyte
April 07, 2006 - 8:27 pm UTC

html (apex) is written using mod_plsql.

it is mod_plsql that doesn't support proxy users. It cannot really. To use proxy users the following takes place:

a) client identifies itself to middle tier strongly. middle tier is mod_plsq. how does mod_plsql know "who" you are - it is not a specific application with some sort of "authorized user list"

b) middle tier authenticates user strongly, understands who user is. This is where mod_plsql says "huh?"

c) middle tier application - which is logged in "as itself" says "Hey, this guy tom authenticated with me, I trust him now, lets start a session in his name"


So, it'll be a hard thing to have happen.

mod_plsql and proxy users

Mark Wooldridge, April 07, 2006 - 9:12 pm UTC

I have also been interested in mod_plsql and thus HTMLDB supporting proxy authentication. I have logged and enhancement request and a bug was logged to get mod_plsql to support proxy users.

Currently the ER has went from SCL to HCL. I am guessing these mean software compatibility list and hardware compatibility list. Not sure.

As for what to do know. I have a way, it is not supported but does work. Oracle Portal supports a form of proxy authentication when mod_plsql is set to SingleSignOn authentication. I have identified the 2 pl/sql procedures mod_plsql utilizes when calling the proxy authentication API. They are the wpg_session package and the wpg_session_private packages. The wpg_session package is not wrapped and clear. I made a few minor changes to this package and still need to review a procedur called execute_invalidations. The other was a bit more difficult since the package body was wrapped. I was able to utilize the comments and define an implementation that works.

We were able to utilize proxy users either through Oracle SSO or simple certificates with Apache.

If you want more information email me.

NOTE: THIS IS CURRETLY NOT SUPPORTED IN ANY WAY BY ORACLE. THIS IS MY IMPLEMENATION OF A COUPLE OF ORACLE PACKAGES TO GET MOD_PLSQL TO DO WHAT I WANTED. AT THIS POINT IT WORKS AND SEEMS TO NOT HAVE ANY BAD SIDE EFFECTS.

Proxy authentication in SQL

Franky Wong, August 30, 2006 - 12:48 pm UTC

Thanks for the information on proxy authentication. They are quite helpful! I also read David Knox's book and I find it very useful.

Q1)

However, the examples provided (in here and in David's book) to preform proxy authentication are in java. Is it possible to do so in SQL?

For example, if I am in a sqlplus session logged in as user "midtier", then is there a SQL or PLSQL package that allows me to proxy to user "scott"?

I know I could do that in Java using OCI Connection Pool, but I would like to know if it is possible and how to do that in shell / SQL script level.

Q2)

In our database environment, we have many developer users who want to access a few production/master user accounts. We do not want them to know the passwords of the master user accounts. One thought I have is to do this:

alter user production_user grant connect through developer_user1;
alter user production_user grant connect through developer_user2;
...
alter user production_user grant connect through developer_user100;

Do you know if this is supported? Can an user have more than one "grant connect thruogh"s, or does Oracle only record the last one I entered (developer_user100)?


Thank you!

Tom Kyte
August 30, 2006 - 5:52 pm UTC

q1) in order to do sql you need a "client language that connects to the database"

to say "how do I do authentication in SQL" is a chicken and egg thing - if you are in SQL, you've already authenticate..

the client that connects to the database (VB, C, C++, Java, whatever) it does that.

q2) that is a really bad idea, you not only DON'T WANT THEM to have the passwords, you do NOT want them to have the access (to what end is hiding the password if you given the full access anyway???!?!?!?!)

Just create a role that has what access they should have and grant it to them, they are logging in as themselves, give them the very limited privileges they should have (eg: they should not be able to MODIFY data in all probability)

Proxy authentication in SQL

Franky Wong, August 30, 2006 - 11:28 pm UTC

Thank you for your prompt responses to my questions!

Q1)

I found out 10g has an enhancement in proxy authentication, where I could do this in SQL*Plus:

connect app_user[user1]/password_for_app_user

The above 10g syntax basically proxy-authenticate app_user as user1. Very cool.

Can this be done in 9i?

Q2)

Your reply makes sense. However, consider these:

If 100 developers have the password to the master schema, then I will have to let all of them know whenver it get changed. And if one of the developers left to another team, I will have to change the password and communicate the new one to all other developers. There is lots of maintenance.

The master schema represent production objects/jobs/packages. Well, for the most part (e.g. schema objects like tables...), I grant privileges to roles, which I in turn assign to developers.

However, we want all production jobs to be ran under the master schema. When the developer needs to create a job for production, he or she will need to submit it (e.g. DBMS_JOB.SUBMIT) under the master schema. I don't think Oracle has object privilege for job submission yet. This means the developers need to connect / login as the master schema user to submit the job. Developers come and go, and if the jobs are submitted in their schema, first we won't know which ones are tests / production, and second when they go and their accounts get locked, then we will have to move some of their jobs to the master schema....

The above illustrate it is not a good idea nor feasible for developer to "share" the passwords of production master schema, and yet there are things like job submission that we could not accomplish using roles/permission. As a result, I think the use of proxy authentication may be the answer to certain specific usage scenarios.

Do I miss anything?

Thanks!!

Tom Kyte
August 31, 2006 - 9:02 am UTC

q1) sqlplus needs to support it, you need the client supporting it. you therefore need 10g SQLPLUS.


q2) developers should NOT HAVE THIS SORT OF ACCESS PERIOD. just create them a role, do NOT give then connect through. That is the last I'll have to say on it, it just does not make sense to give them every privilege under the sun. They should not be doing things in production - period, end of story.

Proxy Users for Oracle Label Security (OLS)

A reader, March 08, 2007 - 4:24 pm UTC

Hi Tom,

My current application uses the "One Big Application User" model (see http://download-west.oracle.com/docs/cd/B14117_01/network.101/b10773/apdvntro.htm#1006171 ) to perform CRUD operations. Specifically, the middle tier of the application connects to the Oracle database using a generic application account (which is a valid database user). However, the application users are *not* actual database users (that is, the application users *do not* have corresponding database accounts). The generic application account (or the "one big application user") performs database CRUD on behalf of the application users.

In this scenario, is it possible to use Oracle Label Security (OLS) in the database to restrict access based on the currently authenticated application user (keep in mind that the application users do not actually have database accounts)?

From my initial testing, it seems that OLS only works if the application users are *also* database users (because of the need to execute "ALTER USER ... GRANT CONNECT THROUGH..." clause). If my application users are not actual database users, then logically I cannot alter the users and grant the "connect through", even if I use a proxy connection in my Java code using the OracleConnection and OracleOCIConnectionPool classes. Is this correct?

Is there a way to somehow use OLS for application users that are not real database users?

Thanks in advance :)

Tom Kyte
March 08, 2007 - 8:40 pm UTC

if the "one big account" sets up appropriate "environments" - sure. it would be responsible for setting the access level.

your one big account, after doing the authentication, would set the right access level - upgrading or downgrading the access. this one big account would have access to everything - and would just limit what can be seen.

Hiding Passwords

Tracy, April 19, 2007 - 10:24 am UTC

We have an OLTP application. For many parts of the app there is a single user which has the privileges to modify the data. This user connects via connection pools and there may be be many 100s of connections at any one time. The username and password are stored in conf files which are read when the resins are restarted. Trouble is that we do not want the admin staff who restart the resins to be able to read the password. We have had occasions where this username/password combination has been used by people in sqlplus, TOAD etc to run adhoc sql, which we do not allow. The user should only be allowed to connect via the OLTP application. What would you recommend as the best way to 'protect' this password? We wish to keep the connection pooling and do not wish to do any application rewrite to overcome this issue.
Tom Kyte
April 19, 2007 - 12:38 pm UTC

your application should encrypt it perhaps before storing it.

then you have to figure out how to protect the encryption key....

You can have the DBA's configure the listener to only accept inbound connections from the application server tier as well - that should prevent toad and such.

another approach would be secure application roles - grant the application account its access via a secure application role - NOT DIRECTLY. Then, make it so that this role can only be enabled via a stored procedure call. This stored procedure call will

a) use sys_context to verify the IP address of the client is correct
b) verify some magic piece of input you pass to it from your application is correct
c) check v$session to verify the program column looks OK

b) and c) are not foolproof (we can figure out what you send if we are persistent, we can spoof the program column) but just make it that much harder.


Proxy user??

Pawan Shrestha, August 27, 2007 - 6:01 pm UTC

Can you please guide me if following is possible:

I have two users "repoDB" and "writerDB". Can I login as "writerDB" to execute following:

CREATE TABLE repoDB.TBL_CLIENTS ( CLIENTID VARCHAR2(20), CLIENTNAME VARCHAR2(100));

I dont want to grant "CREATE ANY TABLE" to "writerDB".
Tom Kyte
September 04, 2007 - 12:29 pm UTC

it is possible to do that, but only if you have the create any table privilege.

if someone allowed writerdb to "become" repodb - without having to know repodb's password - you could achieve your goal:

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:83012348058#408884400346292168

writerdb would become repodb for a while to do the operation

proxy user connect with sqlplus - 9208

A reader, September 15, 2007 - 11:06 pm UTC

Hi Tom,
Is there a way to connect through proxy user using Sqlplus in 9208.

alter user scott grant connect through proxy_user.
When I use syntax
proxy_user[scott]/proxy_user_passwd@connect_string

It says invalid username password.

What other options I have with 9208 for proxy user.
I want to create a user which will be granted proxy acces to certain set of schemas.
Problem is how does he connect to DB from sqlplus using Proxy.
Surprisingly This connection works with 9i toad.
But we cant make users use TOAD when we are using standard tool like sqlplus.

DB is 9208. Sqlplus verions is 9208.


Tom Kyte
September 16, 2007 - 11:28 am UTC

You would need the 10g sqlplus client, the 9i one didn't have that feature yet
http://docs.oracle.com/docs/cd/B10501_01/server.920/a90842/ch13.htm#1008724
is the connect syntax for 9i sqlplus and
http://docs.oracle.com/docs/cd/B19306_01/server.102/b14357/ch12015.htm#i2697450

has the now 3 year old 10g method.

So can I use 10g sqlplus for 9i db proxy user

A reader, September 17, 2007 - 9:43 am UTC

Thanx for your response above.

So can I create proxy user in 9i DB and acces it through

10g sqlplus client.

I tried doing this.

What happens is the syntax works but it connects to the
proxy_user itself not the original user meant to be connected thru proxy.

So proxy_user[scott]/proxy_user_passwd@connect_string

connects to user : proxy_user instead of Scott.

Any suggestions ?

Tom Kyte
September 18, 2007 - 2:52 pm UTC

rats, I should have tried it before presuming it would work :(

contact support and reference bug5601665

Thanx for the BUG no

A reader, September 18, 2007 - 3:48 pm UTC


JPA/EJB3 and the Proxy user

Drikus, June 04, 2008 - 9:17 am UTC

Hi Tom,
First of, great thread thanks.
Is there a way to get the entity manager in the jpa framework to use a proxy user/connection on the database? I would assume that this needs to be done via the JPA implementation, in our case Toplink.
Also as part of the same question, how do you ensure that the persistence framework then returns the connected user back to the original user that the pool was set up with, and not the proxied user before returning the connection to the pool?
Thanks
Drikus
Tom Kyte
June 04, 2008 - 10:23 am UTC

hah, no clue what "JPA" is.

great, another new framework - this must be what, the 103,234th new framework...
http://java.sun.com/javaee/overview/faq/persistence.jsp

it would a question for you to ask of your vendor that supplied you with the JPA framework code.

Proxy User and SQLPlus connection

Emad Kehail, May 19, 2009 - 7:02 am UTC

Hello Tom,

We are currently using Oracle 11g 11.1.0.7.0 and we are about to implement a proxy user for a new web system used to track graduate students. However, I am testing the new SQLPlus feature used to connect as proxy user as follows:

1. I consider user SCOTT as the midtier user.
2. The APP_USER is the end user who will connect through the proxy user.


SQL> create user app_user identified by mypassword;

User created.

SQL> grant create session to app_user;

Grant succeeded.

SQL> alter user app_user grant connect through scott;

User altered.

Then I did the following to connect through the proxy user

SQL> conn scott[app_user]/tiger
Connected.
SQL> select user from dual;

USER                                                                            
------------------------------                                                  
APP_USER                                                                        

SQL> select sys_context('userenv', 'session_user') from dual;

SYS_CONTEXT('USERENV','SESSION_USER')                                           
--------------------------------------------------------------------------------
APP_USER                                                                        

SQL> select sys_context('userenv', 'proxy_user') from dual;

SYS_CONTEXT('USERENV','PROXY_USER')                                             
--------------------------------------------------------------------------------
SCOTT                                                                           

SQL> disc
Disconnected from Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

So far so good. Now I need app_user to enter his password, I did the following:

SQL> alter user app_user revoke connect through scott;

User altered.

SQL> alter user app_user grant connect through scott authentication required;

User altered.

SQL> conn scott[app_user]/tiger
ERROR:
ORA-28150: proxy not authorized to connect as client 

Am I missing something???

Note: I did not used AUTHENTICATED USING PASSWORD since Oracle Documentation says it is depricated.
http://download.oracle.com/docs/cd/B28359_01/server.111/b28286/statements_4003.htm#i2058312

I do appreciate your help

Emad

Tom Kyte
May 23, 2009 - 11:59 am UTC

remember, sqlplus is just a simple command line environment, you will almost surely have to write code to test your connections out in your environment - just because sqlplus doesn't support something doesn't mean a thing. It is just a simple reporting tool


sqlplus doesn't have syntax to accept both passwords.

Great, but in Java proxy user is slow...

Emad Kehail, May 23, 2009 - 12:29 pm UTC

Thanks for the quick feedback. We are currently moving our systems to the web. We are using J2EE as a platform for development.

Anyhow, the developers are complaining the Proxy user authentication is slow when they are using it.

I have send them this thread to follow your instructions. They sent me the following:

<quote>
I implemented a proxy based OCI-JDBC-Connection Pool and the instantiation just went fine, but when trying to connect through the proxy user using ( getProxyConnection() ) it takes a very long time (7 - 13 seconds) every time I try to establish a connection!!
The way I establish a pool is the same as follows:

sql> create user student_fund identified by student_fund;
sql> grant connect, resource to student_fund;
sql> create user a1 identified by TEST;
sql> grant create session to a1 ;
sql> alter user a1 grant connect through student_fund;
sql> create user a2 identified by TEST;
sql> grant create session to a2 ;
sql> alter user a1 grant connect through student_fund;

The Java code as follows:
======================================================================================
static OracleOCIConnectionPool ods;

/**
* @param args the command line arguments
*/
public static void main(String[] args) throws Exception {
// TODO code application logic here

if (args.length < 8) {
System.out.println("Usage: java -jar OraclePooling {HOST} {SERVICE_NAME} {PROXY_USERNAME} {PROXY_PASSWORD} {USERNAME_1} {PASSWORD_1} {USERNAME_2} {PASSWORD_2}");
System.exit(0);
}

ods = new OracleOCIConnectionPool();

String tnsAlias = "(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = " +
" (PROTOCOL = TCP)(HOST = " + args[0] + ")(PORT = 1521)) )" +
" (CONNECT_DATA = (SERVICE_NAME = " + args[1] + ") ) )";

System.out.println(tnsAlias);

for (int i = 0; i < args.length; i++) {
System.out.println("arg " + i + " : " + args);
}

ods.setURL("jdbc:oracle:oci:@" + tnsAlias);
ods.setUser(args[2]);
ods.setPassword(args[3]);

Properties prop = new Properties();

prop.setProperty(OracleOCIConnectionPool.CONNPOOL_MAX_LIMIT, "100");
prop.setProperty(OracleOCIConnectionPool.CONNPOOL_MIN_LIMIT, "10");
prop.setProperty(OracleOCIConnectionPool.CONNPOOL_TIMEOUT, "60000");
prop.setProperty(OracleOCIConnectionPool.CONNPOOL_INCREMENT, "10");
prop.setProperty(OracleOCIConnectionPool.CONNPOOL_NOWAIT, "true");

ods.setPoolConfig(prop);

doSomething(args[4], args[5]);

doSomething(args[6], args[7]);

}

public static void doSomething(String username, String password) throws Exception {
long ts = System.currentTimeMillis();

OracleConnection conn = getPooledConnection(username, password);

long tf = System.currentTimeMillis();

ResultSet rs = conn.createStatement().executeQuery("SELECT user FROM DUAL");

rs.next();

System.out.println(rs.getString(1));

//conn.close(OracleConnection.PROXY_SESSION);

System.out.println(conn);

System.out.println("Time taken " + (tf - ts) + " Milliseconds");
}

public static OracleConnection getPooledConnection(String username, String password) throws Exception {
Properties userNameProp = new Properties();
userNameProp.setProperty(OracleOCIConnectionPool.PROXY_USER_NAME,
username);
userNameProp.setProperty(OracleOCIConnectionPool.PROXY_PASSWORD,
password);
return ods.getProxyConnection(OracleOCIConnectionPool.PROXYTYPE_USER_NAME,
userNameProp);
}
<end of quote>

I am working with them to know where is the problem and why the connection is taking so much time. By the way, if we just did a connection pooling without a proxy authentication then it is so fast...

Is there anything we have missed???

Thanks

Proxy Authentication

Bhushan, October 03, 2009 - 4:19 am UTC

Dear Thomas,
Good to know about Proxy users and their advantages with examples on your site.
I tried it myself and it worked perfectly.
Here is one question that i have,
I connect to DB's (verion from 8i to 10g) using PL/SQL developer the catch was to use the 10g OCI.DLL for a succesfull connection.
Now can i connect to a DB through a proxy user using Oracle forms 4.5
As you say Forms may not be your field of expertise but any guidance or urls's in this regard will be of great help.
Thank You for the great work going on.
Cheers!!!
Bhushan


Tom Kyte
October 07, 2009 - 3:58 pm UTC

please utilize otn.oracle.com -> forums, there is one for developer

"Proxy User"

mfz, February 17, 2010 - 2:42 pm UTC

I am trying to understand the concept of "Proxy User" .
When I create a proxy user on behalf on regular user .. my understanding is that proxy user inherits the rights of the actual user .

What is the significance of Proxy User ( over creating another regular user) , if I have to explicilty grant the privileges to the proxy user to access it .

Please advise , where ( aka Oracle Manuals ) to find more information about proxy users?


SQL> Select username  from dba_users where username like 'SC%';

USERNAME
------------------------------
SCOTT

SQL> grant connect , create session to prx_user  identified by prx$123;

Grant succeeded.

SQL> alter user prx_user grant connect through scott;

User altered.

SQL> conn prx_user/prx$123
Connected.
SQL> select * from emp;
select * from emp
              *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL> select * from scott.emp;
select * from scott.emp
                    *
ERROR at line 1:
ORA-00942: table or view does not exist



SQL> conn scott/tiger
Connected.
SQL> select empno , ename from emp;

     EMPNO ENAME
---------- ----------
      7369 SMITH
      7499 ALLEN
      7521 WARD
      7566 JONES
      7654 MARTIN
      7698 BLAKE
      7782 CLARK
      7788 SCOTT
      7839 KING
      7844 TURNER
      7876 ADAMS

     EMPNO ENAME
---------- ----------
      7900 JAMES
      7902 FORD
      7934 MILLER

14 rows selected.


SQL> grant select on emp to prx_user;

Grant succeeded.

SQL> conn prx_user/prx$123
Connected.
SQL> select * from emp;
select * from emp
              *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL> select empno ,ename  from scott.emp;

     EMPNO ENAME
---------- ----------
      7369 SMITH
      7499 ALLEN
      7521 WARD
      7566 JONES
      7654 MARTIN
      7698 BLAKE
      7782 CLARK
      7788 SCOTT
      7839 KING
      7844 TURNER
      7876 ADAMS

     EMPNO ENAME
---------- ----------
      7900 JAMES
      7902 FORD
      7934 MILLER

14 rows selected.

Tom Kyte
February 18, 2010 - 8:27 am UTC

proxy users allow you to

a) establish a CONNECTION (physical circuit) to the database. The physical connection takes a long time (involves lots of networking and typically the starting of a dedicated server). We want to do this ONCE.

b) establish a session on that connection as some user YOU know the credentials for. Eg: A generic application account - say APP/PW - that just has create session and has been granted "connect thru" on some set of users - user1, user2, ... userN.

c) identify users in your application (which is connected as APP to the database right now) by whatever means you like - you verify that the guy running the browser is user123 - in whatever manner YOU WANT. You just do it right, you do it securely, you do it correctly.

d) "become" that user in the database - create a new session on your existing connection (without created a new connection which is slow) as that user - user123. You are allowed to do this simply because you have proven to the database you know the credentials for the APP account and the APP account was granted connect through to this user.



In short, it lets you do the IDENTIFICATION of the user (user123) whilst allowing the database to perform AUTHORIZATION of that schema (what it can and cannot do), to perform AUDITING of that schema (actions will be audited as being done by APP on behalf of USER123 - you'll know both APP and USER123)

In short, it lets you use that one single account which people oh so like, it lets you connection pool and reuse the connections which is sort of necessary and it lets you use database grants (very very secure, much more secure than relying on your developers to do it - and if someone breaks into your database bypassing your application - they are still under control of grants, not wide open), and lets you see in the database who is doing what (auditing, fine grained access control and so on)

mfz, February 17, 2010 - 8:20 pm UTC

My bad .. 

I had this SQL reversed....

SQL> alter user prx_user grant connect through scott;

User altered.


It should have been 

 alter user scott grant connect through prx_user.


After modifying the script as above , I was able to access scott's data  .

1)However the question is ... by going via "proxy user" , the benefit is the app user does not need to know scott's password .... Is there any other benefit for  this approach?

2) David Knox's book "Effective ... security" is about 9i and 10g . Is "Applied Oracle Security " a upgrade of "Effective Oracle Database 10g Security by Design" ? ...
I am on 11g 

Tom Kyte
February 18, 2010 - 8:53 am UTC

see above. for proxy users.

David Knox says....


This book is purposefully designed as a follow-up to Effective Oracle Database 10g Security By Design, published by McGraw-Hill/Osborne in 2004. In that book, author David Knox, who serves as lead author for this book, takes you from the basics of database security practices and designs to some advanced examples. Included are all the technologies available for the Oracle Database 10g Release 1, including secure application roles, virtual private database, Oracle label security, enterprise user security, proxy authentication, auditing, and database encryption using DBMS_CRYPTO. If you are unfamiliar with those technologies or need a better understanding of them, you should read the first book before you read this one.

While an update to that text that added the new options—transparent data encryption, database vault, and audit vault—was certainly a possibility, we decided to create a new book for two main reasons: First, identity and access management advances were needed to complete any significant and complete description of security. Second, the goal of this book is to show how you can apply those base database technologies to secure database applications such as the business intelligence and Application Express (APEX) applications. Adding the new options, the identity management sections and the application examples would have made the book too large for practical purposes.

In summary, the first book explains the basics of Oracle Database 10g R1 to help the reader understand what the available security technologies can do, and then it shows to get them working. This book adds information about new technologies and applies all the security technologies to the task of building secure database applications.

if proxy authentication fails continue without proxy

Marcus, August 27, 2010 - 10:03 am UTC

I believe this question is an extension of the discussion that has taken place in this thread.

I am working to implement OLS in a system that has up to this point used a single database user, enduser. I was asked to implement the OLS via a proxy user over a thin client. I have created 3 additional users to test a demonstration. I have been successful up to a point.

Select count(*) from myTable;
enduser gets 34 records. -- Can not see any special data
user1 gets 38 records. -- Can see CAT1 data
user2 gets 44 records. -- Can see CAT2 data
user3 gets 48 records. -- Can see CAT1 and CAT2 data

If I issue:
conn.openProxySession(OracleConnection.PROXYTYPE_USER_NAME, prop);

if the user name matches the one of the new users, everything works fine. If it does not, then I get the expected error for failed authentication.

Now, can you suggest a direction for me to accomplish the following:
If the openProxySession fails, then connect without a proxy as the enduser and ignore the proxy attempt?

I have tried to close the connection and get a new one from the pool, but when the page comes back there is no data. That suggests that there is some "phantom" thing going on when the user runs a query on the OLS tables. On a non-OLS table I get the expected data returned. So this is possibly a proxy session interaction with OLS issue.
Tom Kyte
September 07, 2010 - 7:51 am UTC

there is nothing 'phantom' going on, you have a bug in your logic whereby a context is not getting set or something.

A connection is a connection to us - nothing more, nothing less. Look to your logic (debug stuff - write trace files - do something so you can see what you are doing)

Is LDAP+Email server better or DB better?

Dakshinamurty Y., September 21, 2010 - 1:06 am UTC

We have a requirement to enhance storing and retrieving different target lists, use templates for snding and receiving mails etc.
Currently we are storing it in DB with target list id and account id in a row. So, in a target list the number of rows will be as many as the number of accounts. And templates are stored target list wise. Previuosly retail users are only using it and enhancement is to apply this functionality to corporate users also.
I am feeling that this is performance wise very bad for storing and retrieving also. I suspected performance problems even with retail users and asked the team about usage of application. I told that we have to maintain (millions (number of accounts)* number of target lists * average number of accounts/target list) rows in the table and for sending mails also we have to replace place holders from the DB and send mail.
I am not sure of the alternative and felt LDAP + Email server functionality can be used as performance wise better alternative.
What do you say? Are there any other better solutons?

Tom Kyte
September 21, 2010 - 3:45 pm UTC

guess what ldap is... a database (and one that we prefer to store in a relational database - in rows and columns :) )


you sort of need an email server (your smtp host) regardless - so this is a question of "should we use LDAP or maintain our data in our own format"

to which I would respond - if you are using this data only for your own email lists, it wouldn't make sense to me to convert to ldap. I would use ldap only if I needed to use this repository for other things like single sign on.

java and connection pooling... how to move away from EJB

Stuart, December 07, 2010 - 8:51 pm UTC

Hi Tom,

I recently downloaded the demo.zip JDBC sample programs from OTN JDBC page and started looking at the connection pool java examples (similar to above code from previous threads).

I understand the JDBC client software and the thick JDBC driver is required for connection pooling, but is there a requirement to have Oracle middleware (OAS or WebLogic) installed?

We have an application that uses JBoss with EJB's (thin client). I hate the way it connection pools using the same single account, and would like it changed, but if I was to take some of this sample code and modify/compile it, where would I put the binary so it integrates/talks to the middleware layer? (if that was possible)

Being a DBA and not a developer, can you comment on what might be required to convert an EJB application to authenticate using the java code samples? Are we looking at a complete rewrite?
Tom Kyte
December 08, 2010 - 9:31 am UTC

... but is there a requirement to have Oracle middleware (OAS
or WebLogic) installed? ...

java should be able to run under most any application server.



Changing the way it authenticates - easy, minor code modification.

Making it work with more than the single user - that is likely going to be really hard. The people that coded the original bit of logic are relying on a single user, it will rock their world when you change that - they'll have a bit of reworking to do.

school computers

coco, May 04, 2011 - 11:45 am UTC

Hi Tom,

IIIII want to know why the school computers are all block and why are they so i want you to tell me how to get into the school computers to get youtube and facebook to work on my school computer lol get back to me soon!!!

Behavior of proxied connection

Hazel, March 01, 2012 - 2:55 pm UTC

Great thread but I have a variation to this scenario that doesn't make sense to me. Environment is 11.2.0.3 windows & sparc. We have application users proxying in and all is good. Our users are Kerberos authenticated and here is where we are confused. Another user can proxy in as me and assume my identity. Why isn't the sqlnet connection verifying the username against who's logged in? Is this to be expected?

create user "JOHN.SMITH" identified externally as 'john.smith@DOMAIN.COM'; -- active directory user

create user b identified by pwd; -- proxy user

create user "JANE.DOE" identified externally as 'jane.doe@DOMAIN.COM'; -- active directory user

alter user "JOHN.SMITH" grant connect through b;

So why when Jane Doe logs in, does
'connect b[john.smith]/pwd@oracle_sid' work without verifying the [] user is the real user logged in? Is this a feature or a "feature"? It creates all audit against john.smith and not jane.doe.


Tom Kyte
March 01, 2012 - 3:21 pm UTC

when you identified yourself as "b/pwd" - and given that b has the ability to connect as john.smith - "jane.doe" has nothing to do with it anymore.

user B, once authenticated, is allowed to become john.smith - you set it up that way.

A reader, April 23, 2012 - 3:52 pm UTC

Hi Tom,

We are currently using Oracle 10g 10.1.0.5 (I know, this version is too old, but we can´t upgrade this now)
and we are about to implement a proxy user for a forms system. We are migrating from Forms 6i to 11.1.2.

I am testing in the SQL*Plus as follows:

1. I consider user PROXY_USER as the midtier user.

2. The USER0001 is the end user who will connect through the proxy user.


SQL> create user PROXY_USER identified by mypassword;

User created.

SQL> grant create session to PROXY_USER;

Grant succeeded.

SQL> alter user USER0001 grant connect through PROXY_USER;

User altered.

Then I did the following to connect through the proxy user

SQL> conn PROXY_USER[USER0001]/mypassword

Connected.
SQL> select user from dual;

USER                                                                            
------------------------------                                                  
PROXY_USER                                                                        

SQL> select sys_context('userenv', 'session_user') from dual;

SYS_CONTEXT('USERENV','SESSION_USER')                                           
--------------------------------------------------------------------------------
PROXY_USER                                                                       

SQL> select sys_context('userenv', 'proxy_user') from dual;

SYS_CONTEXT('USERENV','PROXY_USER')                                             
--------------------------------------------------------------------------------
NULL

What´s wrong ?? When I do this same test in a Oracle Express (11g), it´s all OK, with the following results:


SQL> select user from dual;

USER                                                                            
------------------------------                                                  
USER0001                                                                        

SQL> select sys_context('userenv', 'session_user') from dual;

SYS_CONTEXT('USERENV','SESSION_USER')                                           
--------------------------------------------------------------------------------
USER0001                                                                       

SQL> select sys_context('userenv', 'proxy_user') from dual;

SYS_CONTEXT('USERENV','PROXY_USER')                                             
--------------------------------------------------------------------------------
PROXY_USER


Thanks in Advance.

Tom Kyte
April 24, 2012 - 7:32 am UTC

what version of sqlplus are you using in your tests.

A reader, April 24, 2012 - 8:56 am UTC

Hi,

I am using:

SQL*Plus: Release 10.2.0.1.0 - Production

Thanks
Tom Kyte
April 24, 2012 - 9:05 am UTC

Sorry, I don't have a 10.1 instance around - I was guessing it might have been sqlplus and parsing of the connect string being done differently but if you used 10.2 against both - it wouldn't have been that.

I can only suggest contacting support to see if there was a known issue/workaround in 10.1

Try it on the database server

Dhruva, April 25, 2012 - 12:40 pm UTC

Try it on the database server, it should work.

Number of failed login attempts

Arky, March 01, 2013 - 5:58 am UTC

Tom,
My users are able to connect to the database using "proxy user" but I have on question:
If they try to connect using a wrong password, then the failed attempt does not seem to count on the "Failed login attempts" defined by their profile. That means they can try to connect as many times as they like without getting locked. Is there a way to handle this, apart from using an application table for holding the login attempts etc?

Thank you in advance
Tom Kyte
March 01, 2013 - 7:13 am UTC

I set my profile to have 3 failed login attempts.

I tested with scott/lion - 3 trys - account is locked. I unlocked the account and then:

ops$tkyte%ORA11GR2> connect scott[ops$tkyte]/tiger;
Connected.
ops$tkyte%ORA11GR2> connect scott[ops$tkyte]/lion;
ERROR:
ORA-01017: invalid username/password; logon denied


Warning: You are no longer connected to ORACLE.
ops$tkyte%ORA11GR2> connect scott[ops$tkyte]/lion;
ERROR:
ORA-01017: invalid username/password; logon denied


ops$tkyte%ORA11GR2> connect scott[ops$tkyte]/lion;
ERROR:
ORA-01017: invalid username/password; logon denied


ops$tkyte%ORA11GR2> connect scott[ops$tkyte]/lion;
ERROR:
ORA-28000: the account is locked




again, the account is locked. what are you seeing? give me a script to reproduce from start to finish - create users, setting up the profile, everything.

Number of failed login attempts

Arky, March 04, 2013 - 4:30 am UTC

Hi Tom

Maybe my question was not clear enough. I was referring to "Failed login attempts" of ops$tkyte at your example, not of proxy's (SCOTT). According to the Oracle Database Security Guide, the "AUTHENTICATED USING PASSWORD" (or "AUTHENTICATION REQUIRED" in newer versions) option ensures that authentication credentials for the user must be presented when the user is authenticated through the specified proxy. What if the user keeps entering invalid credentials?
I forgot to mention that my DB server is rather old - version 9.2.0.8.0

Thank you
Tom Kyte
March 04, 2013 - 4:37 pm UTC

scott is the only one trying to authenticate here, the authentication of ops$tkyte will never fail in this case.

the credentials for the user SCOTT are the only ones in question here.

why would ops$tkyte be affected by this at all ?

and how would this lead to "That means they can try to connect as many
times as they like without getting locked. "??????


Your user is SCOTT, they attempted three times, they struck out and are locked. they cannot become ops$tkyte.


I don't understand what you expect to have happen in this case?

How Oracle Apps Authentication works?

Tareq Alkhateeb, March 11, 2013 - 8:36 am UTC

Hi Tom,

thank you very much for this post, I have a question here.
How oracle ebs build thier identification and authentication?
does they use proxy user?
and as you know there are also fnd_user table that stores all application users, how oracle ebs authentication works, do you have any idea?
thanks

Dffcfc

osamaaljipore, October 23, 2015 - 5:16 pm UTC


A clarification

bijunator, July 28, 2017 - 10:11 am UTC

From a operational perspective where an operation guy want to execute a script in a schema to which he do not have access but he can certainly do "ALTER SESSION SET CURRENT_SCHEMA=...". Will using proxy user give him any advantage other than traceability. I mean alter session certainly looks quicker. Please forgive my naiveness.
Chris Saxon
July 28, 2017 - 10:52 am UTC

With a proxy user you get access to the tables, etc. of the user you're connecting through. But with current_schema the user you're connected as still needs permissions to see the objects:

select user from dual;

USER                          
------------------------------
CHRIS

create table t (
  x int 
);
insert into t values (1);
commit;

grant create session to u1 identified by u1;
create user u2 identified by u2;
alter user chris grant connect through u2;

conn u2[chris]/u2

select * from t;

         X
----------
         1

conn u1/u1

alter session set current_schema = chris;

select * from t;

SQL Error: ORA-00942: table or view does not exist
select * from chris.t;

SQL Error: ORA-00942: table or view does not exist

Password issue of Proxy User

Mark Evans, February 19, 2019 - 1:29 pm UTC

Hi Tim,
In a previous comment you stated:
"It does NOT use the password of the "real user", in fact it was designed to avoid having the real user tell it what its database password in fact is!"

However, we have uncovered a password issue for the "real user" whereby the proxy user can change the password of the "real user" to be whatever he wants - Without knowing what the original password for the real user was. This has resulted in the past of an application breaking because the proxy user changed the "real" user's password instead of his own by mistake.

The following example highlights this using the standard HR schema on Oracle XE - But the issue is the same on Oracle 11.2.0.4 EE.

conn sys/password as sysdba;
create user testuser identified by testpwd;
alter user hr grant connect through testuser;

connect testuser[hr]/testpwd
alter user hr identified by hr2;

-----
User HR altered.
-----

Even though proxy didn't know the "real" user's original password it was able to reset it for the "real" user and prevent the "real" user from logging in until he knows the password - or DBA resets it back again - More worrying it has allowed the proxy user to now log in again directly as the Real user since he is the only one aware of its new password !!


Connor McDonald
February 20, 2019 - 3:32 am UTC

This is true, but this is a reason that it's protected, ie, via:

alter user hr grant connect through testuser;

You are explicitly nominating the user and the proxy for which this relationship exists, and you don't want to be giving it out lightly!

You could also trap such attempts with a DDL trigger, eg


SQL> conn /@db18_pdb1 as sysdba
Connected.

SQL> create or replace
  2  trigger sys.check_alter
  3  before alter on database
  4  declare
  5    l_string   varchar2(4000);
  6    l_sql_text ora_name_list_t;
  7    l_n        number;
  8  begin
  9    l_n := ora_sql_txt(l_sql_text);
 10    for i in 1 .. l_n
 11    loop
 12      l_string := l_string || l_sql_text(i);
 13    end loop;
 14    if lower(l_string) like 'alter%user%identified%'
 15    then
 16      raise_application_error(-20000,'WTF are you doing??!?!?');
 17    end if;
 18  end;
 19  /

Trigger created.

SQL> create user testuser identified by testpwd;

User created.

SQL> alter user hr grant connect through testuser;

User altered.

SQL> connect testuser[hr]/testpwd@db18_pdb1
Connected.

SQL> alter user hr identified by hr2;
alter user hr identified by hr2
*
ERROR at line 1:
ORA-04088: error during execution of trigger 'SYS.CHECK_ALTER'
ORA-00604: error occurred at recursive SQL level 1
ORA-20000: WTF are you doing??!?!?
ORA-06512: at line 13


SQL>


and extend that to determine which ALTER USER commands you'll allow and which ones you will not.

Password issue of Proxy User

Mark Evans, February 20, 2019 - 8:19 am UTC

Many thanks for your reply.
I have previously read about this trigger before. However, this trigger disables ANY user from being able to change their password on the database - Even SYS gets the error when trying to change HR's password - It isn't limited to only preventing proxy users from changing the application password and therefore seems impractical.
I appreciate that the users could tell the DBA that they want to change their own password forcing the DBA to delete the trigger while that person changes their a password and re-create it afterwards but it seems a massive overkill as every user will need DBA assistance when they need to change their own password.
Alternatively I could trap the application user specifically e.g
if lower(l_string) like 'alter%user%hr%identified%'
I would then have to delete this trigger when SYS needs to change the application password and recreate it afterwards (or have a flag in a table to check first if the trigger is active)
But again this seems a bit of a hack around the issue that all I want is to grant a user privileges to interact with a specified application schema without being able to change the application schema password.
Such a shame that Oracle doesn't allow this privilege directly and avoid these password issues by e.g. GRANT CREATE TABLE ON HR TO TESTROLE etc.
Chris Saxon
February 20, 2019 - 2:32 pm UTC

You can check if the current user is a proxy using the PROXY_USER sys_context. And only fire the trigger if they are:

create or replace
trigger check_alter
before alter on database
declare
  l_string   varchar2(4000);
  l_sql_text ora_name_list_t;
  l_n        number;
begin
  l_n := ora_sql_txt(l_sql_text);
  for i in 1 .. l_n
  loop
    l_string := l_string || l_sql_text(i);
  end loop;
  if lower(l_string) like 'alter%user%identified%'
  and sys_context ( 'USERENV', 'PROXY_USER' ) is not null 
  then
    raise_application_error(-20000,'WTF are you doing??!?!?');
  end if;
end;
/

grant create session 
  to testuser identified by testpwd;
alter user hr grant connect through testuser;

alter user hr identified by hr;

connect testuser[hr]/testpwd
alter user hr identified by hr2;

ORA-04088: error during execution of trigger 'CHRIS.CHECK_ALTER'
ORA-00604: error occurred at recursive SQL level 1
ORA-20000: WTF are you doing??!?!?

conn hr/hr

alter user hr identified by hrnew;

conn hr/hrnew

select user from dual;

USER   
HR     

Rajeshwaran Jeyabal, April 05, 2022 - 2:36 am UTC

can you help us to understand why this error message on proxy only connect to a user account?

demo@XEPDB1> alter user scott proxy only connect;
alter user scott proxy only connect
*
ERROR at line 1:
ORA-28185: cannot alter user with administrative privilege to proxy-only connect user

demo@XEPDB1> conn scott/tiger@pdb1
Connected.
scott@XEPDB1> select * from session_roles;

no rows selected

scott@XEPDB1> select * from session_privs;

PRIVILEGE
----------------------------------------
CREATE SESSION
CREATE TABLE

scott@XEPDB1> conn scott/tiger@pdb1 as SYSDBA
ERROR:
ORA-01031: insufficient privileges


Warning: You are no longer connected to ORACLE.
scott@XEPDB1>

Connor McDonald
April 05, 2022 - 7:22 am UTC

anything in dba_role_privs?



Proxy only connect features

Rajeshwaran Jeyabal, April 05, 2022 - 7:55 am UTC

demo@XEPDB1> conn scott/tiger@pdb1
Connected.
scott@XEPDB1> select * from user_role_privs;

no rows selected

scott@XEPDB1>

Chris Saxon
April 05, 2022 - 8:21 am UTC

PROXY ONLY CONNECT is undocumented syntax.

If you want to stop people connecting as scott directly and only access it via proxy users, you should use this (added in 18c):

alter user scott no authentication;

More to Explore

Security

All of the vital components for a secure database are covered in the Security guide.