Connecting to Another Database¶
When you establish a connection with Virtual DataPort, you are connected to one of its databases. This is the “initial session”.
CONNECT creates a new session. The goal of creating a new session is:
Connect to another database.
Or switch to another user account.
To close a session, execute
CLOSE (without any parameter).
; Syntax #1. Connect to another database: CONNECT DATABASE <database:identifier> [ USER_AGENT <user agent:literal> ] ; Syntax #2. Create a session with a different user account: CONNECT <authentication method> [ DATABASE <database:identifier> ] [ USER_AGENT <user agent:literal> ] <authentication method> :== USER <user name:identifier> [ PASSWORD <password:literal> ] ] | TOKEN <Kerberos token encoded in Base64:literal> | OAUTHTOKEN <OAuth 2.0 token:literal>
With “syntax #1”, you create a session with the current user, connected to another database.
Optionally, you can set the “user agent” of the session with the parameter
With “syntax #2, you create a session with a different user account. Note that you have to provide the credentials of this other user account, with the parameters of
For example, if you establish the connection to Virtual DataPort with the user “scott” and then, you execute this:
CONNECT USER lisa PASSWORD 'my_password';
From this moment and for the duration of the session, all the statements will be executed as if “lisa” had established the initial connection. That is, the execution engine will use the privileges of “lisa” to validate if the query can be executed, in the Diagnostic & Monitoring tool, the user that executes the query will be “lisa”, etc. Also it includes the possibility to connect without password, however only users with impersonator role can connect to another user.
For example, if you establish the connection to Virtual DataPort with the user “scott” (who has impersonator role), you will be able to connect with user lisa without password:
CONNECT USER lisa;
Optionally, you can indicate:
DATABASE: to connect to a different database. If you do not specify it, the session will be started in the current database.
USER_AGENT: to set the “user agent” of the session.
For both “syntax #1” and “syntax #2”, the parameter
USER_AGENT sets the property “user agent” of the session.
The section Setting the User Agent of an Application explains why doing this is useful.
Note that setting the user agent does not change the privileges granted to the session. The privileges are only calculated based
on the privileges granted to the user account of the session.
When you create a session without the parameter
USER_AGENT, the new session “inherits” the user agent from the current session.
Sessions are organized as a stack. That is, when you establish the connection with Virtual DataPort, this connection creates an “initial session”. From this initial session, you can create another session and from this one, you can create another, etc. For example:
You establish a connection with Virtual DataPort.
CONNECT. This creates a session. Let us call it “session A”.
CONNECTagain to create another session (“session B”).
CONNECTagain to create another session (“session C”).
CLOSE. This closes “session C” and you are now in “session B”.
CLOSEagain. This closes “session B” and you are now in “session A”.
CLOSEagain. This closes “session A” and you are back to the initial session.
When you close a connection, all its sessions are automatically closed.