System and method for providing secure access to data with user defined table functions
Methods, systems and articles of manufacture are disclosed for providing secure access to data. Public UDTFs, private UDTFs and a metadata processor are utilized to facilitate secure access to data residing in a variety of data repositories.
1. A computer implemented method performed by one or more computer processors, the method comprising:
providing a calling application that submits a first query which is created by a user;
receiving the first query, which references a first user-defined table function (UDTF);
responsive to the received first query, calling the referenced first UDTF;
calling a second user-defined table function referenced by the called first UDTF; and
calling a metadata processor referenced by the called second UDTF for performing operations related to providing data to the second UDTF, wherein the operations comprise:
submitting a second query to gather attributes comprising security attributes of the user and attributes of the second UDTF;
receiving query results which correspond to the second query;
using the query results which correspond to the second query, forming a third query based on the attributes comprising security attributes of the user and attributes of the second UDTF and by operation of the one or more computer processors;
submitting the third query against the table; and
providing query results which correspond to the third query to the second UDTF, wherein the second UDTF is configured to add column headings to the query results, and wherein the first UDTF is configured to eliminate unused columns and convert the query results to a proper data type.
2. The computer implemented method of claim 1 , further comprising, after providing the query results to the second UDTF, providing the query results from the second UDTF to the first UDTF.
3. The computer implemented method of claim 2 , further comprising:
processing the query results received by the first UDTF from the second UDTF; and
providing processed query results to the calling application.
4. The computer implemented method of claim 3 , wherein processing the query results comprises:
converting data within the query results corresponding to the third query to another datatype; and
providing column headings.
5. The computer implemented method of claim 1 , further comprising including in the third query filters corresponding to the user's privileges to particular rows of the table.
6. The computer implemented method of claim 1 , further comprising including in the third query filters corresponding to access privileges of the user to particular cells of the table, wherein cells are particular columns in particular rows.
7. The computer implemented method of claim 1 , wherein the second query references a metadata table and a security table.