Use Oracle's security External Password Store feature to implement a password-free login database

Use Oracle's security External Password Store feature to implement a password-free login database

Author: Zhao Full text network name: Guestart

In this year, many users of the Oracle database suddenly suffered from Bitcoin ransomware, in this regard, the security of the database, we have not attracted enough attention. It's not hard to see that the Oracle database we use in our production environment does have a lot of security risks and security implications. Oracle also has many database security solutions, such as Oracle Audit and database Firewall (AVDF) products, Oracle's Transparent Data encryption (KDE) and Oralce Wallet (also known as Oracle Wallet) to encrypt user passwords, and more.

If you need to connect to an Oracle database from a shell script, these scripts contain database connection details, which can be a major security issue. One solution is to use operating system authentication, but Oracle 10g version 2nd provides the ability to use secure external password storage. Where the Oracle login certificate is stored in the client Oracle wallet, you can use the syntax "/@ db_alias" in the shell script to connect. This is very useful to use the script to log into the database, especially for the enterprise security requirements are very high, do not want the user name and password plaintext in the configuration file, and for password maintenance is very convenient, such as put wallet under the specified path, when changing the password, only need to uniformly cover wallet.

Today I share with you how to use the Oracle Security External Password Store feature to implement a password-free login database so that plaintext passwords are not exposed in the production environment.

So, how is oracle using secure external password storage (secure External Password store) to reach the password-free login database? For us, the password certificate attached to the database is stored in Oracle wallet, this wallet (wallet) is a security software container that is used to store authentication and signing certificates. This wallet can be used to simplify large-scale deployments that rely on password credentials to connect to a database. When you configure this feature, the application code, batch jobs, and scripts no longer require an embedded user name and password. Risk is reduced because such passwords are no longer exposed in a clear way, and password management policies are easier to implement without changing the application code when usernames or passwords change.

Since the cipher ciphertext information stored in the "Secure external password store" is stored in Oracle wallet, let's start by introducing what is Oracle Wallet and what information can be stored in it? Please see the following paragraph in English description, 

Chinese translation is as follows,

The Oracle Wallet is a PKCS#12 container for storing different types of authentication and encryption keys. Therefore, this wallet can be used to store one or more of the following information:

• PKI authentication credentials for Oracle databases

• Network encryption Certificate (SSL/TLS)

• Master encryption key for Oracle advanced secure Transparent Data encryption (TDE)

• Password for Oracle database secure external password store

To put it bluntly, Oracle wallet can be figuratively likened to our everyday wallets, where we can place cards, ID cards, credit cards, company employee cards, dining cards, and so on.

Read the above section of English description and Chinese translation, we must have some knowledge of Oracle wallet. So what exactly is Oracle's secure External Password store (secure external password store)? Let's look at the following paragraph of English description,

Chinese translation is as follows,

With secure external password storage, Oracle securely stores database credentials (that is, user names and Passwords) in Oracle Wallet. When you start a database connection, Oracle accesses the wallet and reads the credentials based on the connection string. Because automatic logon is configured, you can open your wallet and read credentials without a password. A password is required only if you add, change, or remove credentials in your wallet.

The connection string is unique in the e-purse. Only one voucher can be stored per connection string. Different credentials for the same database must be differentiated by different connection strings.

From the Chinese interpretation above, we can tell that the secure external password store is a secure encryption form in which Oracle stores usernames and passwords in Oracle Wallet.

Below we demonstrate and operate according to the secure External Password store feature in the Oracle 10GR2 official documentation, and eventually implement a password-free login database.

While it is said that using Wallet's graphical interface (which can be opened under command line interface) can greatly simplify the management of password certificates, as for why OWM (Oracle Wallet Manager) is not available To create and manage wallet external password stores, instead of using the Mkstore command? Here is a description from the official Oracle documentation:

In general, users (including applications, batch tasks, and scripts) are connected to the Oracle database through a connection statement (Connect statement) of a standard database connection string (database_connect_string). These strings include the user name, password, and network service name, or the TNS alias listed in the Tnsnames.ora file, and another connection string in the form of hostname: Port number: sid (This form is ubiquitous in applications connected to the Oracle database).

For example, the following form of connection:

Where Orasales is the TNS alias, OURHOST37:1527:DB17 is the hostname: Port number: SID.

However, if the client is configured with a secure external password store, you can use the following connection syntax to connect to the database without specifying a user name and password.

In this case, the database certificate, user name, and password are stored securely in the Oracle wallet that was created because wallet's automatic login feature (auto login) is turned on, that is, once the wallet is created, it is automatically opened, So you don't need a password to open wallet. There is a certificate in this wallet, and the information of the user name and password used to connect to the database is stored in this certificate.

Next, we'll start using an external password store to configure the client for Oracle.

1. Check the location and status of the default wallet directory for Oracle Software

As can be seen, the type of wallet is in the form of a file, and the default wallet is located in the $oracle_base/admin/$ORACLE _sid/wallet directory, the state is closed. So, let's go to the appropriate location to see if the Wallet directory exists,

As you can tell, the wallet directory does not exist, because we have never created wallet, its status should be closed, so we are using the Mkstore command (mentioned earlier in the OWM to create wallet) before creating wallet, You must first create an existing wallet directory. Here for the sake of security, but also to prevent the wallet directory mistakenly deleted, we do not put the wallet directory in the V$encryption_wallet view just detected location, that is, $oracle_base/admin/$ORACLE _sid, but put to $ ORACLE_HOME/OWM (because $oracle_home is the directory where ORACLE software is located, and we are not allowed to change directories and files here).

