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,

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 

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 -- 

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.

 

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.

means two months ago, I bought it.:)

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:)

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.

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.

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?

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

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

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.

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

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?

thanks, good to know it.

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, 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?

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.

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.

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.

Awesome. Thanks David!

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?

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?

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

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 ?

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.

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,

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,

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,

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,

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.

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

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

“…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

<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?

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

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

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

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

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

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?

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.

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, 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...

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.

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!

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!!

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 :)

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.

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".

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.

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 ?

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

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

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

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

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.

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 

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.

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?

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?

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!!!

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.

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.

Hi,

I am using:

SQL*Plus: Release 10.2.0.1.0 - Production

Thanks

Try it on the database server, it should work.

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

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

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

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

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.

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

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 !!

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>

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.

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     

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>

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

no rows selected

scott@XEPDB1>

alter user scott no authentication;

hello all,

how do you enforce inactive time in a profile with the proxy client user? Last log in is never populated because they never log in.

select proxy,
client
from proxy_users;

PROXY_MID PROXY_CLIENT

select username, nvl(to_char(last_login,'mm-dd-yyyy HH24:mi:ss'),'NEVER LOGGED IN') as last_login from dba_users;
username last_login
PROXY_MID ------ 12-06-2022 14:03:08
PROXY_CLIENT -- - NEVER LOGGED IN

Is it ignort to think that a log in could be set based on viewing the audit trail?

SELECT username,
returncode,
action_name,
sessionid,
proxy_sessionid
FROM dba_audit_trail
WHERE sessionid IN ('4488501', '4488500')
ORDER BY sessionid;


username returncode action_name sessionid proxy_sessionid
PROXY_MID 0 PROXY AUTHENTICATION ONLY 4488500
PROXY_CLIENT 0 LOGOFF 4488501 4488500
PROXY_CLIENT 0 LOGON 4488501 4488500

Thanks in advanced
Dave

Hello,

Not sure what is the latest on proxy only connect option because I see Chris has mentioned that this is undocumented which is right as I can't see it in 19c documentation.
However, Bug 31547668 - Data Pump and DBMS_METADATA.GET_DDL Do Not Preserve Proxy-Only Connect Property for Schema (Doc ID 31547668.8) appears to suggest that it is a valid option.

SQL> create user proxy_test no authentication proxy only connect;
User created.
 
SQL> select username, authentication_type, proxy_only_connect 
from dba_users where oracle_maintained != 'Y' and common != 'YES' order by 1;
 
USERNAME     AUTHENTICATION_TYPE   PROXY_ONLY_CONNECT
PROXY_TEST   NONE                  Y