Audit members of acl*security for more than 12 months of no activity (May 2023)
Closed, ResolvedPublicSecurity
Actions

Assigned To

Authored By

	Aklapper
	Feb 7 2022, 8:45 PM

Description

Previous tasks: T299400 or T241781.
(Filing this as I don't believe in individual calendars as a task tracking system.)

Run SELECT DISTINCT(CONCAT("https://phabricator.wikimedia.org/p/", userName)), isDisabled FROM phabricator_user.user WHERE phid NOT IN (SELECT trs.authorPHID FROM phabricator_maniphest.maniphest_transaction trs INNER JOIN phabricator_user.user u WHERE FROM_UNIXTIME(trs.dateModified) >= (NOW() - INTERVAL 1 YEAR) AND trs.authorPHID = u.phid) AND phid IN (SELECT e.src FROM phabricator_user.edge e WHERE e.dst = "PHID-PROJ-koo4qqdng27q7r65x3cw" AND e.type= 14) ORDER BY isDisabled DESC,userName; on Phab.

Details

Due Date: Jun 30 2023, 10:00 PM
Risk Rating: Informational
Author Affiliation: WMF Technology Dept

Related Objects

Mentioned In: T337305: Audit members of acl*security for more than 12 months of no activity (May 2024)
Mentioned Here: T337305: Audit members of acl*security for more than 12 months of no activity (May 2024)
T241781: Audit members of #security for more than x duration of no activity (Jan 2020)
T299400: Audit members of acl*security for more than x duration of no activity (Jan 2022)

Event Timeline

Aklapper changed the task status from Open to Stalled.Feb 7 2022, 8:45 PM

Aklapper created this task.

• Dsharpe triaged this task as Medium priority.Feb 14 2022, 5:08 PM

• Dsharpe moved this task from Incoming to Waiting on the Security-Team board.

Aklapper added a project: User-AKlapper.Mar 16 2022, 12:13 PM

Aklapper moved this task from To Triage to Policies on the Phabricator board.Mar 27 2022, 1:00 PM

sbassett moved this task from Waiting to Watching on the Security-Team board.Apr 6 2022, 7:00 PM

Aklapper renamed this task from Audit members of acl*security for more than 12 months of no activity (Jan 2023) to Audit members of acl*security for more than 12 months of no activity (May 2023).May 8 2023, 10:54 AM

Aklapper changed the task status from Stalled to Open.

Aklapper updated the task description. (Show Details)

mysql:phstats@m3-slave.eqiad.wmnet [phabricator_maniphest]> SELECT DISTINCT(CONCAT("https://phabricator.wikimedia.org/p/", userName)), isDisabled FROM phabricator_user.user WHERE phid NOT IN (SELECT trs.authorPHID FROM phabricator_maniphest.maniphest_transaction trs INNER JOIN phabricator_user.user u WHERE FROM_UNIXTIME(trs.dateModified) >= (NOW() - INTERVAL 1 YEAR) AND trs.authorPHID = u.phid) AND phid IN (SELECT e.src FROM phabricator_user.edge e WHERE e.dst = "PHID-PROJ-koo4qqdng27q7r65x3cw" AND e.type= 14) ORDER BY isDisabled DESC,userName;
+------------------------------------------------------------+------------+
| (CONCAT("https://phabricator.wikimedia.org/p/", userName)) | isDisabled |
+------------------------------------------------------------+------------+
| https://phabricator.wikimedia.org/p/aezell                 |          1 |
| https://phabricator.wikimedia.org/p/chasemp                |          1 |
| https://phabricator.wikimedia.org/p/EBjune                 |          1 |
| https://phabricator.wikimedia.org/p/ema                    |          1 |
| https://phabricator.wikimedia.org/p/eprodromou             |          1 |
| https://phabricator.wikimedia.org/p/fsero                  |          1 |
| https://phabricator.wikimedia.org/p/Gilles                 |          1 |
| https://phabricator.wikimedia.org/p/JAufrecht              |          1 |
| https://phabricator.wikimedia.org/p/JBennett               |          1 |
| https://phabricator.wikimedia.org/p/JHedden                |          1 |
| https://phabricator.wikimedia.org/p/Kalliope               |          1 |
| https://phabricator.wikimedia.org/p/LarsWirzenius          |          1 |
| https://phabricator.wikimedia.org/p/marcella               |          1 |
| https://phabricator.wikimedia.org/p/mobrovac               |          1 |
| https://phabricator.wikimedia.org/p/Pchelolo               |          1 |
| https://phabricator.wikimedia.org/p/Phamhi                 |          1 |
| https://phabricator.wikimedia.org/p/Thargrovewmf           |          1 |
| https://phabricator.wikimedia.org/p/APalmer_WMF            |          0 |
| https://phabricator.wikimedia.org/p/CSteigenberger         |          0 |
| https://phabricator.wikimedia.org/p/Jalexander             |          0 |
| https://phabricator.wikimedia.org/p/Kbrown                 |          0 |
| https://phabricator.wikimedia.org/p/Matiia                 |          0 |
| https://phabricator.wikimedia.org/p/Mdennis-WMF            |          0 |
| https://phabricator.wikimedia.org/p/Melos                  |          0 |
| https://phabricator.wikimedia.org/p/mmodell                |          0 |
| https://phabricator.wikimedia.org/p/PEarleyWMF             |          0 |
| https://phabricator.wikimedia.org/p/Pmlineditor            |          0 |
| https://phabricator.wikimedia.org/p/QuiteUnusual           |          0 |
| https://phabricator.wikimedia.org/p/Ruslik0                |          0 |
| https://phabricator.wikimedia.org/p/Rxy                    |          0 |
| https://phabricator.wikimedia.org/p/security_team_bot      |          0 |
| https://phabricator.wikimedia.org/p/Shanmugamp7            |          0 |
| https://phabricator.wikimedia.org/p/Trijnstel              |          0 |
| https://phabricator.wikimedia.org/p/Wim_b                  |          0 |
+------------------------------------------------------------+------------+
34 rows in set (3.790 sec)

Thanks, @Aklapper. Just glancing at the db list above, I think a lot of these folks can simply be removed from any acl*security list. The Security-Team should probably take a first pass at this.

Ok, I did a quick audit of the current acl*security sub-projects, just based upon my own knowledge and removed the following accounts:

https://phabricator.wikimedia.org/p/Halfak - former WMF employee
https://phabricator.wikimedia.org/p/MaxSem - former WMF employee
https://phabricator.wikimedia.org/p/dbarrett - former WMF employee
https://phabricator.wikimedia.org/p/kaldari - former WMF employee
https://phabricator.wikimedia.org/p/BStorm - former WMF employee
https://phabricator.wikimedia.org/p/SPoore - former WMF employee

For the ones in your db list above that are disabled, the only one I could find that still existed within acl*security was @chasemp, as a watcher. There are a few more accounts (notably @Dsharpe) that could also likely be removed from the acl*security watchers list, but I do not have permission to do that.

For the accounts in your db list which are still enabled, the following are active Community members or WMF employees and should likely retain Phab security access even if they do not regularly use it:

The remaining accounts from your db list are summarized as follows:

https://phabricator.wikimedia.org/p/CSteigenberger - former WMF staff but does not appear to have access
https://phabricator.wikimedia.org/p/Jalexander - former WMF staff but does not appear to have access outside of still being a project watcher
https://phabricator.wikimedia.org/p/Matiia - does not appear to have access
https://phabricator.wikimedia.org/p/Melos - does not appear to have access
https://phabricator.wikimedia.org/p/mmodell - former WMF staff, still has access, grandfathered in?
https://phabricator.wikimedia.org/p/Pmlineditor - does not appear to have access
https://phabricator.wikimedia.org/p/Ruslik0 - does not appear to have access
https://phabricator.wikimedia.org/p/Rxy - does not appear to have access
https://phabricator.wikimedia.org/p/security_team_bot - needed for Security-Team future work
https://phabricator.wikimedia.org/p/Shanmugamp7 - does not appear to have access
https://phabricator.wikimedia.org/p/Trijnstel - does not appear to have access

sbassett moved this task from Backlog to In Progress on the user-sbassett board.May 8 2023, 4:49 PM

Ping @Aklapper - any thoughts about that last list of users above? And why they seem to have security access according to Phab's db?

that could also likely be removed from the acl*security watchers list, but I do not have permission to do that.

It's impossible - there is no API or UI for it, for anyone.

but does not appear to have access

And that means that my SQL query is buggy. Sorry! (But good to know and find out!)

No time to looking into the reasons right now, so instead I went to https://phabricator.wikimedia.org/api/project.query with phids = ["PHID-PROJ-koo4qqdng27q7r65x3cw"] and used the members array to feed the phids into SELECT DISTINCT(CONCAT("https://phabricator.wikimedia.org/p/", userName)), isDisabled FROM phabricator_user.user WHERE phid NOT IN (SELECT trs.authorPHID FROM phabricator_maniphest.maniphest_transaction trs INNER JOIN phabricator_user.user u WHERE FROM_UNIXTIME(trs.dateModified) >= (NOW() - INTERVAL 1 YEAR) AND trs.authorPHID = u.phid) AND (phid = "PHID-USER-..." OR phid = "PHID-USER-...");

Output is

+------------------------------------------------------------+------------+
| (CONCAT("https://phabricator.wikimedia.org/p/", userName)) | isDisabled |
+------------------------------------------------------------+------------+
| https://phabricator.wikimedia.org/p/Wiki13                 |          0 |
| https://phabricator.wikimedia.org/p/Wim_b                  |          0 |
| https://phabricator.wikimedia.org/p/APalmer_WMF            |          0 |
| https://phabricator.wikimedia.org/p/QuiteUnusual           |          0 |
| https://phabricator.wikimedia.org/p/Kbrown                 |          0 |
| https://phabricator.wikimedia.org/p/mmodell                |          0 |
| https://phabricator.wikimedia.org/p/priv_eng_sync          |          0 |
| https://phabricator.wikimedia.org/p/Mdennis-WMF            |          0 |
| https://phabricator.wikimedia.org/p/CAshraf                |          0 |
| https://phabricator.wikimedia.org/p/PEarleyWMF             |          0 |
+------------------------------------------------------------+------------+

That leaves us with looking into https://phabricator.wikimedia.org/p/Wiki13 and https://phabricator.wikimedia.org/p/priv_eng_sync/ and https://phabricator.wikimedia.org/p/mmodell/ I guess.

In T301181#8864420, @Aklapper wrote:

That leaves us with looking into https://phabricator.wikimedia.org/p/Wiki13 and https://phabricator.wikimedia.org/p/priv_eng_sync/ and https://phabricator.wikimedia.org/p/mmodell/ I guess.

Wiki13 is a current steward, so they should be ok. Not sure what priv_eng_sync is - @JFishback_WMF? I've gone ahead and removed @mmodell's acl*security access. If they need it as a volunteer, they can re-request it IMO.

Yep, priv_eng_sync is me. Please don't remove.

Sounds like we are done. Thanks everyone. Next year's task is T337305.

Thanks, @Aklapper.

sbassett moved this task from In Progress to Done on the user-sbassett board.May 23 2023, 2:03 PM

sbassett changed the visibility from "Custom Policy" to "Public (No Login Required)".

sbassett changed the edit policy from "Custom Policy" to "All Users".

sbassett changed Risk Rating from N/A to Informational.

Audit members of acl*security for more than 12 months of no activity (May 2023)Closed, ResolvedPublicSecurityActions

Description

Details

Related Objects

Event Timeline

Audit members of acl*security for more than 12 months of no activity (May 2023)
Closed, ResolvedPublicSecurity
Actions