Shulou(Shulou.com)06/01 Report--

Using Security External Password Store function of Oracle to realize password-free login to database

Author: Zhao full text net name: guestart

This year, many domestic users' Oracle databases were suddenly blackmailed by bitcoin. In this respect, the security of the database has not attracted enough attention. In fact, it is not difficult to find that there are many security risks and risks in the Oracle database we use in the production environment. Oracle also has many solutions in database security, such as Oracle Audit and Database Firewall (AVDF) products, Oracle's transparent data encryption function (KDE) and Oralce Wallet (also known as Oracle wallet) encrypting users' passwords and so on.

If you need to connect to an Oracle database from SHELL scripts, 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 2 provides the ability to use secure external password storage. The Oracle login certificate is stored in the client-side Oracle e-wallet, so you can use the syntax "/ @ db_alias" to connect in the SHELL script. This is very useful for using scripts to log in to the database for operations, especially for enterprises with high security requirements, do not want the user name and password plaintext to exist in the configuration file, and for password maintenance is extremely convenient, such as putting wallet in the specified path, when changing the password, you only need to uniformly cover wallet.

Today I would like to share with you how to use Oracle's Security External Password Store feature to log in to the database without a password so as not to expose plaintext passwords in a production environment.

So how does Oracle achieve password-free login to the database through secure external password storage (Secure External Password Store)? For us, the password certificate connected to the database is stored in Oracle wallet, the wallet (wallet) is a secure software container for storing authentication and signing certificates. The use of this wallet simplifies large-scale deployments that rely on password credentials to connect to the database. When configuring this feature, application code, batch jobs, and scripts no longer require embedded usernames and passwords. The risk is reduced because such passwords are no longer exposed in an explicit manner, and when the user name or password changes, the password management policy is easier to implement without changing the application code.

Because the ciphertext information stored in "secure external password storage" is stored in Oracle wallet, let's first introduce what is Oracle wallet and what information can be stored in it? Please see the following English description

The Chinese translation is as follows

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

PKI authentication credentials for the Oracle database

Network encryption Certificate (SSL/TLS)

Master encryption key for Oracle Advanced Security transparent data encryption (TDE)

Oracle database security password stored externally

To put it bluntly, Oracle wallet can be vividly likened to the wallet in our daily life. In the wallet, we can put bank cards, credit cards, company employee cards, meal cards and so on.

After reading the English description and Chinese translation above, you must know something about Oracle wallet. So what exactly is Oracle's Secure External Password Store (secure external password Storage)? Let's take a look at the following English description.

The Chinese translation is as follows

Using secure external password storage, Oracle securely stores database credentials (that is, usernames and passwords) in Oracle e-wallets. When you start a database connection, Oracle accesses the wallet and reads the credentials based on the connection string. Because automatic login is configured, you can open the wallet and read the credentials without a password. A password is required only if you add, change, or delete credentials in an e-wallet.

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

From the Chinese interpretation above, we can see that secure external password storage is a secure encrypted form in which Oracle stores usernames and passwords in Oracle wallet.

Let's demonstrate and operate according to the Secure External Password Store function in the official Oracle 10gR2 documentation, and finally log in to the database without a password.

Although using wallet's graphical interface (which can be opened by entering the owm command under command line interface) can greatly simplify the management of password certificates, why can't you use owm (oracle wallet manager) to create and manage wallet's external password store, but use the mkstore command instead? The following is a description taken from the official Oracle documentation:

In general, users (including applications, batch tasks, and scripts) connect to the Oracle database through a standard database connection string (database_connect_string) connection statement (connect statement). These strings include the user name, password, and network service name, or the TNS alias listed in the tnsnames.ora file, and there is another connection string in the form of the host name: sid (this form can be found everywhere when the application connects to the Oracle database).

For example, the following form of connection:

Where ORASALES is the TNS alias and ourhost37:1527:DB17 hostname: port number: sid.

However, if the client is configured with secure external password storage, 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 securely stored in the created Oracle wallet, and because wallet's automatic login feature (auto login) is turned on, that is, once the wallet is created, it is automatically opened, so there is no need for a password to open wallet. There is a certificate in this wallet, and the user name and password information used to connect to the database is stored in this certificate.

Next, we start using external password storage to configure the client of Oracle.

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

As you can see from the figure above, the type of wallet exists as a file, and the default wallet is located in the $ORACLE_BASE/admin/$ORACLE_SID/wallet directory with the status turned off. So, let's go to the appropriate location to see if the wallet directory exists.

