The power of the Oracle Database Proxy authenticated connections
We recently received this inquiry from a client: "C
an an Oracle database account support two passwords at once so we can roll out updated credentials to application servers gradually rather than having to change them all at the same time? Then once all of the application servers have been configured to use the new/second password we can change or remove the first one?" The short answer is
no. Like most computer technologies, an Oracle database user has only one password that is valid at any given time. However, a very powerful and under-appreciated feature of the Oracle database could be used in this case: It is called
proxy authentication.
How proxy authenication works
Introduced with Oracle Database 10g, the basic premise of proxy authentication is that a user with the required permission can connect to the Oracle database using their own credentials, but proxy into another user in the database. To put it more plainly: connect as USER_A but using the password of USER_B ! The proxy permission is granted through the "CONNECT THROUGH" privilege. Interestingly, it is granted through an ALTER USER command as really it's an "authorization" and property of the user and not truly a privilege like the traditional privileges we're used to:Now USER_B who may not know the password for USER_A can connect as USER_A by specifying the proxy account in square brackets in the connection string:SQL> connect / as sysdba Connected. SQL> alter user USER_A grant connect through USER_B; User altered. SQL>
The password specified was the one for USER_B, not USER_A. Hence the credentials for USER_B were used but the end result is that the session is connected as USER_A! Specifically when a proxy authenticated connection is made the USERENV namespace parameters are updated as follows:SQL> connect USER_B[USER_A]/passw0rd Connected. SQL> show user USER is "USER_A" SQL>
- The "SESSION_USER" becomes USER_A
- The "SESSION_SCHEMA" also becomes USER_A
- The "PROXY_USER" remains USER_B who initiated the connection and who's credentials were used
This is a very quick and simple approach, but isn't quite the same. Doing this only changes the "CURRENT_SCHEMA" which is the currently active default schema. Hence any queries issued without specifying the schema name will default to "CURRENT_SCHEMA". But there are many cases when actually connecting to another user is required. For example, if the DBA needs to drop and re-create a database link then the "current_schema" approach will not suffice. But the proxy authenticated connection alternative will work perfectly. Another case where the "current_schema" approach may be an issue is if the application is user aware. What I mean by this is that possibly the application has some logic such as " if user = USER_A then do suff". If you connect as USER_B and simply changes the current schema then the boolean logic of this condition will evaluate to FALSE. However if you use a proxy authenticated connection user USER_A, the condition will evaluate to TRUE. Previously if the DBA needed to connect to the database as a specific user (maybe to re-create a DB link for example) they might employ the old trick of temporarily changing the user's password, quickly connecting, and then quickly changing it back using the extracted/saved password hash. However there are numerous serious problems with this approach:alter session set current_schema = USER_A;
- The schema may be locked
- The password may be controlled by a PROFILE that may also need to be adjusted.
- Account intrusion detection tools may detect the connection.
- The connection may not be properly audited via Oracle or external auditing tools.
- The application may unsuccessfully try to connect while the password is temporarily changed causing an application failure!
A simple example
Putting it all together into a small example to show how the userenv properties are affected:
SQL> alter user USER_A grant connect through USER_B;
User altered.
SQL> connect USER_B[USER_A]/passw0rd
Connected.
SQL> alter session set current_schema = SCOTT;
Session altered.
SQL> select sys_context('USERENV','SESSION_USER') as session_user,
2 sys_context('USERENV','SESSION_SCHEMA') as session_schema,
3 sys_context('USERENV','CURRENT_SCHEMA') as current_schema,
4 sys_context('USERENV','PROXY_USER') as proxy_id,
5 user
6 from dual;
SESSION_USER SESSION_SCHEMA CURRENT_SCHEMA PROXY_ID USER
-------------- -------------- -------------- -------------- ------------
USER_A SCOTT SCOTT USER_B USER_A
SQL>
As can be seen above, for all intensive purposes the connection has been made to USER_A but using the credentials of USER_B. USER_A's password did not need to be known nor was the USER_A account affected or adjusted in any way.
FAQs
What if USER_A's password is locked or expired? The answer is that the connection will still report the same error as it would have if a direct connection to USER_A was made:Can this be used with other tools such as Data Pump? (Importing as the actual user instead of a DBA user was necessary with Oracle 10g under specific circumstances such as importing JOBs and REFRESH GROUPS). The answer is yes it works with Data Pump and other similar tools:SQL> connect / as sysdba Connected. SQL> alter user USER_A account lock; User altered. SQL> connect USER_B[USER_A]/passw0rd ERROR: ORA-28000: the account is locked Warning: You are no longer connected to ORACLE. SQL>
Notice that the Data Pump master table is created in the USER_A schema even though we connected using the USER_B credentials. Is proxy authentication supported by JDBC/JDBC thin driver? Yes, it works through almost any OCI connection including JDBC connections. What about Oracle Wallets? The answer again is yes, they can support it too! See below for an example using an Oracle Wallet:$ impdp dumpfile=temp.dmp nologfile=y include=JOB Import: Release 11.2.0.4.0 - Production on Wed Oct 15 19:10:13 2014 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. Username: USER_B[USER_A]/passw0rd Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Master table "USER_A"."SYS_IMPORT_FULL_01" successfully loaded/unloaded Starting "USER_A"."SYS_IMPORT_FULL_01": USER_B[USER_A]/******** dumpfile=temp.dmp nologfile=y include=JOB Processing object type SCHEMA_EXPORT/JOB Job "USER_A"."SYS_IMPORT_FULL_01" successfully completed at Wed Oct 15 19:10:22 2014 elapsed 0 00:00:01 $
$ mkstore -wrl "/u01/app/oracle/wallet" -createCredential ORCL USER_A passw0rd Oracle Secret Store Tool : Version 11.2.0.4.0 - Production Copyright (c) 2004, 2013, Oracle and/or its affiliates. All rights reserved. Enter wallet password: Create credential oracle.security.client.connect_string1 $ mkstore -wrl "/u01/app/oracle/wallet" -listCredential Oracle Secret Store Tool : Version 11.2.0.4.0 - Production Copyright (c) 2004, 2013, Oracle and/or its affiliates. All rights reserved. Enter wallet password: List credential (index: connect_string username) 1: ORCL USER_A $ $ sqlplus /@ORCL SQL*Plus: Release 11.2.0.4.0 Production on Wed Oct 15 13:45:04 2014 Copyright (c) 1982, 2013, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> show user USER is "USER_A" SQL> exit Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options $ sqlplus [app_user]/@ORCL SQL*Plus: Release 11.2.0.4.0 Production on Wed Oct 15 13:45:14 2014 Copyright (c) 1982, 2013, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> show user USER is "APP_USER" SQL> exit Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options $
Reporting and Revoking
Finally, how can we report on what proxy authentication authorizations we've granted as we need to clean them up (revoke them)? Or perhaps we just need to report on or audit what's out there? Fortunately, it's as simple as querying a catalog view to see what's been set and we can remove/revoke through another simple ALTER USER command:SQL> select * from PROXY_USERS; PROXY CLIENT AUT FLAGS ------------ ------------ --- ----------------------------------- USER_B USER_A NO PROXY MAY ACTIVATE ALL CLIENT ROLES SQL> alter user USER_A revoke connect through USER_B; User altered. SQL> select * from PROXY_USERS; no rows selected SQL>
