Proxy Connections

Managing development environments is a fickle business. On one hand you want to be able to grant developers whatever privileges they require to make the development process easy. On the other hand you don’t want the environment to become so disorganised that it’s hard to figure out where the hacking ends and the real application begins.

Database applications are typically structured with database objects in one or more accounts (users). Sharing around the password for the application account can lead to trouble, especially in non-development environments where there is supposed to be deployment controls in place. Once an account password has been given out then it can be assumed that it is generally known by all developers. Proxy connections allow access to be given without having to compromise the application account password.

The ALTER USER command is used to configure the proxy connection details. Suppose your application account is named APP_OWNER and you want to connect to it through a developer’s account named MARK. The following is the DDL required:

ALTER USER app_owner GRANT CONNECT THROUGH mark
/

So, now the user MARK is able to connect to the APP_OWNER account as it they had the credentials for that account. Access can be granted for a particular task and then easily revoked later once the task is complete.

So, once set up how do you go about logging into the database using the proxy connection details? The following describes how to do it using the SQL*Plus command line, Oracle’s SQL Developer and Dell’s (formerly Quest) TOAD.

SQL*Plus
Proxy account connections using SQL*Plus involves specifying the details of the account you intend to connect to inside square brackets as part of the connection details, e.g.:

CONNECT mark[app_owner]

This can all be done direct from the OS command prompt when invoking SQL*Plus:

> sqlplus mark[app_owner]@<<database>>

You’ll be prompted for a password. Enter the password for the MARK account and you’ll be connected to the APP_OWNER account.

Oracle SQL Developer
Using Oracle’s SQL Developer, in the connection window there is a check-box near the bottom labelled “Proxy Connection”.

Checking this will cause a new window to open, into which you enter the proxy connection details. Using the basic set up described earlier all that would be required in this second window is proxy account name in the Proxy Client field.

TOAD
Unlike SQL Developer, TOAD doesn’t have any special settings on its connection window for proxy connections. Instead the details are entered directly into the user/schema field, using the square bracket notation that SQL*Plus uses, i.e. username[proxy user] as shown below.