So many database so many accounts and so so many passwords. There are a great many times when logging in as the application or schema owner makes the job of a DBA so much easier such as running a query that doesn’t prefix owners before object names. While the alter session set current_schema can help in some situations there are others that trick won’t work (ie anything that depends on the username during the execution of the processes). In those cases knowing the schema password is great but you may not have easy access to the password for whatever reason.
To get around this a very common trick has been to query the password from dba_users, grab the encrypted value, change the password login and change it back using the identified by values clause. This as a few draw backs
- You must change the password every time you need to login
- If the application tries to login during your trick it could cause application errors
- If enough failed attempts happen before you change the password back the account could be locked
- If you fat finger changing the password back
Two cleaner methods that have been around for a while but aren’t wildly know are proxy authentication and storing credentials in a wallet.
Proxy authentication is basically allowing you to login with your username and password and then switching to another account.
We are going to allow the SCOTT username to be able to login as the APEX_030200 user. Both accounts have already been created as normal database accounts. A simple alter user command is need to allow SCOTT to login as APEX_030200.
$sqlplus / as sysdba sqlplus> alter user APEX_030200 grant connect through scott; User altered.
When we want to login to the database brackets enclose the username we want to connect to directly after the username we are using to log into the database.
$ sqlplus scott[apex_030200] SQL*Plus: Release 188.8.131.52.0 Production on Tue Aug 20 21:05:03 2013 Copyright (c) 1982, 2010, Oracle. All rights reserved. Enter password: Connected to: Oracle Database 11g Enterprise Edition Release 184.108.40.206.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> show user USER is "APEX_030200"
We authenticated with the SCOTT account but proxy to the APEX_030200 just as if we had logged in directly.
To remove proxy rights again a simple alter user command is used.
SQL> conn / as sysdba Connected. SQL> alter user apex_030200 revoke connect through scott; User altered. SQL> conn scott[apex_030200] Enter password: ERROR: ORA-28150: proxy not authorized to connect as client Warning: You are no longer connected to ORACLE.
Another method to connect to the database is using an Oracle wallet to store the credentials.
First create a wallet if not using an existing one.
$ mkstore -wrl wallet -create Oracle Secret Store Tool : Version 220.127.116.11.0 - Production Copyright (c) 2004, 2011, Oracle and/or its affiliates. All rights reserved. Enter password: Enter password again:
The -wrl is followed by the location to create the wallet and then we choose and confirm a password for the wallet.
Next we add the credentials.
$ mkstore -wrl wallet/ -createCredential prod test_gg Oracle Secret Store Tool : Version 18.104.22.168.0 - Production Copyright (c) 2004, 2011, Oracle and/or its affiliates. All rights reserved. Your secret/Password is missing in the command line Enter your secret/Password: Re-enter your secret/Password: Enter wallet password: Create credential oracle.security.client.connect_string1
Again the -wrl is the location of the wallet and the createCredential is used to add a new entry. The prod is the TNS alias used to connect to the database so ensure it is working first. Second is the username we want to store. The first password is the schema password and the second password is the wallet password.
Finally we modify the SQLNET.ora file with the location of the wallet and also set the wallet over-ride to true.
SQLNET.WALLET_OVERRIDE = TRUE WALLET_LOCATION = (SOURCE = (METHOD = FILE) (METHOD_DATA = (DIRECTORY = /u01/app/oracle/admin/src/wallet) ) )
Now lets try it out. Since we are using wallet we don’t specify a username or password. The TNS alias should match tns alias in the wallet.
$ sqlplus /@prod SQL*Plus: Release 22.214.171.124.0 Production on Wed Sep 4 17:04:45 2013 Copyright (c) 1982, 2011, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 126.96.36.199.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> show user USER is "TEST_GG"
We have successfully connected with supplying a username or password. Copying the sqlnet.ora, tnsnames.ora and wallet to an app server would be useful so passwords are not exposed in config files.