redkiteps

0.0.5642

Private/sql.ps1

# todo: assuming subscription + database / connection string info is coming from env vars or the permission itself.
# contd: Imperial code is doing direct calls to KV as part of the database access token retrieval, best to avoid this to keep all the functions clean of other dependencies
# contd: missing env vars I can see: DatabaseName, Subscription.


function Set-RKSQLPermission {
    [CmdletBinding()]
    param([PSCustomObject[]]$Permission)

    Write-Host "Attempting Set-RKSQLPermission: Type: '$($Permission.principalType)', Name: '$($Permission.principalName)', Role: '$($Permission.databaseRole)', Scope: '$($Permission.scope)'"

    $scopeProperties = Format-RKAzureScopeAsHashTable -Scope $Permission.scope

    $databaseAccessToken = Get-RKAzDatabaseAccessToken

    if ($null -eq $databaseAccessToken){
        throw "Error obtaining access token - check your Powershell Az Context."
    }

    $databaseProperties = [DatabaseProperties]@{
        ServerName = "$($scopeProperties['servers']).database.windows.net"
        DatabaseName = $scopeProperties['databases']
        DatabaseAccessToken = $databaseAccessToken
     }

    New-RKDatabaseRole -RoleName $Permission.databaseRole -DatabaseProperties $databaseProperties

    switch ($Permission.principalType.toUpper()) {
        'AAD' { New-RKContainedDatabaseUser -PrincipalName $Permission.principalName -DatabaseProperties $databaseProperties -Refresh }
        'SQL' { New-RKSQLDatabaseUser -PrincipalName $Permission.principalName -DatabaseProperties $databaseProperties -Refresh }
        Default { Write-Error "Principal type $($Permission.principalType) is invalid for this permission."; }
    }

    Set-RKDatabaseRoleMember -PrincipalName $Permission.principalName -RoleName $Permission.databaseRole -DatabaseProperties $databaseProperties
}

function New-RKDatabaseRole {
    param(
        [string]$RoleName,
        [DatabaseProperties]$DatabaseProperties
    )
    $SQL = "SELECT 1 [Exists] FROM sys.database_principals WHERE [name] = '$RoleName' AND type = 'R'"
    $Result = Invoke-RKSQL -SQL $SQL -DatabaseProperties $DatabaseProperties

    if ($Result.exists -ne 1) {
        $SQL = "CREATE ROLE [$RoleName]"
        Invoke-RKSQL -SQL $SQL -DatabaseProperties $DatabaseProperties
    }
}

function New-RKSQLDatabaseUser {
    param(
        [string]$PrincipalName,
        [DatabaseProperties]$DatabaseProperties,
        [switch]$Refresh
    )
    # todo
}

function Invoke-RKSQL {
    param([string]$SQL, [DatabaseProperties]$DatabaseProperties)
    if (!(Get-Module sqlServer -ListAvailable)) {
        Install-Module sqlServer -Scope CurrentUser -Force
    }
    Import-Module sqlServer
    Write-Host "Executing SQL Server: '$($DatabaseProperties.ServerName)' Database: '$($databaseProperties.DatabaseName)', SQL: $($SQL)"
    return (Invoke-Sqlcmd -Query $SQL -ServerInstance $DatabaseProperties.ServerName -Database $DatabaseProperties.DatabaseName -AccessToken $databaseProperties.DatabaseAccessToken)
}

function New-RKContainedDatabaseUser {
    param(
        [string]$PrincipalName,
        [DatabaseProperties]$DatabaseProperties,
        [switch]$Refresh
    )
    $SQL = "SELECT 1 [Exists] FROM sys.database_principals WHERE [name] = '$PrincipalName' AND type IN ('E', 'X')"
    $Result = Invoke-RKSQL -SQL $SQL -DatabaseProperties $DatabaseProperties

    if (($Result.exists -eq 1) -and ($Refresh.IsPresent)) {
        $SQL = "DROP USER [$PrincipalName]; CREATE USER [$PrincipalName] FROM EXTERNAL PROVIDER"
        Invoke-RKSQL -SQL $SQL -DatabaseProperties $DatabaseProperties
    }
    elseif ($Result.exists -ne 1) {
        $SQL = "CREATE USER [$PrincipalName] FROM EXTERNAL PROVIDER"
        Invoke-RKSQL -SQL $SQL -DatabaseProperties $DatabaseProperties
    }
}

function Set-RKDatabaseRoleMember {
    param(
        [string]$RoleName,
        [string]$PrincipalName,
        [DatabaseProperties]$DatabaseProperties
    )
    Write-Host "Attempting Set-RKDatabaseRoleMember: Role: '$($RoleName)', Principal: '$($PrincipalName)'"
    $SQL = "EXEC sp_addrolemember '$($RoleName)', '$($PrincipalName)'"
    Invoke-RKSQL -SQL $SQL -DatabaseProperties $DatabaseProperties
}

function Get-RKAzDatabaseAccessToken {
    param()
    return (Get-RKAzAccessToken -Resource 'https://database.windows.net/')
}

class DatabaseProperties
{
    [ValidateNotNullOrEmpty()][string]$ServerName
    [ValidateNotNullOrEmpty()][string]$DatabaseName
    [ValidateNotNullOrEmpty()][string]$DatabaseAccessToken
}