Monday, July 27, 2020

How to Backup and Restore Shielded VM Local Certifcates


One of the hot new technologies in Hyper-V 2016 is Shielded Virtual Machines. This feature plugs a few long-standing security holes in the hypervisor space that were exacerbated by the rise of hosting providers. It’s ridiculously easy to start using Shielded Virtual Machines, but its simplicity can mask some very serious consequences if the environment and guests are not properly managed. To make matters worse, the current documentation on this feature is sparse and reads more like marketing brochures than technical material.

The material that does exist implies that Shielded Virtual Machines require a complicated Host Guardian Service configuration and a cluster or two. This is not true. You can use Shielded Virtual Machines on standalone hosts without ever even finding any setup for Host Guardian Service (HGS). Using a properly configured HGS is better, but it is not required. Standalone mode is possible. “Standalone” can apply to non-domain-joined hosts and domain-joined hosts that are not members of a cluster. I did verify that I could enable VM shielding on a non-domain-joined host, but I did not, and will not, investigate it any further. This article will discuss using Shielded Virtual Machines on a domain-joined Hyper-V host that is not a member of a cluster and is not governed by a Host Guardian Service.

What are Shielded Virtual Machines?

A Shielded Virtual Machine is protected against tampering. There are several facets to this protection.

Unauthorized Hosts Cannot Start Shielded Virtual Machines

Only systems specifically authorized to operate a Shielded Virtual Machine will be able to start it. Others will receive an error message that isn’t perfectly obvious, but should be decipherable with a bit of thought. The primary error is “The key protector could not be unwrapped. Details are included in the HostGuardianService-Client event log.” The details of the error will be different depending on your overall configuration.

No Starting Shielded VMs on Unauthorized Hosts
No Starting Shielded VMs on Unauthorized Hosts

This feature is most useful when combined with the next.

Unauthorized Hosts Cannot Mount Virtual Hard Disks from Shielded Virtual Machines

The virtual hard disks for a Shielded Virtual Machine cannot be opened or mounted on unauthorized systems. Take care as the error message on an unauthorized host is not nearly as clear as the message that you receive when trying to start a Shielded Virtual Machine on an unauthorized host, and it could be mistaken for a corrupted VHD: “Couldn’t Mount File. The disk image isn’t initialized, or contains partitions that aren’t recognizable, or contains volumes that haven’t been assigned drive letters. Please use the Disk Management snap-in to make sure that the disk, partitions, and volumes are in a usable state.”.

For small businesses, this is the primary benefit of using Shielded Virtual Machines. If your VM’s files are ever stolen, the thieves will need more than that.

VMConnect.exe Cannot be Used on a Shielded Virtual Machine

Even administrators can’t use VMConnect.exe to connect to a Shielded Virtual Machine. In case you didn’t already know, “VMConnect.exe” is a separate executable that Hyper-V Manager and Failover Cluster Manager both call upon when you instruct them to connect to the console of a virtual machine. This connection refusal provides a small level of protection against snooping by a service provider’s employees, but does more against other tenants that might inadvertently have been granted a few too many privileges on the host. Attempting to connect results in a message that “You cannot connect to a shielded virtual machine using a Virtual Machine Connection. Use a Remote Desktop Connection instead.”

No VMConnect for Shielded VMs
No VMConnect for Shielded VMs

The upshot of the VMConnect restriction is that if you create VMs from scratch and immediately set them to be shielded, you’d better have some method in mind of installing an OS without using the console at all (as in, completely unattended WDS).

What are the Requirements for Shielded Virtual Machines?

The requirements for using Shielded Virtual Machines are:

  • Generation 2 virtual machines

That’s it. You’ll read a lot about the need for clusters and services and conditional branches where a physical Trusted Platform Module (TPM) can be used or when administrator sign-off will do and all other sorts of things, but all of those are in regards to Guarded Fabric and involve the Host Guardian Service. Again, HGS is a very good thing to have, and would certainly give you a more resilient and easily managed Shielded Virtual Machine environment, but none of that is required. The only thing that you must absolutely have is a Generation 2 virtual machine. Generation 1 VMs cannot be shielded.

Generation 1 virtual machines can be encrypted by Hyper-V. That’s a topic for another article.

How Does the Shielded Virtual Machine Mechanism Work on a Standalone System?

Do not skip this section just because it might have some dry technical details! Ignorance on this topic could easily leave you with virtual machines whose data you cannot access! Imagine a situation in which you have a single, non-clustered host with a guest on a Scale Out File Server cluster and you enable the Shielded VM feature. Since all of the virtual machine’s data is on an automatically backed-up storage location, you don’t bother doing anything special for backup. One day, your Hyper-V host spontaneously combusts. You buy a new host and import the VM directly from the SOFS cluster, only to learn that you can’t turn it on. What can you do!? You could try crying or drinking or cursing or sacrificing a rubber chicken or anything else that makes you feel better, but nothing that you do short of cracking the virtual machine’s encryption will get any of that data back. If you don’t want that to be you, pay attention to this section.

Shielded Virtual Machines are Locked with Digital Keys

Access to and control of a Shielded Virtual Machine is governed by asymmetric public/private encryption keys. In a single host environment without a configured Host Guardian Service, these keys are created automatically immediately after you set the first virtual machine to be shielded. You can see these certificates in two ways.

