METHOD FOR PROVIDING A CLIENT COMPUTER DEVICE WITH ACCESS TO A DATABASE MANAGEMENT SYSTEM

Abstract

A method for providing a client computer device with access to a database management system via an http connection with an agent server, comprising a step of generating a query by said client device; a step of processing said query by said agent server to search said database, according to the parameters contained in said query, wherein said query is an SQL query; said agent server has a security module parametrizable by code injection, to analyze the content of said query and the parameters thereof; and conditionally ensure the transmission of said query to an extraction module; said extraction module converting the query into JDBC instructions transmitted to said database management system; said extraction module receiving in response the data in JDBC format and converting it into a JSON stream; and the agent server transmits said JSON stream to the client device.

Description

BACKGROUND

[0001] The present invention relates to the field of access by a client computer device to a relational database management system (RDBMS) that can be operated by a data manipulation language and SQL instructions.

[0002] A database management system is composed of several computer programs: an engine, a catalogue, a query processor, a command language, SQL for the present invention and tools.

[0003] The database engine manipulates database files, transmits data to and from other programs, and verifies data consistency and integrity.

[0004] The catalogue contains a description of the database organization, access control lists, the names of persons authorized to manipulate the database and a description of the consistency rules.

[0005] The query processor performs the requested operations.

[0006] The query language makes it possible to manipulate the content of the database. SQL has become the standard language.

[0007] DBMS tools are used to create reports, screens for entering information, import and export data to and from the database, and manipulate the catalogue. These tools are used by the database administrator to perform backups, restore data, authorize or deny access to certain information, and make changes to the content of the database--creation, reading, modification and deletion of information, abbreviated CRUD (create, read, update, delete). These tools are also used to monitor engine activity and perform tuning 26 operations.

[0008] In a relational DBMS, requests made to the DBMS are typically processed in five steps:

[0009] Client software communicates with the DBMS using its programming interface via a network. A DBMS communication device verifies the client's identity, then transmits the client's requests to the DBMS core and transmits to the client the information extracted by the DBMS; the DBMS then creates a thread to process the query. A program controls all threads and decides which ones are executed immediately and which ones will be executed later, depending on the computer's workload;

[0010] when executing the thread, a compiler transforms the text expressed in the DBMS query language into an execution plan, the form of which imitates that of an algebraic expression using relational algebra, then a set of "operator" programs calculate the result of the expression by performing operations such as join, Cartesian product, sorting and selection;

[0011] operators use the database engine, which executes algorithms (called access methods) to retrieve information and maintain the structures of the database files.

[0012] Once the information is obtained by the file manipulation program, it is sent to the execution thread and then to the communication device that transmits it to the client.

[0013] The DBMS is generally run on a computer server communicating with client devices via a computer network, mainly the Internet. These client devices, once mainly computers, are now very diverse: cellular phones (smartphones), tablets, but also communicating objects that sometimes do not have a human-machine interface, to automatically exchange information between local sensors and a remote database.

[0014] More specifically, the invention relates to the technical problem of communication between the client device and the server running the computer code of the relational database management system, and securing access to this server to avoid malicious queries or queries from an unauthorized device that could inappropriately modify or even destroy the data stored in the database.

[0015] The communication between the two devices is generally performed with an http client-server communication protocol using a TCP transport layer.

STATE OF THE ART

[0016] The US patent U.S. Pat. No. 6,882,996 describing a method for querying a parameterized database system is known in the state of the art.

[0017] The method described in this prior art document is intended to enable a client to access a database system on a server via an

[0018] Internet connection from middleware in communication with the client and the server.

[0019] The query language of the database system is SQL. The data that satisfy the query is sent via HTTP protocol in Extensible Markup Language (XML).

[0020] Access to the file is controlled in response to a client query for data containing specific values and methods, it being specified that this query is not an SQL query in order to avoid inappropriate access.

[0021] The middleware includes a router that receives the client's query. It includes a servlet that replaces some parameters in the parametrized instruction with corresponding values from the client query to establish an SQL statement. The servlet sends the SQL statement to the database system for execution.

[0022] The European patent application EP07009943 describing a method and system for transferring data contained in an electronic message, in particular a MIME attachment (multipurpose Internet mail extensions), to a relational database containing the steps of:

[0023] providing an XML sequence that determines how the data should be processed,

[0024] executing a sequencer, which takes the message and the XML sequence as an input, where the execution of the sequence involves the execution of at least one SQL statement to access the relational database.

[0025] The US patent U.S. Pat. No. 6,105,043 describes another example of a method for creating macro language files to execute SQL queries in a relational database management system via the Internet's World Wide Web. In accordance with this prior art solution, web users can ask for information from the RDBMS software via HTML input forms, the query is then used to create an SQL statement for execution by the RDBMS software. The results output by the RDBMS software are in turn transformed into HTML format for presentation to the web user.

