dhis2-users team mailing list archive

[Gomez Phiri <gomezphiri@xxxxxxxxxxx>]

Thanks,
This helps a lot. Now I know where to start from ;)

Date: Fri, 6 Sep 2013 19:46:37 +0630
Subject: Re: [Dhis2-devs] [Dhis2-users] SQL View Permissions
From: jason.p.pickering@xxxxxxxxx
To: gomezphiri@xxxxxxxxxxx
CC: dhis2-users@xxxxxxxxxxxxxxxxxxx; dhis2-devs@xxxxxxxxxxxxxxxxxxx; rwilson@xxxxxxx

I am not sure the restrictions on the user tables make much sense really. I think we should consider removing them,as they are easily circumvented. 

Several approaches are possible as Randy outlines. Just to emphasize...

 

1) Encapsulation of the view in a function . This helps to circumvent the issues Randy outlines with direct views on the database. This could be coupled to a cron job to materialize the result set to a CSV file which could be linked to a resource. A function would allow you to create an SQL view as DHIS would not know what is going on inside the function. 


2) Using a JDBC source and feeding this into a report.


(2) is probably the simplest approach. 

This will only give you the last login however. If you want to knows who logs on frequently however,you would probably need to perform a more detailed analysis of the logs. 

Regards,

Jason

--Sent from my mobile

On Sep 6, 2013 7:10 PM, "Gomez Phiri" <gomezphiri@xxxxxxxxxxx> wrote:




Hi Wilson,
I have pgadmin access and I can run all the queries from there, I just wanted to create the view for some non technical users who cannot query the database for user activity directly through pgadmin.

Thanks
Gomez.

From: rwilson@xxxxxxx
To: gomezphiri@xxxxxxxxxxx; dhis2-users@xxxxxxxxxxxxxxxxxxx; dhis2-devs@xxxxxxxxxxxxxxxxxxx

Subject: RE: [Dhis2-users] SQL View Permissions
Date: Fri, 6 Sep 2013 10:28:10 +0000









I believe those table are blocked to prevent non-system administrators from viewing user data.

 

If you have Pgadmin access or have installed PHPpgadmin on the server, you can run the queries there.  SQL views in DHIS-2 are also problematic especially if
 you refer to any of the resource tables because the analytics and data mart processes try to drop them when running and then they fail.

 

We’ve created a function that creates a number of ‘materialized views’ that our users want to export for Excel pivot table analyses.  This can be scheduled
 to run daily or we can run it on demand.  If it is something you want to view regularly you might go that route. For example most people using pivot tables want to view data with all levels of the hierarchy in a single row (province, district, sector, facilityname,
 dataelementname, startdate, periodtype, categoryoptioncombo, value).  Jason Pickering, a frequent contributor to this list is the master of materialized views and helped us develop ours. 


 

Unfortunately there does not appear to be a way currently to expose those materialized views through the api.  You can now create iReports with a JDBC datasource
 (i.e. not based on a DHIS-2 pivot table), so that might be a way to view the data from within DHIS-2.  

 

Good luck!

 

 



From: Dhis2-users [mailto:dhis2-users-bounces+rwilson=msh.org@xxxxxxxxxxxxxxxxxxx]
On Behalf Of Gomez Phiri

Sent: Friday, September 06, 2013 11:03 AM

To: dhis2-users@xxxxxxxxxxxxxxxxxxx; dhis2-devs@xxxxxxxxxxxxxxxxxxx

Subject: [Dhis2-users] SQL View Permissions



 


Hi all,


 



Is there a way I could get access to special tables in dhis2? I'm trying to create sql view with the statement "SELECT username,lastlogin FROM users order by lastlogin;" and when I try to
 save the sql view I'm getting the message "Not allowance to query in the special tables!" We want to monitor by name who frequently logs-in the system.




 



Gomez.



 		 	   		  

_______________________________________________

Mailing list: https://launchpad.net/~dhis2-devs

Post to     : dhis2-devs@xxxxxxxxxxxxxxxxxxx

Unsubscribe : https://launchpad.net/~dhis2-devs

More help   : https://help.launchpad.net/ListHelp