CODEWITHSUNDEEP
stored-proceduressnowflake-cloud-data-platformsnowflake-schemasql-grant
Nikhil Suthar
Nikhil Suthar

Reputation: 2431

How to get Future Grants over Schema in Snowflake?

I have a use case where I need to check for FUTURE grants on an input database and schema. If FUTURE grants are found and not match with Input Role then I want to revoke them and then assign the permissions to a Input role.

I have written a procedure with ACCOUNTADMIN as the Owner. My concern is that I want to execute this procedure as the owner, not as the caller. If I use EXECUTE AS CALLER, I would need to higher grant permissions to custom Roles for all checks, which I want to avoid. However, SHOW commands are not supported in procedures with EXECUTE AS OWNER.

I have also tried using a parent procedure as the owner and a nested procedure as the caller, but this approach did not work.

How can I design the procedure to work with EXECUTE AS OWNER or there is any way to get FUTURE GRANT other than SHOW Query?

Below is Sample Procedure:

-- Parametes
--    Database Name 
--    Schema Name
--    Object Type such as TABLE, EXTERNAL TABLE, VIEWS etc.
--    Input Role Name


CREATE OR REPLACE PROCEDURE PARENT_SECURE_PROCEDURE("DATABASE_NAME" VARCHAR(16777216), "SCHEMA_NAME" VARCHAR(16777216),
"OBJECT_TYPE" VARCHAR(16777216), "ROLE_NAME" VARCHAR(16777216) )
RETURNS VARCHAR
LANGUAGE SQL
EXECUTE AS OWNER
$$
    DECLARE
        cc VARCHAR;
    BEGIN
        CALL GET_OR_REVOKE_FUTURE_GRANTS(:database_name, :schema_name, :object_type, :role_name);
        cc := (SELECT * FROM TABLE(RESULT_SCAN(LAST_QUERY_ID())));
        EXECUTE IMMEDIATE cc;
        RETURN cc;
        END; 
$$


CREATE OR REPLACE PROCEDURE GET_OR_REVOKE_FUTURE_GRANTS("DATABASE_NAME" VARCHAR(16777216), "SCHEMA_NAME" VARCHAR(16777216), obj_type VARCHAR, role_name VARCHAR)
RETURNS VARCHAR
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
    DECLARE
        future_obj OBJECT;
        sq VARCHAR;
        cc VARCHAR;
    BEGIN
        cc := '--';
        sq := 'SHOW FUTURE GRANTS IN SCHEMA ' || :database_name || '.' || :schema_name;
        EXECUTE IMMEDIATE (sq);
        future_obj := (SELECT OBJECT_AGG(REPLACE("grant_on" ||'S','_',' '),TO_VARIANT(UPPER("grantee_name")))  
            FROM TABLE(RESULT_SCAN(LAST_QUERY_ID())) 
            WHERE "privilege" = 'OWNERSHIP' AND "grant_to" = 'ROLE' GROUP BY "grant_to");

                IF (future_obj[obj_type] IS NULL) THEN                 
                    cc := '-- NO EXISTING FUTURE GRANT FOR OBJECT ' || obj_type || ' , TO THE SCHEMA  ' || schema_name;
                ELSEIF (future_obj[TRIM(UPPER(obj_type))] != role_name) THEN
                    cc := 'REVOKE OWNERSHIP ON FUTURE ' || UPPER(obj_type) || ' IN SCHEMA ' || database_name ||'.'|| schema_name || ' FROM ROLE ' || future_obj[TRIM(UPPER(obj_type))];
                ELSE
                    cc := ' -- SKIPPING, the FUTURE GRANT FOR OBJECT ' || obj_type || ' ,ALERDY ASSIGNED TO THE SCHEMA ROLE ' || role_name;
                END IF;
            RETURN cc;
        END; 
$$

Upvotes: 0

Views: 217

Answers (1)

sprethepa
sprethepa

Reputation: 772

FUTURE GRANTS can be retrieved via the SHOW command only and there is a limitation currently for SHOW command usage in the 'Owner's Rights' Stored Procedure.

https://docs.snowflake.com/en/developer-guide/stored-procedure/stored-procedures-rights#show-and-describe-commands

You can use Caller's rights Stored Procedure for using SHOW commands in the Stored Procedure.

Upvotes: 0

Related Questions