Connecting to a Schema Without Knowing the Password

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

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 11.2.0.2.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 11.2.0.2.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 11.2.0.3.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 11.2.0.3.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 11.2.0.3.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 11.2.0.3.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.

Advertisements

3 thoughts on “Connecting to a Schema Without Knowing the Password

    • One way would be to create a procedure that would have select rights on v$session and the alter system command. You would pass in the sid serial of the session and the procedure would then kill the session.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s