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.

Note: Wallets are like SSH keys. You store them securely and obviously don’t pass them around to people 🙂

 

Catch the video version here

22 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

  3. Hi Connor, when you use /@mydb, I can see it’s connecting as SCOTT, but you don’t have to be logged on as SCOTT at the OS level do you in order to do that ?

  4. Hi Connor,

    How to use multiple wallet folders ?
    Does “WALLET_LOCATION” supports multiple folders ?
    (or)
    Should I use different TNS_ADMIN for my user ?

    Scenario:
    To add new DB user to wallet without disturbing existing wallet, I do not have password for existing wallet (someone else already created)

    Please advise

    Thank you

  5. Hi Connor:
    What if I am super lazy, and I don’t want to type in the database name? I actually have a need to connect just using “sqlplus /” Is there a way to do this? I can define the local variable or ORACLE_SID, but this causes the connection to wanto to connect as the OS Account.

  6. Hi Connor,

    Is there a way to change a user password by not using “REPLACE” clause in the “alter user…” statement?
    We have some kind of automation, where we generate passwords and create a wallet and forget about the passwords.
    Now, with this password verify function, since it is mandatory to old password, we are searching for a solution.

  7. When creating this wallet can this be used to connect over tcps? for instance
    orapki wallet -create -wrl c:\temp\Oracle\Wallets\
    orapki wallet add -wallet c:\temp\Oracle\Wallets\ -dn “cn=skynet,ou=run” -keysize 2048 -pwd

    orapki -wrl c:\oracle\wallet -createCredential “CERT_TEST” TEST_USER

    or

    orapki -wrl c:\oracle\wallet -createCredential “CERT_TEST” EXTERNAL

    create user test_user identified externally as “cn=skynet,ou=run”

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 )

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.