{"id":353610,"date":"1970-01-01T00:00:00","date_gmt":"1970-01-01T00:00:00","guid":{"rendered":"https:\/\/www.ninjaone.com\/?post_type=script_hub&p=353610"},"modified":"2024-10-28T10:16:29","modified_gmt":"2024-10-28T10:16:29","slug":"die-einschraenkung-des-python-zugriffs-in-excel","status":"publish","type":"script_hub","link":"https:\/\/www.ninjaone.com\/de\/script-hub\/die-einschraenkung-des-python-zugriffs-in-excel\/","title":{"rendered":"Die Einschr\u00e4nkung des Python-Zugriffs in Microsoft Excel mit PowerShell"},"content":{"rendered":"

In der modernen Arbeitswelt werden Datensicherheit und Zugriffsmanagement immer wichtiger, vor allem da Unternehmen verschiedene Programmiersprachen und Tools in ihre t\u00e4glichen Abl\u00e4ufe integrieren.<\/p>\n

Eine solche Integration ist die Verwendung von Python in Microsoft Excel, eine leistungsstarke Funktion, die die Produktivit\u00e4t steigern kann, aber auch potenzielle Sicherheitsrisiken birgt. F\u00fcr IT-Experten und Managed Service Provider (MSPs)<\/a> ist die Verwaltung und Einschr\u00e4nkung des Python-Zugriffs in Excel f\u00fcr die Aufrechterhaltung einer sicheren Umgebung unerl\u00e4sslich.<\/p>\n

In diesem Blogbeitrag wird ein PowerShell-Skript<\/a> vorgestellt, mit dem die Verwendung von Python in Excel gesteuert wird<\/strong>, um sicherzustellen, dass die Sicherheitsprotokolle eingehalten werden, ohne die Funktionalit\u00e4t zu beeintr\u00e4chtigen.<\/p>\n

Kontext<\/h2>\n

Mit der wachsenden Popularit\u00e4t von Python in der Datenanalyse und -automatisierung bietet seine Integration in Excel den Benutzer:innen erweiterte M\u00f6glichkeiten. Diese Integration birgt jedoch auch potenzielle Sicherheitsrisiken, insbesondere wenn Python-Skripte ohne angemessene \u00dcbersicht ausgef\u00fchrt werden.<\/p>\n

IT-Experten und MSPs m\u00fcssen die Vorteile der Python-Funktionen mit der Notwendigkeit abw\u00e4gen, sensible Daten zu sch\u00fctzen. Das hier besprochene PowerShell-Skript bietet eine L\u00f6sung, indem es Administratoren die M\u00f6glichkeit gibt, die Verwendung von Python in Excel einzuschr\u00e4nken. Sie k\u00f6nnen n\u00e4mlich entweder Sicherheitsabfragen aktivieren oder den Zugriff auf Python ganz sperren, bzw. zu den Standardeinstellungen von Microsoft zur\u00fcckkehren.<\/p>\n

Das Skript:<\/h2>\n
<#\r\n.SYNOPSIS\r\n    Restricts the use of Python in Excel for all users. By default it'll enable a security prompt but does have the option to block or to set it back to the Microsoft default (no warnings or prompts).\r\n.DESCRIPTION\r\n    Restricts the use of Python in Excel for all users. By default it'll enable a security prompt but does have the option to block or to set it back to the Microsoft default (no warnings or prompts).\r\n.EXAMPLE\r\n    (No Parameters)\r\n    \r\n    Registry::HKEY_USERS\\S-1-5-21-3870645062-3653562310-3850680542-1001\\software\\policies\\microsoft\\office\\16.0\\excel\\security\\PythonFunctionWarnings changed from 1 to 1\r\n    Registry::HKEY_USERS\\S-1-5-21-3870645062-3653562310-3850680542-1002\\software\\policies\\microsoft\\office\\16.0\\excel\\security\\PythonFunctionWarnings changed from 1 to 1\r\n    Set Registry::HKEY_USERS\\S-1-5-21-3870645062-3653562310-3850680542-1003\\software\\policies\\microsoft\\office\\16.0\\excel\\security\\PythonFunctionWarnings to 1\r\n\r\nPARAMETER: -Block\r\n    Blocks the use of Python in Excel.\r\n.EXAMPLE\r\n    -Block\r\n\r\n    Registry::HKEY_USERS\\S-1-5-21-3870645062-3653562310-3850680542-1001\\software\\policies\\microsoft\\office\\16.0\\excel\\security\\PythonFunctionWarnings changed from 1 to 2\r\n    Registry::HKEY_USERS\\S-1-5-21-3870645062-3653562310-3850680542-1002\\software\\policies\\microsoft\\office\\16.0\\excel\\security\\PythonFunctionWarnings changed from 1 to 2\r\n    Set Registry::HKEY_USERS\\S-1-5-21-3870645062-3653562310-3850680542-1003\\software\\policies\\microsoft\\office\\16.0\\excel\\security\\PythonFunctionWarnings to 2\r\n\r\nPARAMETER: -IncludeNewUsers\r\n    Adds the registry key to the Default Profile so that this change carriers over when new accounts are created.\r\n.EXAMPLE\r\n    -IncludeNewUsers\r\n\r\n    Set Registry::HKEY_USERS\\DefaultProfile\\software\\policies\\microsoft\\office\\16.0\\excel\\security\\PythonFunctionWarnings to 1\r\n    Registry::HKEY_USERS\\S-1-5-21-3870645062-3653562310-3850680542-1001\\software\\policies\\microsoft\\office\\16.0\\excel\\security\\PythonFunctionWarnings changed from 1 to 1\r\n    Registry::HKEY_USERS\\S-1-5-21-3870645062-3653562310-3850680542-1002\\software\\policies\\microsoft\\office\\16.0\\excel\\security\\PythonFunctionWarnings changed from 1 to 1\r\n    Set Registry::HKEY_USERS\\S-1-5-21-3870645062-3653562310-3850680542-1003\\software\\policies\\microsoft\\office\\16.0\\excel\\security\\PythonFunctionWarnings to 1\r\n\r\nPARAMETER: -ChangeBackToMicrosoftDefault\r\n    Resets the setting\/restriction back to the Microsoft Default (enabled with no security prompt).\r\n.EXAMPLE\r\n    -ChangeBackToMicrosoftDefault\r\n\r\n    Registry::HKEY_USERS\\S-1-5-21-3870645062-3653562310-3850680542-1001\\software\\policies\\microsoft\\office\\16.0\\excel\\security\\PythonFunctionWarnings changed from 1 to 0\r\n    Registry::HKEY_USERS\\S-1-5-21-3870645062-3653562310-3850680542-1002\\software\\policies\\microsoft\\office\\16.0\\excel\\security\\PythonFunctionWarnings changed from 1 to 0\r\n    Set Registry::HKEY_USERS\\S-1-5-21-3870645062-3653562310-3850680542-1003\\software\\policies\\microsoft\\office\\16.0\\excel\\security\\PythonFunctionWarnings to 0\r\n.LINK\r\n    https:\/\/support.microsoft.com\/en-us\/office\/data-security-and-python-in-excel-33cc88a4-4a87-485e-9ff9-f35958278327\r\n.OUTPUTS\r\n    None\r\n.NOTES\r\n    Minimum OS Architecture Supported: Windows 8.1, Server 2012\r\n    Release Notes: Initial Release\r\nBy using this script, you indicate your acceptance of the following legal terms as well as our Terms of Use at https:\/\/www.ninjaone.com\/terms-of-use.\r\n    Ownership Rights: NinjaOne owns and will continue to own all right, title, and interest in and to the script (including the copyright). NinjaOne is giving you a limited license to use the script in accordance with these legal terms. \r\n    Use Limitation: You may only use the script for your legitimate personal or internal business purposes, and you may not share the script with another party. \r\n    Republication Prohibition: Under no circumstances are you permitted to re-publish the script in any script library or website belonging to or under the control of any other software provider. \r\n    Warranty Disclaimer: The script is provided \u201cas is\u201d and \u201cas available\u201d, without warranty of any kind. NinjaOne makes no promise or guarantee that the script will be free from defects or that it will meet your specific needs or expectations. \r\n    Assumption of Risk: Your use of the script is at your own risk. You acknowledge that there are certain inherent risks in using the script, and you understand and assume each of those risks. \r\n    Waiver and Release: You will not hold NinjaOne responsible for any adverse or unintended consequences resulting from your use of the script, and you waive any legal or equitable rights or remedies you may have against NinjaOne relating to your use of the script. \r\n    EULA: If you are a NinjaOne customer, your use of the script is subject to the End User License Agreement applicable to you (EULA).\r\n#>\r\n\r\n[CmdletBinding()]\r\nparam (\r\n    [Parameter()]\r\n    [Switch]$Block = [System.Convert]::ToBoolean($env:blockPython),\r\n    [Parameter()]\r\n    [Switch]$IncludeNewUsers = [System.Convert]::ToBoolean($env:includeNewUsers),\r\n    [Parameter()]\r\n    [Switch]$ChangeBackToMicrosoftDefault = [System.Convert]::ToBoolean($env:changeBackToMicrosoftDefaultSetting)\r\n)\r\n\r\nbegin {\r\n\r\n    # If incompatible options are detected error out\r\n    if($Block -and $ChangeBackToMicrosoftDefault){\r\n        Write-Error \"-ChangeBackToMicrosoftDefault and -Block cannot be used together. The 'Change Back To Microsoft Default' option is to set Python in Excel back to how Microsoft ships the feature (with all security warnings disabled).\"\r\n        exit 1\r\n    }\r\n\r\n    # Write a warning message for the least secure option\r\n    if($ChangeBackToMicrosoftDefault){\r\n        Write-Warning \"Changing the setting back to the default. All Python security warnings will be disabled...\"\r\n    }\r\n\r\n    function Test-IsElevated {\r\n        $id = [System.Security.Principal.WindowsIdentity]::GetCurrent()\r\n        $p = New-Object System.Security.Principal.WindowsPrincipal($id)\r\n        $p.IsInRole([System.Security.Principal.WindowsBuiltInRole]::Administrator)\r\n    }\r\n\r\n    # Handy registry setting function\r\n    function Set-HKProperty {\r\n        param (\r\n            $Path,\r\n            $Name,\r\n            $Value,\r\n            [ValidateSet('DWord', 'QWord', 'String', 'ExpandedString', 'Binary', 'MultiString', 'Unknown')]\r\n            $PropertyType = 'DWord'\r\n        )\r\n        if (-not $(Test-Path -Path $Path)) {\r\n            # Check if path does not exist and create the path\r\n            New-Item -Path $Path -Force | Out-Null\r\n        }\r\n        if ((Get-ItemProperty -Path $Path -Name $Name -ErrorAction SilentlyContinue)) {\r\n            # Update property and print out what it was changed from and changed to\r\n            $CurrentValue = (Get-ItemProperty -Path $Path -Name $Name -ErrorAction SilentlyContinue).$Name\r\n            try {\r\n                Set-ItemProperty -Path $Path -Name $Name -Value $Value -Force -Confirm:$false -ErrorAction Stop | Out-Null\r\n            }\r\n            catch {\r\n                Write-Error \"[Error] Unable to Set registry key for $Name please see below error!\"\r\n                Write-Error $_\r\n                exit 1\r\n            }\r\n            Write-Host \"$Path\\$Name changed from $CurrentValue to $($(Get-ItemProperty -Path $Path -Name $Name -ErrorAction SilentlyContinue).$Name)\"\r\n        }\r\n        else {\r\n            # Create property with value\r\n            try {\r\n                New-ItemProperty -Path $Path -Name $Name -Value $Value -PropertyType $PropertyType -Force -Confirm:$false -ErrorAction Stop | Out-Null\r\n            }\r\n            catch {\r\n                Write-Error \"[Error] Unable to Set registry key for $Name please see below error!\"\r\n                Write-Error $_\r\n                exit 1\r\n            }\r\n            Write-Host \"Set $Path\\$Name to $($(Get-ItemProperty -Path $Path -Name $Name -ErrorAction SilentlyContinue).$Name)\"\r\n        }\r\n    }\r\n\r\n    # This function will gather all the user profiles on the system for use later\r\n    function Get-UserHives {\r\n        param (\r\n            [Parameter()]\r\n            [ValidateSet('AzureAD', 'DomainAndLocal', 'All')]\r\n            [String]$Type = \"All\",\r\n            [Parameter()]\r\n            [String[]]$ExcludedUsers,\r\n            [Parameter()]\r\n            [switch]$IncludeDefault\r\n        )\r\n    \r\n        # User account SID's follow a particular patter depending on if they're azure AD or a Domain account or a local \"workgroup\" account.\r\n        $Patterns = switch ($Type) {\r\n            \"AzureAD\" { \"S-1-12-1-(\\d+-?){4}$\" }\r\n            \"DomainAndLocal\" { \"S-1-5-21-(\\d+-?){4}$\" }\r\n            \"All\" { \"S-1-12-1-(\\d+-?){4}$\" ; \"S-1-5-21-(\\d+-?){4}$\" } \r\n        }\r\n    \r\n        # We'll need the NTuser.dat file to load each users registry hive. So we grab it if their account sid matches the above pattern. \r\n        $UserProfiles = Foreach ($Pattern in $Patterns) { \r\n            Get-ItemProperty \"HKLM:\\SOFTWARE\\Microsoft\\Windows NT\\CurrentVersion\\ProfileList\\*\" |\r\n                Where-Object { $_.PSChildName -match $Pattern } | \r\n                Select-Object @{Name = \"SID\"; Expression = { $_.PSChildName } },\r\n                @{Name = \"UserName\"; Expression = { \"$($_.ProfileImagePath | Split-Path -Leaf)\" } }, \r\n                @{Name = \"UserHive\"; Expression = { \"$($_.ProfileImagePath)\\NTuser.dat\" } }, \r\n                @{Name = \"Path\"; Expression = { $_.ProfileImagePath } }\r\n        }\r\n    \r\n        # There are some situations where grabbing the .Default user's info is needed.\r\n        switch ($IncludeDefault) {\r\n            $True {\r\n                $DefaultProfile = \"\" | Select-Object UserName, SID, UserHive, Path\r\n                $DefaultProfile.UserName = \"Default\"\r\n                $DefaultProfile.SID = \"DefaultProfile\"\r\n                $DefaultProfile.Userhive = \"$env:SystemDrive\\Users\\Default\\NTUSER.DAT\"\r\n                $DefaultProfile.Path = \"$env:SystemDrive\\Users\\Default\"\r\n    \r\n                $DefaultProfile | Where-Object { $ExcludedUsers -notcontains $_.UserName }\r\n            }\r\n        }\r\n    \r\n        $UserProfiles | Where-Object { $ExcludedUsers -notcontains $_.UserName }\r\n    }\r\n}\r\nprocess {\r\n    if (-not (Test-IsElevated)) {\r\n        Write-Error -Message \"Access Denied. Please run with Administrator privileges.\"\r\n        exit 1\r\n    }\r\n\r\n    # If we're only asked to set it for existing users we won't include the default registry hive\r\n    if($IncludeNewUsers){\r\n        $UserProfiles = Get-UserHives -Type \"All\" -IncludeDefault\r\n    }else{\r\n        $UserProfiles = Get-UserHives -Type \"All\"\r\n    }\r\n    \r\n    $Key = \"software\\policies\\microsoft\\office\\16.0\\excel\\security\"\r\n    $PropertyName = \"PythonFunctionWarnings\"\r\n\r\n    if($ChangeBackToMicrosoftDefault){\r\n        # No Prompt and unlocked\r\n        $Value = 0\r\n    }\r\n\r\n    # This is the default option for the script\r\n    if(-not ($ChangeBackToMicrosoftDefault) -and -not ($Block)){\r\n        # Prompt\r\n        $Value = 1\r\n    }\r\n    \r\n    if($Block){\r\n        # Block\r\n        $Value = 2\r\n    }\r\n\r\n    # Loop through each profile on the machine\r\n    Foreach ($UserProfile in $UserProfiles) {\r\n        # Load User ntuser.dat if it's not already loaded\r\n        If (($ProfileWasLoaded = Test-Path Registry::HKEY_USERS\\$($UserProfile.SID)) -eq $false) {\r\n            Start-Process -FilePath \"cmd.exe\" -ArgumentList \"\/C reg.exe LOAD HKU\\$($UserProfile.SID) `\"$($UserProfile.UserHive)`\"\" -Wait -WindowStyle Hidden\r\n        }\r\n\r\n        Set-HKProperty -Path \"Registry::HKEY_USERS\\$($UserProfile.SID)\\$Key\" -Name $PropertyName -Value $Value\r\n        \r\n        # Unload NTuser.dat\r\n        If ($ProfileWasLoaded -eq $false) {\r\n            [gc]::Collect()\r\n            Start-Sleep 1\r\n            Start-Process -FilePath \"cmd.exe\" -ArgumentList \"\/C reg.exe UNLOAD HKU\\$($UserProfile.SID)\" -Wait -WindowStyle Hidden | Out-Null\r\n        }\r\n    }\r\n}\r\nend {\r\n    \r\n    \r\n    \r\n}<\/pre>\n
 <\/p>\n\n
<\/h2>
<\/p><\/div>
<\/a><\/div><\/div>\n
Detailansicht<\/h2>\n
Das mitgelieferte PowerShell-Skript wurde entwickelt, um Administratoren die Kontrolle \u00fcber die Python-Funktionen in Excel zu erm\u00f6glichen. Im Folgenden wird Schritt f\u00fcr Schritt erkl\u00e4rt, wie das Skript funktioniert:<\/p>\n
1. Skript-Parameter:<\/h3>\n