As you can see from the figure above, the wallet directory does not exist, because we have never created wallet, and its state should be closed, so we must create an existing wallet directory before we can create wallet with the mkstore command (mentioned earlier when we said you can't create wallet with owm). For the sake of security and to prevent the wallet directory from being deleted by mistake, we don't put the wallet directory under $ORACLE_BASE/admin/$ORACLE_SID where we just checked it with the v$encryption_wallet view, but $ORACLE_HOME/owm (because $ORACLE_HOME is the directory where the Oracle software is located, and we are not allowed to change the directories and files here).

Let's create the wallet directory

2. Enable external password store (external password storage) in the Oracle client.

(1) use the mkstore command to create a wallet first, and the command syntax comes from the official document.

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

The above prompt is to enter a password for wallet (you need to enter it when wallet is opened, and Oracle will open automatically after creating wallet), enter Oracle123 here (the password entered is not echoed, oh), and enter it again (also not echoed, if the two inputs are different, Oracle will prompt you to re-enter).

So, let's go to the wallet directory to see what files have been generated.

The cwallet.sso file is used to save the information about whether the wallet is automatically logged in, the ewallet.p12 file is used to save the relevant certificate information, and the user name and password information we want to use is saved in the certificate.

(2) use the mkstore command to create a certificate for the wallet you just created. The command syntax is from the official documentation.

The parameters have been clearly explained in the official documentation, so let's describe them again.

The location of the wallet_location:wallet directory

Db_connect_string: TNS alias in file tnsname.ora

Tnsnames.ora: the file that stores the connection string of the Oracle database, which is also a file established by Oracle. It has been used since the birth of Oracle, so its importance is self-evident. On Linux, it is located in the $ORACLE_HOME/network/admin directory

The user name to connect to the username:Oracle database

The password to be entered by the password:Oracle connection user

In order not to be associated with the TNS alias that Oracle originally created in the tnsnames.ora file, let's create another one separately.

Where BASE is the TNS alias we just created, and YSYKTEST is the pre-existing TNS alias.

Now start creating a certificate for wallet with the mkstore command, as shown in the following figure

When you create a certificate, you need to enter the password of wallet, which is the password you entered when you created the wallet in step 1.

Where base is the TNS alias you just created, and the user name and password must be entered correctly, otherwise, even if the creation is successful, you will not be able to connect to the Oracle database for a while.

In fact, when you create a wallet certificate with the mkstore command, you can enter the user's password according to the command prompt instead of writing the user's password on the command line, as shown in the following figure:

Among them, the password entered the first two times is the password ysyktest of the user szd_base_v2, and the password entered the third time is the password Oracle123 of wallet.

Above is the certificate that created the wallet, and in order for Oracle to use wallet, you must also specify the WALLET_LOCATION parameter and the SQLNET.WALLET_OVERRIDE parameter in the sqlnet.ora file (which, like tnsnames.ora, is located in the $ORACLE_HOME/network/admin directory). Let's proceed to step 3.

(3) add WALLET_LOCATION parameter and SQLNET.WALLET_OVERRIDE parameter to the sqlnet.ora file. The reference syntax comes from the official document.

WALLET_LOCATION = (SOURCE = (METHOD = FILE) (METHOD_DATA = (DIRECTORY = / private/ora102/network/admin)

The value of DIRECTORY in parameter WALLET_LOCATION is wallet directory. Let's add these two parameters to the sqlnet.ora file

Enter the $ORACLE_HOME/network/admin directory and find that there is no sqlnet.ora file, maybe there is no configuration sqlnet.ora file. So we use the netca command to generate the sqlnet.ora file under the graphical interface, and then add those two parameters.

Select the second radio button, name the method configuration, and proceed to the next step.

Add the Local Naming in the above image to the blank pane on the right, and finally click finish. At this point, the sqlnet.ora file will be generated in the $ORACLE_HOME/network/admin directory.

After the sqlnet.ora file is created, it contains only the following lines

Now add the two parameters WALLET_LOCATION and SQLNET.WALLET_OVERRIDE

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

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

Among them, the password entered in the first two times is the password of wallet itself. Enter Oracle123 here, and the password entered in the last two times is the password ysyktest of user szd_base_v2.

Now, we try to use the connection string of base to connect to the Oracle database and check that the login user is szd_base_v2, thus achieving the goal of logging in to the database without entering a password.

Then, using szd_base_v2 users to connect to the Oracle database has the same effect.

Now let's take a look at the changes in the contents of the view v$encryption_wallet.

The following is the content of the view v$encryption_wallet before using Security External Password Store (mentioned earlier before creating wallet, hehe)

From the comparison of the above two figures, it is not difficult to find that Oracle now uses the wallet we just created, and that the wallet is open and in use.

If the password of the user szd_base_v2 is changed, the certificate information in the wallet needs to be changed, otherwise the old password information is still stored in the certificate, and an error will be reported when connecting to the Oracle database. Next, change the password of the user szd_base_v2 to ysykbase, and then log in with sqlplus / @ base to see what errors are reported.

Now, we use the mkstore command to change the certificate in wallet, and then log in with sqlplus / @ base, and we will connect normally.

Among them, the password entered the first two times is the password of the user szd_base_v2 that has just been changed, and the password entered the third time is the password of wallet.

In addition to directly logging into the Oracle database with sqlplus, it can also be used in importing and exporting data and in some SHELL scripts for daily maintenance of the database, and the plaintext password will not be exposed, which can eliminate certain security risks. For example, the following 1 expdp script and 1 SHELL script.

At this point, the implementation of password-free login to the database with the Secure External Password Store function of Oracle is over.

Reference website:

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

Blog http://www.oradba.ch/2014/07/secure-external-password-store-for-rman/ of Stefan Oehrli

If you think this article is helpful to you, you are welcome to follow the DBA study notes on Wechat official account: guestart. Your support is my greatest encouragement!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.