The CERTUTIL.EXE program is available on any system, including those without a GUI. At an elevated command prompt, type:

You’ll be presented with a dialog that shows the Shielded VM Encryption Certificate. Click More Choices and it will expand to show that certificate and the Shielded VM Signing Certificate:

VM Shielding Certificates
VM Shielding Certificates

You can click either of the certificates in the bottom half of the dialog and it will update the information in the top half of the dialog. Click the Click here to view certificate properties link, and you’ll be rewarded with the Certificate Details dialog:

Certificate Details
Certificate Details

This dialog should look fairly familiar if you’ve ever looked at a certificate in Internet Explorer or in the Certificates MMC snap-in. We’ll turn to that snap-in next.

The Microsoft Management Console (MMC.EXE) has a dependency on the Explorer rendering engine, so it is only available on GUI systems. You can use it to connect to systems without a GUI, though, as long as they are in the same or a trusting domain.

  1. After that, you’ll need to indicate which computer to control. In my example, I want the local computer so I’ll leave that selection. You can connect to any computer in the same or a trusting domain, provided that the user account that you started MMC.EXE with has administrative privileges on that computer:
    Choose Local or Remote Computer
    Choose Local or Remote Computer
  2. After you OK out of all of the above dialogs, MMC.EXE will populate with the certificate tree of the targeted computer account. Expand Shielded VM Local Certificates, then click the Certificates node. If you have shielded a virtual machine, you’ll see two certificates:
    VM Shielding Certificates in MMC
    VM Shielding Certificates in MMC

You can open these certificates to view them.

Not to put too fine a point on it, but these two certificates are absolutely vital. If they are lost, any virtual machine that they were used to shield is also permanently lost… unless you have the ability to crack 2048-bit SHA256 encryption. There is no backdoor. There is no plan “B”.

If you are backing up your host’s operating system using traditional backup applications, a standard System State backup will include the certificate store.

If you are not backing up the management operating system, then you need a copy of these keys. I’ll give you directions, but the one thing that you must absolutely not miss is the bit about exporting the private keys. The shielding certificates are completely useless without their private keys!

Using CERTUTIL.EXE is the fastest and safest way to export certificates.

  1. Open an elevated command prompt.
  2. VM Shielded Certificates with Serial Numbers
    VM Shielded Certificates with Serial Numbers
  3. Use the mouse to highlight the first serial number, which should be for the encryption certificate, then press [Enter] to copy it to the clipboard.
  4. To export the VM shielding encryption certificate, type the following, replacing my information with yours. Use right-click to paste the serial number when you come to that point: certutil -exportPFX -p "2Easy2Guess!" "Shielded VM Local Certificates" 169d0cacaea2a396428b62f77545682e c:\temp\SVHV02-VMEncryption.pfx
  5. Use the mouse to highlight the second serial number, which should be for the signing certificate, then press [Enter] to copy it to the clipboard.
  6. To export the VM shielding signing certificate, type the following, replacing my information with yours. Use right-click to paste the serial number when you come to that point: certutil -exportPFX -p "2Easy2Guess!" "Shielded VM Local Certificates" 5d0cb1f0fa8b34b24e1195c41d997c19 c:\temp\SVHV02-VMSigning.pfx
  7. Ensure that the PFX files that you created are moved to a SAFE place and that the password is SECURED!

If you ever need to recover the certificates, use this template:

You’ll be prompted for the password on each one.

The MMC snap-in all but encourages you to do some very silly things, so I would recommend that you use the certutil instructions above instead. If you must use the UI:

  1. Open MMC and the Certificates snap-in using instructions from the “Viewing Shielded Virtual Machine Certificates Using the Certificates MMC Snap-In” section above.
  2. Highlight both certificates. Right-click them, hover over All Tasks, and click Export…
  • Click Next on the informational screen.
  • Leave the defaults on the Export File Format page. If you know what you’re doing, you can select Enable certificate privacy. Do not select the option to Delete the private key!! The host will no longer be able to open its own shielded VMs if you do that!
  • On the Security tab, you must choose to lock the exported certificate to a security principal or a password. It’s tempting to lock it down by security principal, and it might even work for you. I almost always use passwords because they’ll survive where security principals won’t. If you do choose this option, only use domain principals, use groups not names, use more than one, and make double-, triple-, and quadruple- certain that your Active Directory backups are in good shape. If you’re one of those types that likes to leave your Hyper-V hosts outside of the domain for whatever reason, the Groups or user names option is a good way to lose your shielded VMs forever.
  • The final screen is just a summary. Click Finish to complete the export.
  • Ensure that the PFX files that you created are moved to a SAFE place and that the password is SECURED (or if you used one or more security principals, hope that nothing ever happens to them)!

