CN109886028B

CN109886028B - Method for remotely enabling Oracle transparent encryption

Info

Publication number: CN109886028B
Application number: CN201910060996.8A
Authority: CN
Inventors: 姚远; 闻建霞; 柳遵梁; 吕海波; 宁伟嘉
Original assignee: Hangzhou Meichuang Technology Co ltd
Current assignee: Hangzhou Meichuang Technology Co ltd
Priority date: 2019-01-22
Filing date: 2019-01-22
Publication date: 2020-08-25
Anticipated expiration: 2039-01-22
Also published as: CN109886028A

Abstract

The invention discloses a method for remotely starting Oracle transparent encryption, which comprises a database server, a transparent encryption management server, a plurality of application end servers and a plurality of client end hosts, wherein the database server is used for storing a plurality of application end servers; the database server is respectively in data connection with the transparent encryption management server and each application end server, and each application end server is respectively in data connection with each client end host. The invention has the advantages that the safety or usability risk caused by the Agent is avoided; the method has the characteristic of opening Oracle transparent encryption without influencing the database application under the condition of not stopping the database.

Description

Method for remotely enabling Oracle transparent encryption

Technical Field

The invention relates to the technical field of database encryption, in particular to a simple, convenient and safe remote Oracle transparent encryption method without influence on the safety and usability of data.

Background

The Oracle database is the most used database now, and the Oracle database 10g version 2 starts to support transparent database encryption functions. The function is an important safety protection function in the database by encrypting the bottom data under the condition of not influencing the upper application, and can effectively prevent hackers from invading a host, stealing data files of the database in an abnormal way and obtaining user data from the data files. Is a common function in the case of high safety requirements.

Enabling transparent data encryption functions requires several Oracle-specific commands to be executed. The existing method mainly comprises the following steps:

logging in a host of an Oracle database, and executing a corresponding command.

And installing the Agent on the Oracle database host, and executing a corresponding command through the Agent.

Logging on to the host of the Oracle database using a remote link protocol such as SSH, Telnet, etc.

The above methods all have problems of security, availability, etc., and are difficult to deploy and implement in a batch manner in the database environment of all enterprises or units.

Disclosure of Invention

The invention aims to overcome the defect that the security and the usability are influenced by starting the transparent data encryption function in the prior art, and provides a method for remotely starting Oracle transparent encryption, which is simple, convenient and safe and has no influence on the security and the usability of a database.

In order to achieve the purpose, the invention adopts the following technical scheme:

a method for remotely starting Oracle transparent encryption comprises a database server, a transparent encryption management server, a plurality of application end servers and a plurality of client end hosts; the database server is respectively in data connection with the transparent encryption management server and each application server, and each application server is respectively in data connection with each client host; the method comprises the following steps:

(1-1) in the transparent encryption management server, modifying an sqlnet

To enable Orace transparent encryption, configuration parameters need to be added to this file in order to enable transparent encryption. Ora file must specify a directory in the Oracle database server, and therefore, modifying the file must log in to the host where the database server is located, and switch to the Oracle user (or the user with modification authority), and modify the sql ora using a text editing tool (vi under UNIX, notepad under Windows, etc.).

If the software is third-party software, but not a database administrator (DBA) himself, the software logs on a database server, and potential safety hazards exist.

(1-2) generating a key in the transparent encryption management server;

using the commands in the Oracle official document:

ALTER SYSTEM SET ENCRYPTION KEY IDENTIFIED BY "CIPHER";

(1-3) generating a bootstrapping key file in the transparent encryption management server.

The boot key file includes a key for encryption and decryption and configuration information.

If no self-boot key file exists, after the database is restarted, the SQL command "ALTERSYSTEM SET ENCRYPTION WALLET OPEN IDENTIFIED BY" password "needs to be manually used to enable transparent ENCRYPTION.

If the self-starting key file exists, Oracle reads the configuration information in the self-starting key file in the restarting process and automatically starts transparent encryption.

