Error while working on Automation Grant Access to database views / tables in snowflake schema

Question

I have a stored procedure to grant access to the Snowflake database table / views, the procedure compiled successfully but getting the below error while executing:

Step1: What I am trying to do is select the records from the database table having the below column:

 "select PRIVILEGE, GRANTED_ON,NAME, GRANTED_TO,GRANTEE_NAME,TABLE_CATALOG, TABLE_SCHEMA, MODIFIED_ON from USERS_GRANTS_TO_ROLE_TBL" ;

Step2: Get the values into the vairables, form a Grant syntax like below:

`GRANT ` + return_privilege + ` ON ` + return_granted_on + ` ` + return_table_catelog + `.` + return_table_schema + `.` + return_name + ` TO ` + return_granted_to + ` ` + return_grantee_name ;

Step3: Insert the data into below table having columns as below:

"INSERT INTO TEST_GRANTS_TO_ROLE_SCRIPTS_TBL VALUES (" + return_table_catelog + "," + return_modified_on + "," + " " + sql_statement + ";" + " " + ")";

The below error encountered when I call the Stored Proc written using javascript language.

SQL Error [1003] [42000]: Execution error in store procedure SAMPLE_TEST_USER_GRANTS: SQL compilation error: syntax error line 1 at position 60 unexpected 'May'. syntax error line 1 at position 115 unexpected 'GRANT'. At Statement.execute, line 46 position 20

Appreciate, if anyone can help me out with this.

var VAR_SQL_STMT = "select PRIVILEGE, GRANTED_ON,NAME, 
                           GRANTED_TO,GRANTEE_NAME,TABLE_CATALOG, 
                           TABLE_SCHEMA, MODIFIED_ON 
                    from USERS_GRANTS_TO_ROLE_TBL" ; 
var stmt = snowflake.createStatement( { sqlText: VAR_SQL_STMT } );
/* Creates result set */ 
var res = stmt.execute(); 
while (res.next()) { 
   var return_privilege = res.getColumnValue(1); 
   var return_granted_on = res.getColumnValue(2); 
   var return_name = res.getColumnValue(3); 
   var return_granted_to = res.getColumnValue(4); 
   var return_grantee_name = res.getColumnValue(5); 
   var return_table_catelog = res.getColumnValue(6); 
   var return_table_schema = res.getColumnValue(7); 
   var return_modified_on = res.getColumnValue(8); 

   var sql_statement = GRANT  + return_privilege + ` ON ` + 
       return_granted_on + ` ` + return_table_catelog + . + 
       return_table_schema + . + return_name + ` TO ` + 
       return_granted_to + ` ` + return_grantee_name ; 
   var var_sql_text = "INSERT INTO TEST_GRANTS_TO_ROLE_SCRIPTS_TBL 
       VALUES (" + return_table_catelog + "," + return_modified_on + 
       "," + " " + sql_statement + ";" + " " + ")"; 
   var stmt1 = snowflake.createStatement( { sqlText: var_sql_text } ); 
   stmt1.execute();

Answer 1

In your definition of var sql_statement you are missing quotes round a number of strings e.g. GRANT and the periods. It should look something like this:

var sql_statement = 'GRANT ' + return_privilege + ' ON ' + 
       return_granted_on + ' ' + return_table_catelog + '.' + 
       return_table_schema + '.' + return_name + ' TO ' + 
       return_granted_to + ' ' + return_grantee_name ; 

Answer 2

There are multiple issues,The issue was Insert is failing for Date field coming in a incorrect format. Added the below to the insert dynamic script as below:

return_modified_on date column toISOString()
"INSERT INTO TEST_GRANTS_TO_ROLE_SCRIPTS_TBL VALUES" 
+ "(" + " " + "'" + return_table_catelog + "'"+ "," + "'"
+ return_modified_on.toISOString() + "'"+ "," + "'" 
+ sql_statement + "'"+ ")" +";";