dhis2-devs team mailing list archive

[Jason Pickering <jason.p.pickering@xxxxxxxxx>]

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
>
>