And generating a self-starting key file, logging in a host where the database server is located, switching to an oracle user, and executing an orapki script. Or the Agent is installed in the database server, the Agent is remotely commanded, and the orapki script is executed by the Agent.

No matter logging in the database host or using the Agent, the method has great influence on safety and usability, and can not be commercialized.

The Agent refers to a certain program which runs on the database server and is used for receiving the command transmitted by the application end and executing the command on the database server. The Agent program itself may have security or availability issues, thus compromising the Oracle database on the database server.

The method does not use Agent and completes the starting of Oracle transparent encryption through common database link; through the common database link, a backdoor is not needed, extra special link is not needed, potential safety hazard is not increased, and the data safety of the database is ensured; the security or usability risk brought by the Agent is avoided.

The method of the invention can open Oracle transparent encryption without any influence on database application under the condition of not stopping the database.

Preferably, (1-1) comprises the steps of:

(1-1-1) creating an Oracle installation position/network/admin as a directory object;

connecting a database by using JDBC at a transparent encryption management server;

at a transparent encryption management server, a JDBC connection is used for sending a storage function creating command, and a PL/SQL program block is created into an Oracle storage function;

at a transparent encryption management server, sending a storage function calling command by using JDBC connection, calling a storage function, and completing the creation of a directory object;

(1-1-2) binary reading of an sql net.

in a transparent encryption management server, a JDBC connection is used for sending a storage function calling command, a storage function is called, and the sqlnet.

(1-1-3) at the transparent encryption management server, converting and coding the read data of the sqlnet.

(1-1-4) at the transparent encryption management server, scanning the data of the converted sqlnet.ora file, and judging whether transparent encryption configuration has been added;

add a transparent encryption configuration to the sql.

(1-1-5) adding a transparent encryption configuration to sqlnet.

in the transparent encryption management server, a command for calling a storage function is sent by using JDBC connection, the storage function is called, and the function of adding transparent encryption configuration information to the sqlnet.

The noun explains:

directory is a concept in operating systems and is not a proper noun in an Oracle database. In Windows, it is also called a folder.

The folders in Windows can be arranged one by one. For example, there are B folders in the a folders of the C disc, C folders in the B folders, and f.txt files in the C folders.

C: \\ \ A \ B \ C \ f.txt is the complete file name of the file f.txt. Wherein, C: \\ A \ B \ C is the path of the file f.txt.

Directory objects are proper nouns in Oracle. Is a mapping of the Oracle database to a directory (directory: also called folder) in the local server, and the command to create a directory object is as follows:

CRATE DIRECTORY DIRECTORY object name AS 'Path';

and a path C: \ A \ B \ C, the command to create one of its directory objects is as follows:

CRATE DIRECTORY my_dir AS‘C：\A\B\C’；

the above command, create a "my dir" directory object, corresponding to a path (or directory, or folder): c: a \ B \ C

After the directory object is successfully created, Oracle can access the files in the directory (or folder) through the directory object.

In addition, directory objects can also be used to restrict files that can be read and written. Files in the directory (or folder) corresponding to the directory object can only be read and written through Oracle, and files in other directories cannot be accessed.

PL/SQL program: also known as PL/SQL procedure blocks, or stored procedures, stored functions, packages. The method is an Oracle programming language for the database and is executed on the database side.

The program of the present invention is executed in a transparent encryption management server. If the PL/SQL program is used, a command of creating a stored procedure or a stored function is initiated at the transparent encryption management server, the PL/SQL program to be executed is created as the stored procedure or the stored function in the database, and then the PL/SQL program is called. After the call is completed, the stored procedure or stored function is deleted.

Preferably, (1-3) comprises the steps of:

(1-3-1) in the transparent encryption management server, determining whether a bootstrapping key file exists in the database server.

The location of the self-starting key file is fixed, and in the Oracle installation location/network/admin, the name is cwallet.

(1-3-2) if not present, generating a bootstrapping key file in the transparent encryption management server (the generated bootstrapping key file is in the transparent encryption management server).

