## For the script that acutally calls these on a scheduled basis, see 
##      /Users/rsaporta/git/orch/src/DeNormalizing/snowflake_permissioning_update.r


## EXAMPLE
if (FALSE) 
{

  setScienceIfNot("misc")
  setSnowflake(wh=getSnowflakeWH() %>% ifelse(.=="", getWH_already_on(default="looker_wh_large"), .))

  dbname <- "prod"

  ## Get all schemas for the following 'from_owner'
  from_owner <- c("SysAdmin", "BI_QUERIER")
  schemas <-  sfShowSchemas(dbname=dbname)[tolower(owner) %in% tolower(from_owner), name] %>% tolower

  role <- "BI_Querier"

  .sf_grant_permissions_on_schema(schema=schemas, dbname=dbname, role_to_permission=role, grant_db=TRUE, verbose=TRUE, wh=getWH_already_on())
  .sf_grant_permissions_on_schema(schema="bi", dbname=dbname, role_to_permission=role, grant_db=TRUE, verbose=TRUE, wh=getWH_already_on())
}


.sf_grant_permissions_on_dbname <- function(schema, dbname, role_to_permission, wh = getSnowflakeWH(), verbose=TRUE) {
    sfQry(sprintf("GRANT USAGE ON DATABASE %s TO ROLE %s;", dbname, role_to_permission), verbose=verbose)
    sfQry(sprintf("GRANT USAGE ON ALL SCHEMAS IN DATABASE %s TO ROLE %s;", dbname, role_to_permission), verbose=verbose)

  return(invisible(NULL))
}


.sf_grant_permissions_on_schema <- function(schema, dbname, role_to_permission, wh = getSnowflakeWH(), grant_db=FALSE, verbose=TRUE) {
  is.char_of_length1(dbname, fail.if.not=TRUE)
  is.char_of_length1(role_to_permission, fail.if.not=TRUE)
  if (!is.character(schema) || !length(schema))
    stop("schema must be a string with at least length 1")

  ## Optionally grant database permissions
  if (grant_db)
    .sf_grant_permissions_on_dbname(schema=schema, dbname=dbname, role_to_permission=role_to_permission, wh=wh, verbose=verbose)
  

  ## If there is more than one schema, iterate
  if (length(schema) > 1)
    return(lapply(schema, function(s) .sf_grant_permissions_on_schema(schema=s, dbname=dbname, role_to_permission=role_to_permission, wh=wh, grant_db=FALSE, verbose=verbose)))

  ds <- dbschematbl(dbname=dbname, schema=schema, tbl=NULL)

  ## Run the permissioning queries
  sfQry( sprintf("GRANT SELECT ON ALL TABLES        IN SCHEMA %s TO ROLE %s;", ds, role_to_permission),  verbose=verbose)
  sfQry( sprintf("GRANT SELECT ON ALL VIEWS         IN SCHEMA %s TO ROLE %s;", ds, role_to_permission),  verbose=verbose)
  sfQry( sprintf("GRANT  USAGE ON ALL STAGES        IN SCHEMA %s TO ROLE %s;", ds, role_to_permission),  verbose=verbose)
  sfQry( sprintf("GRANT  USAGE ON ALL FILE FORMATS  IN SCHEMA %s TO ROLE %s;", ds, role_to_permission),  verbose=verbose)
  sfQry( sprintf("GRANT  USAGE ON ALL SEQUENCES     IN SCHEMA %s TO ROLE %s;", ds, role_to_permission),  verbose=verbose)

  return(invisible(NULL))
}