Connection shortcuts with a wallet

Posted by

I’m lazy when I connect to the database, especially on my laptop.  Anything that saves a few keystrokes I’m keen on Smile

So rather than type “sqlplus username/password@database” I like to take advantage of a wallet

In my private sqlnet.ora, or the global one if it makes sense to do so, I add the details of wallet



SQLNET.WALLET_OVERRIDE=TRUE
WALLET_LOCATION=(SOURCE=(METHOD=FILE)(METHOD_DATA=(DIRECTORY=c:\oracle\wallet)))

This tells Oracle, that when I begins a connection to the database, we will look in directory “c:\oracle\wallet” for wallet entries, which I’ll now create



C:\oracle\wallet>mkstore -create -wrl c:\oracle\wallet
Oracle Secret Store Tool : Version 12.1.0.2
Copyright (c) 2004, 2014, Oracle and/or its affiliates. All rights reserved.

Enter password: ********
Enter password again: ********

This creates an empty wallet.  You’ll need a password for the wallet, which is required whenever maintenance to the wallet is required.  Then I add an entry to the wallet for my database, in this case, “MYDB”


C:\oracle\wallet>mkstore -wrl c:\oracle\wallet -createCredential MYDB scott tiger
Oracle Secret Store Tool : Version 12.1.0.2
Copyright (c) 2004, 2014, Oracle and/or its affiliates. All rights reserved.

Enter wallet password: ********
Create credential oracle.security.client.connect_string1

I also have a MYDB entry in my tnsnames.ora

MYDB =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1525))
)
(CONNECT_DATA =
(SERVICE_NAME = np12)
)
)

At this point, we can now connect securely to the database without ever requiring a password to be hard coded on command line, or in an environment variable etc.

The wallet is consulted, which maps MYDB to scott/tiger, and also maps to MYDB in tnsnames.ora, and the two then form the database connection.

C:\oracle\wallet>sqlplus /@mydb

SQL*Plus: Release 12.1.0.2.0 Production on Mon Sep 21 16:22:43 2015

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Last Successful login time: Thu Sep 10 2015 11:40:45 +08:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
SQL> show user
USER is “SCOTT”

You can list the credentials stored in the wallet using

mkstore -wrl c:\oracle\wallet -listCredential

When prompted for a password, enter the password used to create the wallet.

So for me, its about saving on keystrokes, but you can see how using a wallet, also means you can now avoid hard-coding passwords in any of your applications or scripts.  There are also additional features for wallets which you can use to prohibit them from being copied across machines etc.

10 comments

  1. Isn’t it true that anyone with a login to the server containing the wallet can use the “sqlplus /@MYDB” syntax to login without knowing the password? I get the convenience aspect, but to say that not requiring a password is more secure than hardcoding one doesn’t make sense to me. Am I missing something there?

    1. The wallet typically has permissions exclusive to that of the OS account, so only *that* particular account can access/use the wallet. An obviously, if you are giving the password to that OS account out to lots of people….well, then you have another problem 🙂

  2. Hi Connor,

    thanks a lot for this post.
    You mentioned, that it is possible to prohibit a wallet from being copied across machines. Can you give more details on that?

    thanks,
    krystian

Got some thoughts? Leave a comment

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 )

Google photo

You are commenting using your Google 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 )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.