And (1-3-3) binary transmitting the self-starting key file generated in the last step from the transparent encryption management server to a directory corresponding to the directory object MC _ DIR of the host of the database server.

Therefore, the invention has the following beneficial effects: the enabling of the Oracle transparent encryption is completed through common database linking without using Agent; through the common database link, a backdoor is not needed, extra special link is not needed, potential safety hazard is not increased, and the data safety of the database is ensured; the safety or usability risk brought by the Agent is avoided; the Oracle transparent encryption can be started without stopping the database and having any influence on the application of the database.

Drawings

FIG. 1 is a schematic diagram of an embodiment of the present invention.

In the figure: the system comprises a database server 1, a transparent encryption management server 2, an application server 3 and a client host 4.

Detailed Description

The invention is further described with reference to the following figures and detailed description.

As shown in fig. 1, a method for remotely enabling Oracle transparent encryption includes a database server 1, a transparent encryption management server 2, a plurality of application servers 3 and a plurality of client hosts 4; the database server is respectively in data connection with the transparent encryption management server and each application server, and each application server is respectively in data connection with each client host; the method comprises the following steps:

(1-1) in the transparent encryption management server, modifying sqlnet. Ora file is a configuration file for Oracle to save network settings;

(1-1-2) binary reading of an sql net.

add a transparent encryption configuration to the sql.

(1-1-5) adding a transparent encryption configuration to sqlnet.

(1-2) generating a key in the transparent encryption management server;

using the commands in the Oracle official document:

ALTER SYSTEM SET ENCRYPTION KEY IDENTIFIED BY "CIPHER";

and (1-3) generating a self-starting key file in the transparent encryption management server and transmitting the self-starting key file to the database server.

Example (c):

(1) and creating a directory object.

The "Oracle install location/network/admin" is created as a directory object. "Oracle install location/network/admin" is a path in the operating system (which can be understood as a folder in the operating system) that holds Oracle network related files whenever Oracle is installed.

If "Oracle install location" is/opt/Oracle, the complete path corresponding to the directory object to be created would be: "/opt/oracle/network/admin/".

To open Oracle transparent encryption, sqlnet. Oracle, which is a network-related file, is stored in sql. It is in the "Oracle install location/network/admin". The directory object corresponding to the 'Oracle installation position/network/admin' is created for the purpose of reading and modifying the sql network.

The PL/SQL program key statements that create the directory object are as follows:

sys.dbms_system.get_env(’ORACLE_HOME’，ora_home)；

str_ddl：＝’CREATE OR REPLACE DIRECTORY mc_dir AS”’||ora_home||’/network/admin/”’；

execute immediate str_ddl：

line 1 serves to get the "oracle install location" and assign this location to the variable ora _ home.

Line 2, double vertical bar "|" is a string join operation in the Oracle PL/SQL program. This line of commands splices multiple strings into a completed command to create a directory object:

CREATE DIRECTORY mc_dir AS’ora_home/network/admin/’；

the italic and underlined ora _ home are variables in line 1 that record the "oracle installation location".

Line 3, line 2, having generated the command to create the directory object, is placed in the str _ ddl variable, but has not yet been executed, and line 3 is the command to execute the variable str _ ddl to create the directory object.

The operation that creates the directory object is a PL/SQL program block. As previously mentioned, the flow for performing it is such that:

1. at the transparent encryption management server, the database is connected using JDBC.

2. And (3) sending a command for creating a storage function through the connection of the step (1) at the transparent encryption management server, and creating the PL/SQL program block in the step into an Oracle storage function.

3. And (3) at the transparent encryption management server, sending a storage function calling command through the connection of the step (1), calling the storage function of the step (2), and completing the creation of the directory object.

The PL/SQL program blocks of the later steps are also executed in this manner.

(2) Binary read directory object mc dir, sql file

