Control: 1.3.1 Ensure inactive users are reviewed and removed periodically

Description

Track inactive user accounts and periodically remove them.

Rationale

User accounts that have been inactive for a long period of time are enlarging the surface of attack. Inactive users with high-level privileges are of particular concern, as these accounts are more likely to be targets for attackers. This could potentially allow access to large portions of an organization should such an attack prove successful. It is recommended to remove them as soon as possible in order to prevent this.

Audit

For each repository in use, verify that all user accounts are active.

Remediation

For each repository in use, review inactive user accounts (members that left the organization, etc.) and remove them.

Usage

Run the control in your terminal:

powerpipe control run github_compliance.control.cis_supply_chain_v100_1_3_1

Snapshot and share results via Turbot Pipes:

powerpipe login
powerpipe control run github_compliance.control.cis_supply_chain_v100_1_3_1 --share

SQL

This control uses a named query:

with repo as (
  select
    repository_full_name,
    count(sha) as commit_count,
    author_login
  from
    github_commit c
    join github_my_repository r on c.repository_full_name = r.name_with_owner
  where
    authored_date >= now() - interval '30 day'
  group by
    repository_full_name,
    author_login
),
logins as (
  select
    c.user_login as login,
    name_with_owner as repository_full_name,
    url
  from
    github_my_repository
    join github_repository_collaborator c on c.repository_full_name = name_with_owner
)
select
  -- Required Columns
  l.url as resource,
  case when commit_count is null then
    'alarm'
  else
    'ok'
  end as status,
  case when commit_count is null then
    login || ' is an inactive user.'
  else
    login || ' is an active user.'
  end as reason,
  -- Additional Dimensions
  l.repository_full_name
from
  logins l
  left join repo r on l.login = r.author_login
  and l.repository_full_name = r.repository_full_name;