snowflake-cloud-data-platform - 雪花 - 清除用户 - 提醒角色所有者用户长时间不活动

Question

议程 - 通知角色所有者用户长时间处于非活动状态。

我正在创建一个程序，我需要捕获超过 90 天的非活动用户并捕获他的角色和角色所有者，并且需要编译并将列表发送给角色所有者，角色所有者将在稍后采取行动。

以下是我开发的程序

    CREATE OR REPLACE PROCEDURE "Notify_Owner_Inactive_Users"()
    RETURNS VARCHAR(16777216)
    LANGUAGE JAVASCRIPT
    EXECUTE AS CALLER
    AS '
    var users = "SHOW USERS;" ;
    var users_drtemp = snowflake.createStatement({ sqlText: users });
    users_drtemp.execute();
    
    
    var inactive_users = snowflake.createStatement(
      
      { sqlText: 
        `
        SELECT "login_name",DATEDIFF("Day","last_success_login",CURRENT_DATE) as not_login_since,owner FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()))
         where DATEDIFF("Day","last_success_login",CURRENT_DATE) > 90 or last_success_login is null 
         and "disabled"='false'
         and "snowflake_lock"='false'
         ;
    `
    }
    );
    var rs_inactive_users= inactive_users.execute();
    
    while (rs_inactive_users.next()) 
    
    {
    try{
           var user_name = rs_inactive_users.getColumnValue(1);
           var owner_name = rs_inactive_users.getColumnValue(3);
           
    var roles = "SHOW ROLES;" ;
    var users_Roles = snowflake.createStatement({ sqlText: roles });
    users_Roles.execute();
    
    var owner_email_id = snowflake.createStatement(
      
      { sqlText: 
        `
        
        select distinct split_part(b.data,':',2) as Owner_Email from (
        select a.value::string as data from TABLE(RESULT_SCAN(LAST_QUERY_ID())),
        lateral flatten(input=>split(comment,',')) a) b where b.data like '%Owner%';
        
         ;
    `
    }
    );
    var rs_owner_email= owner_email_id.execute();
    
    while (rs_owner_email.next()) 
    
    {
    try{
           var email_address = rs_owner_email.getColumnValue(1);
           
    catch(err)
          {
                 return("Job Failure");   
          }
         
    }
    
    return("Job Completed")
    ';

我需要获取用户列表并映射到角色所有者并向所有者发送电子邮件有人可以在其上进行开发吗

标签： snowflake-cloud-data-platform