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”.

The statement CONNECT creates a new session. The goal of creating a new session is:

  1. Connect to another database.

  2. Or switch to another user account.

To close a session, execute CLOSE (without any parameter).

Syntax of the CONNECT statement
; 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>

Syntax #1

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 USER_AGENT.

Syntax #2

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 <authentication method>.

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.

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.

Notes

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:

  1. You establish a connection with Virtual DataPort.

  2. You execute CONNECT. This creates a session. Let us call it “session A”.

  3. You execute CONNECT again to create another session (“session B”).

  4. You execute CONNECT again to create another session (“session C”).

  5. You execute CLOSE. This closes “session C” and you are now in “session B”.

  6. You execute CLOSE again. This closes “session B” and you are now in “session A”.

  7. You execute CLOSE again. This closes “session A” and you are back to the initial session.

When you close a connection, all its sessions are automatically closed.