Creating a Snowflake User

Learn about creating a Snowflake user.

To integrate Reltio Connected Data for Snowflake with your Snowflake database, you must have a valid Snowflake administrator account with specific privileges. Perform the following steps to create a user in your Snowflake account.

  1. Generate a Private Key to associate with the user. Then, extract a Public Key from the generated Private Key to assign to a user profile. You can perform this step using any standard tool such as openssl Console utility

    Example: The following commands create two files (private_key.pem and public_key.pub respectively) in a directory from which they are called:

    openssl genrsa 2048 | openssl pkcs8 -topk8 -out private_key.pem -nocrypt 
    openssl rsa -in private_key.pem -pubout -out public_key.pub
  2. Open the Public Key file and copy the key base64 string itself. Ensure to omit -----BEGIN PUBLIC KEY----- and -----END PUBLIC KEY-----, and remove all the new line symbols.
  3. Log in to the Snowflake Web UI application as an administrator, create a new worksheet, and run the commands given below to create a user, role, warehouse, database, and schema.
    Note: While creating a user, role, warehouse, database, and schema, consider the following points:
    • Provide the minimum database access to the warehouse, database, and schema.
    • Provide administrator access to the database if the database is used only by the Reltio platform.

      Create User, Role, Warehouse, Database, and Schema

      USE ROLE ACCOUNTADMIN;
       
      //Creates the Database, Data Warehouse, Database Schema, and Role.
      CREATE WAREHOUSE "<WAREHOUSE>";
      CREATE DATABASE "<DATABASE>";
      CREATE SCHEMA "<DATABASE>"."<SCHEMA>"; 
      CREATE ROLE "<RELTIO_ROLE>";
      
      //Provides the Database-level specific access OR Admin access to the Database, if it is used only by the Reltio platform.
      GRANT USAGE ON WAREHOUSE "<WAREHOUSE_NAME>" TO ROLE "<RELTIO_ROLE>";
      GRANT EXECUTE TASK ON ACCOUNT TO ROLE "<RELTIO_ROLE>";
      GRANT USAGE ON DATABASE "<DATABASE>" TO ROLE "<RELTIO_ROLE>";
      GRANT USAGE ON SCHEMA "<DATABASE>"."<SCHEMA>" TO ROLE "<RELTIO_ROLE>";
      GRANT CREATE TABLE ON SCHEMA "<DATABASE>"."<SCHEMA>" TO ROLE "<RELTIO_ROLE>";
      GRANT CREATE VIEW ON SCHEMA "<DATABASE>"."<SCHEMA>" TO ROLE "<RELTIO_ROLE>";
      GRANT CREATE FILE FORMAT ON SCHEMA "<DATABASE>"."<SCHEMA>" TO ROLE "<RELTIO_ROLE>";
      GRANT CREATE STAGE ON SCHEMA "<DATABASE>"."<SCHEMA>" TO ROLE "<RELTIO_ROLE>";
      GRANT CREATE PIPE ON SCHEMA "<DATABASE>"."<SCHEMA>" TO ROLE "<RELTIO_ROLE>";
      GRANT CREATE TASK ON SCHEMA "<DATABASE>"."<SCHEMA>" TO ROLE "<RELTIO_ROLE>";
      GRANT CREATE STREAM ON SCHEMA "<DATABASE>"."<SCHEMA>" TO ROLE "<RELTIO_ROLE>";
      GRANT CREATE FUNCTION ON SCHEMA "<DATABASE>"."<SCHEMA>" TO ROLE "<RELTIO_ROLE>";
      
      //Creates and grants access to storage integration
      GRANT CREATE INTEGRATION ON ACCOUNT TO ROLE "<RELTIO_ROLE>";
       
      //Creates optional storage integration. This is only for the users who do not want to use pre-configured storage integration.    
      create or replace storage integration "s3_int" type = external_stage enabled = true storage_allowed_locations = ('s3://my-bucket/mypath/') storage_provider = s3 storage_aws_role_arn = "arn:aws:iam::1234:role/myrole"
      
      //Provides access to storage integration, if storage integration is created by the user. You can skip the creating and granting access to storage integration step.
      GRANT USAGE ON INTEGRATION "<INTEGRATION_NAME>" TO ROLE "RELTIO_ROLE";
       
      //Creates a Snowflake user. 
      CREATE USER "<RELTIO_USER>" RSA_PUBLIC_KEY="<PUBLIC_KEY>" DEFAULT_ROLE = "<RELTIO_ROLE>";
      GRANT ROLE "<RELTIO_ROLE>" TO USER "<RELTIO_USER>";

      Example

      USE ROLE ACCOUNTADMIN;
      
      //Creates the Database, Data Warehouse, Database Schema, and Role. 
      CREATE DATABASE "RELTIO_DB";
      CREATE SCHEMA "RELTIO_DB"."RELTIO_SCHEMA"; 
      CREATE ROLE "RELTIO_INTEGRATION";
      CREATE ROLE "<RELTIO_ROLE>";
      
      //Provides the Database-level specific access OR Admin access to the Database, if it is used only by the Reltio platform.
      GRANT CREATE INTEGRATION ON ACCOUNT TO ROLE "RELTIO_INTEGRATION";
      GRANT USAGE ON WAREHOUSE "DEMO_WH" TO ROLE "RELTIO_INTEGRATION";
      GRANT USAGE ON DATABASE "RELTIO_DB" TO ROLE "RELTIO_INTEGRATION";
      GRANT USAGE ON SCHEMA "RELTIO_DB"."RELTIO_SCHEMA" TO ROLE "RELTIO_INTEGRATION";
      GRANT CREATE TABLE ON SCHEMA "RELTIO_DB"."RELTIO_SCHEMA" TO ROLE "RELTIO_INTEGRATION";
      GRANT CREATE VIEW ON SCHEMA "RELTIO_DB"."RELTIO_SCHEMA" TO ROLE "RELTIO_INTEGRATION";
      GRANT CREATE FILE FORMAT ON SCHEMA "RELTIO_DB"."RELTIO_SCHEMA" TO ROLE "RELTIO_INTEGRATION";
      GRANT CREATE STAGE ON SCHEMA "RELTIO_DB"."RELTIO_SCHEMA" TO ROLE "RELTIO_INTEGRATION";
      GRANT CREATE PIPE ON SCHEMA "RELTIO_DB"."RELTIO_SCHEMA" TO ROLE "RELTIO_INTEGRATION";
      GRANT CREATE TASK ON SCHEMA "RELTIO_DB"."RELTIO_SCHEMA" TO ROLE "RELTIO_INTEGRATION";
      GRANT CREATE STREAM ON SCHEMA "RELTIO_DB"."RELTIO_SCHEMA" TO ROLE "RELTIO_INTEGRATION";
      GRANT CREATE FUNCTION ON SCHEMA "RELTIO_DB"."RELTIO_SCHEMA" TO ROLE "RELTIO_INTEGRATION";
      GRANT EXECUTE TASK ON ACCOUNT TO ROLE "RELTIO_INTEGRATION";
      
      //Creates and grants access to storage integration
      GRANT CREATE INTEGRATION ON ACCOUNT TO ROLE "RELTIO_INTEGRATION";
       
      //Creates optional storage integration. This is only for the users who do not want to use pre-configured storage integration. 
      create or replace storage integration "{{integration_name}}" type = external_stage enabled = true storage_allowed_locations =('s3://my-bucket/mypath/') storage_provider = S3 storage_aws_role_arn = "arn:aws:iam::1234:role/myrole"
      
      //Provides access to storage integration, if storage integration is created by the user. You can skip the creating and granting access to storage integration step.
      GRANT USAGE ON INTEGRATION "<INTEGRATION_NAME>" TO ROLE "RELTIO_INTEGRATION";
      
      //Creates a Snowflake user. 
      CREATE USER "RELTIO" RSA_PUBLIC_KEY="**************" DEFAULT_ROLE = "RELTIO_INTEGRATION";
      GRANT ROLE "RELTIO_INTEGRATION" TO USER "RELTIO";
  4. You need the Username and Private Key to register the account in Reltio. Format the Private Key as follows to register properly. Go back to the previously created Private Key and open the key. Perform the same transformation as mentioned in step 2 for the Public Key. Copy only the base64 single line string as a Private Key.