Security

Tutorials

Audit CODEOWNERS Against Okta to Find Orphaned Code Ownership with SQL

Blog header for auditing GitHub CODEOWNERS files against Okta user directory with SQL

TL;DR: The Git Source Integration syncs CODEOWNERS files. Join with Okta and GitHub data to find orphaned code ownership — accounts that left the company but remain in CODEOWNERS.

CODEOWNERS files assign code responsibility to specific GitHub users. When engineers leave the company, their GitHub handles remain in CODEOWNERS files. Pull requests still notify non-existent accounts. Code ownership documentation decays. Tracking this manually means comparing CODEOWNERS files across hundreds of repos against your identity provider, then creating tickets to update each stale entry.

The Git Source Integration syncs CODEOWNERS content. Combined with the Okta Source Integration and GitHub Source Integration, you can query orphaned code ownership with SQL.

Required Plugins

This use case requires three CloudQuery source integrations:

  1. Git Source Integration - Syncs CODEOWNERS file content
  2. GitHub Source Integration - Maps GitHub handles to email addresses
  3. Okta Source Integration - Provides employee status from your identity provider

Configuration

Configure the Git source integration to sync CODEOWNERS files:

tables:
- git_files:
glob_patterns:
- '**/CODEOWNERS'

Configure GitHub and Okta plugins per their documentation to sync organization members and user data.

Query Orphaned CODEOWNERS

Find CODEOWNERS entries for inactive Okta accounts:

WITH codeowners_parsed AS (
SELECT
gf.repository_url,
regexp_matches(
convert_from(gf.content, 'UTF8'),
'(?:^|\s)@([a-zA-Z0-9_-]+(?:/[a-zA-Z0-9_-]+)?)',
'gm'
) AS owner_handle_match
FROM git_files gf
WHERE gf.name = 'CODEOWNERS'
),
owners_flat AS (
SELECT DISTINCT
repository_url,
owner_handle_match[1] AS github_handle
FROM codeowners_parsed
WHERE owner_handle_match[1] NOT LIKE '%/%' -- Exclude team handles like org/team
),
owners_with_email AS (
SELECT
o.repository_url,
o.github_handle,
gm.email AS github_email
FROM owners_flat o
LEFT JOIN github_organization_members gm ON lower(o.github_handle) = lower(gm.login)
)
SELECT
owe.repository_url,
owe.github_handle,
owe.github_email,
COALESCE(u.status, 'NOT_FOUND') AS okta_status
FROM owners_with_email owe
LEFT JOIN okta_users u ON lower(owe.github_email) = lower(u.email)
WHERE owe.github_email IS NULL -- GitHub user not found or has no public email
OR u.status IS NULL -- No matching Okta account
OR u.status != 'ACTIVE' -- Okta account inactive
ORDER BY owe.repository_url

The query:

  1. Parses CODEOWNERS content to extract GitHub handles using regex
  2. Excludes team handles (format: @org/team-name)
  3. Joins GitHub handles with organization member emails
  4. Joins GitHub emails with Okta user status
  5. Filters for missing or inactive accounts

Results

The query returns:

  • repository_url - Repository with stale CODEOWNERS entry
  • github_handle - GitHub username in CODEOWNERS
  • github_email - Email from GitHub profile (may be NULL)
  • okta_status - ACTIVE, DEPROVISIONED, SUSPENDED, or NOT_FOUND

Create tracking issues for repos with inactive owners. Assign code ownership to active team members.

Team Ownership Analysis

Find repos owned by specific teams:

WITH codeowners_parsed AS (
SELECT
gf.repository_url,
regexp_matches(
convert_from(gf.content, 'UTF8'),
'(?:^|\s)@([a-zA-Z0-9_-]+/[a-zA-Z0-9_-]+)',
'gm'
) AS team_handle_match
FROM git_files gf
WHERE gf.name = 'CODEOWNERS'
),
teams_flat AS (
SELECT DISTINCT
repository_url,
team_handle_match[1] AS team_handle
FROM codeowners_parsed
)
SELECT
repository_url,
team_handle
FROM teams_flat
WHERE team_handle = 'your-org/platform-team'
ORDER BY repository_url

This identifies all repos with specific team ownership assignments.

Missing CODEOWNERS Files

Find repositories without CODEOWNERS:

WITH repos_with_codeowners AS (
SELECT repository_url FROM git_files WHERE name = 'CODEOWNERS'
)
SELECT gr.url, gr.owner, gr.full_name
FROM git_repositories gr
LEFT JOIN repos_with_codeowners rc ON rc.repository_url = gr.url
WHERE rc.repository_url IS NULL

Repos without CODEOWNERS files have undefined code ownership, making it difficult to route reviews and assign responsibility.

