Snowflake grants to role keeps resetting everyday

Question

I have created a new user 'u' using my 'security_admin' role, and assigned role 'r' (which already has some select/usage permission on few tables) to the user. Now I want add more permissions to the role. I have ran below statement:

grant select on all tables in schema db_name.schema_name to role r;

The above statement gives necessary permission to the user 'u', i have checked with show grants to role r statement. However the granted permission is valid only for one day, the next day the user does not have the permission that is granted using above statement. It keeps happening for sometime. I do not know what the problem is. I do not have any automated SQL script to recreate users & grants daily.

Answer 1

If you could share the actual error message you get, that would be helpful for us to understand where the problem lies. But I suspect that it's just a case of not having access on FUTURE TABLES (tables that has been created after the grants was applied).

These commands should cover you:

use role securityadmin;
grant usage on database db_name to role r;
grant usage on schema db_name.schema_name to role r;
grant select on all tables in schema db_name.schema_name to role r;
grant select on future tables in schema db_name.schema_name to role r;

Answer 2

One plausible scenario is that tables are recreated with CREATE OR REPLACE TABLE command. It could be checked using query history view.

SELECT *
FROM snowflake.account_usage.query_history
WHERE DATABASE_NAME = 'DATABASE_NAME_HERE'
  AND QUERY_TEXT ILIKE '%CREATE%OR%REPLACE%TABLE%'
ORDER BY START_TIME DESC;

---

In such case the permissions may not be preserved, unless specyfing COPY GRANTS option.

Optional parameters:

COPY GRANTS

Specifies to retain the access privileges from the original table when a new table is created using any of the following CREATE TABLE variants:

        CREATE OR REPLACE TABLE
        CREATE TABLE … LIKE
        CREATE TABLE … CLONE