Hi,
Getting the following error while trying to get user list for a given organization unit in sql view:
“SQL query contains references to protected tables”
The query is here:
Hi,
Yes, the main user tables are protected.
If you have access to the “backend” database, you can bypass it using a construct (query/function) that “re-packages” the user table information so that it can be accessed via sql view. Works, but obviously it creates a security risk (the main user tables are protected for a reason).
Regards
Calle
···
On 13 March 2018 at 12:16, Himanshu Ardawatia himanshu.ardawatia@nrc.no wrote:
Hi,
Getting the following error while trying to get user list for a given organization unit in sql view:
“SQL query contains references to protected tables”
The query is here:
SELECT
userinfo.surname,
userinfo.firstname,
FROM
public.usermembership,
public.userinfo,
public.organisationunit
WHERE
usermembership.userinfoid = userinfo.userinfoid AND
organisationunit.organisationunitid = usermembership.organisationunitid;
Perhaps the userinfo table is protected. Is there a workaround this so the said data can be accessed? OR any other method?
Thanks,
Himanshu
Mailing list: https://launchpad.net/~dhis2-devs
Post to : dhis2-devs@lists.launchpad.net
Unsubscribe : https://launchpad.net/~dhis2-devs
More help : https://help.launchpad.net/ListHelp
–
Calle Hedberg
46D Alma Road, 7700 Rosebank, SOUTH AFRICA
Tel/fax (home): +27-21-685-6472
Cell: +27-82-853-5352
Iridium SatPhone: +8816-315-19119
Email: calle.hedberg@gmail.com
Skype: calle_hedberg
1 Like
@Calle_Hedberg I appreciate your suggestion and would like to request further clarification on the solution you mentioned. I didn’t fully grasp its details.
@JohnasSolomon What I referred to is that whereas you cannot run any SQL View that includes protected tables, you CAN insert your own FUNCTIONS or VIEWS into the back end that do include the required data from protected tables. There is obviously a security risk doing this, but it is much better than turning off the sensitive table protection altogether (which can be done in dhis.conf).
So let us say you want to be able to use an SQL View to get a list of all active usernames and their phonenumber and whatsapp numbers. You can then write/insert a function or view into the database itself called let us say “Users_With_Phone_Whatsapp_numbers”, where content is like this:
SELECT username, phonenumber, whatsapp FROM userinfo WHERE disabled=false ORDER BY 1;
Then you set up an SQL View called “Usernames and phone-whatsapp numbers” with this content:
SELECT * from Users_With_Phone_Whatsapp_numbers;
“Users_With_Phone_Whatsapp_numbers” is not in the protected table list, so the SQL View will call the stored function/view called Users_With_Phone_Whatsapp_numbers and display what it returns.
Just make sure you ONLY use this method to extract what you really need.
Regards
Calle
1 Like