Get Started

The Git Source Integration, GitHub Source Integration, and Okta Source Integration are available on CloudQuery Hub. See the Git tables documentation for the full schema. Download the CloudQuery CLI to start auditing code ownership.

Related:

FAQ

What if GitHub handles in CODEOWNERS don't have public emails?

GitHub users can hide email addresses. The query returns NULL for github_email when emails are private. You'll need to manually verify these accounts or use GitHub Enterprise with required email visibility.

How do I handle CODEOWNERS entries for external contributors?

External contributors won't appear in your Okta directory. Filter the query to exclude known external collaborator emails or GitHub handles, or flag them separately for manual review.

Can I audit CODEOWNERS against other identity providers?

Yes. Replace the Okta plugin with Entra ID, Google Workspace, or any identity provider CloudQuery supports. The SQL pattern remains the same.

What if CODEOWNERS uses email addresses instead of GitHub handles?

Modify the regex pattern to extract email addresses. Some CODEOWNERS files use emails directly rather than @username handles. Parse both patterns and union the results.

How often should I run this audit?

Running monthly catches recently departed employees whose accounts were removed from active status. Combine with CloudQuery scheduled syncs to automate the audit and generate monthly reports.

Can I check CODEOWNERS in subdirectories?

Yes. The glob pattern **/CODEOWNERS matches CODEOWNERS files at any directory depth. monorepos often include CODEOWNERS files in subdirectories for per-package ownership.

What if the same person appears in multiple CODEOWNERS files?

The query returns one row per repository-handle combination. Aggregate by github_handle to see all repos where a specific inactive user is listed as an owner.

How do I automate remediation?

Export query results and use GitHub API or git automation to create pull requests removing stale entries. Or integrate with Jira/Linear to create tracking issues for each affected repo.

Try CloudQuery yourself

Self-hosted, open source, and free to start. Or schedule a demo to see the managed version.

Related posts

AWS

Security

Tutorials

Finding AWS Unrestricted Outbound Access in 2026: EC2, Lambda, and Beyond

Find EC2, Lambda, and ECS Fargate resources with unrestricted outbound access in AWS using SQL. Why egress controls still break attack chains in 2026.

Joe Karlsson

Joe Karlsson

May 5, 2026

AWS

Security

Tutorials

AWS IAM Identity Center with Google Workspace as an IdP: 2026 Step-by-Step Guide

Set up AWS IAM Identity Center with Google Workspace: SAML authentication, SCIM user provisioning, and group sync with CLI or ssosync. Updated for 2026.

Joe Karlsson

Joe Karlsson

May 5, 2026

GCP

Security

Tutorials

Creating Cross-Project Service Accounts in GCP (Console + gcloud CLI)

Create a GCP service account that reads across all your projects with one credential. Covers gcloud CLI, org-level vs.

Joe Karlsson

Joe Karlsson

May 5, 2026

AWS

Cloud Infrastructure

Security

Tutorials

Integrate AbuseIPDB Threat Intelligence with CloudQuery

Enrich your cloud asset inventory with AbuseIPDB threat intelligence to identify public cloud IPs with known abuse reports.

Joe Karlsson

Joe Karlsson

April 20, 2026

AWS

Security

Tutorials

AWS IAM Identity Center (Formerly AWS SSO): The Complete 2026 Guide

AWS SSO is now AWS IAM Identity Center. Set it up, configure permission sets, use the AWS CLI, and audit access across accounts with SQL.

Joe Karlsson

Joe Karlsson

April 6, 2026

AWS

Security

Tutorials

Set Up AWS CLI with SSO (IAM Identity Center) — Step by Step

Set up AWS CLI v2 with IAM Identity Center in 5 minutes. Covers SSO registration scopes, sso-session config, token refresh, and multi-account profiles.

Yevgeny Pats

Yevgeny Pats

February 25, 2026

Security

Tutorials

How to Detect Public Databases Across AWS, Azure, and GCP with CloudQuery Policies

Detect publicly accessible databases across AWS RDS, Azure, and GCP Cloud SQL. Step-by-step CloudQuery Policies tutorial with copy-paste ClickHouse SQL.

Joe Karlsson

Joe Karlsson

February 12, 2026

Security

Tutorials

Find Commits by Departed Employees Across All Repos with SQL

Identify code changes made by employees who left your organization by cross-referencing git commit authors against your identity provider with SQL.

Joe Karlsson

Joe Karlsson

January 4, 2026

Security

Tutorials

Find Every repo Missing a LICENSE File with One SQL Query

Identify repositories without LICENSE files across your organization using SQL. No cloning required, no scripts to maintain - just query your git file data.

Joe Karlsson

Joe Karlsson

January 4, 2026

Own your pipeline.
Own your data.