How to list all system users on a Plesk server?

• 

  Michel Otte September 10, 2018 15:22 (Edited September 10, 2018 15:24)

  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
• 

  Michel Otte September 10, 2018 15:30

  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
• 

  Ivan Postnikov September 11, 2018 14:06

  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
• 

  Michel Otte September 11, 2018 14:27

  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
• 

  Pavel Mikhaylov September 19, 2018 20:01

  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.