Systems and methods for enforcement of security profiles in multi-tenant database
Embodiments relate to systems and methods for the enforcement of security profiles in a multi-tenant database. A multi-tenant database can be populated with data from different users or other entities. Different users may enjoy different sets of permissions to access, modify, store, and/or otherwise manipulate sets of data within the database. After authentication, a user's associated set of permissions are retrieved. When data is requested, matching tables or other objects located in the database are identified based on the user's query. Rather than retrieving matching tables or other objects directly, a meta data security engine can check the requesting user's permissions, and apply any filters or restrictions required by those permissions to the data present in the table(s). A substitution can be made of a table-valued function, including any filtered data entries, for the table itself. Flexible and granular data security rules can thereby be applied, transparently to the user.
1. A method of enforcing data privileges, comprising:
receiving a query from a user to access a multi-tenant database;
accessing, via a metadata security engine, a set of permissions specifying the user's data privileges to the multi-tenant database;
identifying a set of objects, the set of objects including at least one object containing data matching the query in the multi-tenant database;
authenticating, via the metadata security engine, the user's data privileges to at least one column of at least one object in the set of objects containing data matching the query, the metadata security engine being independent of the multi-tenant database;
for each object in the set of objects having the user's data privileges authenticated, substituting one or more table-valued functions associated with the received query based on the set of permissions, the one or more table-valued functions conforming the at least one object containing data matching the query with the set of permissions specifying the user's data privileges, the substituting of the one or more table-valued functions comprising:
identifying a first table in a FROM statement associated with the received query;
replacing the first table with a first table-valued function;
determining if the first table is the last table;
replacing an INNER JOIN with LEFT OUTER JOIN when the first table is not the last table;
determining whether a second table's permissions are covered by the first table-valued function; and
replacing the second table with a second table-valued function when the second table's permissions are not covered by the first table-valued function; and
transmitting a response to the received query, the response to the received query including the one or more table-valued functions for each object in the set of objects having the user's data privileges authenticated.
2. The method of claim 1 , wherein the multi-tenant database comprises a set of tables.
3. The method of claim 2 , wherein the at least one object comprises at least one table in the set of tables.
4. The method of claim 3 , wherein the substituting comprises filtering at least one of rows or columns of at least one table of the set of tables based on the set of permissions.
5. The method of claim 2 , wherein the set of permissions comprise data privileges associated with at least one of table in the set of tables, a row in the set of tables, a column in the set of tables, a field in the set of tables, an attribute in the set of tables, or a variable in the set of tables.
6. The method of claim 1 , wherein the multi-tenant database comprises a structured query language (SQL) database.
7. The method of claim 1 , wherein the set of permissions comprises at least one of access permission, read permission, write permission, storage permission, update permission, import permission, or export permission.
8. The method of claim 1 , further comprising performing an authentication process on a login of the user before receiving the query.
9. The method of claim 1 , wherein the query is received via a data service independent of the multi-tenant database.
10. The method of claim 1 , further comprising exposing a schema of the data in the multi-tenant database via an entity data model.
11. A system, comprising:
a first network interface to a user operating a device
a second network interface to a multi-tenant database; and
a processor, communicating with the device via the first network interface and the multi-tenant database via the second network interface, the processor being configured to
receive a query from the user to access the multi-tenant database,
access, via a metadata security engine, a set of permissions specifying the user's data privileges to the multi-tenant database,
identify a set of objects, the set of objects including at least one object containing data matching the query in the multi-tenant database,
authenticate, via a metadata security engine, the user's data privileges to at least one column of at least one object in the set of objects containing data matching the query, the metadata security engine being independent of the multi-tenant database,
for each object in the set of objects having the user's data privileges authenticated, substitute one or more table-valued functions associated with the received query based on the set of permissions, the one or more table-valued functions conforming the at least one object containing data matching the query with the set of permissions specifying the user's data privileges, the substituting of the one or more table-valued functions configured to:
identify a first table in a FROM statement associated with the received query;
replace the first table with a first table-valued function;
determine if the first table is the last table;
replace an INNER JOIN with LEFT OUTER JOIN when the first table is not the last table;
determine whether a second table's permissions are covered by the first table-valued function; and
replace the second table with a second table-valued function when the second table's permissions are not covered by the first table-valued function; and
transmit a response to the received query, the response to the received query including the one or more table-valued functions for each object in the set of objects having the user's data privileges authenticated.
12. The system of claim 11 , wherein the multi-tenant database comprises a set of tables.
13. The system of claim 12 , wherein the at least one object comprises at least one table in the set of tables.
14. The system of claim 13 , wherein the substituting comprises filtering at least one of rows or columns of at least one table of the set of tables based on the set of permissions.
15. The system of claim 12 , wherein the set of permissions comprise data privileges associated with at least one of table in the set of tables, a row in the set of tables, a column in the set of tables, a field in the set of tables, an attribute in the set of tables, or a variable in the set of tables.
16. The system of claim 11 , wherein the multi-tenant database comprises a structured query language (SQL) database.
17. The system of claim 11 , wherein the set of permissions comprises at least one of access permission, read permission, write permission, storage permission, update permission, import permission, or export permission.
18. The system of claim 11 , wherein the processor is further configured to access results of an authentication process performed on a login of the user before receiving the query.
19. The system of claim 11 , wherein the query is received via a data service independent of the multi-tenant database.
20. The system of claim 11 , further comprising exposing a schema of the data in the multi-tenant database via an entity data model.