Through a large amount of researches, the Oracle contains a UTL _ FILE packet, and the reading and writing of the local FILE of the database server can be realized. And the package can only read and write the files in the directory object, so that the safety can be ensured. However, the use of this packet has problems with character sets and conversion of the type of target data. Through continuous research and test, the UTL _ FILE packet is finally found to be used for reading and writing the local FILE of the database end in a pure binary mode, and then the read content is processed and converted at the management end which is transparently encrypted, so that the problems caused by various character sets are avoided. The method is a basic function of a database end file read-write module. Using this method, the content in sqlnet.

Note: when the self-starting key file is generated, the file reading and writing functions using the method are also available.

The related focus commands are as follows (commands in PL/SQL program block):

open sqlnet. ora file in mc _ dir, open in binary read mode:

f_sqlnet：＝

UTL_FILE.FOPEN(mc_dir，’sqlnet.ora’，’RB’，32767)；

if the command is successfully executed, the file handle is returned to f _ sql net.

A file handle, corresponding to a number. The first open file is number 1. The second open file, number 2. After the file is found, the file is read and written through the number.

Read the content of sqlnet.

UTL_FILE.get_raw(f_sqlnet，v_sqlnet，32767)；

Where f _ sql net is the file handle. V _ sql net is a variable that holds read data.

Next, we want to determine the data length in v _ sql net, and according to the length, determine whether the file is read successfully:

tmp_int：＝length(v_sqlnet)；

If tmp_int＝0 then

return 0；

end if；

if the length is 0 bytes, indicating that the read sqlnet.

The data is stored in binary form in the variable v _ sql net, which will return it to the client (i.e. the client is the transparent cryptographic management server) in binary form. This avoids the data conversion problem caused by different character sets of the database and the client.

After the client obtains the binary data of the variable v _ sql, the binary data is converted into common ASCII code in an ASCII code mode, and then the complete sql.

The main code is as follows (Java code of transparent encryption management server, no Oracle connection is needed for execution):

char sqlnet[32767]；

for(int i＝0；i＜len；i++)

sqlnet[i]＝v_sqlnet[i]；

that is, the data in v _ sqlnet is directly transmitted to a character array, and then the conversion can be completed. The variable len, is the length of v _ sql net returned from Oracle.

Then, using a simple string comparison, it can be determined whether or not transparent encryption related configuration (Java code of the transparent encryption management server, no Oracle connection execution) has been added to sqlnet.

for(int i＝0；i＜len；i++)

If(strcmp(&sqlnet[i]，“ENCRYPTION_WALLET_LOCATION”)＝＝0)

return 0；

"ENCRYPTION _ wall _ LOCATION", if transparent ENCRYPTION is turned on, there must be this string in sqlnet. The above code is to search for "ENCRYPTION _ wall _ LOCATION" in the character array sql, which stores data of sql. If found, return 0, and the process terminates, indicating that the database has been set to transparent encryption. If not, continuing to execute the next step: "add transparent encryption related configuration to sqlnet.

(3) Adding a transparent encryption related configuration to sqlnet

The additional data is very simple, and the related PL/SQL program is as follows:

f_sqlnet：＝UTL_FILE.FOPEN(mc_dir，’sqlnet.ora’，’A’)；

str_key：＝

’ENCRYPTION_WALLET_LOCATION＝

(SOURCE＝(METHOD＝FILE)(METHOD_DATA＝

(DIRECTORY＝’||ora_home||’/network/admin)))’；

UTL_FILE.put_line(f_sqlnet，str_key)；

where f _ sql net is the file handle. And str _ key, is the data content to be written into the sqlnet. This data content is provided by the Oracle official document, does not need to be modified or understood, and can complete the preparation work of opening the transparent encryption by writing it into sqlnet.

Whether the execution of the statement is successful or not (i.e. whether the modification of sqlnet. ora is successful) can be determined by using the exception, and the use of the exception is general knowledge of a programming language and is not described in detail.

(4) Generating a self-starting key file

Determining whether a self-boot key file exists

The location of the bootstrapping key file is fixed, and in "Oracle installation location/network/admin", the name is "cwallet.

