Monday, July 29, 2013

Connect Through - Connect Without Knowing the Password

At times it may be necessary to grant access to a database schema but not necessarily give the password for the login. Granting connect through comes useful in such cases. Below example demonstrate the usage. Assume user appadmin is the main application user and appguest user whom access to appadmin schema will be granted.
SQL> create user appadmin identified by appadmin;
User created.

SQL> create user appguest identified by appguest;
User created.

SQL> grant connect to appadmin;
Grant succeeded.

SQL> grant connect to appguest;
Grant succeeded.
Grant appguest connect through as appadmin
SQL> alter user appadmin grant connect through appguest;
User altered.
With this command user appguest would be able to connect to appadmin without specifiying appadmin password.
SQL>  conn appguest[appadmin]/appguest
SQL> show user
Password specified above is the password of the appguest user and the connected schema is the appadmin schema.

For JDBC proxy connections refer the following metalink notes.
How to use Proxy Users to connect to a Database using Credential Mapping or User Injection. [1529978.1]
How to Ensure USER Session Variable Accurately Reflects "real" User When Using Proxy Authentication with JDBC [431202.1]