<# .SYNOPSIS SecurityInsight Pro -- ONE TIME: let the product updater (caj-si-updater) apply a release's database schema and grants. .DESCRIPTION THE PRODUCT DOES THIS NOW: the install's database setup job (caj-si-dbinit) makes the updater's database user and its rights as its own identity -- at the install and on every update -- so no person needs database admin rights for it. This script stays as a fallback: run it only when Invardia support asks for it. Since #352 an update applies the release's Pro database schema (additive, gated) and the grants its code needs BEFORE it rolls any code -- never new code on an old schema. For that the updater's identity needs, on the Pro database: db_datareader + db_datawriter its state rows and the template gate (most installs already have these) db_ddladmin the additive schema (CREATE TABLE / VIEW / INDEX, ALTER TABLE ... ADD) db_securityadmin the web app's and MCP server's grants, the audit role, the DENYs Until it has them, an update STOPS before rolling anything and says so (Operations > Guards (safety checks): update.schema.failed). Shows the statements by default; -Apply runs them AS THE CALLER, who must be the SQL server's Entra admin (or a member of its admin group) -- the same way Deploy-SIManagement.ps1 -GrantSql creates the web app's users. The identity is found by name (a user-assigned identity, the default uami-si-updater) or given by -ClientId (an SPN); its database user is found by SID, so an existing user is reused whatever its name. Idempotent. .EXAMPLE .\setup\Grant-SIUpdaterDatabase.ps1 -SubscriptionId _{-ResourceGroupName -SqlServer .database.windows.net -SqlDatabase Plan only (prints the T-SQL). Add -Apply to run it as yourself (the server's Entra admin). .LINK https://invardia.com/docs/si/scripts/Grant-SIUpdaterDatabase/ .NOTES Requirements: PowerShell 7 (pwsh). Windows PowerShell 5.1 is refused at once (#Requires -Version 7.0). Role: the Pro SQL server's Entra admin (member of the SQL admin group) -- on the Pro database Hosts: *.blob.core.windows.net, *.queue.core.windows.net, *.table.core.windows.net, *.vault.azure.net, 169.254.169.254, api.loganalytics, api.loganalytics.io, api.msrc.microsoft.com, api.security.microsoft.com, api.securitycenter.microsoft.com, api.securityplatform.microsoft.com, database.windows.net, entra.microsoft.com, graph.microsoft.com, invardia.com, licenses.nuget.org, login.microsoftonline.com, management.azure.com, monitor.azure.com, nvd.nist.gov, outlook.office.com, outlook.office365.com, portal.invardia.com, storage.azure.com, vault.azure.net, www.powershellgallery.com #> [CmdletBinding(SupportsShouldProcess)] param( [Parameter(Mandatory)][string]$SubscriptionId, [Parameter(Mandatory)][string]$SqlServer, [Parameter(Mandatory)][string]$SqlDatabase, [string]$ResourceGroupName, [string]$IdentityName = 'uami-si-updater', # an SPN instead of a managed identity: its app (client) id; -UserName names a NEW database user (default: the identity name) [string]$ClientId, [string]$UserName, [switch]$Apply ) #Requires -Version 7.0 # SecurityInsight 3.0.87 -- Grant-SIUpdaterDatabase.ps1, standalone (Community edition). Built 2026-11-10T18:18:49Z from commit 691877329f6d6e76f7158e56c3aa79198f6856ab, source SOLUTIONS/SecurityInsight/setup/Grant-SIUpdaterDatabase.ps1, # by release/Build-SISupportScripts.ps1 (framework SUPPORT-DL-1). Download: Invoke-WebRequest https://invardia.com/support/si/Grant-SIUpdaterDatabase.ps1 -OutFile Grant-SIUpdaterDatabase.ps1 # It carries the 173 SecurityInsight file(s) the script loads (listed in $__siSupportFiles below, readable, each line # prefixed with "|"); at run time it writes them to a private temporary folder, checks each SHA-256, runs the original # script from there with your parameters, and removes the folder. No PowerShell modules are needed. $script:SIScriptVersion = '3.0.87' # SI #547: the version this copy reports first (stamped into its embedded setup/_shared/SI-ScriptRun.ps1 too) $__siSupportVersion = '3.0.87' $__siSupportEntry = 'setup/Grant-SIUpdaterDatabase.ps1' $__siSupportFiles = [ordered]@{} $__siSupportFiles['setup/Grant-SIUpdaterDatabase.ps1'] = @{ Sha256 = '1dbdd0637275bd4ee402aa43b0615ff82afdc6191bdc33e5878f75dda8ea82b5'; Text = @' |<# |.SYNOPSIS | SecurityInsight Pro -- ONE TIME: let the product updater (caj-si-updater) apply a release's database schema and grants. | |.DESCRIPTION | THE PRODUCT DOES THIS NOW: the install's database setup job (caj-si-dbinit) makes the updater's database user and its rights | as its own identity -- at the install and on every update -- so no person needs database admin rights for it. This | script stays as a fallback: run it only when Invardia support asks for it. | | Since #352 an update applies the release's Pro database schema (additive, gated) and the grants its code needs BEFORE it | rolls any code -- never new code on an old schema. For that the updater's identity needs, on the Pro database: | db_datareader + db_datawriter its state rows and the template gate (most installs already have these) | db_ddladmin the additive schema (CREATE TABLE / VIEW / INDEX, ALTER TABLE ... ADD) | db_securityadmin the web app's and MCP server's grants, the audit role, the DENYs | Until it has them, an update STOPS before rolling anything and says so (Operations > Guards (safety checks): update.schema.failed). | | Shows the statements by default; -Apply runs them AS THE CALLER, who must be the SQL server's Entra admin (or a member | of its admin group) -- the same way Deploy-SIManagement.ps1 -GrantSql creates the web app's users. The identity is found | by name (a user-assigned identity, the default uami-si-updater) or given by -ClientId (an SPN); its database user is | found by SID, so an existing user is reused whatever its name. Idempotent. | |.EXAMPLE | .\setup\Grant-SIUpdaterDatabase.ps1 -SubscriptionId -ResourceGroupName -SqlServer .database.windows.net -SqlDatabase | Plan only (prints the T-SQL). Add -Apply to run it as yourself (the server's Entra admin). | |.LINK | https://invardia.com/docs/si/scripts/Grant-SIUpdaterDatabase/ |#> |[CmdletBinding(SupportsShouldProcess)] |param( | [Parameter(Mandatory)][string]$SubscriptionId, | [Parameter(Mandatory)][string]$SqlServer, | [Parameter(Mandatory)][string]$SqlDatabase, | [string]$ResourceGroupName, | [string]$IdentityName = 'uami-si-updater', | # an SPN instead of a managed identity: its app (client) id; -UserName names a NEW database user (default: the identity name) | [string]$ClientId, | [string]$UserName, | [switch]$Apply |) | |#Requires -Version 7.0 # after the help and the param block: before the help, it hides the help from Get-Help |# framework section 12.7 / 12.10: the documentation link first, a transcript of the run, -WhatIf writes nothing |. ([IO.Path]::Combine($PSScriptRoot, '_shared', 'SI-ScriptRun.ps1')) |$siRun = Start-SIScriptRun -Script 'Grant-SIUpdaterDatabase' -Quiet:($false) |if ($WhatIfPreference) { $Apply = $false } # -WhatIf = the plan: nothing is granted |try { |$ErrorActionPreference = 'Stop' |$siRoot = Split-Path -Parent $PSScriptRoot |. (Join-Path $siRoot 'engine\pro-settings\SI-ProSchema.ps1') |# #672 (framework §12.23 P4 / P9): the certificate-signed grant procedure -- REMOVE-ME switch SI_Setup_SignedGrantProcedure, |# OFF by default until a fresh test install has proven it. ON = the full model: the component roles, the signed procedure, |# the updater in si_updater (EXECUTE on it) and NO db_securityadmin. OFF = today's rights. |. (Join-Path $siRoot 'engine\pro-settings\SI-GrantProcedure.ps1') |$signedGrants = Test-SIGrantProcedureSetup | |function Initialize-SIGrantSignIn { | # #521: setup's own REST sign-in (an existing one in this process is reused; else a person signs in -- Cloud Shell or the | # browser; an Invardia support session gives its tokens). Never the az CLI. | if (-not (Get-Command -Name Get-SISetupAzureContext -ErrorAction SilentlyContinue)) { . ([IO.Path]::Combine($PSScriptRoot, '_shared', 'SI-SetupAzure.ps1')) } | . ([IO.Path]::Combine($PSScriptRoot, '_shared', 'SI-SetupArm.ps1')) # ALWAYS (#616): its API versions are script values of THIS script's scope | if (-not (Get-SISetupAzureContext)) { $null = Connect-SISetupAzure -SubscriptionId $SubscriptionId } |} | |if (-not $ClientId) { | if (-not $ResourceGroupName) { throw 'pass -ResourceGroupName (to find the identity by name) or -ClientId.' } | . Initialize-SIGrantSignIn | $id = Get-SIUserAssignedIdentity -ResourceGroupName $ResourceGroupName -Name $IdentityName -SubscriptionId $SubscriptionId -ErrorAction Stop | $ClientId = [string]$id.ClientId |} |if (-not $UserName) { $UserName = $IdentityName } |$sql = Get-SIUpdaterDatabaseAccessSql -UserName $UserName -ClientId $ClientId -NoSecurityAdmin:$signedGrants |$procBatches = if ($signedGrants) { @(Get-SIGrantProcedureAdminSql -UpdaterUser $UserName) } else { @() } |Write-Host ("== The updater's database rights on {0}/{1} (identity {2}, client id {3})" -f $SqlServer, $SqlDatabase, $UserName, $ClientId) -ForegroundColor Cyan |Write-Host (($sql -split "`n" | ForEach-Object { ' ' + $_.TrimEnd() }) -join "`n") -ForegroundColor DarkGray |if ($signedGrants) { Write-Host (' + the signed grant procedure (SI_Setup_SignedGrantProcedure is ON): {0}' -f (($procBatches | ForEach-Object { $_.Step }) -join ', ')) -ForegroundColor DarkGray } |if (-not $Apply) { Write-Host 'Plan only -- nothing was changed. Re-run with -Apply as the SQL server''s Entra admin.' -ForegroundColor Yellow; return } | |# a guided install hands its own sign-in in ($global:SI_InstallTokenProvider); otherwise setup's own sign-in (no az CLI) |$tp = Get-Variable -Scope Global -Name SI_InstallTokenProvider -ValueOnly -ErrorAction Ignore |if (-not ($tp -is [scriptblock])) { . Initialize-SIGrantSignIn } |# Open-SISqlConnectionRetry reads no script value of its file, so finding it defined is enough |if (-not (Get-Command -Name Open-SISqlConnectionRetry -ErrorAction SilentlyContinue)) { . ([IO.Path]::Combine($PSScriptRoot, '_shared', 'SI-SetupArm.ps1')) } |# opened with a bounded retry on "Login failed" / 40615 only, a new token each time: the server's administrator or its |# firewall rule may have changed a moment ago |$dbUser = $UserName |$cn = Open-SISqlConnectionRetry -Server $SqlServer -Database $SqlDatabase -Token { | param([bool]$Fresh) | if ($tp -is [scriptblock]) { ([string](& $tp 'https://database.windows.net/')).Trim() } | else { ([string](Get-SISetupAccessToken -Resource 'https://database.windows.net/' -ForceRefresh:$Fresh)).Trim() } |} |try { | $cmd = $cn.CreateCommand(); $cmd.CommandText = $sql | $rd = $cmd.ExecuteReader() | try { | if ($rd.Read()) { | $ok = ([int]$rd['DdlAdmin'] -eq 1) -and (([int]$rd['SecurityAdmin'] -eq 1) -or $signedGrants) -and ([int]$rd['DataReader'] -eq 1) -and ([int]$rd['DataWriter'] -eq 1) | $dbUser = [string]$rd['UserName'] | Write-Host (" {0}: db_ddladmin {1}, db_securityadmin {2}, db_datareader {3}, db_datawriter {4}" -f $rd['UserName'], $rd['DdlAdmin'], $rd['SecurityAdmin'], $rd['DataReader'], $rd['DataWriter']) -ForegroundColor $(if ($ok) { 'Green' } else { 'Yellow' }) | if (-not $ok) { throw 'the roles were not all granted -- run this as the SQL server''s Entra admin.' } | } | } finally { $rd.Close() } | # the full model: the roles, the procedure, its signature and the updater's EXECUTE (each batch idempotent) | if ($signedGrants) { | foreach ($b in @(Get-SIGrantProcedureAdminSql -UpdaterUser $dbUser)) { | $cmd = $cn.CreateCommand(); $cmd.CommandText = $b.Sql; $cmd.CommandTimeout = 0; [void]$cmd.ExecuteNonQuery() | Write-Host (" signed grant procedure: {0}" -f $b.Purpose) -ForegroundColor Green | } | } |} finally { $cn.Dispose() } |Write-Host 'Done: the updater can now apply a release''s database schema and grants before it rolls the code.' -ForegroundColor Green |} finally { Stop-SIScriptRun -Run $siRun } '@ } $__siSupportFiles['setup/_shared/SI-ScriptRun.ps1'] = @{ Sha256 = 'be581140700329c1e2c4c23cc644e03d60a16efbd2f66b397f2fcff0bd02729f'; Text = @' |#Requires -Version 5.1 |<# | EVERY SCRIPT A PERSON RUNS (framework DOCS/REQUIREMENTS.md section 12.7 SCRIPT-DOC-1 + section 12.10 SECURITY-PACK-1; SI #481 / #484). | One helper, called first by each such script: | | $siRun = Start-SIScriptRun -Script '}