Privileged Access Review Automation

Project Overview

The Privileged Access Review Automation project focused on developing a solution to streamline the process of reviewing and certifying privileged access across the organization. By automating data collection, identifying outliers, and generating actionable reports, this solution significantly improved the efficiency and effectiveness of access reviews.

The Challenge

Privileged access reviews are critical for security but often face challenges:

• Time-consuming manual data collection from multiple systems
• Difficulty identifying high-risk access patterns and outliers
• Inconsistent review processes across different departments
• Limited visibility into review completion and findings
• Challenges in maintaining an audit trail for compliance purposes

The Solution

I developed a comprehensive automation solution that:

• Automatically collects privileged access data from multiple systems
• Applies analytics to identify outliers and potential risks
• Generates customized review tasks for system owners and managers
• Tracks review progress and sends automated reminders
• Creates detailed reports for auditors and security teams

Risk Scoring Dashboard

This interactive dashboard provides a real-time view of privileged access risks across the organization. The risk scoring algorithm considers factors such as account dormancy, excessive privileges, and unusual access patterns. Reviewers can filter by department, system, or risk level to focus their attention on the highest-risk areas.

Try Interactive Dashboard

Implementation Highlights

Data Collection Automation

The first phase focused on automating data collection from various systems:

import ldap3
from ldap3 import Server, Connection, ALL, NTLM, SUBTREE
import pandas as pd
import datetime

def extract_privileged_groups():
    """
    Extract members of privileged AD groups and export to CSV
    """
    # Connection parameters
    server = Server('ldap://dc.example.com', get_info=ALL)
    conn = Connection(
        server,
        user='domain\\service_account',
        password='password',
        authentication=NTLM,
        auto_bind=True
    )
    
    # List of privileged groups to check
    privileged_groups = [
        'Domain Admins',
        'Enterprise Admins',
        'Schema Admins',
        'Backup Operators',
        'Account Operators',
        'Server Operators'
    ]
    
    all_results = []
    
    # Search each privileged group
    for group in privileged_groups:
        group_dn = f'CN={group},CN=Builtin,DC=example,DC=com'
        
        # Find the group
        conn.search(
            search_base='DC=example,DC=com',
            search_filter=f'(&(objectClass=group)(cn={group}))',
            search_scope=SUBTREE,
            attributes=['distinguishedName']
        )
        
        if len(conn.entries) > 0:
            group_dn = conn.entries[0].distinguishedName.value
            
            # Get group members
            conn.search(
                search_base=group_dn,
                search_filter='(objectClass=user)',
                search_scope=SUBTREE,
                attributes=['sAMAccountName', 'displayName', 'mail', 'whenCreated', 'lastLogon']
            )
            
            # Process results
            for entry in conn.entries:
                try:
                    member_data = {
                        'Group': group,
                        'Username': entry.sAMAccountName.value,
                        'DisplayName': entry.displayName.value if hasattr(entry, 'displayName') else '',
                        'Email': entry.mail.value if hasattr(entry, 'mail') else '',
                        'Created': entry.whenCreated.value if hasattr(entry, 'whenCreated') else '',
                        'LastLogon': datetime.datetime.fromtimestamp(int(entry.lastLogon.value) / 10000000 - 11644473600) if hasattr(entry, 'lastLogon') and entry.lastLogon.value != 0 else 'Never'
                    }
                    all_results.append(member_data)
                except Exception as e:
                    print(f"Error processing {entry.sAMAccountName.value}: {e}")
    
    # Create DataFrame and export
    df = pd.DataFrame(all_results)
    export_date = datetime.datetime.now().strftime("%Y%m%d")
    df.to_csv(f'privileged_access_{export_date}.csv', index=False)
    print(f"Exported {len(df)} privileged account records")
    
    return df

if __name__ == "__main__":
    extract_privileged_groups()
                

Analytics and Risk Identification

The solution includes advanced analytics to identify potential risks:

-- SQL Query to identify potential access anomalies
-- This query finds users with privileged access who haven't logged in for 90+ days

WITH PrivilegedUsers AS (
    SELECT 
        u.UserID,
        u.Username,
        u.Department,
        u.LastLoginDate,
        COUNT(pa.AccessRightID) AS PrivilegeCount
    FROM 
        Users u
    JOIN 
        UserAccessRights uar ON u.UserID = uar.UserID
    JOIN 
        PrivilegedAccess pa ON uar.AccessRightID = pa.AccessRightID
    WHERE 
        pa.AccessLevel >= 8 -- High privilege threshold
    GROUP BY 
        u.UserID, u.Username, u.Department, u.LastLoginDate
)

SELECT 
    pu.UserID,
    pu.Username,
    pu.Department,
    pu.LastLoginDate,
    pu.PrivilegeCount,
    DATEDIFF(day, pu.LastLoginDate, GETDATE()) AS DaysSinceLogin,
    s.SystemName,
    s.Criticality,
    ar.AccessRightName,
    ar.AccessLevel
FROM 
    PrivilegedUsers pu
JOIN 
    UserAccessRights uar ON pu.UserID = uar.UserID
JOIN 
    AccessRights ar ON uar.AccessRightID = ar.AccessRightID
JOIN 
    Systems s ON ar.SystemID = s.SystemID
WHERE 
    (pu.LastLoginDate IS NULL OR DATEDIFF(day, pu.LastLoginDate, GETDATE()) > 90)
    AND s.Criticality >= 'High'
ORDER BY 
    s.Criticality DESC,
    DaysSinceLogin DESC,
    pu.PrivilegeCount DESC;
                

Review Progress Tracking

Review Progress Dashboard

This dashboard provides real-time visibility into the review process, showing completion rates by department, outstanding high-risk items, and review trends over time. The interactive filters allow stakeholders to drill down into specific areas of concern and track remediation progress.

Results and Impact

The Privileged Access Review Automation solution delivered significant benefits:

Business Impact

• Enhanced security posture through more thorough privileged access reviews
• Improved compliance with regulatory requirements
• Reduced administrative burden on IT and security teams
• Better visibility into privileged access across the organization
• More effective risk-based approach to access governance