PROTECTED HANDLING OF DATABASE QUERIES

• US 20150127680A1
• Filed: 10/31/2014
• Published: 05/07/2015
• Est. Priority Date: 11/07/2013
• Status: Active Application

• Current Assignee: Salesforce.com, Inc.
• Original Assignee: Salesforce.com, Inc.
• Inventors: Dutta, Arup, Collins, Jesse Yarbro, Veeramani, Prem
• Granted Patent: US 10,268,721 B2
• Time in Patent Office: Days
• US Class Current: 707/771
• CPC Class Codes: G06F 16/245 Query processing
• CPC Class Codes: G06F 21/6218 to a system of files or objects, e.g. local or distributed file system or database
• CPC Class Codes: G06F 21/85 interconnection devices, e.g. bus-connected or in-line devices

Abstract

Embodiments regard protected handling of database queries. An embodiment of a method for querying database system views and tables includes: receiving a user query from a user, the user query being directed to one or both of a view and a table of a database, wherein the user is not a database administrator; parsing the user query with a query parser to identify elements of the user query, parsing the query including determining whether the query meets certain database access criteria; automatically generating a database query based on the parsing of the user query, the generation of the database query including generating a database query that is limited by the database access criteria; accessing the one or both of the view and the table using the generated database query, wherein the access is limited to read-only access; and obtaining a result of the access of the one or both of the view and table.

Claims

1. A method for querying database system views and tables, the method comprising:

receiving a user query from a user, the user query being directed to one or both of a view and a table of a database, wherein the user is not a database administrator;

parsing the user query with a query parser to identify elements of the user query, parsing the query including determining whether the query meets certain database access criteria;

automatically generating a database query based on the parsing of the user query, the generation of the database query including generating a database query that is limited by the database access criteria;

accessing the one or both of the view and the table using the generated database query, wherein the access is limited to read-only access; and

obtaining a result of the access of the one or both of the view and table.

2. The method of claim 1, further comprising determining whether the user is authorized to provide the user query.

3. The method of claim 1, further comprising returning the result of the access to the user interface.

4. The method of claim 1, wherein parsing the user query according to the database access criteria includes determining whether the user query is limited to appropriate data.

5. The method of claim 4, wherein determining whether the user query is limited to appropriate data includes determining whether each table or view to be accessed matches entries in a whitelist of tables and views for the database.

6. The method of claim 1, wherein parsing the user query according to the database access criteria includes determining whether the user query is limited to read-only access of tables and views.

7. The method of claim 6, wherein determining whether the query is limited to read-only access includes determining whether functions to be used in the query match entries in a whitelist of functions for the database.

8. The method of claim 1, further comprising obtaining a read-only database connection from a read-only connection pool and running the query using the obtained read-only database connection.

9. The method of claim 1, wherein obtaining the results of the access includes limiting the results of the database query according to a threshold.

10. The method of claim 1, further comprising logging the query in a query log stored in a memory upon the query running successfully.

11. An apparatus for querying database system views and tables, the apparatus comprising:

an interface for receipt of queries of databases, the interface including a query parser to parse received queries;

a processor to process data, including data for database queries; and

a memory for storage of data;

wherein the system is to automatically;

receive from a user a user query directed to one or both of a view and a table of a database, wherein the user is not a database administrator;

parse the user query with the query parser to identify elements of the user query, parsing the query including determining whether the query meets certain database access criteria;

generate a database query based on the parsing of the user query, the generation of the database query including generating a database query that is limited by the database access criteria;

access the one or both of the view and the table based on the query, wherein the access is limited to read-only access; and

obtain a result of the access of the one or both of the view and table.

12. The apparatus of claim 11, wherein the apparatus is further to determine whether the user is authorized to provide the query.

13. The apparatus of claim 11, wherein the apparatus is further to return the result of the access to the user.

14. The apparatus of claim 11, wherein determining whether the user query meets the database access criteria includes determining whether the user query is limited to appropriate data.

15. The apparatus of claim 14, wherein determining whether the user query is limited to appropriate data includes determining whether each table or view to be accessed matches entries in a whitelist of tables and views for the database.

16. The apparatus of claim 11, wherein determining whether the user query meets the database access criteria includes determining whether the user query is limited to read-only access of tables and views.

17. The apparatus of claim 16, wherein determining whether the query is limited to read-only access includes determining whether functions to be used in the query match entries in a whitelist of functions for the database.

18. The apparatus of claim 11, wherein the apparatus is further to obtain a read-only database connection from a read-only connection pool and to run the query using the obtained read-only database connection.

19. The apparatus of claim 11, wherein obtaining the results of the access includes limiting the results of the database query according to a threshold.

20. The apparatus of claim 11, wherein the apparatus is further to log the query in a query log stored in the memory upon the query running successfully.

21. A non-transitory computer-readable storage medium having stored thereon data representing sequences of instructions that, when executed by a processor, cause the processor to perform operations comprising:

receiving a user query from a user, the user query being directed to one or both of a view and a table of a database, wherein the user is not a database administrator;

parsing the user query with a query parser to identify elements of the user query, parsing the query including determining whether the query meets certain database criteria;

automatically generating a database query based on the parsing of the user query, the generation of the database query including generating a database query that is limited by the database access criteria;

accessing the one or both of the view and the table using the generated database query, wherein the access is limited to read-only access; and

obtaining a result of the access of the one or both of the database system view and database system table.

22. The medium of claim 21, further comprising instructions that, when executed by the processor, cause the processor to perform operations comprising:

determining whether the user is authorized to provide the user query.

23. The medium of claim 21, further comprising instructions that, when executed by the processor, cause the processor to perform operations comprising:

returning the result of the access to the user interface.

24. The medium of claim 21, wherein parsing the user query includes determining whether the user query is limited to appropriate data.

25. The medium of claim 24, wherein determining whether the user query is limited to appropriate data includes determining whether each table or view to be accessed matches entries in a whitelist of tables and views for the database.

26. The medium of claim 21, wherein parsing the user query includes determining whether the user query is limited to read-only access of tables and views.

27. The medium of claim 26, wherein determining whether the query is limited to read-only access includes determining whether functions to be used in the query match entries in a whitelist of functions for the database.

28. The medium of claim 21, further comprising instructions that, when executed by the processor, cause the processor to perform operations comprising:

obtaining a read-only database connection from a read-only connection pool and running the query using the obtained read-only database connection.

29. The medium of claim 21, wherein obtaining the results of the access includes limiting the results of the database query according to a threshold.

30. The medium of claim 21, further comprising instructions that, when executed by the processor, cause the processor to perform operations comprising:

logging the query in a query log stored in a memory upon the query running successfully.