dhis2-devs team mailing list archive

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

This is really the only way to get it, since users can have multiple user
roles and organisation units, which would not be able to be properly
represented in a flat CSV list anyway!

Regards,
Jason


On Tue, Apr 11, 2017 at 4:01 PM, Raminosoa Rabemanantsoa, Tantely <
traminosoa@xxxxxxxxxx> wrote:

> Ok, thanks. I have to get my feet wet with this method! I will try to
> adapt this syntax with our case.
>
> Regards,
>
> Tantely.
>
> On Tue, Apr 11, 2017 at 4:52 PM, Jason Pickering <
> jason.p.pickering@xxxxxxxxx> wrote:
>
>> Maybe
>>
>> https://play.dhis2.org/dev/api/users?fields=firstName,surnam
>> e,email,userCredentials[username,lastLogin,created,disabled,
>> userRoles[id,name]],organisationUnits[id,name]&paging=false
>>
>> Regards,
>> Jason
>>
>>
>>
>> On Tue, Apr 11, 2017 at 3:46 PM, Raminosoa Rabemanantsoa, Tantely <
>> traminosoa@xxxxxxxxxx> wrote:
>>
>>> Hi Jason,
>>>
>>> If I would like to bypass this SQL scripts and use the Web API as you
>>> already proposed, how could we write the Web API in order to pull out a
>>> list of the following informations from the users:
>>>
>>> username,surname, firstname, email, phonenumber, lastlogin, created,
>>> disabled, user role, user orgunits
>>>
>>> Regards,
>>>
>>> Tantely.
>>>
>>> On Tue, Apr 11, 2017 at 4:29 PM, Jason Pickering <
>>> jason.p.pickering@xxxxxxxxx> wrote:
>>>
>>>> Hi there.
>>>> We have had enough security problems lately, It may not be a good idea
>>>> to encourage even more of them.
>>>>
>>>> This is a "good" way around the security attempts which have put in
>>>> place, but its a very bad idea to have this table as an SQL view. This was
>>>> the entire reason of why we attempted to protect it from direct access via
>>>> SQL views, but, the regular expression which does this does not catch the
>>>> use of the schema name. We will fix it
>>>>
>>>> Of course, its easy enough to dump the password hash out with a small
>>>> change to the SQL view, but you would probably not want to do this.
>>>>
>>>> See below for an example with a  small modification to your SQL view.
>>>>
>>>>
>>>> Regards,
>>>> Jason
>>>>
>>>>
>>>>
>>>>
>>>>    - "admin",
>>>>    - "$2a$10$wjLPViry3bkYEcjwGRqnYO1bT2Kl.ZY0kO.fwFDfMX53hitfx5.3C",
>>>>    - "Traore",
>>>>    - "John",
>>>>    - "someone@xxxxxxxxx",
>>>>    - "",
>>>>    - "2017-04-11 13:24:55.914",
>>>>    - "2013-04-18 17:15:08.401",
>>>>    - "false",
>>>>    - "MNCH / PNC (Adult Woman) program",
>>>>    - "ZyjSDLHGPv4"
>>>>
>>>>
>>>> On Tue, Apr 11, 2017 at 3:01 PM, Raminosoa Rabemanantsoa, Tantely <
>>>> traminosoa@xxxxxxxxxx> wrote:
>>>>
>>>>> Hi Harivola,
>>>>>
>>>>> Thank you very much for the SQL scripts querying the users
>>>>> information. The syntax is accepted by SQL View, but I am a bit
>>>>> encountering a blank result as illustrated by the attached file. I am
>>>>> wondering where I am doing wrong?
>>>>>
>>>>> Thank you for your help!
>>>>>
>>>>> Regards,
>>>>>
>>>>> Tantely.
>>>>>
>>>>>
>>>>>
>>>>> On Tue, Apr 11, 2017 at 3:38 PM, Harivola RANDRIANJAFY <
>>>>> harivolar@xxxxxx> wrote:
>>>>>
>>>>>> Tantely,
>>>>>>
>>>>>>
>>>>>>
>>>>>> Below the SQL View :
>>>>>>
>>>>>>
>>>>>>
>>>>>> SELECT
>>>>>>
>>>>>> "public".users.username,
>>>>>>
>>>>>> "public".userinfo.surname,
>>>>>>
>>>>>> "public".userinfo.firstname,
>>>>>>
>>>>>> "public".userinfo.email,
>>>>>>
>>>>>> "public".userinfo.phonenumber,
>>>>>>
>>>>>> "public".users.lastlogin,
>>>>>>
>>>>>> "public".users.created,
>>>>>>
>>>>>> "public".users.disabled,
>>>>>>
>>>>>> "public".userrole."name",
>>>>>>
>>>>>> "public".users.uid
>>>>>>
>>>>>> FROM
>>>>>>
>>>>>> "public".users
>>>>>>
>>>>>> INNER JOIN "public".userinfo ON "public".users.userid =
>>>>>> "public".userinfo.userinfoid
>>>>>>
>>>>>> INNER JOIN "public".userrole ON "public".userrole.userid =
>>>>>> "public".userinfo.userinfoid
>>>>>>
>>>>>>
>>>>>>
>>>>>>
>>>>>>
>>>>>> Thanks,
>>>>>>
>>>>>> Harivola
>>>>>>
>>>>>>
>>>>>>
>>>>>> *De :* Dhis2-users [mailto:dhis2-users-bounces+harivolar=
>>>>>> psi.mg@xxxxxxxxxxxxxxxxxxx] *De la part de* Raminosoa Rabemanantsoa,
>>>>>> Tantely
>>>>>> *Envoyé :* lundi 10 avril 2017 17:19
>>>>>> *À :* DHIS 2 Users list <dhis2-users@xxxxxxxxxxxxxxxxxxx>; DHIS 2
>>>>>> Developers list <dhis2-devs@xxxxxxxxxxxxxxxxxxx>
>>>>>> *Objet :* [Dhis2-users] DHIS2 users list from SQL View
>>>>>>
>>>>>>
>>>>>>
>>>>>> Dear Community,
>>>>>>
>>>>>>
>>>>>>
>>>>>> I would like to output from SQL View a list of all DHIS2 users by
>>>>>> using headers like the information listed by the users details information,
>>>>>> like illustrated by the image below (inside red ellipse).
>>>>>>
>>>>>>
>>>>>>
>>>>>>
>>>>>> ​
>>>>>>
>>>>>>
>>>>>>
>>>>>> I am using DHIS2 2.24. I would like to check the users table fields
>>>>>> but I could not output any information from the following query
>>>>>>
>>>>>>
>>>>>>
>>>>>> select * from users;
>>>>>>
>>>>>>
>>>>>>
>>>>>> as trhere are some protected info.
>>>>>>
>>>>>>
>>>>>>
>>>>>> My question is in which fields should I base the SQL query to output
>>>>>> such information?
>>>>>>
>>>>>>
>>>>>>
>>>>>> Thank you for your support,
>>>>>>
>>>>>>
>>>>>>
>>>>>> Regards,
>>>>>>
>>>>>>
>>>>>>
>>>>>> Tantely
>>>>>>
>>>>>> ​
>>>>>>
>>>>>>
>>>>>>
>>>>>>
>>>>>>
>>>>>>
>>>>>>
>>>>>>
>>>>>> *This message and its attachments are confidential and solely for the
>>>>>> intended recipients. If received in error, please delete them and notify
>>>>>> the sender via reply e-mail immediately.*
>>>>>>
>>>>>>
>>>>>> <https://www.avast.com/sig-email?utm_medium=email&utm_source=link&utm_campaign=sig-email&utm_content=emailclient> Garanti
>>>>>> sans virus. www.avast.com
>>>>>> <https://www.avast.com/sig-email?utm_medium=email&utm_source=link&utm_campaign=sig-email&utm_content=emailclient>
>>>>>>
>>>>>
>>>>>
>>>>> *This message and its attachments are confidential and solely for the
>>>>> intended recipients. If received in error, please delete them and notify
>>>>> the sender via reply e-mail immediately.*
>>>>>
>>>>> _______________________________________________
>>>>> 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
>>>>>
>>>>>
>>>>
>>>>
>>>> --
>>>> Jason P. Pickering
>>>> email: jason.p.pickering@xxxxxxxxx
>>>> tel:+46764147049 <+46%2076%20414%2070%2049>
>>>>
>>>
>>> *This message and its attachments are confidential and solely for the
>>> intended recipients. If received in error, please delete them and notify
>>> the sender via reply e-mail immediately.*
>>>
>>
>>
>>
>> --
>> Jason P. Pickering
>> email: jason.p.pickering@xxxxxxxxx
>> tel:+46764147049 <+46%2076%20414%2070%2049>
>>
>
>
> *This message and its attachments are confidential and solely for the
> intended recipients. If received in error, please delete them and notify
> the sender via reply e-mail immediately.*
>



-- 
Jason P. Pickering
email: jason.p.pickering@xxxxxxxxx
tel:+46764147049