Monthly Archives: November 2014

This yet undocumented feature allows to define application schemas which can only be accessed through a proxy user. It makes a very useful to assure that no user connects directly to the application schema, even by knowing its password.

Here how it works:

SQL> CREATE USER app_user IDENTIFIED BY xyz;
User created.

SQL> GRANT CREATE SESSION TO app_user;
Grant succeeded.

SQL> ALTER USER app_user PROXY ONLY CONNECT;
User altered.

SQL> CREATE USER personal_user IDENTIFIED BY prx1;
User created.

SQL> ALTER USER app_user GRANT CONNECT THROUGH personal_user;
User altered.

SQL> CONNECT app_user/xyz;
ERROR:
ORA-28058: login is allowed only through a proxy

SQL> CONNECT personal_user[app_user]/prx1;
Connected.

SQL> SELECT user FROM dual;
USER
------------------------------
APP_USER

The information that app_user accepts to be connected only through proxy user can be seen at the new DBA_USERS column PROXY_ONLY_CONNECT.

As usual, the use of undocumented features are not supported by Oracle. The syntax to rollback the change is:

SQL> ALTER USER app_user CANCEL PROXY ONLY CONNECT;