mkstore<\/strong>. They allow to store a username and password in a secure wallet accessible only by its owner. The wallet is then accessed by the Oracle Client to connect to a remote database, meaning that you DON’T HAVE to specify any username and password!<\/p>\nLet’s see how to implement this the quick way:<\/p>\n
Create a directory that will contain your wallet:<\/p>\n
$ mkdir .wlt<\/pre>\nCreate the wallet, use an arbitrary complex password to protect it:<\/p>\n
$ mkstore -wrl \/home\/ludovico\/.wlt -create\r\nOracle Secret Store Tool : Version 12.1.0.1\r\nCopyright (c) 2004, 2012, Oracle and\/or its affiliates. All rights reserved.\r\n\r\nEnter password:\r\n\r\nEnter password again:\r\n\r\n$ ls -lia .wlt\r\n\r\ntotal 16\r\n1973369 drwxrwxr-x 2 ludovico ludovico 4096 Feb 2 22:33 .\r\n1973368 drwx------ 3 ludovico ludovico 4096 Feb 2 22:33 ..\r\n1973377 -rw------- 1 ludovico ludovico 2901 Feb 2 22:33 cwallet.sso\r\n1973376 -rw------- 1 ludovico ludovico 0 Feb 2 22:33 cwallet.sso.lck\r\n1973375 -rw------- 1 ludovico ludovico 2856 Feb 2 22:33 ewallet.p12\r\n1973373 -rw------- 1 ludovico ludovico 0 Feb 2 22:33 ewallet.p12.lck<\/pre>\nImmagine that you’ve a user created with a very complex password:<\/p>\n
SQL > create user batch identified by sDGVA_NNZBNsdRiW2h9mSyzbqBYJo;\r\n\r\nUser created.<\/pre>\nThen you need to insert these credentials, including the connect string, into the wallet.<\/p>\n
$ mkstore -wrl \/home\/ludovico\/.wlt -createCredential PROD batch<\/pre>\nOracle Secret Store Tool : Version 12.1.0.1\r\nCopyright (c) 2004, 2012, Oracle and\/or its affiliates. All rights reserved.\r\n\r\nYour secret\/Password is missing in the command line\r\nEnter your secret\/Password: \r\n\r\nRe-enter your secret\/Password: \r\n\r\nEnter wallet password:\r\n\r\nCreate credential oracle.security.client.connect_string1<\/pre>\nKeep in mind that you can have multiple credentials in the wallet for different remote descriptors (connect strings), but if you want many credentials for the very same connect string you need to create different wallets in different directories.<\/p>\n
Now you need to tell your Oracle Client to use that wallet by using the wallet_location parameter in your sqlnet.ora, so you need to have a private TNS_ADMIN:<\/p>\n
$ ls -l $TNS_ADMIN\r\ntotal 8\r\n-rw-r--r-- 1 ludovico ludovico 169 Feb 2 22:47 sqlnet.ora\r\n-rw-r----- 1 ludovico ludovico 1751 Feb 2 22:45 tnsnames.ora\r\n\r\n$ cat \u00a0$TNS_ADMIN\/sqlnet.ora\r\nWALLET_LOCATION= (SOURCE= (METHOD=file) (METHOD_DATA= (DIRECTORY=\/home\/ludovico\/.wlt)))\r\nSQLNET.WALLET_OVERRIDE = TRUE\r\nSSL_CLIENT_AUTHENTICATION = FALSE\r\nSSL_VERSION = 0<\/pre>\nIf everything’s alright, you should be able to connect to the database PROD as the batch user, without specifying any username or password.<\/p>\n
$ sqlplus \/@PROD\r\n\r\nSQL*Plus: Release 12.1.0.1.0 Production on Sun Feb 2 22:50:49 2014\r\n\r\nCopyright (c) 1982, 2013, Oracle. All rights reserved.\r\n\r\nConnected to:\r\nOracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production\r\nWith the Partitioning, OLAP, Advanced Analytics and Real Application Testing options\r\n\r\nSQL> show user\r\nUSER is \"BATCH\"<\/pre>\nAttention<\/strong>: when mkstore modifies the wallet, only the clients with the same or above versions will be able to access the wallet, so if you access your wallet with a 11g client you shouldn’t modify the wallet with a 12c version of mkstore. This is not documented by Oracle, but you can infer it from different “not a bug” entries on MOS \ud83d\ude42<\/p>\nProxy Users
\n<\/strong>You can push things a little farther, and hook your wallet with a proxy user, in order to connect to arbitrary users. That’s it, a proxy user is entitled to connect to the database on behalf of other users. In this example, we’ll see how, through the batch account, we can connect as OE, SH or HR:<\/p>\nSYSTEM@PROD > alter user OE grant connect through BATCH;\r\n\r\nUser altered.\r\n\r\nSYSTEM@PROD > alter user HR grant connect through BATCH;\r\n\r\nUser altered.\r\n\r\nSYSTEM@PROD > alter user SH grant connect through BATCH;\r\n\r\nUser altered.\r\n\r\nSYSTEM@PROD > select proxy, client from dba_proxies;\r\n\r\nPROXY CLIENT\r\n---------- --------------------\r\nBATCH HR\r\nBATCH SH\r\nBATCH OE<\/pre>\nNow I can specify with which user I want to work on the DB, connect to it through the batch account, without specifying the password thanks to the wallet:<\/p>\n
<\/p>\n
$ sqlplus [SH]@PROD\r\n\r\nConnected to:\r\nOracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production\r\nWith the Partitioning, OLAP, Advanced Analytics and Real Application Testing options\r\n\r\nSQL > show user\r\n\r\nUSER is \"SH\"\r\nSQL > connect [HR]@PROD\r\nConnected.\r\n\r\nSQL > show user\r\nUSER is \"HR\"\r\nSQL> connect [OE]\/@PROD\r\nConnected.\r\n\r\nSQL> show user\r\n\r\nUSER is \"OE\"\r\nOE@PROD > select sys_context('USERENV','PROXY_USER') from dual;\r\n\r\nSYS_CONTEXT('USERENV','PROXY_USER')\r\n----------------------------------------------------------------\r\nBATCH<\/pre>\nSee how it’s easy? But don’t forget to keep your wallet secure using unix\/windows permissions!<\/p>\n","protected":false},"excerpt":{"rendered":"
Very often I encounter customers that include Oracle account passwords in their scripts in order to connect to the database. For DBAs, sometimes things are easier when they run scripts locally using the oracle account, since the “connect \/ as … Continue reading →<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"om_disable_all_campaigns":false,"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"footnotes":""},"categories":[336,326,3,330,132],"tags":[145,144,147,22,146,143],"class_list":["post-552","post","type-post","status-publish","format-standard","hentry","category-devops","category-oracle","category-oracledb","category-oracle-inst-upg","category-triblog","tag-authentication","tag-external-store","tag-hardening","tag-oracle-database","tag-security","tag-wallet"],"aioseo_notices":[],"_links":{"self":[{"href":"https:\/\/www.ludovicocaldara.net\/dba\/wp-json\/wp\/v2\/posts\/552","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.ludovicocaldara.net\/dba\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.ludovicocaldara.net\/dba\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.ludovicocaldara.net\/dba\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.ludovicocaldara.net\/dba\/wp-json\/wp\/v2\/comments?post=552"}],"version-history":[{"count":4,"href":"https:\/\/www.ludovicocaldara.net\/dba\/wp-json\/wp\/v2\/posts\/552\/revisions"}],"predecessor-version":[{"id":556,"href":"https:\/\/www.ludovicocaldara.net\/dba\/wp-json\/wp\/v2\/posts\/552\/revisions\/556"}],"wp:attachment":[{"href":"https:\/\/www.ludovicocaldara.net\/dba\/wp-json\/wp\/v2\/media?parent=552"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.ludovicocaldara.net\/dba\/wp-json\/wp\/v2\/categories?post=552"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.ludovicocaldara.net\/dba\/wp-json\/wp\/v2\/tags?post=552"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}
![\"\"](\"http:\/\/upload.wikimedia.org\/wikipedia\/commons\/f\/f0\/Secret_Service_agents_stand_guard.jpg\")