In the modern workplace, data security and access management have become increasingly crucial, especially as organizations integrate various programming languages and tools into their daily operations.
One such integration is the use of Python in Microsoft Excel, a powerful feature that can enhance productivity but also poses potential security risks. For IT professionals and Managed Service Providers (MSPs), managing and restricting Python access within Excel is essential to maintaining a secure environment.
This blog post explores a PowerShell script designed to control Python’s use in Excel, ensuring that security protocols are upheld without compromising functionality.
Background
With Python’s growing popularity in data analysis and automation, its integration into Excel provides users with advanced capabilities. However, this integration also introduces potential vulnerabilities, particularly if Python scripts are executed without proper oversight.
IT professionals and MSPs must balance the benefits of Python’s capabilities with the need to safeguard sensitive data. The PowerShell script discussed here offers a solution by allowing administrators to restrict Python use in Excel, either by enabling security prompts, blocking Python access altogether, or reverting to Microsoft’s default settings.
The Script:
<#
.SYNOPSIS
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).
.DESCRIPTION
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).
.EXAMPLE
(No Parameters)
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
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
Set Registry::HKEY_USERS\S-1-5-21-3870645062-3653562310-3850680542-1003\software\policies\microsoft\office\16.0\excel\security\PythonFunctionWarnings to 1
PARAMETER: -Block
Blocks the use of Python in Excel.
.EXAMPLE
-Block
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
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
Set Registry::HKEY_USERS\S-1-5-21-3870645062-3653562310-3850680542-1003\software\policies\microsoft\office\16.0\excel\security\PythonFunctionWarnings to 2
PARAMETER: -IncludeNewUsers
Adds the registry key to the Default Profile so that this change carriers over when new accounts are created.
.EXAMPLE
-IncludeNewUsers
Set Registry::HKEY_USERS\DefaultProfile\software\policies\microsoft\office\16.0\excel\security\PythonFunctionWarnings to 1
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
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
Set Registry::HKEY_USERS\S-1-5-21-3870645062-3653562310-3850680542-1003\software\policies\microsoft\office\16.0\excel\security\PythonFunctionWarnings to 1
PARAMETER: -ChangeBackToMicrosoftDefault
Resets the setting/restriction back to the Microsoft Default (enabled with no security prompt).
.EXAMPLE
-ChangeBackToMicrosoftDefault
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
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
Set Registry::HKEY_USERS\S-1-5-21-3870645062-3653562310-3850680542-1003\software\policies\microsoft\office\16.0\excel\security\PythonFunctionWarnings to 0
.LINK
https://support.microsoft.com/en-us/office/data-security-and-python-in-excel-33cc88a4-4a87-485e-9ff9-f35958278327
.OUTPUTS
None
.NOTES
Minimum OS Architecture Supported: Windows 8.1, Server 2012
Release Notes: Initial Release
By using this script, you indicate your acceptance of the following legal terms as well as our Terms of Use at https://ninjastage2.wpengine.com/terms-of-use.
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.
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.
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.
Warranty Disclaimer: The script is provided “as is” and “as available”, 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.
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.
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.
EULA: If you are a NinjaOne customer, your use of the script is subject to the End User License Agreement applicable to you (EULA).
#>
[CmdletBinding()]
param (
[Parameter()]
[Switch]$Block = [System.Convert]::ToBoolean($env:blockPython),
[Parameter()]
[Switch]$IncludeNewUsers = [System.Convert]::ToBoolean($env:includeNewUsers),
[Parameter()]
[Switch]$ChangeBackToMicrosoftDefault = [System.Convert]::ToBoolean($env:changeBackToMicrosoftDefaultSetting)
)
begin {
# If incompatible options are detected error out
if($Block -and $ChangeBackToMicrosoftDefault){
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)."
exit 1
}
# Write a warning message for the least secure option
if($ChangeBackToMicrosoftDefault){
Write-Warning "Changing the setting back to the default. All Python security warnings will be disabled..."
}
function Test-IsElevated {
$id = [System.Security.Principal.WindowsIdentity]::GetCurrent()
$p = New-Object System.Security.Principal.WindowsPrincipal($id)
$p.IsInRole([System.Security.Principal.WindowsBuiltInRole]::Administrator)
}
# Handy registry setting function
function Set-HKProperty {
param (
$Path,
$Name,
$Value,
[ValidateSet('DWord', 'QWord', 'String', 'ExpandedString', 'Binary', 'MultiString', 'Unknown')]
$PropertyType = 'DWord'
)
if (-not $(Test-Path -Path $Path)) {
# Check if path does not exist and create the path
New-Item -Path $Path -Force | Out-Null
}
if ((Get-ItemProperty -Path $Path -Name $Name -ErrorAction SilentlyContinue)) {
# Update property and print out what it was changed from and changed to
$CurrentValue = (Get-ItemProperty -Path $Path -Name $Name -ErrorAction SilentlyContinue).$Name
try {
Set-ItemProperty -Path $Path -Name $Name -Value $Value -Force -Confirm:$false -ErrorAction Stop | Out-Null
}
catch {
Write-Error "[Error] Unable to Set registry key for $Name please see below error!"
Write-Error $_
exit 1
}
Write-Host "$Path\$Name changed from $CurrentValue to $($(Get-ItemProperty -Path $Path -Name $Name -ErrorAction SilentlyContinue).$Name)"
}
else {
# Create property with value
try {
New-ItemProperty -Path $Path -Name $Name -Value $Value -PropertyType $PropertyType -Force -Confirm:$false -ErrorAction Stop | Out-Null
}
catch {
Write-Error "[Error] Unable to Set registry key for $Name please see below error!"
Write-Error $_
exit 1
}
Write-Host "Set $Path\$Name to $($(Get-ItemProperty -Path $Path -Name $Name -ErrorAction SilentlyContinue).$Name)"
}
}
# This function will gather all the user profiles on the system for use later
function Get-UserHives {
param (
[Parameter()]
[ValidateSet('AzureAD', 'DomainAndLocal', 'All')]
[String]$Type = "All",
[Parameter()]
[String[]]$ExcludedUsers,
[Parameter()]
[switch]$IncludeDefault
)
# User account SID's follow a particular patter depending on if they're azure AD or a Domain account or a local "workgroup" account.
$Patterns = switch ($Type) {
"AzureAD" { "S-1-12-1-(\d+-?){4}$" }
"DomainAndLocal" { "S-1-5-21-(\d+-?){4}$" }
"All" { "S-1-12-1-(\d+-?){4}$" ; "S-1-5-21-(\d+-?){4}$" }
}
# 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.
$UserProfiles = Foreach ($Pattern in $Patterns) {
Get-ItemProperty "HKLM:\SOFTWARE\Microsoft\Windows NT\CurrentVersion\ProfileList\*" |
Where-Object { $_.PSChildName -match $Pattern } |
Select-Object @{Name = "SID"; Expression = { $_.PSChildName } },
@{Name = "UserName"; Expression = { "$($_.ProfileImagePath | Split-Path -Leaf)" } },
@{Name = "UserHive"; Expression = { "$($_.ProfileImagePath)\NTuser.dat" } },
@{Name = "Path"; Expression = { $_.ProfileImagePath } }
}
# There are some situations where grabbing the .Default user's info is needed.
switch ($IncludeDefault) {
$True {
$DefaultProfile = "" | Select-Object UserName, SID, UserHive, Path
$DefaultProfile.UserName = "Default"
$DefaultProfile.SID = "DefaultProfile"
$DefaultProfile.Userhive = "$env:SystemDrive\Users\Default\NTUSER.DAT"
$DefaultProfile.Path = "$env:SystemDrive\Users\Default"
$DefaultProfile | Where-Object { $ExcludedUsers -notcontains $_.UserName }
}
}
$UserProfiles | Where-Object { $ExcludedUsers -notcontains $_.UserName }
}
}
process {
if (-not (Test-IsElevated)) {
Write-Error -Message "Access Denied. Please run with Administrator privileges."
exit 1
}
# If we're only asked to set it for existing users we won't include the default registry hive
if($IncludeNewUsers){
$UserProfiles = Get-UserHives -Type "All" -IncludeDefault
}else{
$UserProfiles = Get-UserHives -Type "All"
}
$Key = "software\policies\microsoft\office\16.0\excel\security"
$PropertyName = "PythonFunctionWarnings"
if($ChangeBackToMicrosoftDefault){
# No Prompt and unlocked
$Value = 0
}
# This is the default option for the script
if(-not ($ChangeBackToMicrosoftDefault) -and -not ($Block)){
# Prompt
$Value = 1
}
if($Block){
# Block
$Value = 2
}
# Loop through each profile on the machine
Foreach ($UserProfile in $UserProfiles) {
# Load User ntuser.dat if it's not already loaded
If (($ProfileWasLoaded = Test-Path Registry::HKEY_USERS\$($UserProfile.SID)) -eq $false) {
Start-Process -FilePath "cmd.exe" -ArgumentList "/C reg.exe LOAD HKU\$($UserProfile.SID) `"$($UserProfile.UserHive)`"" -Wait -WindowStyle Hidden
}
Set-HKProperty -Path "Registry::HKEY_USERS\$($UserProfile.SID)\$Key" -Name $PropertyName -Value $Value
# Unload NTuser.dat
If ($ProfileWasLoaded -eq $false) {
[gc]::Collect()
Start-Sleep 1
Start-Process -FilePath "cmd.exe" -ArgumentList "/C reg.exe UNLOAD HKU\$($UserProfile.SID)" -Wait -WindowStyle Hidden | Out-Null
}
}
}
end {
}
Access over 300+ scripts in the NinjaOne Dojo
Detailed Breakdown
The provided PowerShell script is designed to give administrators control over Python’s functionality in Excel. Here’s a step-by-step breakdown of how the script works:
1. Script Parameters:
- -Block: This parameter blocks the use of Python in Excel by setting the PythonFunctionWarnings registry key to 2, effectively preventing any Python execution.
- -IncludeNewUsers: When this parameter is used, the script also applies the restriction to new user profiles, ensuring that any future users created on the system inherit the same settings.
- -ChangeBackToMicrosoftDefault: This option resets the registry key to its default value (0), which allows Python to run without any security prompts or warnings.
2. Registry Manipulation:
- The script operates by modifying the Windows Registry, specifically targeting the PythonFunctionWarningskey located under the Excel security settings. Depending on the parameters passed, the script sets this key to 0, 1, or 2, corresponding to no warnings, a security prompt, or a complete block, respectively.
3. User Profile Handling:
- The script retrieves all user profiles on the system and applies the registry changes accordingly. It also includes a function to load and unload the NTUSER.DAT file for each user profile, ensuring that the changes are applied even if the user is not currently logged in.
4. Error Handling and Elevation Check:
- The script checks if it is being run with elevated privileges (Administrator rights) and will terminate with an error message if it is not. This is crucial as modifying the registry requires such privileges.
Potential Use Cases
Imagine an MSP managing a large organization’s IT infrastructure. They have recently introduced Python integration into Excel for advanced data analytics but are concerned about the potential risks of unregulated Python script execution.
By deploying this PowerShell script, the MSP can enforce security prompts across all user accounts, ensuring that any attempt to run Python in Excel requires explicit approval. Additionally, they can block Python execution entirely on machines that handle sensitive information, such as financial data, to mitigate risks further.
Comparisons
There are other methods to restrict Python usage in Excel, such as Group Policy settings or manual registry edits. However, these approaches may not offer the same level of granularity or automation that the PowerShell script provides.
For instance, while Group Policy can be effective, it may not easily apply to all user profiles or allow for selective blocking versus prompting. Manual registry edits, on the other hand, are error-prone and impractical for large-scale deployments.
FAQs
1. What happens if I use the -Block and -ChangeBackToMicrosoftDefault parameters together?
- The script will terminate with an error, as these two options are incompatible. Blocking Python and reverting to default settings cannot be done simultaneously.
2. Do I need to run the script as an administrator?
- Yes, the script requires elevated privileges to modify the registry.
3. Can I apply these settings to new users automatically?
- Yes, by using the -IncludeNewUsers parameter, the script ensures that new user profiles inherit the same Python restrictions.
Implications
Restricting Python usage in Excel can have significant implications for data security within an organization. By enforcing security prompts or blocking Python altogether, IT administrators can prevent unauthorized or malicious script execution, thereby protecting sensitive data. However, it is also essential to consider the impact on productivity, as overly restrictive policies may hinder legitimate use cases.
Recommendations
When using this script, it is advisable to:
- Test the settings on a small group of users before a wide-scale deployment to ensure that it does not disrupt normal operations.
- Regularly review and update the restrictions based on the organization’s evolving security needs and the introduction of new tools or practices.
- Consider using the -IncludeNewUsers parameter to maintain consistent security policies across the organization.
Final Thoughts
Managing Python integration in Excel is a critical task for IT professionals and MSPs, particularly in environments where data security is paramount. This PowerShell script provides a robust solution for controlling Python usage, offering flexibility and automation that other methods may lack.
For those looking to streamline and secure their IT operations further, NinjaOne offers comprehensive tools and services designed to meet the needs of modern IT management. Whether it’s through automation, monitoring, or security management, NinjaOne can help you maintain a secure and efficient IT infrastructure.