oracle database encryption methods

Oracle Database File Encryption Explained

In the Oracle database system, if a user wants to log in to the Oracle database as a privileged user, he or she can have two authentication methods, i.e., using the authentication integrated with the operating system or using the Oracle database’s password file for authentication. It is important to manage the password file to control authorized users logging on to the Oracle database system from remote or local locations to perform database administration

The Oracle database password file stores the password of the superuser INTERNAL/SYS and other privileged users’ usernames/passwords, and it is usually stored in the ORACLE_HOME/DATABASE directory

Creating a password file

When you create a database instance using OracleInstanceManager, a corresponding password file is automatically created in the ORACLE_HOME\DATABASE directory under the name PWDSIDORA, where SID stands for the corresponding Oracle database system identifier. The password file is the basis for the initial database management work after which the administrator can also use the tool ORAPWDEXE to manually create the password file as needed with the following command format


The meanings of the parameters of the commands are

FILENAME password file name;

PASSWORD sets the password for the INTERNAL/SYS account

The MAX_USERS password file can hold the The maximum number of users that can be stored in the MAX_USERS password file corresponds to the maximum number of users that are allowed to log in to the database with SYSDBA/SYSOPER privileges. Since the password file will need to be rebuilt in the event that the number of users exceeds this limit during future maintenance, this parameter can be set to be as large as necessary

After the password file, the initialization parameter REMOTE_LOGIN_PASSWORDDFILE needs to be set to set the password file to be the password of the SYS account. PASSWORDFILE to control the use of the password file

Setting the Initialization Parameter

In the initialization parameter file for an Oracle database instance, this parameter controls the use of the password file and its status, and it can have the following options

NONE instructs the Oracle system not to use the password file to privilege user logins through the The operating system performs authentication

EXCLUSIVE indicates that only one instance of the database can use the password file, and only with this setting can the password file contain information about users other than INTERNAL/SYS, i.e., system privileges SYSOPER/SYSDBA are allowed to be granted to users other than INTERNAL/SYS

SHARED indicates that multiple database instances can use this password file. With this setting, only the INTERNAL/SYS account can be recognized by the password file, and even if the file contains information about other users, they will not be allowed to log in with SYSOPER/SYSDBA privileges. PASSWORDFILE parameter is set to EXCLUSIVESHARED in the case of the Oracle system to search for the password file in the order of the system registry library to find the value of the ORA_SID_PWFILE parameter (which is the full pathname of the password file); if not found, then look for the value of the ORA_PWFILE parameter; if it is still not found, then the use of the default values of ORACLE_HOME\DATABASE\PWDSIDORA; where SID stands for the corresponding Oracle database system identifier

Adding and deleting users to the password file

When the initialization parameter REMOTE_LOGIN_PASSWORDFILE is set to EXCLUSIVE, the system permits Users other than INTERNAL/SYS to log on to the Oracle database system as administrators, either remotely or locally, to perform database administration tasks; these user names must be present in the password file for the system to recognize them because the password file, whether it is created automatically during the creation of the database instance or manually using the ORAPWDEXE utility, only contains the names of INTERNAL/SYS user information; for this reason, in practice, it may be necessary to add or delete other user accounts to the password file

Since only users who have been granted SYSOPER/SYSDBA system privileges exist in the password file, so when a user is granted or withdrawn from the SYSOPER/SYSDBA system privileges, their account will be added to the password file or removed from the password file accordingly. So when SYSOPER/SYSDBA privileges are granted or withdrawn to a user, their account will be added to or deleted from the password file accordingly. Adding or deleting a user to or from the password file is in effect granting or withdrawing SYSOPER/SYSDBA privileges to that user

To perform this authorization, you need to be connected to the database with SYSDBA privileges (or an INTERNAL account) and the initialization parameter REMOTE_LOGIN_PASSWORD LOGIN_PASSWORDFILE must be set to EXCLUSIVE The specific steps are as follows

Create the appropriate password file;

Set the initialization parameter REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE;

