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
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
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.
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
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.
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.
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?
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
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
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.
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
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?
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.
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?
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.
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.
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.
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?
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?
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
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 ?
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
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
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
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
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.
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.
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
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
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?
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
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
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
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
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
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
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.
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
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...
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!
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!!
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 :)
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.
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".
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.
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 ?
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
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
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
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.
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
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.
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?
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?
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.
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.
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
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
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
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.
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 !!
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.
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>
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>
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;
enforcing inactive time with proxy "client"
dave, December 06, 2022 - 7:14 pm UTC
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
Re: Proxy only connect features
Narendra, January 31, 2024 - 10:12 am UTC
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