Introduction
Key pairs are more secure because they use two keys (one public, one private), keep the private key secret, allow you to verify who sent a message, are easier to manage, and provide proof of sending messages.
1. Two Keys: A key pair uses two keys – a public key and a private key. The public key locks (encrypts) the data, and only the private key can unlock (decrypt) it. This means if someone gets the public key, they still can't read the data without the private key.
2. Private Key Stays Secret: The private key is kept secret and never shared. This means only you can unlock the locked data with your public key, making it very secure.
3. Verify Identity: Key pairs allow you to sign messages with your private key. The recipient uses your public key to verify the signature, ensuring the message is really from you and hasn't been tampered with.
4. Easier Management: Instead of sharing one key with everyone (like with a single password), each person has their own key pair. This makes it easier to manage and more secure because you don't have to share your private key with anyone.
5. Proof of Sending: When you sign a message with your private key, it proves you sent it. No one else could have signed it because no one else has your private key.
Creating a Key in Snowflake
Simply run the code below, paste the resulting private key into Astrato.
-- 1️⃣ Create the function which generates the private key
CREATE OR REPLACE FUNCTION generate_key_pair_udtf(passphrase varchar default null)
returns table(
encrypted_pem_private_key varchar(2000),
pem_private_key varchar(2000),
pem_public_key varchar(500),
passphrase varchar,
private_key varchar(2000),
public_key varchar(500) )
language python
runtime_version = '3.11'
packages = ('cryptography')
handler = 'GenerateKeyPair'
comment = 'Function to generate private and public keys for Snowflake authentication in multiple formats with and without a passphrase. If you do not provide a passphrase, a random 20 character string will be generated for you.'
as $$
from cryptography.hazmat.primitives import serialization as s
from cryptography.hazmat.primitives.asymmetric import rsa
import base64, secrets, string
pem = s.Encoding.PEM
der = s.Encoding.DER
pkcs8 = s.PrivateFormat.PKCS8
pub = s.PublicFormat.SubjectPublicKeyInfo
class GenerateKeyPair:
# Define main function which generates a Snowflake-compliant key pair
def process(self, passphrase):
rsa_key = rsa.generate_private_key(public_exponent=65537, key_size=2048)
if not passphrase:
passphrase = ''.join((secrets.choice(string.ascii_letters) for i in range(20)))
yield (
rsa_key.private_bytes(pem, pkcs8, s.BestAvailableEncryption(passphrase.encode('utf-8'))).decode('utf-8'),
rsa_key.private_bytes(pem, pkcs8, s.NoEncryption()).decode('utf-8'),
rsa_key.public_key().public_bytes(pem, pub).decode('utf-8'),
passphrase,
base64.b64encode(rsa_key.private_bytes(der, pkcs8, s.NoEncryption())).decode('utf-8'),
base64.b64encode(rsa_key.public_key().public_bytes(der, pub)).decode('utf-8')
)
$$;
-- 2️⃣ Create a temporary table, which includes the code you need to run
create or replace temporary table my_temp_key as
select T.*,
'ALTER USER "'||CURRENT_USER()||'" SET RSA_PUBLIC_KEY = '''||
public_key||''';' as ALTER_USER_PK1_STMT,
'ALTER USER "'||CURRENT_USER()||'" SET RSA_PUBLIC_KEY_2 = '''||
public_key||''';' as ALTER_USER_PK2_STMT
from table(generate_key_pair_udtf('Snowflake')) T;
-- 3️⃣ Run the code found in "ALTER_USER_PK1_STMT", "ALTER_USER_PK2_STMT"
select * from MY_TEMP_KEY;
-- 4️⃣ Paste the private key into Astrato, from the field "PEM_PRIVATE_KEY"
Creating a Key - Online method
The first step for creating the connection is to create a key pair.
We'll use open ssl in this example.
On the website select generate keys from the drop-down menu
Generate a private_key, and select RSA and 2048 as key length.
Give the key file a name and click run.
In the next step, generate a public key, select the private key file from the previous step as input, and click run.
Select files from the drop-down menu. And download the two files created.
Snowflake- Assign The Key To A User.
Open a worksheet in the snowflake instance.
Note
Only owners of a user, or users with the SECURITYADMIN role or higher can alter a user. For more information, see Overview of Access Control and GRANT OWNERSHIP
Exclude the public key delimiters in the SQL statement.
Open the public key file and copy the content.
(only the gray text )
In Snowflake, write this code.
ALTER USER DEMO_USER SET RSA_PUBLIC_KEY='MIIBI...';
Define a Data Connection in Astrato
Open a new Snowflake data connection in Astrato
Enter the Snowflake instance URL and the user name, and check the Use private key box.
Open the private key file and copy all the file content.
-----BEGIN PRIVATE KEY-----
MIIFN...
-----END PRIVATE KEY-----
Paste it in the password box.Click test connection.
Select the database for the data connection, give the connection a name, and create it.