How to list all system users on a Plesk server?

Follow

Comments

5 comments

  • Avatar
    Michel Otte (Edited )

    The query is wrong, it should be something like:

        select d.name, u.login from hosting h, domains d, sys_users u where d.id = h.dom_id and d.id = h.dom_id and h.sys_user_id = u.id

    0
    Comment actions Permalink
  • Avatar
    Michel Otte

    Or, if you prefer ANSI-92 SQL join syntax:

        select name, login from domains inner join hosting on domains.id = hosting.dom_id inner join sys_users on hosting.sys_user_id = sys_users.id where domains.id = hosting.dom_id

    0
    Comment actions Permalink
  • Avatar
    Ivan Postnikov

    Hello @Michel,

    I have checked the queries from the article and they work as expected:

    # plesk db "select name, login from domains inner join sys_users on domains.id = sys_users.id"
    +---------------+-----------+
    | name | login |
    +---------------+-----------+
    | example.com | jdoe |
    | plesktest.tld | plesktest |

     

    MariaDB [psa]> select name, login from domains inner join sys_users on domains.id = sys_users.id;
    +---------------+-----------+
    | name | login |
    +---------------+-----------+
    | example.com | jdoe |
    | plesktest.tld | plesktest |

     

    The query you provided gives a list of domains, not subscriptions:
    MariaDB [psa]> select name, login from domains inner join hosting on domains.id = hosting.dom_id inner join sys_users on hosting.sys_user_id = sys_users.id where domains.id = hosting.dom_id;
    +---------------+-----------+
    | name | login |
    +---------------+-----------+
    | plesktest.tld | plesktest |
    | example.com | jdoe |
    | example.net | jdoe |

    0
    Comment actions Permalink
  • Avatar
    Michel Otte

    Hi,

     

    I get complete bogus results with these queries on several production environments with several hundred domains. If you look at the table definitions of domains and sys_users, you'll see that both tables have an auto-generated id column, so there's no guarantee the results are correct with the queries you suggest here! If the auto increment fields are out of sync (which they are in our case), you will get the wrong username for a subscription. That's where the hosting table comes in, as it uses the real reference to domains / sys_users by dom_id / sys_user_id.

     

    You are correct however that my queries return users for all domains, as opposed to all subscriptions. That can easiliy be fixed by adding "AND domains.parentDomainId=0", i.e.:

     

        select d.name, u.login from hosting h, domains d, sys_users u where d.id = h.dom_id and d.id = h.dom_id and h.sys_user_id = u.id and d.parentDomainId = 0

    or:

        select name, login from domains inner join hosting on domains.id = hosting.dom_id inner join sys_users on hosting.sys_user_id = sys_users.id
        where domains.id = hosting.dom_id and domains.parentDomainId = 0

    0
    Comment actions Permalink
  • Avatar
    Pavel Mikhaylov

    Hi Michel,

    Thanks for noticing. To get correct results, please use the following query:

    SELECT name, login FROM domains JOIN hosting ON domains.id=hosting.dom_id JOIN sys_users ON hosting.sys_user_id=sys_users.id

    1
    Comment actions Permalink

Please sign in to leave a comment.

Have more questions? Submit a request