Create stored procedure that create users in snowflake

Question

I am trying to create a stored procedure in snowflake that create users in snowflake. But I am unable to bind variables.

Here is what I tried using snowflake scripting. I was not successful in creating a version that works


create or replace procedure create_user(user_name varchar, password_value varchar)
  returns varchar
  language sql
  as
  $$
    begin
        create user :USER_NAME password = :password_value;
        return 'Completed.';
    end;
  $$
  ;

In the above stored procedure, when I use :USER_NAME (I have tried upper and lower case). I get an error saying unexpected :. Password_value seems to work correctly.

I have also tried javascript version

CREATE OR REPLACE PROCEDURE security.create_user_v2(USER_NAME varchar, PASSWORD_VALUE varchar)
RETURNS BOOLEAN
LANGUAGE JAVASCRIPT
AS
$$
  var cmd = "create user :1 password = :2;";
  var stmt = snowflake.createStatement(
            {
            sqlText: cmd,
            binds: [USER_NAME, PASSWORD_VALUE]
            }
            );
  var result = stmt.execute();
  result.next();
  return true
$$
;

I get the same here as well, says unexpected :.

This version works, but this is concatenation, i want to avoid this (possible sql injection)

CREATE OR REPLACE PROCEDURE security.create_user_v2(USER_NAME varchar, PASSWORD_VALUE varchar)
RETURNS BOOLEAN
LANGUAGE JAVASCRIPT
AS
$$
  var cmd = "create user "+USER_NAME+" password = :1;";
  var stmt = snowflake.createStatement(
            {
            sqlText: cmd,
            binds: [PASSWORD_VALUE]
            }
            );
  var result = stmt.execute();
  result.next();
  return true
$$
;

How do I create a snowflake scripting version or javascript version that bind variables when creating users in stored procedure.

user_5070879 · Answer

User name must be enclosed with IDENTIFIER in order to use variable:

CREATE OR REPLACE PROCEDURE security.create_user_v2(USER_NAME varchar,
                                                    PASSWORD_VALUE varchar)
RETURNS BOOLEAN
LANGUAGE JAVASCRIPT
AS
$$
  var cmd = "create user IDENTIFIER(:1) password = :2;";
  var stmt = snowflake.createStatement(
            {
            sqlText: cmd,
            binds: [USER_NAME, PASSWORD_VALUE]
            }
            );
  var result = stmt.execute();
  result.next();
  return true
$$
;

Call:

CALL security.create_user_v2('user1', 'password123');
CALL security.create_user_v2('"user1@x.com"', 'password123');

SHOW USERS;

Snowflake Scripting:

create or replace procedure security.create_user(user_name varchar,
                                                 password_value varchar)
  returns varchar
  language sql
  as
  $$
    begin
        create user IDENTIFIER(:USER_NAME) password = :password_value;
        return 'Completed.';
    end;
  $$;

Create stored procedure that create users in snowflake

1 Answers