If you ever need to recover these certificates, I would again recommend using certutil.exe instead. The GUI still makes some dangerous suggestions and it takes much longer. If you insist on the GUI:

  1. Open MMC and the Certificates snap-in using instructions from the “Viewing Shielded Virtual Machine Certificates Using the Certificates MMC Snap-In” section above.
  2. Right-click in the center pane and hover over All Tasks, and click Import…
  3. Click Next on the introductory screen.
  4. On the File to Import screen, navigate to where your certificate backups are. Note that you’ll need to change the filter from *.cer, *.crt to *.pfx, *.p12 to see them.
  5. The Password part of the Private key protection screen is fairly easy to figure out (and won’t be necessary at all if you protected by security principal). Do make sure to check the Mark this key as exportable box. If you don’t, then you won’t be able to export the private key. It’s not strictly necessary, since you do have the file that you’re importing from. At least, you have it right now. Something could happen to it, and then you’d have no way to generate a new one.
    Import as Exportable
    Import as Exportable
  6. The final screen is just a summary. Click Finish to import the certificates.

Do take good care of these certificates. They are literally the keys to your Shielded Virtual Machines.

Why Does the Certificate Say “Untrusted Guardian”?

The consequence of not using a full Host Guardian Service is that there’s no independent control over these certificates. With HGS, there’s independent “attestation” that a host is allowed to run a particular virtual machine because the signature on the VM and the signing certificate will match up and, most importantly, the signing certificate was issued by someone else. In this case, the certificate is “self-signed”. You’ll see the term “self-signed” used often, and usually incorrectly. Most of the time, I see it used to refer to certificates that were signed by someone’s internal certificate authority, like their private domain’s Enterprise CA. That is not self-signed! A true self-signed certificate is signed and issued by a host that is not a valid certificate authority and is only used by that host. The most literal meaning of a self-signed certificate is: “I certify that this content was signed/encrypted by me because I say so.” There is no independent verification of any kind for a true self-signed certificate.

Can I Use Shielded VMs from an “Untrusted Guardian” on Another Hyper-V Host?

Yes. These virtual machines are not permanently matched to their source host. That’s a good thing, because otherwise you’d never be able to restore them after a host failure. All that you need to do is import the keys that were used to sign and encrypt those virtual machines on the new target host into its “Shielded VM Local Certificates” store, and it will then be able to immediately open those VMs. There will not be any conflict with any certificates that are already there. This should work for Live Migrations as well, although I only tested export/import.

If you like, you can unshield the VMs and then reshield them. That will shield the VMs under the keyset of the new target host.

What Happens When the Certificate Expires?

I didn’t test, so I don’t know. You could try it out by forcing your clock 10 years into the future.

Realistically, nothing bad will happen when the certificate expires. An expired certificate still matches perfectly to whatever it signed and/or encrypted, so I see no reason why the VMs wouldn’t still work. You can’t renew these certificates, though, so the host will no longer be able to use them to sign or encrypt new VMs. If this is still something that you’re concerned about 9 years and 11 months after shielding your first VM, be happy that your host made it that long and then unshield all of the VMs, delete the certificates, and reshield the VMs. New 10 year certificates will be automatically created and give you another decade to worry about the problem.

How Do I Know if a VM is Shielded?

The “easiest” way is the checkbox on the GUI tab. There’s also PowerShell:

Virtual hard drives are a bit tougher. Get-VHD, even on Server 2016, does not show anything about encryption. You can test it in a hex editor or something else that can poke at the actual bits, of course, but other than that I don’t know of a way to tell.

Are Shielded VMs a Good Idea on an Untrusted Host?

I’m not sure if there is a universal answer to this question. Without the Host Guardian Service being fully configured, there is a limit to the usefulness of Shielded VMs. I would say that if you have the ability to configure HGS, do that.

That said, shielding a VM on an untrusted host still protects its data if the files for the VM are ever copied to a system outside of your control. Just remember that anyone with administrative access to the host has access to the certificate. What you can do, if you’ve got an extremely solid protection plan, is export, delete, and re-import the certificate without marking the private key as exportable. That’s risky, because you’re then counting on never forgetting or losing that exported certificate. However, even a local admin won’t be able to steal virtual machines without having access to the exported key as well

Friday, July 24, 2020

sigcheck

sigcheck -u -e c:\windows\system32

Install Windows 10 from a USB Flash Drive

If you encounter at step
10 DISKPART> active

Error:
The selected disk is not a fixed MBR disk.
The ACTIVE command can only be used on fixed MBR disks.

run DISKPART> exit
redo all steps until 8, after DISKPART> clean is successful
run following DISKPART> convert MBR, continue with step 8 and the rest described here



------------------------------------------


Steps
1. Insert a usb drive at least 4gb in size

2. Open a command prompt as administrator
Hit Windows Key, type cmd and hit Ctrl+Shift+Enter. This will force it to open as admin.

3. Run diskpart
This will open and run the Diskpart command line utility, which allows you to manage disks, partitions and volumes.

C:\Windows\system32> diskpart

4. Run list disk
This will list all disks on your system. You’ll see the something similar to this:

DISKPART> list disk

  Disk ###  Status         Size     Free     Dyn  Gpt
  --------  -------------  -------  -------  ---  ---
  Disk 0    Online          238 GB      0 B
  Disk 1    Online          465 GB      0 B
  Disk 2    Online           29 GB      0 B

5. Select your flash drive by running select disk #
Find the item that corresponds with your flash drive and select the disk. In the example above, my flash drive is disk 2 so I’ll run:

DISKPART> select disk 2

Disk 2 is now the selected disk.

6. Run clean
WARNING: This deletes all data on your drive
The clean command marks all data on the drive as deleted and therefore removes all partitions and volumes. Make sure you want to do this! If you are sure, run:

DISKPART> clean

7. Create a partition
 DISKPART> create partition primary

8. Select the new partition
Since we know there is only one partition, we can just run this:

DISKPART> select partition 1
Without checking the partition number. If you’re really curious, run list partition to check.

9. Format the partition
To format it, we’ll use the NTFS file system and run a quick format:

DISKPART> format fs=ntfs quick
10. Set the current partition as Active
Run:

DISKPART> active

11. Exit diskpart
Run exit. This will exit diskpart, but leave the command window open.

12. Mount your ISO
Use Virtual CloneDrive or similar.

13. Navigate to the mounted image and install a bootsector
My ISO is mounted as G:\, so I’ll navigate to G:\boot and run:

C:Windowssystem32> G:
G:\> cd boot
G:\boot> bootsect.exe /nt60 E:
Where E:\ in this case is my flash drive’s letter.

14. Copy the entire contents of the ISO to your flash drive

Friday, July 17, 2020

Reset 11g password

alter profile DEFAULT limit PASSWORD_LIFE_TIME UNLIMITED;

SET LINESIZE 300
SET PAGESIZE 999
SELECT 'ALTER USER '|| name ||' IDENTIFIED BY VALUES '''|| spare4 ||';'|| password ||''';' FROM sys.user$ WHERE name='DBSNMP';

Gnupg.psm1

Original URL:


--------------------------------------------

function Install-GnuPg 
    <# 
    .SYNOPSIS 
        This function installed the GnuPg for Windows application.  It the installer file is not in 
        the DownloadFolderPath, the function will download the file from the Internet and then execute a silent installation. 
    .PARAMETER  DownloadFolderPath 
        The folder path where you'd like to download the GnuPg for Windows installer into. 
 
    .PARAMETER  DownloadUrl 
        The URL that will be used to download the EXE setup installer. 
 
    .EXAMPLE 
        PS> Install-GnuPg -DownloadFolderPath C:\Downloads 
 
        This will first check to ensure the GnuPg for Windows installer is in the C:\Downloads folder.  If not, it will then 
        download the file from the default URL set at DownloadUrl.  Once downloaded, it will then silently execute 
        the installation and get the application installed with default parameters. 
     
    .INPUTS 
        None. This function does not accept pipeline input. 
 
    .OUTPUTS 
        None. If successful, this function will not return any output. 
    #> 
     
    [CmdletBinding()] 
    param 
    ( 
        [Parameter(Mandatory)] 
        [ValidateNotNullOrEmpty()] 
        [string]$DownloadFolderPath, 
         
        [Parameter()] 
        [ValidateNotNullOrEmpty()] 
        [string]$DownloadUrl = 'http://files.gpg4win.org/gpg4win-2.2.5.exe' 
         
    ) 
    process 
    { 
        try 
        { 
            $DownloadFilePath = "$DownloadFolderPath\$($DownloadUrl | Split-Path -Leaf)" 
            if (-not (Test-Path -Path $DownloadFilePath -PathType Leaf)) 
            { 
                Write-Verbose -Message "Downloading [$($DownloadUrl)] to [$($DownloadFilePath)]" 
                Invoke-WebRequest -Uri $DownloadUrl -OutFile $DownloadFilePath 
            } 
            else 
            { 
                Write-Verbose -Message "The download file [$($DownloadFilePath)] already exists" 
            } 
            Write-Verbose -Message 'Attempting to install GPG4Win...' 
            Start-Process -FilePath $DownloadFilePath -ArgumentList '/S' -NoNewWindow -Wait -PassThru 
            Write-Verbose -Message 'GPG4Win installed' 
        } 
        catch 
        { 
            Write-Error $_.Exception.Message 
        } 
    } 
 
function Add-Encryption 
    <# 
    .SYNOPSIS 
        This function uses the GnuPG for Windows application to symmetrically encrypt a set of files in a folder. 
 
    .DESCRIPTION 
        A detailed description of the function. 
 
    .PARAMETER FolderPath 
        This is the folder path that contains all of the files you'd like to encrypt. 
 
    .PARAMETER  Password 
        This is the password that will be used to encrypt the files. 
 
    .EXAMPLE 
        PS> Add-Encryption -FolderPath C:\TestFolder -Password secret 
 
        This example would encrypt all of the files in the C:\TestFolder folder with the password of 'secret'.  The encrypted 
        files would be created with the same name as the original files only with a GPG file extension. 
 
    .INPUTS 
        None. This function does not accept pipeline input. 
 
    .OUTPUTS 
        System.IO.FileInfo 
    #> 
     
    [CmdletBinding()] 
    [OutputType([System.IO.FileInfo])] 
    param 
    ( 
        [Parameter(Mandatory)] 
        [ValidateNotNullOrEmpty()] 
        [ValidateScript({Test-Path -Path $_ -PathType Container})] 
        [string]$FolderPath, 
     
        [Parameter(Mandatory)] 
        [ValidateNotNullOrEmpty()] 
        [string]$Password, 
     
        [Parameter()] 
        [ValidateNotNullOrEmpty()] 
        [string]$GpgPath = 'C:\Program Files (x86)\GNU\GnuPG\gpg2.exe' 
         
    ) 
    process { 
        try 
        { 
            Get-ChildItem -Path $FolderPath | foreach { 
                Write-Verbose -Message "Encrypting [$($_.FullName)]" 
                Start-Process -FilePath $GpgPath -ArgumentList "--batch --passphrase $Password -c $($_.FullName)" -Wait -NoNewWindow 
            } 
            Get-ChildItem -Path $FolderPath -Filter '*.gpg' 
        } 
        catch 
        { 
            Write-Error $_.Exception.Message 
        } 
    } 
 
function Remove-Encryption 
    <# 
    .SYNOPSIS 
        This function decrypts all files encrypted with the Add-Encryption function. Once decrypted, it will add the files 
        to the same directory that contains the encrypted files and will remove the GPG file extension. 
 
    .PARAMETER FolderPath 
        The folder path that contains all of the encrypted *.gpg files. 
 
    .PARAMETER Password 
        The password that was used to encrypt the files. 
 
    .EXAMPLE 
        PS> Remove-Encryption -FolderPath C:\MyFolder -Password secret 
 
        This example will attempt to decrypt all files inside of the C:\MyFolder folder using the password of 'secret' 
 
    .INPUTS 
        None. This function does not accept pipeline input. 
 
    .OUTPUTS 
        System.IO.FileInfo 
     
    #> 
     
    [CmdletBinding()] 
    param 
    ( 
        [Parameter(Mandatory)] 
        [ValidateNotNullOrEmpty()] 
        [ValidateScript({ Test-Path -Path $_ -PathType Container })] 
        [string]$FolderPath, 
         
        [Parameter(Mandatory)] 
        [ValidateNotNullOrEmpty()] 
        [string]$Password, 
         
        [Parameter()] 
        [ValidateNotNullOrEmpty()] 
        [string]$GpgPath = 'C:\Program Files (x86)\GNU\GnuPG\gpg2.exe' 
    ) 
    process 
    { 
        try 
        { 
            Get-ChildItem -Path $FolderPath -Filter '*.gpg' | foreach { 
                $decryptFilePath = $_.FullName.TrimEnd('.gpg') 
                Write-Verbose -Message "Decrypting [$($_.FullName)] to [$($decryptFilePath)]" 
                $startProcParams = @{ 
                    'FilePath' = $GpgPath 
                    'ArgumentList' = "--batch --yes --passphrase $Password -o $decryptFilePath -d $($_.FullName)"  
                    'Wait' = $true 
                    'NoNewWindow' = $true 
                } 
                $null = Start-Process @startProcParams 
            } 
            Get-ChildItem -Path $FolderPath | where {$_.Extension -ne 'gpg'} 
        } 
        catch 
        { 
            Write-Error $_.Exception.Message 
        } 
    } 
}

FileCryptography.psm1

Original URL:


------------------------------------------------------------------------

function New-CryptographyKey()
{
<#
.SYNOPSIS 
Generates a random cryptography key.

.DESCRIPTION
Generates a random cryptography key based on the desired key size.

.PARAMETER Algorithm
Algorithm to generate key for.

.PARAMETER KeySize
Number of bits the generated key will have.

.PARAMETER AsPlainText
Returns a String instead of SecureString.

.OUTPUTS
System.Security.SecureString. New-CryptographyKey return the key as a SecureString by default.
System.String. New-CryptographyKey will return the key in plain text as a string if the -AsPlainText parameter is specified.

.EXAMPLE
$key = New-CryptographyKey
This example generates a random 256-bit AES key and stores it in the variable $key.

.NOTES
Author: Tyler Siegrist
Date: 9/22/2017
#>
[CmdletBinding()]
[OutputType([System.Security.SecureString])]
[OutputType([String], ParameterSetName='PlainText')]
Param(
    [Parameter(Mandatory=$false, Position=1)]
    [ValidateSet('AES','DES','RC2','Rijndael','TripleDES')]
    [String]$Algorithm='AES',
    [Parameter(Mandatory=$false, Position=2)]
    [Int]$KeySize,
    [Parameter(ParameterSetName='PlainText')]
    [Switch]$AsPlainText
)
    Process
    {
        try
        {
            $Crypto = [System.Security.Cryptography.SymmetricAlgorithm]::Create($Algorithm)
            if($PSBoundParameters.ContainsKey('KeySize')){
                $Crypto.KeySize = $KeySize
            }
            $Crypto.GenerateKey()
            if($AsPlainText)
            {
                return [System.Convert]::ToBase64String($Crypto.Key)
            }
            else
            {
                return [System.Convert]::ToBase64String($Crypto.Key) | ConvertTo-SecureString -AsPlainText -Force
            }
        }
        catch
        {
            Write-Error $_
        }
        
    }
}

Function Protect-File
{
<#
.SYNOPSIS 
Encrypts a file using a symmetrical algorithm.

.DESCRIPTION
Encrypts a file using a symmetrical algorithm.

.PARAMETER FileName
File(s) to be encrypted.

.PARAMETER Key
Cryptography key as a SecureString to be used for encryption.

.PARAMETER KeyAsPlainText
Cryptography key as a String to be used for encryption.

.PARAMETER CipherMode
Specifies the block cipher mode to use for encryption.

.PARAMETER PaddingMode
Specifies the type of padding to apply when the message data block is shorter than the full number of bytes needed for a cryptographic operation.

.PARAMETER Suffix
Suffix of the encrypted file to be removed.

.PARAMETER RemoveSource
Removes the source (decrypted) file after encrypting.

.OUTPUTS
System.IO.FileInfo. Protect-File will return FileInfo with the SourceFile, Algorithm, Key, CipherMode, and PaddingMode as added NoteProperties

.EXAMPLE
Protect-File 'C:\secrets.txt' $key
This example encrypts C:\secrets.txt using the key stored in the variable $key. The encrypted file would have the default extension of '.AES' and the source (decrypted) file would not be removed.

.EXAMPLE
Protect-File 'C:\secrets.txt' -Algorithm DES -Suffix '.Encrypted' -RemoveSource
This example encrypts C:\secrets.txt with a randomly generated DES key. The encrypted file would have an extension of '.Encrypted' and the source (decrypted) file would be removed.

.EXAMPLE
Get-ChildItem 'C:\Files' -Recurse | Protect-File -Algorithm AES -Key $key -RemoveSource
This example encrypts all of the files under the C:\Files directory using the key stored in the variable $key. The encrypted files would have the default extension of '.AES' and the source (decrypted) files would be removed.

.NOTES
Author: Tyler Siegrist
Date: 9/22/2017
#>
[CmdletBinding(DefaultParameterSetName='SecureString')]
[OutputType([System.IO.FileInfo[]])]
Param(
    [Parameter(Mandatory=$true, Position=1, ValueFromPipeline=$true, ValueFromPipelineByPropertyName=$true)]
    [Alias('PSPath','LiteralPath')]
    [string[]]$FileName,
    [Parameter(Mandatory=$false, Position=2)]
    [ValidateSet('AES','DES','RC2','Rijndael','TripleDES')]
    [String]$Algorithm = 'AES',
    [Parameter(Mandatory=$false, Position=3, ParameterSetName='SecureString')]
    [System.Security.SecureString]$Key = (New-CryptographyKey -Algorithm $Algorithm),
    [Parameter(Mandatory=$true, Position=3, ParameterSetName='PlainText')]
    [String]$KeyAsPlainText,
    [Parameter(Mandatory=$false, Position=4)]
    [System.Security.Cryptography.CipherMode]$CipherMode,
    [Parameter(Mandatory=$false, Position=5)]
    [System.Security.Cryptography.PaddingMode]$PaddingMode,
    [Parameter(Mandatory=$false, Position=6)]
    [String]$Suffix = ".$Algorithm",
    [Parameter()]
    [Switch]$RemoveSource
)
    Begin
    {
        #Configure cryptography
        try
        {
            if($PSCmdlet.ParameterSetName -eq 'PlainText')
            {
                $Key = $KeyAsPlainText | ConvertTo-SecureString -AsPlainText -Force
            }

            #Decrypt cryptography Key from SecureString
            $BSTR = [System.Runtime.InteropServices.Marshal]::SecureStringToBSTR($Key)
            $EncryptionKey = [System.Convert]::FromBase64String([System.Runtime.InteropServices.Marshal]::PtrToStringAuto($BSTR))

            $Crypto = [System.Security.Cryptography.SymmetricAlgorithm]::Create($Algorithm)
            if($PSBoundParameters.ContainsKey('CipherMode')){
                $Crypto.Mode = $CipherMode
            }
            if($PSBoundParameters.ContainsKey('PaddingMode')){
                $Crypto.Padding = $PaddingMode
            }
            $Crypto.KeySize = $EncryptionKey.Length*8
            $Crypto.Key = $EncryptionKey
        }
        Catch
        {
            Write-Error $_ -ErrorAction Stop
        }
    }
    Process
    {
        $Files = Get-Item -LiteralPath $FileName
    
        ForEach($File in $Files)
        {
            $DestinationFile = $File.FullName + $Suffix

            Try
            {
                $FileStreamReader = New-Object System.IO.FileStream($File.FullName, [System.IO.FileMode]::Open)
                $FileStreamWriter = New-Object System.IO.FileStream($DestinationFile, [System.IO.FileMode]::Create)

                #Write IV (initialization-vector) length & IV to encrypted file
                $Crypto.GenerateIV()
                $FileStreamWriter.Write([System.BitConverter]::GetBytes($Crypto.IV.Length), 0, 4)
                $FileStreamWriter.Write($Crypto.IV, 0, $Crypto.IV.Length)

                #Perform encryption
                $Transform = $Crypto.CreateEncryptor()
                $CryptoStream = New-Object System.Security.Cryptography.CryptoStream($FileStreamWriter, $Transform, [System.Security.Cryptography.CryptoStreamMode]::Write)
                $FileStreamReader.CopyTo($CryptoStream)
    
                #Close open files
                $CryptoStream.FlushFinalBlock()
                $CryptoStream.Close()
                $FileStreamReader.Close()
                $FileStreamWriter.Close()

                #Delete unencrypted file
                if($RemoveSource){Remove-Item -LiteralPath $File.FullName}

                #Output ecrypted file
                $result = Get-Item $DestinationFile
                $result | Add-Member –MemberType NoteProperty –Name SourceFile –Value $File.FullName
                $result | Add-Member –MemberType NoteProperty –Name Algorithm –Value $Algorithm
                $result | Add-Member –MemberType NoteProperty –Name Key –Value $Key
                $result | Add-Member –MemberType NoteProperty –Name CipherMode –Value $Crypto.Mode
                $result | Add-Member –MemberType NoteProperty –Name PaddingMode –Value $Crypto.Padding
                $result
            }
            Catch
            {
                Write-Error $_
                If($FileStreamWriter)
                {
                    #Remove failed file
                    $FileStreamWriter.Close()
                    Remove-Item -LiteralPath $DestinationFile -Force
                }
                Continue
            }
            Finally
            {
                if($CryptoStream){$CryptoStream.Close()}
                if($FileStreamReader){$FileStreamReader.Close()}
                if($FileStreamWriter){$FileStreamWriter.Close()}
            }
        }
    }
}

Function Unprotect-File
{
<#
.SYNOPSIS 
Decrypts a file encrypted with Protect-File.

.DESCRIPTION
Decrypts a file using a provided cryptography key.

.PARAMETER FileName
File(s) to be decrypted.

.PARAMETER Key
Cryptography key as a SecureString be used for decryption.

.PARAMETER KeyAsPlainText
Cryptography key as a String to be used for decryption.

.PARAMETER CipherMode
Specifies the block cipher mode that was used for encryption.

.PARAMETER PaddingMode
Specifies the type of padding that was applied when the message data block was shorter than the full number of bytes needed for a cryptographic operation.

.PARAMETER Suffix
Suffix of the encrypted file to be removed.

.PARAMETER RemoveSource
Removes the source (encrypted) file after decrypting.

.OUTPUTS
System.IO.FileInfo. Unprotect-File will return FileInfo with the SourceFile as an added NoteProperty

.EXAMPLE
Unprotect-File 'C:\secrets.txt.AES' $key
This example decrypts C:\secrets.txt.AES using the key stored in the variable $key. The decrypted file would remove the default extension of '.AES' and the source (encrypted) file would not be removed.

.EXAMPLE
Unprotect-File 'C:\secrets.txt.Encrypted' -Algorithm DES -Key $key -Suffix '.Encrypted' -RemoveSource
This example decrypts C:\secrets.txt.Encrypted using DES and the key stored in the variable $key. The decrypted file would remove the extension of '.Encrypted' and the source (encrypted) file would be removed.

.EXAMPLE
Get-ChildItem 'C:\Files' -Recurse | Unprotect-File -Algorithm AES -Key $key -RemoveSource
This example decrypts all of the files under the C:\Files directory using the key stored in the variable $key. The decrypted files would remove the default extension of '.AES' and the source (encrypted) files would be removed.

.NOTES
Author: Tyler Siegrist
Date: 9/22/2017
#>
[CmdletBinding(DefaultParameterSetName='SecureString')]
[OutputType([System.IO.FileInfo[]])]
Param(
    [Parameter(Mandatory=$true, Position=1, ValueFromPipeline=$true, ValueFromPipelineByPropertyName=$true)]
    [Alias('PSPath','LiteralPath')]
    [string[]]$FileName,
    [Parameter(Mandatory=$false, Position=2, ValueFromPipelineByPropertyName=$true)]
    [ValidateSet('AES','DES','RC2','Rijndael','TripleDES')]
    [String]$Algorithm = 'AES',
    [Parameter(Mandatory=$true, Position=3, ValueFromPipelineByPropertyName=$true, ParameterSetName='SecureString')]
    [System.Security.SecureString]$Key,
    [Parameter(Mandatory=$true, Position=3, ParameterSetName='PlainText')]
    [String]$KeyAsPlainText,
    [Parameter(Mandatory=$false, Position=4, ValueFromPipelineByPropertyName=$true)]
    [System.Security.Cryptography.CipherMode]$CipherMode = 'CBC',
    [Parameter(Mandatory=$false, Position=5, ValueFromPipelineByPropertyName=$true)]
    [System.Security.Cryptography.PaddingMode]$PaddingMode = 'PKCS7',
    [Parameter(Mandatory=$false, Position=6)]
    [String]$Suffix, #Assigning default value in code due to it not processing ".$Algorithm" properly when Algorithm is ValueFromPipelineByPropertyName
    [Parameter()]
    [Switch]$RemoveSource
)
    Process
    {
        #Configure cryptography
        try
        {
            if($PSCmdlet.ParameterSetName -eq 'PlainText')
            {
                $Key = $KeyAsPlainText | ConvertTo-SecureString -AsPlainText -Force
            }

            #Decrypt cryptography Key from SecureString
            $BSTR = [System.Runtime.InteropServices.Marshal]::SecureStringToBSTR($Key)
            $EncryptionKey = [System.Convert]::FromBase64String([System.Runtime.InteropServices.Marshal]::PtrToStringAuto($BSTR))

            $Crypto = [System.Security.Cryptography.SymmetricAlgorithm]::Create($Algorithm)
            $Crypto.Mode = $CipherMode
            $Crypto.Padding = $PaddingMode
            $Crypto.KeySize = $EncryptionKey.Length*8
            $Crypto.Key = $EncryptionKey
        }
        Catch
        {
            Write-Error $_ -ErrorAction Stop
        }

        if(-not $PSBoundParameters.ContainsKey('Suffix'))
        {
            $Suffix = ".$Algorithm"
        }

        #Used to store successfully decrypted file names.
        $Files = Get-Item -LiteralPath $FileName

        ForEach($File in $Files)
        {
            #Verify file ends with supplied suffix
            If(-not $File.Name.EndsWith($Suffix))
            {
                Write-Error "$($File.FullName) does not have an extension of '$Suffix'."
                Continue
            }

            $DestinationFile = $File.FullName -replace "$Suffix$"

            Try
            {
                $FileStreamReader = New-Object System.IO.FileStream($File.FullName, [System.IO.FileMode]::Open)
                $FileStreamWriter = New-Object System.IO.FileStream($DestinationFile, [System.IO.FileMode]::Create)

                #Get IV from file
                [Byte[]]$LenIV = New-Object Byte[] 4
                $FileStreamReader.Seek(0, [System.IO.SeekOrigin]::Begin) | Out-Null
                $FileStreamReader.Read($LenIV,  0, 3) | Out-Null
                [Int]$LIV = [System.BitConverter]::ToInt32($LenIV,  0)
                [Byte[]]$IV = New-Object Byte[] $LIV
                $FileStreamReader.Seek(4, [System.IO.SeekOrigin]::Begin) | Out-Null
                $FileStreamReader.Read($IV, 0, $LIV) | Out-Null
                $Crypto.IV = $IV

                #Peform Decryption
                $Transform = $Crypto.CreateDecryptor()
                $CryptoStream = New-Object System.Security.Cryptography.CryptoStream($FileStreamWriter, $Transform, [System.Security.Cryptography.CryptoStreamMode]::Write)
                $FileStreamReader.CopyTo($CryptoStream)

                #Close open files
                $CryptoStream.FlushFinalBlock()
                $CryptoStream.Close()
                $FileStreamReader.Close()
                $FileStreamWriter.Close()

                #Delete encrypted file
                if($RemoveSource){Remove-Item $File.FullName}

                #Output decrypted file
                Get-Item $DestinationFile | Add-Member –MemberType NoteProperty –Name SourceFile –Value $File.FullName -PassThru
            }
            Catch
            {
                Write-Error $_
                If($FileStreamWriter)
                {
                    #Remove failed file
                    $FileStreamWriter.Close()
                    Remove-Item -LiteralPath $DestinationFile -Force
                }
                Continue
            }
            Finally
            {
                if($CryptoStream){$CryptoStream.Close()}
                if($FileStreamReader){$FileStreamReader.Close()}
                if($FileStreamWriter){$FileStreamWriter.Close()}
            }
        }
    }
}

Export-ModuleMember -Function New-CryptographyKey
Export-ModuleMember -Function Protect-File
Export-ModuleMember -Function Unprotect-File

Tuesday, July 14, 2020

Oracle Connect as another user

CONNECT SYSTEM/&system_password@&target_database;
ALTER USER &local_schema GRANT CONNECT THROUGH SYSTEM;
DISCONNECT;

CONNECT SYSTEM[&local_schema]/&system_password@&target_database;

ALTER USER &local_schema REVOKE CONNECT THROUGH SYSTEM;

Friday, July 10, 2020

Wednesday, July 8, 2020

SORT_AREA_SIZE

ALTER SESSION SET workarea_size_policy=MANUAL;
ALTER SESSION SET sort_area_size=2147483647;

Monday, July 6, 2020

Oracle: Readers can block Writers (in distributed transactions)

https://docs.oracle.com/cd/E11882_01/server.112/e25494/ds_txns.htm#i1007836

Queries that start after a node has prepared cannot access the associated locked data until all phases complete.

A distributed transaction is ANY operation across a database link. It will result in a total table lock and access will be serialized. :(

This means a long running query for a data warehouse will lock the source tables for the duration of the transaction, until the client COMMIT's. It also means that the client will wait until it can obtain a table-level lock on the object. This behaviour is influenced by the system parameter DISTRIBUTED_LOCK_TIMEOUT.

It also implies that, SCN's are local, and foreign context is limited.

Summary of Locking Behavior

The database maintains several different types of locks, depending on the operation that acquired the lock. In general, the database uses two types of locks: exclusive locks and share locks. Only one exclusive lock can be obtained on a resource such as a row or a table, but many share locks can be obtained on a single resource.

Locks affect the interaction of readers and writers. A reader is a query of a resource, whereas a writer is a statement modifying a resource. The following rules summarize the locking behavior of Oracle Database for readers and writers:

  • A row is locked only when modified by a writer.

    When a statement updates one row, the transaction acquires a lock for this row only. By locking table data at the row level, the database minimizes contention for the same data. Under normal circumstances the database does not escalate a row lock to the block or table level.

  • A writer of a row blocks a concurrent writer of the same row.

    If one transaction is modifying a row, then a row lock prevents a different transaction from modifying the same row simultaneously.

  • A reader never blocks a writer.

    Because a reader of a row does not lock it, a writer can modify this row. The only exception is a SELECT ... FOR UPDATE statement, which is a special type of SELECT statement that does lock the row that it is reading.

  • A writer never blocks a reader.

    When a row is being changed by a writer, the database uses undo data data to provide readers with a consistent view of the row.

    Note:

    Readers of data may have to wait for writers of the same data blocks in very special cases of pending distributed transactions.