The PL/SQL program for judging whether the file exists is as follows:

lines 1 to 3, to create the "Oracle install location/network/admin" location as directory object mc _ dir.

Lines 4 to 8 call utl _ file.fgetattr to get the basic attributes (file presence, file size, block size) of file cwallet.sso in directory object "mc _ dir".

Fgetattr passes the result of whether a file exists to the variable l _ file _ exists.

Lines 9 to 13, the result of the variable l _ file _ exists is judged, if the file does not exist, the variable is _ success is assigned with 1, and the value of the variable is _ success is returned.

At the transparent encryption management server, a special program calls the PL/SQL storage function, and if the return value is found to be 1, the self-starting key file is known to exist.

At the transparent encryption management server, generating a self-starting key file:

according to the conventional steps, the self-starting key file is generated by using an Oracle command 'orapki'.

$JRE_HOME/bin/java $JAVAMODE -cp$PKI_JAR：$OJMISC_JAR：$OWM_JAR：$OSDT_CORE_JAR：$0SDT_CERT_JARoracle.security.pki.textui.OraclePKITextUI″$@″

This line of the script program of orapki is generated from the boot key file.

Calling java and a plurality of JAR packages, transferring parameters and generating a self-starting key file. The method comprises the following specific steps:

$ JRE _ HOME/bin/java: java program

$ JAVAMODE: the number of bits of the host CPU, 64 bits or 32 bits.

-cp: parameter(s)

"$ PKI _ JAR: $ OJMISC _ JAR: $ OWM _ JAR: $ OSDT _ CORE _ JAR: $ OSDT _ CER T _ JAR ": with the colon symbol ": "separate, multiple JAR packages. Each $ XXXX, is a JAR packet.

oracle, security, pki, textui, oracle pkitextui: parameter(s)

"$": additional command line parameters when invoking orapki.

Before the code line, adding a line of codes as follows:

echo $ JRE _ HOME/bin/java $ JAVAMODE-cp $ PKI _ JAR: (for convenient review, increase line feed)

$OJMISC_JAR：$OWM_JAR：$OSDT_CORE_JAR：$OSDT_CERT_JAR

And executing the orapki command again to display the position and the name of the called JAR packet. Taking the $ PKI _ JAR as an example, its corresponding JAR script, in my host, is as follows:

“/oracle/oral 1204/db_1/jlib/oraclepki.jar”

its data is read to the transparent encryption management server using the PL/SQL procedure block as follows:

1 f_sqlnet：＝UTL_FILE.FOPEN(l_loc，’oraclepki.jar’，’RB’，32767)；

2UTL_FILE.get_raw(f_sqlnet，v_jar，32767)；

3return v_jar；

reading and writing database-side files, such techniques have been used many times in the foregoing, with the directory object created first, and this portion is not listed. The above codes only describe how to read the file from the database side to the transparent encryption management server.

Line 1, the oraclepki. jar file in the directory object "l _ loc" is opened in a binary read-only manner, and the file handle is returned to the variable f _ sqlnet.

On line 2, the data in the file handle f _ sqlnet is read, into the variable v _ jar.

Jar data read from oraclepkin. jar is returned to the calling side, i.e. the transparent encryption management server program, on line 3. And the program of the transparent encryption management server writes the program into the temporary position, and temporarily transfers oraclepki.

According to the above method, JAR packages corresponding to $ OJMISC _ JAR, $ OWM _ JAR, $ OSDT _ CORE _ JAR, $ OSDT _ CERT _ JAR are all temporarily read to a certain temporary location of the transparent encryption management server. Then calling java of the transparent encryption management server and the JAR packages to generate a self-starting key file at the transparent encryption management server.

And after the generation is finished, deleting each JAR packet temporarily stored in the transparent encryption management server and the temporary position.

Binary system transmits the self-starting key file to the corresponding directory of the database host MC _ DIR: the main contents of the PL/SQL memory function are as follows:

1 create or replace function mc_auto_start(v_cwallet in raw)

return int

