郎速科技
754 lines
29 KiB
PowerShell
754 lines
29 KiB
PowerShell
[CmdletBinding(DefaultParameterSetName = 'SqlCredential')]
|
|
param(
|
|
[Parameter(Mandatory = $true)]
|
|
[ValidateLength(1, 260)]
|
|
[string]$Server,
|
|
|
|
[Parameter(Mandatory = $true)]
|
|
[ValidatePattern('^[A-Za-z0-9_][A-Za-z0-9_.-]{0,127}$')]
|
|
[string]$Database,
|
|
|
|
[Parameter(Mandatory = $true, ParameterSetName = 'SqlCredential')]
|
|
[Management.Automation.PSCredential]$Credential,
|
|
|
|
[Parameter(Mandatory = $true, ParameterSetName = 'WindowsCredential')]
|
|
[switch]$UseWindowsAuthentication,
|
|
|
|
[Parameter(Mandatory = $true)]
|
|
[string]$OutputPath,
|
|
|
|
[ValidateRange(5, 60)]
|
|
[int]$ConnectionTimeoutSeconds = 15,
|
|
|
|
[ValidateRange(5, 120)]
|
|
[int]$CommandTimeoutSeconds = 60
|
|
)
|
|
|
|
Set-StrictMode -Version 2.0
|
|
$ErrorActionPreference = 'Stop'
|
|
if ($PSVersionTable.PSVersion -lt [Version]'5.1' -or
|
|
[string]$PSVersionTable.PSEdition -ne 'Desktop' -or
|
|
[string]::IsNullOrWhiteSpace($env:SystemRoot)) {
|
|
throw 'lserp_select_only_catalog_failed:windows_powershell_51_required'
|
|
}
|
|
|
|
$utf8 = [Text.UTF8Encoding]::new($false, $true)
|
|
$maximumCatalogEntries = 100000
|
|
$maximumOutputBytes = 16 * 1024 * 1024
|
|
$maximumToolBytes = 2 * 1024 * 1024
|
|
$toolPath = [string]$PSCommandPath
|
|
$connection = $null
|
|
$connectionBuilder = $null
|
|
$metadataCommand = $null
|
|
$metadataReader = $null
|
|
$outputFull = $null
|
|
$outputCreated = $false
|
|
$published = $false
|
|
|
|
function Throw-CatalogError([string]$Code) {
|
|
throw ('lserp_select_only_catalog_failed:' + $Code)
|
|
}
|
|
|
|
function Assert-NoReparseDirectoryChain([string]$Directory, [string]$Code) {
|
|
try {
|
|
$current = [IO.DirectoryInfo]::new([IO.Path]::GetFullPath($Directory))
|
|
while ($null -ne $current) {
|
|
if (-not $current.Exists -or
|
|
(($current.Attributes -band
|
|
[IO.FileAttributes]::ReparsePoint) -ne 0)) {
|
|
Throw-CatalogError $Code
|
|
}
|
|
$current = $current.Parent
|
|
}
|
|
}
|
|
catch {
|
|
if ($_.Exception.Message.StartsWith(
|
|
'lserp_select_only_catalog_failed:')) { throw }
|
|
Throw-CatalogError $Code
|
|
}
|
|
}
|
|
|
|
function Resolve-NewJsonPath([string]$Path) {
|
|
try {
|
|
$full = [IO.Path]::GetFullPath($Path)
|
|
if ([IO.Path]::GetExtension($full) -ine '.json' -or
|
|
[IO.File]::Exists($full) -or
|
|
[IO.Directory]::Exists($full)) {
|
|
Throw-CatalogError 'output_path_invalid'
|
|
}
|
|
$directory = [IO.Path]::GetDirectoryName($full)
|
|
if ([string]::IsNullOrWhiteSpace($directory)) {
|
|
Throw-CatalogError 'output_path_invalid'
|
|
}
|
|
Assert-NoReparseDirectoryChain $directory 'output_path_invalid'
|
|
return $full
|
|
}
|
|
catch {
|
|
if ($_.Exception.Message.StartsWith(
|
|
'lserp_select_only_catalog_failed:')) { throw }
|
|
Throw-CatalogError 'output_path_invalid'
|
|
}
|
|
}
|
|
|
|
function Assert-SafeServer([string]$Value) {
|
|
if (-not [Text.RegularExpressions.Regex]::IsMatch(
|
|
$Value,
|
|
'^[A-Za-z0-9][A-Za-z0-9.-]{0,252}(?:,[1-9][0-9]{0,4})?$')) {
|
|
Throw-CatalogError 'server_invalid'
|
|
}
|
|
$comma = $Value.LastIndexOf(',')
|
|
if ($comma -ge 0) {
|
|
$port = 0
|
|
if (-not [int]::TryParse($Value.Substring($comma + 1), [ref]$port) -or
|
|
$port -lt 1 -or $port -gt 65535) {
|
|
Throw-CatalogError 'server_invalid'
|
|
}
|
|
}
|
|
}
|
|
|
|
function Assert-SafeText(
|
|
[string]$Value,
|
|
[int]$MinimumLength,
|
|
[int]$MaximumLength,
|
|
[string]$Code
|
|
) {
|
|
if ($null -eq $Value -or
|
|
$Value.Length -lt $MinimumLength -or
|
|
$Value.Length -gt $MaximumLength -or
|
|
-not [string]::Equals(
|
|
$Value,
|
|
$Value.Trim(),
|
|
[StringComparison]::Ordinal)) {
|
|
Throw-CatalogError $Code
|
|
}
|
|
foreach ($character in $Value.ToCharArray()) {
|
|
if ([char]::IsControl($character)) { Throw-CatalogError $Code }
|
|
}
|
|
}
|
|
|
|
function Get-Sha256Text([string]$Value) {
|
|
$algorithm = [Security.Cryptography.SHA256]::Create()
|
|
try {
|
|
$bytes = $utf8.GetBytes($Value)
|
|
return ([BitConverter]::ToString(
|
|
$algorithm.ComputeHash($bytes))).Replace('-', '').ToLowerInvariant()
|
|
}
|
|
finally {
|
|
$algorithm.Dispose()
|
|
}
|
|
}
|
|
|
|
function Read-Int64(
|
|
[Data.Common.DbDataReader]$Reader,
|
|
[string]$Name,
|
|
[long]$Minimum,
|
|
[long]$Maximum
|
|
) {
|
|
try {
|
|
$ordinal = $Reader.GetOrdinal($Name)
|
|
if ($Reader.IsDBNull($ordinal)) {
|
|
Throw-CatalogError 'query_contract_invalid'
|
|
}
|
|
$value = [Convert]::ToInt64(
|
|
$Reader.GetValue($ordinal),
|
|
[Globalization.CultureInfo]::InvariantCulture)
|
|
if ($value -lt $Minimum -or $value -gt $Maximum) {
|
|
Throw-CatalogError 'query_contract_invalid'
|
|
}
|
|
return $value
|
|
}
|
|
catch {
|
|
if ($_.Exception.Message.StartsWith(
|
|
'lserp_select_only_catalog_failed:')) { throw }
|
|
Throw-CatalogError 'query_contract_invalid'
|
|
}
|
|
}
|
|
|
|
function Read-Text(
|
|
[Data.Common.DbDataReader]$Reader,
|
|
[string]$Name,
|
|
[int]$MinimumLength,
|
|
[int]$MaximumLength
|
|
) {
|
|
try {
|
|
$ordinal = $Reader.GetOrdinal($Name)
|
|
if ($Reader.IsDBNull($ordinal)) {
|
|
Throw-CatalogError 'query_contract_invalid'
|
|
}
|
|
$value = [Convert]::ToString(
|
|
$Reader.GetValue($ordinal),
|
|
[Globalization.CultureInfo]::InvariantCulture)
|
|
Assert-SafeText $value $MinimumLength $MaximumLength `
|
|
'query_contract_invalid'
|
|
return $value
|
|
}
|
|
catch {
|
|
if ($_.Exception.Message.StartsWith(
|
|
'lserp_select_only_catalog_failed:')) { throw }
|
|
Throw-CatalogError 'query_contract_invalid'
|
|
}
|
|
}
|
|
|
|
function Get-CurrentToolFileSha256([string]$Path) {
|
|
try {
|
|
if ([string]::IsNullOrWhiteSpace($Path)) {
|
|
Throw-CatalogError 'tool_source_invalid'
|
|
}
|
|
$full = [IO.Path]::GetFullPath($Path)
|
|
$item = Get-Item -LiteralPath $full -Force
|
|
if ($item.PSIsContainer -or
|
|
$item.Length -le 0 -or
|
|
$item.Length -gt $maximumToolBytes -or
|
|
(($item.Attributes -band
|
|
[IO.FileAttributes]::ReparsePoint) -ne 0)) {
|
|
Throw-CatalogError 'tool_source_invalid'
|
|
}
|
|
Assert-NoReparseDirectoryChain $item.DirectoryName `
|
|
'tool_source_invalid'
|
|
$hash = (Get-FileHash -LiteralPath $full `
|
|
-Algorithm SHA256).Hash.ToLowerInvariant()
|
|
if ($hash -cnotmatch '^[0-9a-f]{64}$') {
|
|
Throw-CatalogError 'tool_source_invalid'
|
|
}
|
|
return $hash
|
|
}
|
|
catch {
|
|
if ($_.Exception.Message.StartsWith(
|
|
'lserp_select_only_catalog_failed:')) { throw }
|
|
Throw-CatalogError 'tool_source_invalid'
|
|
}
|
|
}
|
|
|
|
function Assert-SelectOnlyPermissionGate(
|
|
[Data.Common.DbConnection]$connection,
|
|
[string]$permissionQuery,
|
|
[int]$commandTimeoutSeconds
|
|
) {
|
|
$permissionCommand = $null
|
|
$permissionReader = $null
|
|
try {
|
|
$permissionCommand = $connection.CreateCommand()
|
|
$permissionCommand.CommandType = [Data.CommandType]::Text
|
|
$permissionCommand.CommandText = $permissionQuery
|
|
$permissionCommand.CommandTimeout = $commandTimeoutSeconds
|
|
$permissionReader = $permissionCommand.ExecuteReader(
|
|
[Data.CommandBehavior]::SingleResult)
|
|
if (-not $permissionReader.Read()) {
|
|
Throw-CatalogError 'permission_contract_invalid'
|
|
}
|
|
$permissionColumns = @(
|
|
'database_control', 'database_alter', 'database_insert',
|
|
'database_update', 'database_delete', 'database_execute',
|
|
'database_create_table', 'database_create_procedure',
|
|
'database_create_view', 'database_create_function',
|
|
'server_control', 'server_alter_login', 'server_impersonate_login',
|
|
'sysadmin_member', 'db_owner_member', 'db_ddladmin_member',
|
|
'db_datawriter_member', 'writable_object_count',
|
|
'executable_object_count', 'writable_schema_count'
|
|
)
|
|
foreach ($column in $permissionColumns) {
|
|
if ((Read-Int64 $permissionReader $column 0 10000000) -ne 0) {
|
|
Throw-CatalogError 'database_principal_not_select_only'
|
|
}
|
|
}
|
|
if ($permissionReader.Read() -or $permissionReader.NextResult()) {
|
|
Throw-CatalogError 'permission_contract_invalid'
|
|
}
|
|
}
|
|
finally {
|
|
if ($null -ne $permissionReader) { $permissionReader.Dispose() }
|
|
if ($null -ne $permissionCommand) { $permissionCommand.Dispose() }
|
|
}
|
|
}
|
|
|
|
function New-RestrictedFileSecurity {
|
|
try {
|
|
$currentUser = [Security.Principal.WindowsIdentity]::GetCurrent().User
|
|
$localSystem = [Security.Principal.SecurityIdentifier]::new(
|
|
[Security.Principal.WellKnownSidType]::LocalSystemSid,
|
|
$null)
|
|
$security = New-Object Security.AccessControl.FileSecurity
|
|
$security.SetOwner($currentUser)
|
|
$security.SetAccessRuleProtection($true, $false)
|
|
$allow = [Security.AccessControl.AccessControlType]::Allow
|
|
foreach ($identity in @($currentUser, $localSystem)) {
|
|
$rule = [Security.AccessControl.FileSystemAccessRule]::new(
|
|
$identity,
|
|
[Security.AccessControl.FileSystemRights]::FullControl,
|
|
$allow)
|
|
[void]$security.AddAccessRule($rule)
|
|
}
|
|
return $security
|
|
}
|
|
catch {
|
|
Throw-CatalogError 'output_acl_invalid'
|
|
}
|
|
}
|
|
|
|
function Publish-RestrictedJson([string]$Path, [string]$Json) {
|
|
$stream = $null
|
|
$writer = $null
|
|
try {
|
|
$bytes = $utf8.GetByteCount($Json)
|
|
if ($bytes -le 0 -or $bytes -gt $maximumOutputBytes) {
|
|
Throw-CatalogError 'output_size_invalid'
|
|
}
|
|
$security = New-RestrictedFileSecurity
|
|
$stream = [IO.FileStream]::new(
|
|
$Path,
|
|
[IO.FileMode]::CreateNew,
|
|
[Security.AccessControl.FileSystemRights]::Write,
|
|
[IO.FileShare]::None,
|
|
4096,
|
|
[IO.FileOptions]::WriteThrough,
|
|
$security)
|
|
$script:outputCreated = $true
|
|
$writer = [IO.StreamWriter]::new($stream, $utf8, 4096, $false)
|
|
$stream = $null
|
|
$writer.Write($Json)
|
|
$writer.Flush()
|
|
$writer.Dispose()
|
|
$writer = $null
|
|
|
|
$item = Get-Item -LiteralPath $Path -Force
|
|
if ($item.Length -le 0 -or $item.Length -gt $maximumOutputBytes -or
|
|
(($item.Attributes -band [IO.FileAttributes]::ReparsePoint) -ne 0)) {
|
|
Throw-CatalogError 'output_publish_invalid'
|
|
}
|
|
$acl = [IO.File]::GetAccessControl($Path)
|
|
if (-not $acl.AreAccessRulesProtected) {
|
|
Throw-CatalogError 'output_acl_invalid'
|
|
}
|
|
}
|
|
finally {
|
|
if ($null -ne $writer) { $writer.Dispose() }
|
|
if ($null -ne $stream) { $stream.Dispose() }
|
|
}
|
|
}
|
|
|
|
$permissionQuery = @'
|
|
SELECT
|
|
CONVERT(int, ISNULL(HAS_PERMS_BY_NAME(DB_NAME(), 'DATABASE', 'CONTROL'), 0)) AS database_control,
|
|
CONVERT(int, ISNULL(HAS_PERMS_BY_NAME(DB_NAME(), 'DATABASE', 'ALTER'), 0)) AS database_alter,
|
|
CONVERT(int, ISNULL(HAS_PERMS_BY_NAME(DB_NAME(), 'DATABASE', 'INSERT'), 0)) AS database_insert,
|
|
CONVERT(int, ISNULL(HAS_PERMS_BY_NAME(DB_NAME(), 'DATABASE', 'UPDATE'), 0)) AS database_update,
|
|
CONVERT(int, ISNULL(HAS_PERMS_BY_NAME(DB_NAME(), 'DATABASE', 'DELETE'), 0)) AS database_delete,
|
|
CONVERT(int, ISNULL(HAS_PERMS_BY_NAME(DB_NAME(), 'DATABASE', 'EXECUTE'), 0)) AS database_execute,
|
|
CONVERT(int, ISNULL(HAS_PERMS_BY_NAME(DB_NAME(), 'DATABASE', 'CREATE TABLE'), 0)) AS database_create_table,
|
|
CONVERT(int, ISNULL(HAS_PERMS_BY_NAME(DB_NAME(), 'DATABASE', 'CREATE PROCEDURE'), 0)) AS database_create_procedure,
|
|
CONVERT(int, ISNULL(HAS_PERMS_BY_NAME(DB_NAME(), 'DATABASE', 'CREATE VIEW'), 0)) AS database_create_view,
|
|
CONVERT(int, ISNULL(HAS_PERMS_BY_NAME(DB_NAME(), 'DATABASE', 'CREATE FUNCTION'), 0)) AS database_create_function,
|
|
CONVERT(int, ISNULL(HAS_PERMS_BY_NAME(NULL, NULL, 'CONTROL SERVER'), 0)) AS server_control,
|
|
CONVERT(int, ISNULL(HAS_PERMS_BY_NAME(NULL, NULL, 'ALTER ANY LOGIN'), 0)) AS server_alter_login,
|
|
CONVERT(int, ISNULL(HAS_PERMS_BY_NAME(NULL, NULL, 'IMPERSONATE ANY LOGIN'), 0)) AS server_impersonate_login,
|
|
CONVERT(int, ISNULL(IS_SRVROLEMEMBER('sysadmin'), 0)) AS sysadmin_member,
|
|
CONVERT(int, ISNULL(IS_MEMBER('db_owner'), 0)) AS db_owner_member,
|
|
CONVERT(int, ISNULL(IS_MEMBER('db_ddladmin'), 0)) AS db_ddladmin_member,
|
|
CONVERT(int, ISNULL(IS_MEMBER('db_datawriter'), 0)) AS db_datawriter_member,
|
|
CONVERT(bigint,
|
|
(
|
|
SELECT COUNT_BIG(1)
|
|
FROM sys.objects AS writable_object
|
|
WHERE writable_object.is_ms_shipped = 0
|
|
AND writable_object.type IN ('U', 'V')
|
|
AND
|
|
(
|
|
ISNULL(HAS_PERMS_BY_NAME(
|
|
QUOTENAME(SCHEMA_NAME(writable_object.schema_id)) + N'.' +
|
|
QUOTENAME(writable_object.name), 'OBJECT', 'INSERT'), 0) = 1
|
|
OR ISNULL(HAS_PERMS_BY_NAME(
|
|
QUOTENAME(SCHEMA_NAME(writable_object.schema_id)) + N'.' +
|
|
QUOTENAME(writable_object.name), 'OBJECT', 'UPDATE'), 0) = 1
|
|
OR ISNULL(HAS_PERMS_BY_NAME(
|
|
QUOTENAME(SCHEMA_NAME(writable_object.schema_id)) + N'.' +
|
|
QUOTENAME(writable_object.name), 'OBJECT', 'DELETE'), 0) = 1
|
|
OR ISNULL(HAS_PERMS_BY_NAME(
|
|
QUOTENAME(SCHEMA_NAME(writable_object.schema_id)) + N'.' +
|
|
QUOTENAME(writable_object.name), 'OBJECT', 'ALTER'), 0) = 1
|
|
OR ISNULL(HAS_PERMS_BY_NAME(
|
|
QUOTENAME(SCHEMA_NAME(writable_object.schema_id)) + N'.' +
|
|
QUOTENAME(writable_object.name), 'OBJECT', 'CONTROL'), 0) = 1
|
|
)
|
|
)) AS writable_object_count,
|
|
CONVERT(bigint,
|
|
(
|
|
SELECT COUNT_BIG(1)
|
|
FROM sys.objects AS executable_object
|
|
WHERE executable_object.is_ms_shipped = 0
|
|
AND executable_object.type IN ('P', 'PC', 'FN', 'IF', 'TF')
|
|
AND
|
|
(
|
|
ISNULL(HAS_PERMS_BY_NAME(
|
|
QUOTENAME(SCHEMA_NAME(executable_object.schema_id)) + N'.' +
|
|
QUOTENAME(executable_object.name), 'OBJECT', 'EXECUTE'), 0) = 1
|
|
OR ISNULL(HAS_PERMS_BY_NAME(
|
|
QUOTENAME(SCHEMA_NAME(executable_object.schema_id)) + N'.' +
|
|
QUOTENAME(executable_object.name), 'OBJECT', 'ALTER'), 0) = 1
|
|
OR ISNULL(HAS_PERMS_BY_NAME(
|
|
QUOTENAME(SCHEMA_NAME(executable_object.schema_id)) + N'.' +
|
|
QUOTENAME(executable_object.name), 'OBJECT', 'CONTROL'), 0) = 1
|
|
)
|
|
)) AS executable_object_count,
|
|
CONVERT(bigint,
|
|
(
|
|
SELECT COUNT_BIG(1)
|
|
FROM sys.schemas AS writable_schema
|
|
WHERE writable_schema.name NOT IN (N'sys', N'INFORMATION_SCHEMA')
|
|
AND
|
|
(
|
|
ISNULL(HAS_PERMS_BY_NAME(
|
|
QUOTENAME(writable_schema.name), 'SCHEMA', 'ALTER'), 0) = 1
|
|
OR ISNULL(HAS_PERMS_BY_NAME(
|
|
QUOTENAME(writable_schema.name), 'SCHEMA', 'CONTROL'), 0) = 1
|
|
)
|
|
)) AS writable_schema_count;
|
|
'@
|
|
|
|
$metadataQuery = @'
|
|
SELECT
|
|
CONVERT(nvarchar(128), DB_NAME()) AS database_name,
|
|
CONVERT(nvarchar(128), SERVERPROPERTY('ServerName')) AS actual_server_name,
|
|
CONVERT(nvarchar(128), SUSER_SNAME()) AS effective_principal,
|
|
CONVERT(int, SERVERPROPERTY('ProductMajorVersion')) AS sql_server_major_version,
|
|
CONVERT(int, current_database.compatibility_level) AS compatibility_level,
|
|
CONVERT(bigint, (SELECT COUNT_BIG(1) FROM sys.tables WHERE is_ms_shipped = 0)) AS user_table_count,
|
|
CONVERT(bigint, (SELECT COUNT_BIG(1) FROM sys.views WHERE is_ms_shipped = 0)) AS user_view_count,
|
|
CONVERT(bigint, (SELECT COUNT_BIG(1) FROM sys.procedures WHERE is_ms_shipped = 0)) AS user_procedure_count,
|
|
CONVERT(bigint, (SELECT COUNT_BIG(1) FROM sys.triggers WHERE is_ms_shipped = 0)) AS user_trigger_count,
|
|
CONVERT(bigint,
|
|
(
|
|
SELECT COUNT_BIG(1)
|
|
FROM sys.objects
|
|
WHERE schema_id = SCHEMA_ID(N'dbo')
|
|
AND
|
|
(
|
|
(type = 'U' AND name IN
|
|
(
|
|
N'p_agent_business_audit',
|
|
N'p_agent_business_source_document',
|
|
N'p_agent_command_idempotency',
|
|
N'p_agent_integration_outbox',
|
|
N'p_agent_purchase_currency_crosswalk',
|
|
N'p_agent_purchase_row_scope',
|
|
N'p_agent_workflow_adapter_evidence',
|
|
N'p_agent_workflow_adapter_evidence_v2'
|
|
))
|
|
OR
|
|
(type = 'P' AND name IN
|
|
(
|
|
N'p_lserp_agent_workflow_read',
|
|
N'p_lserp_agent_workflow_read_compat100',
|
|
N'p_lserp_agent_workflow_readiness',
|
|
N'p_lserp_agent_workflow_readiness_v2',
|
|
N'p_lserp_agent_workflow_readiness_v3',
|
|
N'p_lserp_agent_workflow_write',
|
|
N'p_lserp_agent_workflow_write_leave_compat100',
|
|
N'p_lserp_agent_workflow_write_purchase_compat100'
|
|
))
|
|
)
|
|
)) AS agent_workflow_object_count
|
|
FROM sys.databases AS current_database
|
|
WHERE current_database.database_id = DB_ID();
|
|
|
|
SELECT TOP (100001)
|
|
catalog_entry.entry_kind,
|
|
catalog_entry.schema_name,
|
|
catalog_entry.object_name,
|
|
catalog_entry.object_kind,
|
|
catalog_entry.member_name
|
|
FROM
|
|
(
|
|
SELECT
|
|
CONVERT(varchar(16), 'object') AS entry_kind,
|
|
CONVERT(nvarchar(128), SCHEMA_NAME(catalog_object.schema_id)) AS schema_name,
|
|
CONVERT(nvarchar(128), catalog_object.name) AS object_name,
|
|
CONVERT(varchar(16), CASE catalog_object.type
|
|
WHEN 'U' THEN 'table'
|
|
WHEN 'V' THEN 'view'
|
|
WHEN 'P' THEN 'procedure'
|
|
ELSE 'invalid' END) AS object_kind,
|
|
CONVERT(nvarchar(128), N'') AS member_name
|
|
FROM sys.objects AS catalog_object
|
|
WHERE catalog_object.is_ms_shipped = 0
|
|
AND catalog_object.type IN ('U', 'V', 'P')
|
|
|
|
UNION ALL
|
|
|
|
SELECT
|
|
CONVERT(varchar(16), 'column'),
|
|
CONVERT(nvarchar(128), SCHEMA_NAME(catalog_object.schema_id)),
|
|
CONVERT(nvarchar(128), catalog_object.name),
|
|
CONVERT(varchar(16), CASE catalog_object.type
|
|
WHEN 'U' THEN 'table'
|
|
WHEN 'V' THEN 'view'
|
|
ELSE 'invalid' END),
|
|
CONVERT(nvarchar(128), catalog_column.name)
|
|
FROM sys.objects AS catalog_object
|
|
INNER JOIN sys.columns AS catalog_column
|
|
ON catalog_column.object_id = catalog_object.object_id
|
|
WHERE catalog_object.is_ms_shipped = 0
|
|
AND catalog_object.type IN ('U', 'V')
|
|
|
|
UNION ALL
|
|
|
|
SELECT
|
|
CONVERT(varchar(16), 'parameter'),
|
|
CONVERT(nvarchar(128), SCHEMA_NAME(catalog_procedure.schema_id)),
|
|
CONVERT(nvarchar(128), catalog_procedure.name),
|
|
CONVERT(varchar(16), 'procedure'),
|
|
CONVERT(nvarchar(128), catalog_parameter.name)
|
|
FROM sys.procedures AS catalog_procedure
|
|
INNER JOIN sys.parameters AS catalog_parameter
|
|
ON catalog_parameter.object_id = catalog_procedure.object_id
|
|
AND catalog_parameter.parameter_id > 0
|
|
WHERE catalog_procedure.is_ms_shipped = 0
|
|
) AS catalog_entry
|
|
ORDER BY
|
|
catalog_entry.object_kind,
|
|
catalog_entry.schema_name,
|
|
catalog_entry.object_name,
|
|
catalog_entry.entry_kind,
|
|
catalog_entry.member_name;
|
|
'@
|
|
|
|
try {
|
|
Assert-SafeServer $Server
|
|
$outputFull = Resolve-NewJsonPath $OutputPath
|
|
$executedScriptText = $null
|
|
try {
|
|
$executedScriptText = [string]
|
|
$MyInvocation.MyCommand.ScriptBlock.Ast.Extent.Text
|
|
}
|
|
catch {
|
|
Throw-CatalogError 'tool_source_invalid'
|
|
}
|
|
if ([string]::IsNullOrWhiteSpace($executedScriptText) -or
|
|
$utf8.GetByteCount($executedScriptText) -gt $maximumToolBytes) {
|
|
Throw-CatalogError 'tool_source_invalid'
|
|
}
|
|
$executedToolSha256 = Get-Sha256Text $executedScriptText
|
|
$initialToolFileSha256 = Get-CurrentToolFileSha256 $toolPath
|
|
if ($initialToolFileSha256 -cne $executedToolSha256) {
|
|
Throw-CatalogError 'tool_source_changed'
|
|
}
|
|
if ($null -ne $Credential) {
|
|
Assert-SafeText $Credential.UserName 1 128 'credential_user_invalid'
|
|
}
|
|
|
|
$connectionBuilder = [System.Data.SqlClient.SqlConnectionStringBuilder]::new()
|
|
$connectionBuilder.DataSource = $Server
|
|
$connectionBuilder.InitialCatalog = $Database
|
|
$connectionBuilder.ConnectTimeout = $ConnectionTimeoutSeconds
|
|
$connectionBuilder.Encrypt = $true
|
|
$connectionBuilder.TrustServerCertificate = $false
|
|
$connectionBuilder.ApplicationIntent =
|
|
[System.Data.SqlClient.ApplicationIntent]::ReadOnly
|
|
$connectionBuilder.PersistSecurityInfo = $false
|
|
$connectionBuilder.Pooling = $false
|
|
$connectionBuilder.MultipleActiveResultSets = $false
|
|
$connectionBuilder.ApplicationName =
|
|
'Langsu Lserp SELECT-only catalog snapshot'
|
|
if ($PSCmdlet.ParameterSetName -eq 'WindowsCredential') {
|
|
$connectionBuilder.IntegratedSecurity = $true
|
|
}
|
|
else {
|
|
$connectionBuilder.IntegratedSecurity = $false
|
|
$connectionBuilder.UserID = $Credential.UserName
|
|
$connectionBuilder.Password = $Credential.GetNetworkCredential().Password
|
|
}
|
|
|
|
$connection = [System.Data.SqlClient.SqlConnection]::new(
|
|
$connectionBuilder.ConnectionString)
|
|
$connection.Open()
|
|
|
|
Assert-SelectOnlyPermissionGate `
|
|
$connection $permissionQuery $CommandTimeoutSeconds
|
|
|
|
$metadataCommand = $connection.CreateCommand()
|
|
$metadataCommand.CommandType = [Data.CommandType]::Text
|
|
$metadataCommand.CommandText = $metadataQuery
|
|
$metadataCommand.CommandTimeout = $CommandTimeoutSeconds
|
|
$metadataReader = $metadataCommand.ExecuteReader(
|
|
[Data.CommandBehavior]::SequentialAccess)
|
|
if (-not $metadataReader.Read()) {
|
|
Throw-CatalogError 'metadata_contract_invalid'
|
|
}
|
|
$actualDatabase = Read-Text $metadataReader 'database_name' 1 128
|
|
$actualServer = Read-Text $metadataReader 'actual_server_name' 1 128
|
|
$effectivePrincipal = Read-Text $metadataReader 'effective_principal' 1 128
|
|
if (-not [string]::Equals(
|
|
$actualDatabase,
|
|
$Database,
|
|
[StringComparison]::OrdinalIgnoreCase)) {
|
|
Throw-CatalogError 'database_identity_mismatch'
|
|
}
|
|
$sqlServerMajorVersion = Read-Int64 `
|
|
$metadataReader 'sql_server_major_version' 9 99
|
|
$compatibilityLevel = Read-Int64 `
|
|
$metadataReader 'compatibility_level' 80 200
|
|
$userTableCount = Read-Int64 `
|
|
$metadataReader 'user_table_count' 0 10000000
|
|
$userViewCount = Read-Int64 `
|
|
$metadataReader 'user_view_count' 0 10000000
|
|
$userProcedureCount = Read-Int64 `
|
|
$metadataReader 'user_procedure_count' 0 10000000
|
|
$userTriggerCount = Read-Int64 `
|
|
$metadataReader 'user_trigger_count' 0 10000000
|
|
$agentWorkflowObjectCount = Read-Int64 `
|
|
$metadataReader 'agent_workflow_object_count' 0 16
|
|
if ($metadataReader.Read() -or -not $metadataReader.NextResult()) {
|
|
Throw-CatalogError 'metadata_contract_invalid'
|
|
}
|
|
|
|
$catalogHashSet = [Collections.Generic.HashSet[string]]::new(
|
|
[StringComparer]::Ordinal)
|
|
while ($metadataReader.Read()) {
|
|
if ($catalogHashSet.Count -ge $maximumCatalogEntries) {
|
|
Throw-CatalogError 'catalog_too_large'
|
|
}
|
|
$entryKind = Read-Text $metadataReader 'entry_kind' 1 16
|
|
$schemaName = Read-Text $metadataReader 'schema_name' 1 128
|
|
$objectName = Read-Text $metadataReader 'object_name' 1 128
|
|
$objectKind = Read-Text $metadataReader 'object_kind' 1 16
|
|
$memberMinimum = if ($entryKind -eq 'object') { 0 } else { 1 }
|
|
$memberName = Read-Text `
|
|
$metadataReader 'member_name' $memberMinimum 128
|
|
if ($entryKind -cnotin @('object', 'column', 'parameter') -or
|
|
$objectKind -cnotin @('table', 'view', 'procedure') -or
|
|
($entryKind -eq 'object' -and $memberName.Length -ne 0) -or
|
|
($entryKind -eq 'column' -and $objectKind -eq 'procedure') -or
|
|
($entryKind -eq 'parameter' -and $objectKind -ne 'procedure')) {
|
|
Throw-CatalogError 'catalog_contract_invalid'
|
|
}
|
|
$catalogKey = [string]::Join(
|
|
[char]0x1f,
|
|
@(
|
|
$entryKind,
|
|
$schemaName.ToLowerInvariant(),
|
|
$objectName.ToLowerInvariant(),
|
|
$objectKind,
|
|
$memberName.ToLowerInvariant()
|
|
))
|
|
$entryHash = Get-Sha256Text $catalogKey
|
|
if (-not $catalogHashSet.Add($entryHash)) {
|
|
Throw-CatalogError 'catalog_contract_invalid'
|
|
}
|
|
}
|
|
if ($catalogHashSet.Count -le 0 -or $metadataReader.NextResult()) {
|
|
Throw-CatalogError 'metadata_contract_invalid'
|
|
}
|
|
$metadataReader.Dispose()
|
|
$metadataReader = $null
|
|
|
|
Assert-SelectOnlyPermissionGate `
|
|
$connection $permissionQuery $CommandTimeoutSeconds
|
|
$finalToolFileSha256 = Get-CurrentToolFileSha256 $toolPath
|
|
if ($finalToolFileSha256 -cne $initialToolFileSha256 -or
|
|
$finalToolFileSha256 -cne $executedToolSha256) {
|
|
Throw-CatalogError 'tool_source_changed'
|
|
}
|
|
|
|
[string[]]$catalogHashes = @($catalogHashSet)
|
|
[Array]::Sort($catalogHashes, [StringComparer]::Ordinal)
|
|
$catalogSetSha256 = Get-Sha256Text(
|
|
[string]::Join("`n", $catalogHashes))
|
|
$toolSha256 = $executedToolSha256
|
|
$databaseScopeFingerprint = Get-Sha256Text([string]::Join(
|
|
"`n",
|
|
@(
|
|
'sqlserver-select-only-catalog-v1',
|
|
$Server.ToLowerInvariant(),
|
|
$actualServer.ToLowerInvariant(),
|
|
$actualDatabase.ToLowerInvariant(),
|
|
$effectivePrincipal.ToLowerInvariant()
|
|
)))
|
|
$snapshot = [ordered]@{
|
|
schemaVersion = '1.1'
|
|
snapshotType = 'select_only_sqlserver_catalog_hashes'
|
|
generatedAtUtc = [DateTime]::UtcNow.ToString(
|
|
'yyyy-MM-ddTHH:mm:ss.fffffffZ',
|
|
[Globalization.CultureInfo]::InvariantCulture)
|
|
toolSha256 = $toolSha256
|
|
databaseScopeFingerprint = $databaseScopeFingerprint
|
|
identity = [ordered]@{
|
|
requestedServerSha256 = Get-Sha256Text ($Server.ToLowerInvariant())
|
|
actualServerSha256 = Get-Sha256Text ($actualServer.ToLowerInvariant())
|
|
databaseNameSha256 = Get-Sha256Text ($actualDatabase.ToLowerInvariant())
|
|
effectivePrincipalSha256 = Get-Sha256Text `
|
|
($effectivePrincipal.ToLowerInvariant())
|
|
}
|
|
database = [ordered]@{
|
|
sqlServerMajorVersion = $sqlServerMajorVersion
|
|
compatibilityLevel = $compatibilityLevel
|
|
userTableCount = $userTableCount
|
|
userViewCount = $userViewCount
|
|
userProcedureCount = $userProcedureCount
|
|
userTriggerCount = $userTriggerCount
|
|
agentWorkflowObjectCount = $agentWorkflowObjectCount
|
|
agentWorkflowObjectsPresent = ($agentWorkflowObjectCount -eq 16)
|
|
}
|
|
permissionGate = [ordered]@{
|
|
passed = $true
|
|
checkedBeforeCatalogRead = $true
|
|
checkedAfterCatalogRead = $true
|
|
databaseWritePermissionCount = 0
|
|
serverWritePermissionCount = 0
|
|
writableObjectCount = 0
|
|
executableObjectCount = 0
|
|
writableSchemaCount = 0
|
|
}
|
|
catalog = [ordered]@{
|
|
canonicalization = 'lower_invariant_unit_separator_v1'
|
|
entryCount = $catalogHashes.Count
|
|
setSha256 = $catalogSetSha256
|
|
entrySha256 = $catalogHashes
|
|
}
|
|
safety = [ordered]@{
|
|
connectionEncrypted = $true
|
|
serverCertificateValidated = $true
|
|
applicationIntent = 'ReadOnly'
|
|
effectivePrincipalSelectOnly = $true
|
|
toolSourceBytesStable = $true
|
|
businessRowsRead = $false
|
|
storedProceduresExecuted = $false
|
|
writesAttempted = $false
|
|
}
|
|
}
|
|
$json = ConvertTo-Json -InputObject $snapshot -Depth 8 -Compress
|
|
Publish-RestrictedJson $outputFull $json
|
|
$published = $true
|
|
$snapshotSha256 = (Get-FileHash -LiteralPath $outputFull `
|
|
-Algorithm SHA256).Hash.ToLowerInvariant()
|
|
[pscustomobject][ordered]@{
|
|
schemaVersion = '1.1'
|
|
passed = $true
|
|
code = 'ok'
|
|
outputPath = $outputFull
|
|
snapshotSha256 = $snapshotSha256
|
|
databaseScopeFingerprint = $databaseScopeFingerprint
|
|
catalogEntryCount = $catalogHashes.Count
|
|
catalogSetSha256 = $catalogSetSha256
|
|
}
|
|
}
|
|
catch {
|
|
if ($_.Exception.Message.StartsWith(
|
|
'lserp_select_only_catalog_failed:')) { throw }
|
|
Throw-CatalogError 'catalog_snapshot_failed'
|
|
}
|
|
finally {
|
|
if ($null -ne $metadataReader) { $metadataReader.Dispose() }
|
|
if ($null -ne $metadataCommand) { $metadataCommand.Dispose() }
|
|
if ($null -ne $connection) {
|
|
try { $connection.Close() } catch { }
|
|
$connection.Dispose()
|
|
}
|
|
if ($null -ne $connectionBuilder) {
|
|
$connectionBuilder.Password = [string]::Empty
|
|
$connectionBuilder.UserID = [string]::Empty
|
|
}
|
|
if ($outputCreated -and -not $published -and
|
|
$null -ne $outputFull -and [IO.File]::Exists($outputFull)) {
|
|
try { [IO.File]::Delete($outputFull) } catch { }
|
|
}
|
|
}
|