Dedicated SQL services for cross-system SQL access to an application-server-managed database

Abstract

Methods for using SQL statements to access an application-server-managed database are disclosed herein. In some embodiments, a user sends, either directly or indirectly (i.e., remotely) a SQL statement from an ODBC application or server, respectively, to an application server that preprocesses SQL statements for accessing data from a centralized database. The application server may have a SQL endpoint, and access to the SQL endpoint may be determined by a user's logon credentials, a user's presentation of a SAML token, or a user's presentation of a valid certificate. The application server may then parse the SQL statement and determine the user's authorization to access certain objects in the centralized database based on a SQL handler design-time configuration. A result from the statement may be sent back to the user either directly or indirectly and exposed.

Having thus described various embodiments of the invention, what is claimed as new and desired to be protected by Letters Patent includes the following: 
     
       1. A method of accessing data from a database in an application-server-managed database system (ASMDS) by routing structured-query-language (SQL) statements through an application server, the method comprising:
 receiving, by the application server, a certificate from an Open Database Connectivity (ODBC) driver; 
 receiving, from the ODBC driver, an SQL statement by a SQL endpoint in the application server; 
 handling the SQL statement in connection with a design-time configured SQL handler comprising:
 a service definition listing objects to be exposed; and 
 a service binding comprising configuration data to expose said objects; 
 wherein a SQL service artifact associated with the service binding is generated upon activation of the service binding; 
 
 parsing the SQL statement and extracting schema names referenced in the SQL statement; 
 in response to validating the certificate and determining that data access is permitted, retrieving centralized data corresponding to the SQL statement from a centralized database; and 
 exposing the service binding to a user as a schema containing the centralized data. 
 
     
     
       2. The method of  claim 1 , further comprising: permitting the user to call procedures on the ASMDS using the SQL statement, wherein the objects to be exposed are the procedures performed in the ASMDS. 
     
     
       3. The method of  claim 1 , further comprising: converting character-based data to semantic data by one of:
 converting the character-based data to the semantic data using an ODBC driver within an ODBC-connected application, or 
 specifying in the SQL statement to convert the character-based data to the semantic data in the ASMDS. 
 
     
     
       4. The method of  claim 1 , further comprising: exposing the data from the database via implicit or explicit virtual tables in an ODBC-connected application. 
     
     
       5. The method of  claim 1 , further comprising: limiting user access to the SQL endpoint based on a user authentication and authorization of the user. 
     
     
       6. The method of  claim 5 , further comprising: performing the user authentication by one of:
 submitting valid logon credentials to authenticate the user, or 
 receiving a valid Single Sign-On (SSO) token from a third-party identity provider to authenticate the user, or 
 storing a valid certificate on a SQL Personal Security Environment (PSE) in an ODBC application, and transmitting the valid certificate to the application server, or 
 storing a valid certificate on the application server, and matching the valid certificate to the SQL PSE in the ODBC application. 
 
     
     
       7. The method of  claim 5 , further comprising: limiting access to SQL services based on the authorization of the user by:
 checking the user's authorization for the SQL service artifact; 
 resolving the service definition associated with the SQL service artifact; 
 checking the objects in the SQL statement against the exposed objects from the service definition; 
 wherein the exposed objects not listed in the service definition are not accessible; and 
 performing record-level authorizations based on a resulting set of objects. 
 
     
     
       8. One or more non-transitory computer-readable media storing computer executable instructions that, when executed by a processor, perform a method of accessing data from a database in an application-server-managed database system (ASMDS) by routing SQL statements through an application server, the method comprising:.
 receiving, by the application server, a certificate from an Open Database Connectivity (ODBC) driver; 
 receiving, from the ODBC driver, an SQL statement by a SQL endpoint in the application server; 
 handling the SQL statement in connection with a design-time configured SQL handler comprising: 
 a service definition listing objects to be exposed; and 
 a service binding comprising configuration data to expose said objects; 
 wherein a SQL service artifact associated with the service binding is generated upon activation of the service binding; 
 parsing the SQL statement and extracting schema names referenced in the SQL statement; 
 in response to validating the certificate and determining that data access is permitted, retrieving centralized data corresponding to the SQL statement from a centralized database; and 
 exposing the service binding to a user as a schema containing the centralized data. 
 
     
     
       9. The media of  claim 8 , wherein the computer-executable instructions are further executed to perform: permitting the user to call procedures on the ASMDS using the SQL statement, wherein the objects to be exposed are the procedures performed in the ASMDS. 
     
     
       10. The media of  claim 8 , wherein the computer-executable instructions are further executed to perform: converting character-based data to semantic data by one of:
 converting the character-based data to the semantic data using an ODBC driver within an ODBC-connected application, or 
 specifying in the SQL statement to convert the character-based data to the semantic data in the ASMDS. 
 
     
     
       11. The media of  claim 8 , the computer-executable instructions are further executed to perform: exposing the data from the database via implicit or explicit virtual tables in an ODBC-connected application. 
     
     
       12. The media of  claim 8 , wherein the computer-executable instructions are further executed to perform: limiting user access to the SQL endpoint based on a user authentication and authorization of the user. 
     
     
       13. The media of  claim 12 , wherein the computer-executable instructions are further executed to perform: performing the user authentication by one of:
 submitting valid logon credentials to authenticate the user, or 
 receiving a valid Single Sign-On (SSO) token from a third-party identity provider to authenticate the user, or 
 storing a valid certificate on a SQL Personal Security Environment (PSE) in an ODBC application, and transmitting the valid certificate to the application server along with the SQL statement to authenticate the user, or 
 storing a valid certificate on the application server, and matching the valid certificate to a SQL PSE in an ODBC application. 
 
     
     
       14. The media of  claim 12 , wherein the computer-executable instructions are further executed to perform: limiting access to SQL services based on the authorization of the user by:
 checking the user's authorization for the SQL service artifact; 
 resolving the service definition associated with the SQL service artifact; 
 checking the objects in the SQL statement against the exposed objects from the service definition; 
 wherein the exposed objects not listed in the service definition are not accessible; and 
 performing record-level authorizations based on a resulting set of objects. 
 
     
     
       15. A system for accessing data in an Application-Server-Managed Database System (ASMDS)comprising:
 an application server to receive a certificate from an Open Database Connectivity (ODBC) driver; 
 an database managed by the application server; 
 and ODBC-connected application used to connect to the ASMDS; and 
 one or more non-transitory computer-readable media storing computer executable instructions that, when executed by a processor, perform a method of accessing the data from the application-server-managed database in the ASMDS by routing SQL statements through the application server, the method comprising:
 receiving, from the ODBC driver, an SQL statement by a SQL endpoint in the application server; 
 handling the SQL statement in connection with a design-time configured SQL handler comprising: 
 a service definition listing objects to be exposed; and 
 a service binding comprising configuration data to expose said objects; 
 
 wherein a SQL service artifact associated with the service binding is generated upon activation of the service binding; 
 parsing the SQL statement and extracting schema names referenced in the SQL statement; 
 in response to validating the certificate and determining that data access is permitted, retrieving centralized data corresponding to the SQL statement from a centralized database; and 
 exposing the service binding to a user as a schema containing the centralized data. 
 
     
     
       16. The system of  claim 15 , wherein the computer-executable instructions are further executed to perform: permitting the user to call procedures on the ASMDS using the SQL statement, wherein the objects to be exposed are the procedures performed in the ASMDS. 
     
     
       17. The system of  claim 15 , wherein the computer-executable instructions are further executed to perform: converting character-based data to semantic data by one of:
 converting the character-based data to the semantic data using an ODBC driver within an ODBC-connected application, or 
 specifying in the SQL statement to convert the character-based data to the semantic data in the ASMDS. 
 
     
     
       18. The system of  claim 15 , the computer-executable instructions are further executed to perform: exposing the data from the database via implicit or explicit virtual tables in an ODBC-connected application. 
     
     
       19. The system of  claim 15 , wherein the computer-executable instructions are further executed to perform: limiting user access to the SQL endpoint based on a user authentication of the user, wherein the user authentication is performed by one of:
 submitting valid logon credentials to authenticate the user, or 
 receiving a valid Single Sign-On (SSO) token from a third-party identity provider to authenticate the user, or 
 storing a valid certificate on a SQL Personal Security Environment (PSE) in an ODBC application, and transmitting the valid certificate to the application server along with the SQL statement to authenticate the user, or 
 storing a valid certificate on the application server, and matching the valid certificate to a SQL PSE in an ODBC application. 
 
     
     
       20. The system of  claim 19 , wherein the computer-executable instructions are further executed to perform: limiting access to SQL services based on authorization of the user by:
 checking the user's authorization for the SQL service artifact; 
 resolving the service definition associated with the SQL service artifact; 
 checking the objects in the SQL statement against the exposed objects from the service definition; 
 wherein the exposed objects not listed in the service definition are not accessible; and 
 performing record-level authorizations based on a resulting set of objects.