## snowflake_permissioning_update.r

## This file updates permissions on tables, views etc in Snowflake
## Many of these tables/views are dropped every night as part of a sync process
## The main scripts are located in the file
##      ~/git/misc/rscripts/utils/DB_Utils/SF_Grant_Permissions.r

setScienceIfNot(proj="DeNormalizing")

sinkfile <- newLogFile(name="snowflake_permissioning_update")
sinkOn(filename=sinkfile, zarchive.old.files=TRUE)

setSnowflake(wh=getSnowflakeWH() %>% ifelse(.=="", getWH_already_on(default=c("CRON_JOBS_SMALL", "SCIENCE", "LOOKER_WH_LARGE")), .))

dbname <- "prod"

roles <- c("BI_Querier", "Snowflake_Querier")

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


for (role in roles)
  .sf_grant_permissions_on_schema(schema=schemas, dbname=dbname, role_to_permission=role, grant_db=TRUE, verbose=TRUE, wh=getWH_already_on())


catnn("", "\n", pasteR(100), "\t\tDone Running Script", pasteR(100))
sinkOff()