Log in with SYSDBA privileges. CONNECTSYS/internal_user_passeswordASSYSDBA;

Start the database instance and open the database;

Create the appropriate user accounts to grant permissions to their authorizations (both SYSOPER and SYSDBA) GRANTSYSDBATOuser_name;

Create the appropriate user accounts to grant permissions to their authorizations (both SYSOPER and SYSDBA). p>

Take back the privileges REVOKESYSDBAFROMuser_nameThese users can now log in to the database system as administrators

Logging in with a password file

With a password file in place the user can use the password file to log in to an instance of the Oracle database with SYSOPER/SYSDBA privileges. The initialization parameter REMOTE_LOGIN_PASSWORDFILE should be set to EXCLUSIVE or SHARED Any user logged in with SYSOPER/SYSDBA privileges will be located under the SYS user’s Schema The following are two examples of logins

Logging in as an administrator

Assuming that user Scott has been granted SYSDBA privileges then he can log in using the following commands


Log in as INTERNAL


Protecting the password file

Viewing members of the password file

You can query the view V$PWFILE_USERS to get information about users with SYSOPER/SYSDBA system privileges The value of the SYSOPER/SYSDBA column in the table, TRUE/FALSE, indicates whether or not the user has the corresponding privileges. These users are the corresponding members of the password file

Modifying the Status of the Password File

Information about the status of the password file is stored in this file, which has a default status of SHARED when it is created. The status of the password file can be changed by changing the setting of the initialization parameter REMOTE_LOGIN_PASSWORDFILE. When starting a database instance Oracle system reads the settings of the REMOTE_LOGIN_PASSWORDFILE parameter from the initialization parameter file; when loading the database the system compares this parameter with the state of the password file if different then update the state of the password file If you plan to allow the startup of a database instance from more than one client due to the fact that there must be an initialization parameter file on each client machine so you should Ensure that the initialization parameter file is consistent across clients to avoid accidentally changing the state of the password file and causing the database login to fail

Modifying the password file storage location

The location of the password file can be moved as needed, but this change should be made by modifying the system registry’s parameter or environment variable that points to the location of the password file

Deleting the password file

The password file can be deleted from the database if the database instance is planned to be started from multiple clients. p>

Delete the password file


How to Implement Transparent Data Encryption in oracle10gr2

Setting Encryption Keys:

Oracle Transparent Data Encryption provides the key management infrastructure necessary to implement encryption. Encryption works by passing plaintext data, along with a secret (called a key), to an encryption program. The encryption program encrypts the plaintext data using the provided key and then returns the encrypted data. Previously, the task of creating and maintaining the key was left to the application program.Oracle Transparent Data Encryption solves this problem by automatically generating a universal key for the entire database. When starting an Oracle database, the administrator must open an OracleWallet object with a different password than the system password or DBA password. The administrator then initializes the database universal key. The universal key is generated automatically.


Encryption typically affects existing application indexes because index data is not encrypted.Oracle Transparent Data Encryption encrypts the index values associated with a given application table. This means that equal searches in the application have little or no impact on performance. For example, suppose an index exists for the application card_id and this application executes the following statement:


Oracle Database will use the existing application indexes , even though the card_id information is already encrypted in the database.

Preparing the database for encryption:

In this section, you will update sqlnet.ora, create an encryption money folder (ewallet.p12), open this money folder, and create the universal key for TDE. Do the following:

1. You need to update the sqlnet.ora file to include an ENCRYPTED_WALLET_LOCATION entry. Open a terminal window and enter the following command:



Add the following entry to the end of the file:


If you don’t add this entry, the following error will be prompted:

SQL&gt. altersystemsetkeyidentifiedby “hurray”


altersystemsetkeyidentifiedby “hurray”



ORA- 28368:cannotauto-createwallet

The /opt/oracle/admin/test97/wallet/ directory is used to store the generated wallet.

Any directory can be chosen for the encrypted wallet, but the path should not point to the standard fuzzy wallet (cwallet.sso) created during the database installation.

