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
C:\>ORAPWDFILE=<FILENAME>PASSWORD=<PASSWORD>. ENTRIES=<MAX_USERS>
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
CONNECTscott/tigerASSYSDBA
Log in as INTERNAL
CONNECTINTERNAL/INTERNAL_PASSWORD
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
lishixin/Article/program/Oracle/201311/16762
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.
Performance:
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:
SQL>Selectcashfromcredit_cardwherecard_id=’1025023590′;
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:
cd$ORACLE_HOME/network/admin
geditsqlnet.ora
Add the following entry to the end of the file:
ENCRYPTION_WALLET_LOCATION=( SOURCE=(METHOD=FILE)(METHOD_DATA=(DIRECTORY=/opt/oracle/admin/test97/wallet/)))
If you don’t add this entry, the following error will be prompted:
SQL>. altersystemsetkeyidentifiedby “hurray”
2;
altersystemsetkeyidentifiedby “hurray”
*
ERRORatline1:
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”;
SQL>condodd/dodd123
createtabletest(idnumber,credit _card_numbervarchar2(16)ENCRYPTNOSALT);
SQL> insertintotestvalues(1,’1231243242′);
1rowcreated.
SQL>. insertintotestvalues(2,’33245235′);
SQL>commit;
Commitcomplete.
SQL>select*fromtest;
IDCREDIT_CARD_ NUMB
————————–
11231243242
233245235
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:
SQL>shutdownimmediate
Databaseclosed.
Databasedismounted.
ORACLEinstanceshutdown.
SQL>SQL>startup
ORACLEinstancestarted.
TotalSystemGlobalArea524288000bytes
FixedSize1979968bytes
VariableSize138414528bytes
DatabaseBuffers377487360bytes
RedoBuffers6406144bytes
Databasemounted.
Databaseopened.
SQL>select*fromdodd.test;
select*fromdodd.test
*
ERRORatline1:
ORA-28365. walletisnotopen
SQL>selectidfromdodd.test;
ID
———-
1
2
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>conn/assysdba
Connected.
SQL>altersystemsetwalletopenidentifiedby “welcome1”;
Systemaltered.
SQL>conndodd/dodd123
Connected.
SQL>select*fromtest;
IDCREDIT_CARD_NUMB
——- ——————-
11231243242
233245235
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.
MessageDigestmd=MessageDigest.getInstance(“MD5”);
byte[]b=md.digest(here is your encrypted password.getBytes(“utf8”));
StringBuilderret=newStringBuilder(b.length<<1);
for(inti=0;i<b.length;i++){
ret.append(Character.forDigit((b[ i]>>4)&0xf,16));
ret.append(Character.forDigit(b[i]&0xf,16));
}
ret.toString();
System.out.println(ret.toString());
Stringresult=””;
for(inti=0;i<b.length;i++){
Stringtmp=Integer. toHexString(b[i]&0xFF);
if(tmp.length()==1){
result+=”0″+tmp;
}else{
result+=tmp;
}
}
}
p>System.out.println(newString(result));
}
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
setNLS_LANG=CHINESE_CHINA. ZHS16GBK
2, create a stored procedure file
In E:\ under the creation of test.sql file, the content is as follows:
createorreplaceproceretest1(iinnumber)as
begin
dbms_ output.put_line(‘Input parameter is ‘||to_char(i));
end;
3. Encryption
c:\>cd%ORACLE_HOME%\BIN
c:\>wrapiname=e:\test1.sql
PL/SQLWrapper:Release8.1.7.0.0-ProctiononTueNov2722:26:482001
Copyright(c)OracleCorporation1993,2000.AllRightsReserved.
Processingtest1.sqltotest1.plb
4. Run test1.plb
sqlplus/nolog
SQL>connTEST/TEST@db01
SQL>@e:\a. plb
Procedure created.
5. Run the stored procedure
SQL>setserveroutputon
SQL>executetest1(1);
The input parameter is 1
The PL/SQL procedure has completed successfully.
6. View the code of the stored procedure
SQL>selectname,textfromall_sourcewheretype=’PROCEDURE’andname=’TEST1′;
Results: its original code is not visible