Database Sessions#

Normally a connection is tied to a database session. Hence, when the Connection Pool reuses a connection, the resp. session is reused, too.

Warning

Depending on your use case this may be inadequate or even problematic if users have different permissions or session settings.

Additionally, both aspects can change over time, i.e. a user gets granted more or less permissions than before or session parameters are updated with SQL command ALTER SESSION.

Ultimately, the desired policy needs to be implemented by the application using the connection pool incl. whether the resp. database session should be reused or not.

Session Reset#

Tied to a session are

  • Transactions: SQLAlchemy parameter reset_on_return supports to either commit or rollback open transaction when returning a connection to the pool.

  • Changes with SQL command ALTER SESSION

  • The current user, see section Changing the User for an Existing Connection below.

  • Temporary objects: Not available in Exasol

Currently there is no command in Exasol to reset the current session.

However, the application could use the default value “rollback” for Pool.reset_on_return, listen to event reset, and execute additional commands to reset the session as desired, see Session Reset in our list of examples.

Changing the User for an Existing Connection#

SQLAlchemy API does not allow altering the user for an existing connection. Instead, the application could use a dedicated pool for each user (account, tenant, client).

Exasol offers statement IMPERSONATE allowing user <U1> to impersonate another user <U2>. User <U1> must therefore have related privileges for any or a specific user or role to impersonate.

As the other user <U2> usually will not have this privilege, this is a one-way operation that cannot be reverted. Hence, when executing this statement within a given connection, the connection may not be used to impersonate other users afterward.

In consequence such a connection may not be suited for further reuse and needs to be discarded.