Home » Developer & Programmer » Forms » Connect using proxy user
Connect using proxy user [message #549311] Thu, 29 March 2012 08:19 Go to next message
mani_jagtap
Messages: 7
Registered: May 2010
Location: glasgow
Junior Member
Hi All,

I need some help in Oracle Forms.

We had a database NSSAPP4d with only one schema say A , but now requirement is we will have two schemas in NSSAPP4d with existing schema A and new B, now both these schemas have same tables except few.

In Schema 'A' all objects had Public Synonyms which needs to be removed and instead have Private synonyms in both Schemas A & B.

We will have a proxy user for which all Private synonyms for all objects will be created.

I have created a proxy user e.g. MJ_TEST_PROXY with necessary privileges.

I have created other user MJ_TEST with necessary privileges.

Now I am running below script
ALTER USER mj_test_proxy grant connect through mj_test with role <all roles of mj_test_proxy>

If I connect as mj_test/mj_test@NSSAPP4d then I get ORA-04067 Error.

When try to connect through fron-end(forms) I need to give mj_test[mj_test_proxy]/mj_test$NSSAPP4d

I want to avoid giving proxy name , but want to connect thorough proxy.

Could you please let me know how this can be achieved?

Thanks,
Manisha
Re: Connect using proxy user [message #549322 is a reply to message #549311] Thu, 29 March 2012 08:46 Go to previous messageGo to next message
Michel Cadot
Messages: 58861
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You cannot.
How Oracle will know the proxy if you don't give it?

Regards
Michel
Re: Connect using proxy user [message #549334 is a reply to message #549322] Thu, 29 March 2012 10:51 Go to previous messageGo to next message
mani_jagtap
Messages: 7
Registered: May 2010
Location: glasgow
Junior Member
I don't want user to explicitly give proxy each time when user logs in .
There are many existing user who will go through proxy after this change and we don't want to change the way users are used to log-in.
So I was suggesting if there is any way if proxy can be picked up automatically.

Hope I am clear.

Thanks,
Manisha
Re: Connect using proxy user [message #549337 is a reply to message #549334] Thu, 29 March 2012 11:04 Go to previous messageGo to next message
Michel Cadot
Messages: 58861
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Can you explain what you want to do with the proxy user?
It seems you do it the wrong way.

Regards
Michel
Re: Connect using proxy user [message #549341 is a reply to message #549337] Thu, 29 March 2012 12:18 Go to previous messageGo to next message
owais_baba
Messages: 289
Registered: March 2008
Location: MUSCAT
Senior Member

drop user neutral;
drop user one_gets_in;
drop user one_wants_in;
 
create user neutral identified by neutral;
grant create session to neutral;
 
create user one_gets_in identified by gets;
grant create session to one_gets_in;
 
 
create user one_wants_in identified by wants;
alter user one_wants_in grant connect through one_gets_in;
grant create session to one_wants_in;
 
 
 
set heading off
 
 
connect neutral/neutral@ora11b
select 'AUTHENTICATED_IDENTITY: '|| sys_context('USERENV','AUTHENTICATED_IDENTITY'),
       'CURRENT_SCHEMA........: '|| sys_context('USERENV','CURRENT_SCHEMA'),
       'CURRENT_SCHEMAID......: '|| sys_context('USERENV','CURRENT_SCHEMAID'),	
       'ENTERPRISE_IDENTITY...: '|| sys_context('USERENV','ENTERPRISE_IDENTITY'), 	
       'IDENTIFICATION_TYPE...: '|| sys_context('USERENV','IDENTIFICATION_TYPE'), 	
       'OS_USER...............: '|| sys_context('USERENV','OS_USER'), 		
       'PROXY_USER............: '|| sys_context('USERENV','PROXY_USER'), 		
       'PROXY_USERID..........: '|| sys_context('USERENV','PROXY_USERID'), 		
       'SESSION_USER..........: '|| sys_context('USERENV','SESSION_USER'), 		
       'SESSION_USERID........: '|| sys_context('USERENV','SESSION_USERID'), 		
       'SESSIONID.............: '|| sys_context('USERENV','SESSIONID'), 		
       'SID...................: '|| sys_context('USERENV','SID')
   from dual;
 
rem Standard connect for 'Gets'
connect one_gets_in/gets@ora11b
/ 
 
connect neutral/neutral@ora11b
 
rem standard connect for 'Wants'
connect one_wants_in/wants@ora11b
/ 
 
rem connect proxy[user]/proxy_password
rem these should not work:
rem 
rem User wants using wants password
connect one_wants_in[one_gets_in]/wants@ora11b
/ 
 
rem User wants using gets passworf
connect one_wants_in[one_gets_in]/gets@ora11b
/ 
 
rem User gets using gets password
connect one_gets_in[one_wants_in]/wants@ora11b
/ 
 
rem this should work
connect one_gets_in[one_wants_in]/gets@ora11b
/ 
 
 
rem and now switch the schema we a reusing
alter session set current_schema=neutral;
 
select 'AUTHENTICATED_IDENTITY: '|| sys_context('USERENV','AUTHENTICATED_IDENTITY'),
       'CURRENT_SCHEMA........: '|| sys_context('USERENV','CURRENT_SCHEMA'),
       'CURRENT_SCHEMAID......: '|| sys_context('USERENV','CURRENT_SCHEMAID'),	
       'ENTERPRISE_IDENTITY...: '|| sys_context('USERENV','ENTERPRISE_IDENTITY'), 	
       'IDENTIFICATION_TYPE...: '|| sys_context('USERENV','IDENTIFICATION_TYPE'), 	
       'OS_USER...............: '|| sys_context('USERENV','OS_USER'), 		
       'PROXY_USER............: '|| sys_context('USERENV','PROXY_USER'), 		
       'PROXY_USERID..........: '|| sys_context('USERENV','PROXY_USERID'), 		
       'SESSION_USER..........: '|| sys_context('USERENV','SESSION_USER'), 		
       'SESSION_USERID........: '|| sys_context('USERENV','SESSION_USERID'), 		
       'SESSIONID.............: '|| sys_context('USERENV','SESSIONID'), 		
       'SID...................: '|| sys_context('USERENV','SID')
   from dual;


Cool
Re: Connect using proxy user [message #549342 is a reply to message #549341] Thu, 29 March 2012 12:50 Go to previous messageGo to next message
owais_baba
Messages: 289
Registered: March 2008
Location: MUSCAT
Senior Member
Very Simple Example

Oracle Proxy Users by example


The following example illustrates the use of Oracle proxy users.

First we'll create two database users. One normal application user, pu_user_1, and a Big Application User, pu_pool_user. The big application user will be used by a web application to connect to the database. Usually the application user, in this case pu_user_1, will be authenticated by the web application.

create user pu_pool_user identified by pu_pool_user;
grant create session to pu_pool_user;
 
create user pu_user_1 identified by pu_user_1;
alter user pu_user_1 grant connect through pu_pool_user;
grant create session to pu_user_1;

By granting pu_user_1 the connect through privilege, you allow pu_pool_user to proxy for pu_user_1, i.e., 
pu_pool_user connects to the database, but it will be as if pu_user_1 is connected.

First a normal connection. The following example connects to oracle using the user pu_pool_user:

	
private void normalConnection() throws Exception {
    Properties properties = new Properties();
    Connection conn = DriverManager.getConnection(
            "jdbc:oracle:thin:pu_pool_user/pu_pool_user@localhost:1521:ora1012",
            properties);
    printUserInfo(conn);
    conn.close();
}

If you ask oracle who is connected, oracle will identify the connected user as pu_pool_user. 
This is displayed using the following code:
	
private void printUserInfo(Connection conn) throws Exception {
    System.out.println(
        "Is proxy session: " + ((OracleConnection) conn).isProxySession());
 
    PreparedStatement stmt = conn.prepareStatement(
            " select user, username "
             +", sys_context('USERENV','PROXY_USER') "
             +", sys_context('USERENV','CURRENT_USER') "
             +", sys_context('USERENV','SESSION_USER') "
             +" from sys.v_$session"
            );
    ResultSet rset = stmt.executeQuery();
 
    if (rset.next()) {
        System.out.println("user                  : " + rset.getString(1));
        System.out.println("username              : " + rset.getString(2));
        System.out.println("userenv proxy_user    : " + rset.getString(3));
        System.out.println("userenv current_user  : " + rset.getString(4));
        System.out.println("userenv session_user  : " + rset.getString(5));
    }
 
    stmt.close();
}

This method prints the following info for the normal connection:
	
Is proxy session: false
user                  : PU_POOL_USER
username              : null
userenv proxy_user    : null
userenv current_user  : PU_POOL_USER
userenv session_user  : PU_POOL_USER

The following code connects to the database as pu_pool_user, 
but then we tell the connection that we're actually proxying for pu_user_1.
	
private void proxyConnection() throws Exception {
    Properties properties = new Properties();
    properties.put("PROXY_USER_NAME", "pu_user_1");
 
    OracleConnection conn = (OracleConnection) DriverManager.getConnection(
            "jdbc:oracle:thin:pu_pool_user/pu_pool_user@localhost:1521:ora1012",
            properties);
    conn.openProxySession(OracleConnection.PROXYTYPE_USER_NAME, properties);
 
    printUserInfo(conn);
    conn.close();
}

The result of calling printUserInfo:
	
Is proxy session: true
user                  : PU_USER_1
username              : PU_USER_1
userenv proxy_user    : PU_POOL_USER
userenv current_user  : PU_USER_1
userenv session_user  : PU_USER_1

As you can see, USER now returns PU_USER_1, eventhough we connected to the database as pu_pool_user.

This is very convenient, you do not have to rewrite your security code and your auditing code.


hope u got everything nothing left behind

Cool


[Updated on: Fri, 30 March 2012 10:21] by Moderator

Report message to a moderator

Re: Connect using proxy user [message #549384 is a reply to message #549342] Fri, 30 March 2012 01:34 Go to previous messageGo to next message
Michel Cadot
Messages: 58861
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
"Your" first code is from Hans Forbrich at https://forums.oracle.com/forums/thread.jspa?threadID=679250
"Your" second example is from Andrej Koelewijn at http://www.andrejkoelewijn.com/wp/2005/09/12/oracle-proxy-users-by-example/

Instead of getting respect with valuable answers, stealing others' work you just get what you deserve to get: disgust and contempt. ./fa/4073/0/
Thiefs are not welcome.

Regards
Michel
Re: Connect using proxy user [message #549414 is a reply to message #549384] Fri, 30 March 2012 07:01 Go to previous messageGo to next message
owais_baba
Messages: 289
Registered: March 2008
Location: MUSCAT
Senior Member
mr @CADOT ----XYZ have u read where i have wrote that i have prepared that code just given solution wheither where ever i got this his problem Should be solved if you are not capable to solved their problem then just put your finger on your lips we are not here for any compition or for any examination we are all are for helping each others without paying/getting anything dont feel shame if u dont know anything go to institute and learn something first then join this forum


Re: Connect using proxy user [message #549415 is a reply to message #549414] Fri, 30 March 2012 07:12 Go to previous messageGo to next message
cookiemonster
Messages: 10908
Registered: September 2008
Location: Rainy Manchester
Senior Member
@owais - Michel's point is simple and correct. Don't post other peoples work as though it's your own. Post a link instead, it's not difficult.
Do so next time and don't complain because others have a go at you for not doing so.
And to suggest Michel doesn't know anything is quite simply stupid.
I suggest you apologize.
Re: Connect using proxy user [message #549419 is a reply to message #549415] Fri, 30 March 2012 07:59 Go to previous messageGo to next message
owais_baba
Messages: 289
Registered: March 2008
Location: MUSCAT
Senior Member
Normally i am very polite and down 2 earth person peoples r saying like this and my friends r always saying that i never used these kind of words sometime we need to get solution from here & there to solved someone's problems because might be someone's having their job problem that's why i always tried to provide complete help normally i have not that much time to used this forum because i most of the time lives in a italy nowadays i am free and travleing to all over the world specially in a middle east. anyway if i hurts anyone i personally apologize specially to @Cadot



Best Regards
Re: Connect using proxy user [message #549452 is a reply to message #549419] Fri, 30 March 2012 10:26 Go to previous messageGo to next message
Michel Cadot
Messages: 58861
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Apology accepted but do not use IM/SMS speak (and even less in a such message).

Here you can't give a useful example because you do not know what OP is actually trying to do and we do not even know if proxy user is the appropriate way to do what he wants to do (and not what he tries to do).
The first thing is to understand the (underlying) problem, then to help to get a solution (or provide one when the problem is really complex to explain).

Regards
Michel

[Updated on: Fri, 30 March 2012 10:26]

Report message to a moderator

owais_baba, a copycat [message #549468 is a reply to message #549452] Fri, 30 March 2012 14:34 Go to previous messageGo to next message
Littlefoot
Messages: 19476
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Oh well.

This is an awful copy-paste from OTN Forum (author: Waseem Haroon).

Want to store images is copy-paste from Cats who code, written by Jean-Baptiste Jung.

Hijri calendar messages copied from OTN. By the way, contents seems to be under copyright (taken from My Oracle Support).

Weight machine is stolen from Java samples (written by Steven Lim).

Serial communication post is stolen from Sun. The original code begins with a copyright information that clearly says that Sun
Sun grants you a licnese to use (...) and redistribute this software (...) provided that i) this copyright notice and license appear on all copies of the software (...)
(which you didn't do).

Calendar instructions copied from CoolInterview pages (posted by Binu Thomas).

The same topic, taken from IT Knowledge Exchange (written by AminAdatia).

A Client_text_io.get_line answer is an exact copy from bytes.com, posted by @hussaintal in 2008. You even took his exact words, sayinghussaintal and you

if you ask about the code we are using the following code


Tree node icons stolen from Project WowNow. The second part of the message copied from OTN forums (author: Rizwan Shafiq).

Restricting the user copied from OTN Forums (Andreas Weiden).

Want some more? No problem, next time I have nothing to do.




owais baba
i always tried to provide complete help

I wouldn't call that "a complete help". This is just a blind copy-paste. I bet you don't understand 10% of anything you posted here. Any additional question people asked lead to another copy-paste (from the same or another source). Looks like Frankenstein (a little bit from here, a little bit from there).
Re: owais_baba, a copycat [message #549474 is a reply to message #549468] Fri, 30 March 2012 16:03 Go to previous messageGo to next message
owais_baba
Messages: 289
Registered: March 2008
Location: MUSCAT
Senior Member
woh woh woh @littlefoot what's wrong with u most of the linked which u have posted have already answered by me many time ago may be 8-10 years back i dont use word stolen but many peoples took these codes for their help my logic is circulating on different plateforms but its good why are you so displeased what do u think i should not apologize to @cadot but i feel that iwas wrong then i apologized i dont wanna quarrel here this is not a place for quarrel here peoples are learning included u offcourse me also why are you peoples feeling this much insult i respect you always specially u and @cookiemonster please please dont use this plate form for ethnical purpose i personally request u if u dont wanted to person should answer from middle east its ok i would not answer next time but please away from ethnical.


best regards

[Updated on: Fri, 30 March 2012 16:05]

Report message to a moderator

Re: owais_baba, a copycat [message #549476 is a reply to message #549474] Fri, 30 March 2012 17:11 Go to previous messageGo to next message
owais_baba
Messages: 289
Registered: March 2008
Location: MUSCAT
Senior Member
sorry for forget to say gud by tommo. i going back to my hometown and my business is not related to IT. again if any my word gesture you hurts u then again i am sorry specially to @littlefoot please count down my nature is not like that to hurt anyone but please dont try to insult anyone by posting links what was u trying to proove i dont know anyway sorry again.


Re: owais_baba, a copycat [message #549489 is a reply to message #549476] Sat, 31 March 2012 01:30 Go to previous messageGo to next message
Michel Cadot
Messages: 58861
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Once again do BNOT use IM/SMS speak, this is contempt to continue despite we asked you.
You show there disrespect among people and their work.
Yes, we talk here about code of ethics as this is the basis of professional life (and every day life).

Littlefoot didn't insult you, he just showed what you did; if this is insulting you then this means you know you did wrong. If you didn't know it now you do and as it has been said, now, do not post code and answers as it was from you but post links to the original work (be sure it will be appreciated) and you will get back our respect; for the moment you don't.

Regards
Michel
Re: owais_baba, a copycat [message #549493 is a reply to message #549489] Sat, 31 March 2012 03:15 Go to previous messageGo to next message
Littlefoot
Messages: 19476
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
owais_baba
most of the linked which u have posted have already answered by me many time ago may be 8-10 years back (...) my logic is circulating on different plateforms


Oh COME ON! Have a closer look at my avatar and stop doing this! If you were capable of solving such problems that long time ago, how come you suddenly "forgot" everything about much simpler things, such as declaring global variables (which was one of your first messages here, just 4 years ago)?

Or how to blink a record?

Or you being unable to sort out compilation errors?

Should I continue?

You told a lie. Probability that you created such an advanced code is the same as possibility that I'm the next pope of the catholic church (which is possible, but not probable at all).

The fact is: you took someone else's knowledge and presented it as if it was yours. Some of the code is free to use (but it doesn't mean that you shouldn't give credits to the author). Some is protected by copyright (which you violated). As far as I'm concerned, such a behavior is morally doubtful. If I insulted you (you?!?) by pointing that out, then the world has turned upside down. On the contrary, it was you who insulted many people (authors in the first place).

You were caught, you were presented evidence (not just one!), but you still don't want to admit it.

I've seen things, so your case is just another one on the list. For example, we've had a member who opened two profiles; he asked a question using one of them and then, smartly, answered it using another one. He was caught and then he disappeared from the Forum.

See the correlation? All of the sudden you have some business not related to IT and - I suppose - have an excuse to leave the Forum. Yeah, right.

Finally: what's wrong with you, mentally? What made you even THINK of mentioning "ethnic" (I'm afraid Michel misread what you said; "ethic" is not in your vocabulary) and "Middle East" issues?!? What does the above have to do with what you did?

And yes, I'm both mad and sad. I just hope that you'll learn to respect other people's efforts, gain your own knowledge and be proud of it. It takes YEARS, but it's worth at the end.
Re: owais_baba, a copycat [message #549501 is a reply to message #549493] Sat, 31 March 2012 06:40 Go to previous messageGo to next message
owais_baba
Messages: 289
Registered: March 2008
Location: MUSCAT
Senior Member
first of fall who told you i m leaving this forum dont make ideas i am not going anywhere just gonna change terrority
2ndly my this account has been using by also junior members these question which u have posted asked by that time newly appointed in our organization i was allowed them to use my account to ask questions from this forum anyway where we are taking this forum now pls. finished this discussions. you peoples are superiors i accepted gold medal goes to you. pls dont Inclement enviroment of this forum. i personally requested to you already.
hope no more discussion regarding this topic.



regards
baba
Re: owais_baba, a copycat [message #549508 is a reply to message #549501] Sat, 31 March 2012 08:32 Go to previous messageGo to next message
Littlefoot
Messages: 19476
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I find your explanation rather doubtful (you let "juniors" use your account? Why? What prevented them to create their own account? It is free).

Moreover, according to your profile, 10 years ago (which is time you produced that "logic" that is "circulating on different platforms"), you were 17 years old; I suppose I should say that I'm glad to know someone who is capable of doing such a great things at that (young) age, because - if compared to myself - I didn't even know how to spell "Oracle" at that time. I think I'd rather stay with already stated "have another look at my avatar".

However, I must agree with you - this discussion takes too much energy and the outcome is questionable, so - I'm going to leave it as well.
Re: owais_baba, a copycat [message #549512 is a reply to message #549508] Sat, 31 March 2012 09:21 Go to previous message
Michel Cadot
Messages: 58861
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I'm afraid Michel misread what you said; "ethic" is not ...


Oops! you're right, it is so far from me hearing someone making this accusation about ethnic against us that I could't read it. Smile

@owais_baba, just have a look at Forum statistics and you will realize your ethnical error.


Regards
Michel
Previous Topic: Assign multiple values to variables
Next Topic: FRM-93652 Forms Error
Goto Forum:
  


Current Time: Fri Aug 22 23:05:12 CDT 2014

Total time taken to generate the page: 0.09951 seconds