OraFAQ Forum: SQL & PL/SQL » How to impersonate while switching db schema

Home » SQL & PL/SQL » SQL & PL/SQL » How to impersonate while switching db schema (Oracle 11gR2)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

How to impersonate while switching db schema [message #601123]

Sat, 16 November 2013 12:17

mkaresh85
Messages: 4
Registered: November 2013
Location: Bangalore

Junior Member

Hi,

I have come across SQL:- alter session set current_schema=<schema-name> to use a different schema which does not belong to an user.
But how to impersonate the schema1 user to be able to create/update tables in schema2 table-space?

I have 2 local users (have minimal privileges) having 2 different schema. I'm looking for a way for making schema1 user connect to schema2 tables and perform create/update operation.. Heard this can be done impersonating system, but not sure how?

Is it by using - "ALTER USER user1 GRANT CONNECT THROUGH system;"
Anyways this does not help me.

Report message to a moderator

Re: How to impersonate while switching db schema [message #601124 is a reply to message #601123]

Sat, 16 November 2013 12:35

pablolee
Messages: 2882
Registered: May 2007
Location: Scotland

Senior Member

Proxy authentication

Report message to a moderator

Re: How to impersonate while switching db schema [message #601125 is a reply to message #601124]

Sat, 16 November 2013 12:51

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

Why not just connect as schema2 user?

Report message to a moderator

Re: How to impersonate while switching db schema [message #601126 is a reply to message #601124]

Sat, 16 November 2013 12:56

mkaresh85
Messages: 4
Registered: November 2013
Location: Bangalore

Junior Member

Thanks for the quick response.
I tried to grant the local user the privileges described and granted connect through SYSTEM as shown below:

1) GRANT CONNECT, RESOURCE, CREATE ANY DIRECTORY, DROP ANY DIRECTORY TO user1;
2) ALTER USER system GRANT CONNECT THROUGH user1; commit;

But I'm still getting insufficient privileges for INSERT command. Any other privileges required? Thanks in advance.

Error:-
SQL Error: ORA-01031: insufficient privileges
01031. 00000 - "insufficient privileges"
*Cause: An attempt was made to change the current username or password
without the appropriate privilege. This error also occurs if
attempting to install a database without the necessary operating
system privileges.
When Trusted Oracle is configure in DBMS MAC, this error may occur
if the user was granted the necessary privilege at a higher label
than the current login.
*Action: Ask the database administrator to perform the operation or grant
the required privileges.
For Trusted Oracle users getting this error although granted the
the appropriate privilege at a higher label, ask the database
administrator to regrant the privilege at the appropriate label.

Report message to a moderator

Re: How to impersonate while switching db schema [message #601127 is a reply to message #601125]

Sat, 16 November 2013 12:58

mkaresh85
Messages: 4
Registered: November 2013
Location: Bangalore

Junior Member

Ideally I would be having a single JDBC datasource (with schema1 user details - but need to switch schema and act on different schemas).. I'm trying to achieve this programmatically.

Report message to a moderator

Re: How to impersonate while switching db schema [message #601128 is a reply to message #601127]

Sat, 16 November 2013 13:15

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

http://docs.oracle.com/cd/E16655_01/server.121/e17209/statements_4003.htm#SQLRF53642

"CONNECT THROUGH Clause

Identify the proxy connecting to Oracle Database. Oracle Database expects the proxy to authenticate the user unless you specify the AUTHENTICATION REQUIRED clause."

sometimes you will spend more time & effort looking for a "short cut" than you will actually save by taking it.

Again, I advise that you create another session as SCHEMA2, but you are free to pursue single schema solution since it is you time & database.

http://docs.oracle.com/cd/E16655_01/network.121/e17607/authentication.htm#DBSEG99851

[Updated on: Sat, 16 November 2013 13:22]

Report message to a moderator

Re: How to impersonate while switching db schema [message #601136 is a reply to message #601128]

Sun, 17 November 2013 01:07

mkaresh85
Messages: 4
Registered: November 2013
Location: Bangalore

Junior Member

Any help on this? Still I'm not able to figure out this! I'm able to SELECT tables but not INSERT/UPDATE/DELETE..
It's critical and need to get this task completed ASAP.

Thanks a bunch in advance.

Report message to a moderator

Re: How to impersonate while switching db schema [message #601141 is a reply to message #601136]

Sun, 17 November 2013 03:43

John Watson
Messages: 8931
Registered: January 2010
Location: Global Village

Senior Member

This,

alter session set current_schema=<schema-name>

does not change the identity of your session: your security context is unchanged. All it does is change the context in which names are resolved. You need to grant your schema1 insert/update/delete privileges on the tables in schema2, then your code should work.

Using proxy authentication through a pooled connection should work, but I think (I am of course open to correction on this) you would need to use the thick JDBC client, and code appropriate OCI calls, which is probably a lot mote work.

Report message to a moderator

Previous Topic:	cursor for loop
Next Topic:	blob update

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Apr 25 03:20:49 CDT 2024