[DB-SIG] cx_Oracle - any way to change the user of a connection?

[Andy Todd]

Harald Armin Massa wrote:
> I want to have a connections which stays activated:
> 
> cn=cx_Oracle.connect("dataset", "lowprivuser", "password")
> 
> 
> and change the user of that connection.
> 
> Reason: I want to have a web application, which connects ONE TIME (per 
> process/thread) to Oracle, and switches the privileges on the fly.
> 
> I looked into "SET ROLE <ACTIVEUSERax>"; but as much as I understand 
> thats pureley additional, so:
> 
> cn=cx_Oracle.connect("dataset", "lowprivuser", "password")
> # gives the privs of "lowprivuser"
> cs=cn.cursor()
> 
> cs.execute ("SET ROLE USERFISCH identified by secret2006")
> 
> then gives the privs of "lowprivuser" plus the privs of  USERFISCH,
> 
> and an additional
> 
> cs.execute ("SET ROLE USERFOO identified by othersecret")
> 
> adds the privs of "USERFOO", WITHOUT loosing those of USERFISCH.
> 
> 
> Is there any way to do this?
> 
> Best wishes,
> 
> Harald
> 
> 

Technically no. The definition of a connection to an Oracle database is 
the user you connect as and the database you connect to. Change one or 
the other and you have a different connection.

It's not like, for instance, MySQL where you can change the 'database' 
you are connected to by updating an attribute on the connection.

Roles are a whole different kettle of fish and can indeed grant you 
rights and privileges that the user you connect as doesn't have, but you 
  will still be that user.

If it is any consolation it's an Oracle thing and nothing to do with the 
implementation of cx_Oracle.

About the best thing that you can do is create a fresh connection with 
the same name;

 >>> db1 = cx_Oracle.connect('username/password at database')
 >>> cursor1 = db1.cursor()
 >>> cursor1.execute('<Some valid SQL>')
...

 >>> db1 = cx_Oracle.connect('another user/password at database')
 >>> cursor2 = db1.cursor()
...

Of course, any objects you create using the original definition of db1 
will maintain that connection. In this example cursor1 will continue to 
use the privileges of 'username' whilst cursor2 has the privileges of 
'another user'. I can't help but think that this would get very 
confusing very fast.

I'm not entirely sure what you are trying to achieve in your 
application, the overheads for creating a fresh connection are fairly 
light (on the client side at least ;-) but it's generally bad form to 
change users on the fly like that in the Oracle world.

The usual approach - which you've already got 50% of the way towards - 
is to connect as a user with low or no privileges and then 'assume' a 
specific role that has been granted the rights you require to perform 
the operations necessary for that particular session.

Regards,
Andy
-- 
--------------------------------------------------------------------------------
 From the desk of Andrew J Todd esq - http://www.halfcooked.com/