2. Next, you need to open the money clip and create the universal encryption key. From a terminal window, enter the following command:

connect/assysdbaaltersystemsetkeyidentifiedby “welcome1”;

This command does the following:

l If the encrypted money clip doesn’t exist in the specified directory, the encrypted money clip will be created ( ewallet.p12), opens this wallet, and creates/re-created the TDE universal key.

l If the ewallet exists in the specified directory, the ewallet will be opened and the universal key for TDE will be created/recreated.

After that, you can test the data.

Here’s the lab log:

altersystemsetkeyidentifiedby “welcome1”;


createtabletest(idnumber,credit _card_numbervarchar2(16)ENCRYPTNOSALT);

SQL> insertintotestvalues(1,’1231243242′);


SQL&gt. insertintotestvalues(2,’33245235′);








As you can see, the data view is in plaintext because at this point, the encrypted money clip is open and the data can be decrypted.

At this point, stop the database and open it again:


















ORA-28365. walletisnotopen






As you can see, because the encrypted money folder is in the closed state after the database restart, this time as soon as the you query the encrypted columns, you will be prompted that the encrypted money clip is not open.

If a user wants to open the wallet, they must have altersystem privileges.

The following opens the wallet:



SQL>altersystemsetwalletopenidentifiedby “welcome1”;






——- ——————-



As you can see, the encrypted money clip opens and the data can be decrypted.

One more thing: the table of the sys user cannot be encrypted.

As you can see, OracleTDE encrypts data in tables at the data level and does not affect the existing privilege control policies of the database.

salt actually introduces a randomness to the encryption process. Simply put, it means that in general, the same plaintext produces the same ciphertext, which leads to easy decryption by the decryptor by analyzing the word frequency and so on (encryption and decryption I do not quite understand) to crack the plaintext through the ciphertext, if you specify salt, then even if the same plaintext encrypted ciphertext is not the same.

nosalt, naturally, is the same plaintext will produce the same ciphertext. It is understandable that nosalt is required for indexes

If you lose your ewallet encryption wallet, you can no longer decrypt the data.

Oracle10gR2’s TDE feature can play a role in preventing the leakage of confidential information!

Seek OracleMD5 encryption, and java program processing steps

Today I just made a encrypted program: share:

You can do this: you can create a new password to call the following method to bring the password encrypted at this time to the database to insert the encrypted field. When you log in, you enter the password, which of course is converted into an encrypted ciphertext, which matches the ciphertext you added at the beginning, and if it matches, then you can log in.


byte[]b=md.digest(here is your encrypted password.getBytes(“utf8”));



ret.append(Character.forDigit((b[ i]>>4)&0xf,16));







Stringtmp=Integer. toHexString(b[i]&0xFF);










oracle field encryption

This mainly depends on the encryption method of the software developer

The general process is,

1. when a new user is created, or when a password is changed, the password entered by the user is encrypted according to a certain encryption method and then stored in the database

2. when a user logs in, the password entered by the user is encrypted again and compared with the encrypted password in the database. Comparison with the encrypted password in the database

The advantage of doing so is to prevent the password from being intercepted during network transmission, for example, hackers can analyze the data transmitted over the network by grabbing the packets, and for the encrypted password is not transmitted in clear text during transmission, if the data is stolen, it is useless if the data can’t be cracked efficiently, which increases the reliability.

How to create encrypted stored procedures in the database

Oracle can be used as an example of its own Wrap tool to achieve this tool in the $ ORACLE_HOME/BIN directory, the specifics are as follows

1, set the character set environment variables


2, create a stored procedure file

In E:\ under the creation of test.sql file, the content is as follows:



dbms_ output.put_line(‘Input parameter is ‘||to_char(i));


3. Encryption






4. Run test1.plb



SQL>@e:\a. plb

Procedure created.

5. Run the stored procedure



The input parameter is 1

The PL/SQL procedure has completed successfully.

6. View the code of the stored procedure


Results: its original code is not visible