DRAWBACKS OF THE PRIOR ART

[0026] The prior art solutions focus on the security of exchanges between the client and the DBMS by using an instruction format that expressly excludes SQL instructions. This implies the use of a dedicated application on the client device, with disadvantages regarding the robustness of the exchanges, the processing time and the IT resources implemented on the client device.

SUMMARY

[0027] In order to remedy these disadvantages, the invention, in its broadest sense, relates to a method for providing a client computer device with access to a database management system via an http connection with an agent server, comprising:

[0028] a step of generating a query by said client device

[0029] a step of processing said query by said agent server to search said database, according to the parameters contained in said query, characterized in that:

[0030] said query is an SQL query

[0031] said agent server has a security module parametrizable by code injection,

[0032] to analyze the content of said query and the parameters thereof and

[0033] conditionally ensure the transmission of said query to an extraction module

[0034] said extraction module converting the query into JDBC instructions transmitted to said database management system

[0035] said extraction module receiving in response the data in JDBC format and converting it into a JSON stream

[0036] the agent server transmits said JSON stream to the client device.

[0037] Advantageously, said parametrizable security module includes means for limiting the number of transmitted lines.

[0038] According to a particular alternative solution, said parametrizable safety module includes means for triggering an action according to said analyzed query, said action particularly consisting in

[0039] sending a message of revocation of a user

[0040] the sending of an alert message.

[0041] Preferably, said parametrizable security module includes a default configuration file.

BRIEF DESCRIPTION OF THE DRAWINGS

[0042] The characteristics and advantages of the invention will appear upon reading the following description, given only by way of a non-restrictive example while referring to the appended drawings, wherein:

[0043] FIG. 1 is a block diagram of a system according to an exemplary embodiment of the invention,

[0044] FIG. 2 is a block diagram of the agent server;

[0045] FIG. 3 is a representation of the interface of the DatabaseConfigurator configuration module

[0046] FIG. 4 shows an example of a default configuration file.

DETAILED DESCRIPTION

[0047] Hardware Architecture

[0048] FIG. 1 shows a block diagram of a system according to the invention.

[0049] The client devices 1 to 3 can be a computer 1, a cellular phone ("smartphone") or a tablet 2 or a connected object 3. The client devices 1 to 3 have a communication interface including a physical layer, a data link layer, an IP network layer, a TCP transport layer and an http session layer.

[0050] They are connected via the Internet network to an agent server 4 which is in turn connected to a server 5 for processing the database system 6.

[0051] The agent server 4 has the ability to maintain a session specific to it. It can, for example, be an ICAP server, or an agent server with a status memory.

[0052] The latter also includes modules described in greater details in FIG. 2, for processing the queries from the client devices 1 to 3 and the exchanges with the server 5 for processing the database system 6.

[0053] The agent server 4 has an analysis stage 10 that extracts SQL statements from the client data as an http query, as well as the identifier of the client device that transmitted the query.

[0054] Of course, the example described is not exhaustive, the invention can be implemented with a single server, combining the functionalities of the agent server 4 and the server 5 for processing the database 6.

[0055] This data is then transmitted to a parametrizable filter stage 11 comparing the data via a program 12 which makes it possible to define authorized commands for the client device corresponding to the identifier extracted by the analysis stage 10. This program can be modified by code injection, to adapt the authorized or prohibited commands according to the specificities of the context of use of the database concerned.

[0056] If the query transmitted by the analysis stage 10 contains unauthorized commands, the filtering stage 11 sends the client device a notification that the query has not been processed.

[0057] If the query transmitted by the analysis stage 10 contains incorrect commands, the filtering stage 11 sends back to the client device an error notification with a code indicating the nature of the error and a message.

[0058] If the query contains only authorized commands, the filter stage 11 transmits to a JDBC pilot constituting a gateway for access to a database 6 management system 5.

[0059] The query is executed on the database 6 management system 5 and the response is then transmitted to the client device 1 to 3 corresponding to the above-mentioned identifier.

Detailed Exemplary Embodiment

[0060] The following description presents examples of a code for a query to connect to a database according to the invention.

[0061] A query to connect to the remote database makes an http call in GET or POST from the client device.

[0062] Example with an http call in GET;

[0063] https: //www.acme.com/aceql/user1/my_db/connect?pass word=MySecret_1234

[0064] The agent server understands that the connection identifier (user1, MySecret_1234) wants to connect to the SQL my_db database.

[0065] The security module checks, via the injected Java authentication code, if the couple (user1, MySecret_1234) has the authorization to connect.