2 as

……

3 Begin

……

4 f_sqlnet：＝

UTL_FILE.FOPEN(l_loc，’cwallet.sso’，’WB’，32767)；

5 UTL_FILE.put_raw(f_sqlnet，v_cwallet)。

unlike the previous code, the present invention lists the creation statements of the memory function, mainly to explain the origin of the variable v _ cwallet.

Line 1, an Oracle storage function "mc _ auto _ start" is created, which has a parameter variable, v _ cwalle.

The program of the transparent encryption management server should call the storage function as follows:

binary reading is performed in the step (2), and the self-starting key file generated in the self-starting key file is generated in the transparent encryption management server.

Let us assume that the binary data of the read bootstrapping key file is assigned to the variable key _ file.

Connecting the Oracle database, using the commands in the example, creates a storage function mc _ auto _ start.

And fourthly, calling the memory function mc _ auto _ start, taking the key _ file as a parameter, and transmitting the value of the key _ file to the parameter v _ cwallet.

Thus, in the mc _ auto _ start storage function, the value of the real argument v _ cwallet is the data of the bootstrapping key file.

Thereafter, the commands to create the directory object are not listed.

Line 4, with the foregoing basis, the PL/SQL program of this line simply opens the "cwallet. sso" file in the directory object "l _ loc" in a binary write.

If the file does not exist, Oracle will create this file. This is what i want to do, creating a "cwallet.

After successful creation, the file handle is in f _ sqlnet.

Line 5, call "UTL _ file. put _ raw" to write the data of the real argument v _ cwallet into the file corresponding to the f _ sql net handle, i.e., cwallet.

To this end, the object of transferring the cwallet. Moreover, the system is connected through a common database, and does not remotely log on a database end host.

Whether the transmission is successful or not is judged, and common concepts of abnormal object-oriented program design can be judged through abnormity.

All the operations of the invention are completed in a transparent encryption management server (a green host computer in the figure) through a common database connection in a mode of SQL or calling a stored procedure. No login to the server and no Agent is required. The transparent encryption management server is a common application server and does not need special configuration.

It should be understood that this example is for illustrative purposes only and is not intended to limit the scope of the present invention. Further, it should be understood that various changes or modifications of the present invention may be made by those skilled in the art after reading the teaching of the present invention, and such equivalents may fall within the scope of the present invention as defined in the appended claims.

Claims

1. A method for remotely starting Oracle transparent encryption is characterized by comprising a database server (1), a transparent encryption management server (2), a plurality of application end servers (3) and a plurality of client end hosts (4); the database server is respectively in data connection with the transparent encryption management server and each application server, and each application server is respectively in data connection with each client host; the method comprises the following steps:

(1-1) in the transparent encryption management server, modifying an sqlnet.

(1-1-2) binary reading of sqlnet.

at a transparent encryption management server, a command for calling a storage function is sent by using JDBC connection, the storage function is called, and the sqlnet.

if the transparent encryption configuration is not added in the sqlnet.ora file, adding the transparent encryption configuration to the sqlnet.ora;

(1-1-5) adding a transparent encryption configuration to sqlnet.ora in a database server through a general database connection in a transparent encryption management server;

at the transparent encryption management server, a command for calling a storage function is sent by using JDBC connection, the storage function is called, and the function of adding transparent encryption configuration information to an sqlnet.

(1-2) generating a key in the transparent encryption management server;

using the commands in the Oracle official document:

ALTER SYSTEM SET ENCRYPTION KEY IDENTIFIED BY "CIPHER";

and (1-3) in the transparent encryption management server, generating a self-starting key file through common database connection, and transmitting the self-starting key file to the database server.

2. The method for remotely enabling Oracle transparent encryption according to claim 1, wherein (1-3) comprises the steps of:

(1-3-1) in the transparent encryption management server, judging whether a self-starting key file exists in a database server;

(1-3-2) if not, generating a self-starting key file in the transparent encryption management server, the generated self-starting key file being in the transparent encryption management server;