Oracle 12.1 – Proxy only connect user property 1
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;