← Back to team overview

dhis2-users team mailing list archive

Re: [Dhis2-devs] DHIS2 users list from SQL View

 

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

JPEG image


References