The following creates the wallet directory,

2. Open the external password store (external password store) on the Oracle client.

(1) Use the Mkstore command to create a wallet with command syntax from official documents

The wallet_location parameter is specified as the Wallet directory we just created

Above hint, want to give wallet enter a password (need input when wallet open, after creation Wallet, Oracle will open automatically), here enter Oracle123 (input password does not echo OH), enter again again (same does not echo, if 2 times input of different, Oracle will prompt for re-entry).

So, let's look under the wallet directory to see what files are generated.

Where the Cwallet.sso file is used to save wallet automatic login information, EWALLET.P12 file is used to save the relevant certificate information, and we want to use the user name and password information is stored in the certificate.

(2) Use the Mkstore command to create a certificate for the wallet you just created, with the command syntax coming from an official document

With the parameters of <>, the official documentation has been explained very clearly, let us describe again.

Wallet_location:wallet Directory Location

Db_connect_string: TNS alias in File Tnsname.ora

Tnsnames.ora: The file that holds the Oracle database connection string, and Oracle's established file, has been used since the birth of Oracle, and its importance is evident. Under the Linux system, located in the $oracle_home/network/admin directory

Username:oracle the user name to connect to the database

Password:oracle connect the user to enter the password

In order not to be associated with the TNS aliases originally created by Oracle in the Tnsnames.ora file, we create a separate one.

Where base is the TNS alias we just created, and Ysyktest is the previously existing TNS alias.

Now start using the Mkstore command to create the wallet certificate, see

When you create a certificate, you need to enter a password for wallet, and the password you entered when you created wallet in step 1th.

Where base is the TNS alias you just created, and the user name and password must be entered correctly, or even if the creation succeeds, the Oracle database is not even in a moment.

In fact, with the Mkstore command to create a wallet certificate, you can also do not write the user's password on the command line, but according to the command prompt input (input password does not echo), see the Operation:

Wherein, the first two entered the password for the user szd_base_v2 password ysyktest, the third time entered the password is wallet password Oracle123.

Above is the certificate that created the wallet, in order for ORACLE to be able to use wallet, it must also be in the Sqlnet.ora file (like Tnsnames.ora, also in the $oracle_home/network/admin directory) Specify the Wallet_location parameter and the Sqlnet.wallet_override parameter. The 3rd step is entered below,

(3) Add wallet_location parameter and sqlnet.wallet_override parameter in Sqlnet.ora file, reference syntax from official document

Wallet_location = (SOURCE = (METHOD = FILE) (Method_data = (DIRECTORY =/private/ora102/network/admin)))

among them, directory is the WALLET directory in the wallet_location parameter. The following 2 parameters are added to the Sqlnet.ora file,

Enter the $oracle_home/network/admin directory and discover that there are no sqlnet.ora files, and it is possible that you have not configured Sqlnet.ora files. Then we use the NETCA command to generate the Sqlnet.ora file under the graphical interface, and then add the 2 parameters.

Select the 2nd radio button, name the method configuration, and go to the next step

Add the local naming to the right-hand blank pane and finish with the last point. Then, the Sqlnet.ora file will be generated in the $oracle_home/network/admin directory at this time.

After the Sqlnet.ora file has just been created, the contents are only the following lines,

now add the 2 parameters of Wallet_location and Sqlnet.wallet_override,

The secure external password store for the Oracle client is now configured.

To simplify the procedure, you can also combine the steps (1) and (2) in one step to create the wallet and its certificate directly, as shown in the following:

Wherein, the first two entered the password for wallet itself password, here input Oracle123, after two times entered password for the user szd_base_v2 password ysyktest.

Now, we are trying to connect the Oracle database with the connection string of base, to see the login user as SZD_BASE_V2, so as to achieve the purpose of logging into the database without losing the password.

Then, using SZD_BASE_V2 users to connect to the Oracle database is the same effect.

Now let's look at the changes in the contents of the View V$encryption_wallet,

The following is the contents of the view V$encryption_wallet before using the security External Password store (previously mentioned before creating wallet, hehe)

By comparing the above 2 graphs, it is not difficult to see that Oracle now uses the wallet we have just created, and that the wallet is open and in use.

If the user szd_base_v2 password changes, but also need to change the certificate information in the wallet, or the certificate is stored in the old password information, when connected to the Oracle database will be an error. Below the user szd_base_v2 password to ysykbase, and then use sqlplus/@base login, see what error.

Now, we use the Mkstore command to change the certificate in the wallet, and then use the sqlplus/@base log in, the connection will be normal.

Where the first two entered the password for the user just changed szd_base_v2 password, the third time entered the password is wallet password.

In addition to using Sqlplus to log directly into the Oracle database, you can use it in importing and exporting data and some shell scripts for daily maintenance of the database, without exposing plaintext passwords, which can eliminate certain security risks. For example, the following 1 EXPDP scripts and a shell script.

This is done with the Oracle Secure External Password store feature without password logon database demo.

Reference URL:

Official Document http://docs.oracle.com/cd/B19306_01/network.102/b14266/cnctslsh.htm#g1033548

Stefan Oehrli's blog http://www.oradba.ch/2014/07/secure-external-password-store-for-rman/

If you feel that this article is helpful to you, please follow the public number: Guestart's DBA study notes, your support is my greatest encouragement!

This article from the "Blue Melancholy" blog, reproduced please contact the author!

Use Oracle's security External Password Store feature to implement a password-free login database