[0066] This is done via the Java code of the login method

[0067] (String username, char [ ]) of the instance of the DatabaseConfigurator class type injected into the security module.

[0068] If so, a single security token for use in the following http mailings is returned as a response in a JSON envelope:

TABLE-US-00001 { status":"OK" "token":" qsd6bmhqnc8bosebc3crvipr81" }

[0069] If not so, an http 401 UNAUTHORIZED status is returned to the customer.

[0070] It is now possible to program an SQL query from the client device, via an http call in GET or POST, by passing the authentication token again

[0071] https: //www.acme.corn/aceq1/user1/qsd6bmhqnc8bosebc3crvipr81/my_db/select

[0072] with the http parameter:

[0073] name "sql"

[0074] value: the content of the SQL query is for example "select * from customer" URL encoding with uTF-8 encoding. (A DELETE, INSERT, UPDATE instruction could also have been sent).

[0075] The http call dispatches the query to the agent server. The security module via the injected code of the instance of the DatabaseConfigurator class checks that this call is authorized, via several control methods:

[0076] verifyAuthToken

[0077] Verification that the token is valid/authentic and not expired.

[0078] AfterAfterAnalysis Allocation:

[0079] Method that makes it possible to analyze in detail the syntax of the SQL query, as well as the parameters passed.

[0080] allowStatementClass:

[0081] Method that makes it possible to authorize or not a Statement that is not a PreparedStatement

[0082] If the security checks fail, an http code 401 is returned to the customer device.

[0083] If the security checks are successful, the agent server then passes the order to the SQL database via a JDBC call. The JDBC call returns a response that is forwarded to the agent server.

[0084] The agent server then formats the SQL data in JSON and returns this JSON content to the client device:

TABLE-US-00002 { status":"OK" query_return_lines":[ row_l":[ { "col_index":1, "col_name":"customer_id", "col_value": 1111 }, { "col_index":2, "col_name":"customer_name","col_value":"Smith" }, { "col_index":3, "col_name":"customer_age", "col_value": 30 } r row_2":[ { "col_index":1, "col_name":"customer_id", "col_value":2222 }, { "col_index":2, "col_name":"customer_name", "col_value":"Wesson" }, { "col_index":3, "col_name":"customer_age", "col value":31 } ] } ] }

[0085] SQL connection modification commands can also be sent. Examples:

[0086] Switching to transaction mode:

[0087] https: //www.acme.com/aceq1/username/qsd6bmhqnc8bose bc3crvipr81/my_db/set_auto_commit/false

[0088] Validation of an ongoing transaction:

[0089] https: //www.acme.com/aceq1/username/qsd6bmhqnc8bose bc3crvipr81/my_db/committee

[0090] Cancellation of an ongoing transaction:

[0091] https: //www.acme.corn/aceq1/username/qsd6bmhqnc8bose bc3crvipr81/my_db/rollback

[0092] When the client device has finished its session, it can safely send a disconnection command:

[0093] https://www.acme.com/aceq1/user1/qsd6bmhqnc8bosebc3crvipr81/my_db/d- isconnect

[0094] Configuration module interface

[0095] FIG. 3 shows the DatabaseConfigurator configuration module interface.

[0096] The configuration module provides a default configuration, allowing a quick start without the need for prior programming.

[0097] It also includes a configuration file of the JDBC link to the database(s) that could be accessed from the client device. FIG. 4 shows an example of a default configuration file, providing for the transmission of all commands, and a session duration of 24 hours.

Claims

1. A method for providing a client computer device with access to a database management system via an http connection with an agent server, comprising a step of generating a query by said client device a step of processing said query by said agent server to search said database, according to the parameters contained in said query, wherein said query is an SQL query said agent server has a security module parametrizable by code injection, to analyze the content of said query and the parameters thereof; and conditionally ensure the transmission of said query to an extraction module; said extraction module converting the query into JDBC instructions transmitted to said database management system; said extraction module receiving in response the data in JDBC format and converting it into a JSON stream; and the agent server transmits said JSON stream to the client device.

2. The method for providing a client computer device with access to a database management system according to claim 1, wherein said parametrizable security module includes means for limiting the number of transmitted lines.

3. The method for providing a client computer device with access to a database management system according to claim 1, wherein said parametrizable security module includes means for triggering an action according to said analyzed query.

4. The method for providing a client computer device with access to a database management system according to claim 3, wherein said action comprises sending a message of revocation of a user.

5. The method for providing a client computer device with access to a database management system according to claim 3, wherein said action comprises sending of an alert message.

6. The method for providing a client computer device with access to a database management system according to claim 1, wherein said parametrizable security module includes a default configuration file.