Database access controls defined through logical expressions
Provided herein is a process that converts unprotected data into protected data based on permissions defined using logical operators. As such, user permissions can be managed with a simple string of data. In one example, the method may include receiving a request for data from a user device, identifying a permission object corresponding to the user device, retrieving a string comprising a logical expression with a logical operator and one or more values from the permission object, wherein the logical expression defines access permissions of the user device with respect to a database, executing a database query on the database based on the request to generate results, and filtering the results from the database query based on the logical expression to generate filtered results and outputting the filtered results to the user device.
1 . A computing system comprising:
a network interface configured to receive a request for data from a user device of a user of a software application; and
a processor configured to:
identify a permission object corresponding to the user;
retrieve a string comprising a logical operator and values from an authorization model of the permission object, the values identifying two or more rows of a database of the software application, wherein the values comprise at least one of a lower bound value or an upper bound value which are expected by the logical operator, the logical operator being combined with the values to define access permissions of the user with respect to the two or more rows of the database, the authorization model being stored within the permission object and defining row-level security for data in the database;
generate a database query based on the logical operator, the values, and the authorization model; and
execute the database query on the database to generate query results and output the query results to the user device.
2 . The computing system of claim 1 , wherein the processor is configured to generate a structured query language (SQL) query based on a logical expression that compares the values and returns a Boolean response.
3 . The computing system of claim 2 , wherein the processor is configured to execute a filter function during runtime of the SQL query to filter data from the database based on the logical operator and the values.
4 . The computing system of claim 1 , wherein the logical operator comprises one or more of ALL, AND, ANY, BETWEEN, EXISTS, IN, LIKE, NOT, OR, and SOME.
5 . The computing system of claim 4 , wherein both of the lower bound value and the upper bound value are expected by the logical operator.
6 . The computing system of claim 1 , wherein the processor is configured to execute a script to execute the database query and simultaneously filter results from the database query during runtime of the database query.
7 . The computing system of claim 1 , wherein the processor is further configured to display a user interface comprising a workspace for configuring the authorization model, receive user inputs via the workspace on the user interface, and define the authorization model based on the received user inputs.
8 . A method comprising:
receiving a request for data from a user device;
identifying a permission object corresponding to the user device;
retrieving a string comprising a logical expression with a logical operator and values from an authorization model of the permission object, the values identifying two or more rows of a database of the software application, wherein the values comprise at least one of a lower bound value or an upper bound value which are expected by the logical operator, the logical expression defining access permissions of the user device with respect to the two or more rows of the database, the authorization model being stored within the permission object and defining row-level security for data in the database;
executing a database query on the database based on the request to generate results; and
filtering the results from the database query based on the logical expression to generate filtered results and outputting the filtered results to the user device.
9 . The method of claim 8 , wherein the executing comprises generating a structured query language (SQL) query on the database to generate the results, and the filtering comprises filtering each result based on a logical expression that compares values in the respective result and returns a Boolean response.
10 . The method of claim 9 , wherein the executing comprises executing a filter function during runtime of the SQL query to filter data from the database based on the logical operator and the one or more values.
11 . The method of claim 8 , wherein the logical operator comprises one or more of ALL, AND, ANY, BETWEEN, EXISTS, IN, LIKE, NOT, OR, and SOME.
12 . The method of claim 11 , wherein the values comprise a lower bound value and an upper bound value which are expected by the logical operator.
13 . The method of claim 8 , wherein the filtering comprises simultaneously filtering the results from the database query during runtime of the database query.
14 . The method of claim 8 , wherein the method further comprises displaying a user interface comprising a workspace for configuring the authorization model, receiving user inputs from the user device via the workspace on the user interface, and defining the authorization model based on the received user inputs.
15 . A computer-readable medium comprising program instructions which when executed by a processor cause a computer to perform a method comprising:
receiving a request for data from a user device;
identifying a permission object corresponding to the user device;
retrieving a string comprising a logical operator and values from an authorization model of the permission object, the values identifying two or more rows of a database of the software application, wherein the values comprise at least one of a lower bound value or an upper bound value which are expected by the logical operator, the logical operator being combined with the one or more values to define access permissions of the user device with respect to the two or more rows of the database, the authorization model being stored within the permission object and defining row-level security for data in the database;
executing a database query based on a data store to retrieve data from the database based on the request for data from the user device; and
filtering results from the database query based on the logical operator and the one or more values and outputting the filtered results to the user device.
16 . The computer-readable medium of claim 15 , wherein the executing comprises generating a structured query language (SQL) query on the database to generate the results and filtering each result based on a logical expression that compares values in the respective result and returns a Boolean response.
17 . The computer-readable medium of claim 16 , wherein the executing comprises executing a filter function during runtime of the SQL query to filter data from the database based on the logical operator and the one or more values.
18 . The computer-readable medium of claim 15 , wherein the filtering comprises simultaneously filtering the results from the database query during runtime of the database query.