I’m lazy when I connect to the database, especially on my laptop. Anything that saves a few keystrokes I’m keen on
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
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 18.104.22.168 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 22.214.171.124 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
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1525))
(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.
SQL*Plus: Release 126.96.36.199.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
Oracle Database 12c Enterprise Edition Release 188.8.131.52.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
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?
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 🙂
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?
The “-auto_login_local” parameter binds a wallet to be local the machine it was created on.
Hope this help
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 ?
No – this is not the same as OS authentication.
How to use multiple wallet folders ?
Does “WALLET_LOCATION” supports multiple folders ?
Should I use different TNS_ADMIN for my user ?
To add new DB user to wallet without disturbing existing wallet, I do not have password for existing wallet (someone else already created)
I think in years gone by, you could have a “.sqlnet.ora” (leading dot) in your home directory, but I think that has been phased out. So TNS_ADMIN to control where to look
Wow, It worked. Marvellous TAT
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.
Not sure if it works in conjunction with wallets, but try TWO_TASK
TWO_TASK alone isn’t enough, but with some additional tricks it’s doable:
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.
A DBA level account can change any user without needing the old password if that whats you mean, ie
alter user XXX identified by YYY
Is it possible to use the wallet to connect the Oracle cloud database to non cloud Oracle database?
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
orapki -wrl c:\oracle\wallet -createCredential “CERT_TEST” EXTERNAL
create user test_user identified externally as “cn=skynet,ou=run”